100 lines
3.3 KiB
SQL
100 lines
3.3 KiB
SQL
ALTER TABLE app.candidate_experience
|
|
DROP CONSTRAINT IF EXISTS ai_data_requires_document,
|
|
DROP CONSTRAINT IF EXISTS candidate_experience_source_document_id_fkey,
|
|
DROP CONSTRAINT IF EXISTS candidate_experience_candidate_id_key;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'employer'
|
|
) AND EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'employer_name'
|
|
) THEN
|
|
UPDATE app.candidate_experience
|
|
SET employer_name = employer
|
|
WHERE employer_name IS NULL
|
|
AND employer IS NOT NULL;
|
|
|
|
ALTER TABLE app.candidate_experience DROP COLUMN employer;
|
|
ELSIF EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'employer'
|
|
) AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'employer_name'
|
|
) THEN
|
|
ALTER TABLE app.candidate_experience RENAME COLUMN employer TO employer_name;
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'position'
|
|
) AND EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'role_title'
|
|
) THEN
|
|
UPDATE app.candidate_experience
|
|
SET role_title = "position"
|
|
WHERE role_title IS NULL
|
|
AND "position" IS NOT NULL;
|
|
|
|
ALTER TABLE app.candidate_experience DROP COLUMN "position";
|
|
ELSIF EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'position'
|
|
) AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'app'
|
|
AND table_name = 'candidate_experience'
|
|
AND column_name = 'role_title'
|
|
) THEN
|
|
ALTER TABLE app.candidate_experience RENAME COLUMN "position" TO role_title;
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
ALTER TABLE app.candidate_experience
|
|
ADD COLUMN IF NOT EXISTS country VARCHAR,
|
|
ADD COLUMN IF NOT EXISTS city VARCHAR,
|
|
ADD COLUMN IF NOT EXISTS facility_type VARCHAR;
|
|
|
|
ALTER TABLE app.candidate_experience
|
|
DROP COLUMN IF EXISTS source_document_id,
|
|
DROP COLUMN IF EXISTS duration_months,
|
|
DROP COLUMN IF EXISTS is_full_time,
|
|
DROP COLUMN IF EXISTS duties,
|
|
DROP COLUMN IF EXISTS ai_employer,
|
|
DROP COLUMN IF EXISTS ai_position,
|
|
DROP COLUMN IF EXISTS ai_department,
|
|
DROP COLUMN IF EXISTS ai_start_date,
|
|
DROP COLUMN IF EXISTS ai_end_date,
|
|
DROP COLUMN IF EXISTS ai_duration_months,
|
|
DROP COLUMN IF EXISTS ai_is_full_time,
|
|
DROP COLUMN IF EXISTS ai_duties,
|
|
DROP COLUMN IF EXISTS ai_extracted_by,
|
|
DROP COLUMN IF EXISTS ai_extraction_date;
|