21 lines
839 B
SQL
21 lines
839 B
SQL
-- 035_job_post_recruiter_ids.sql
|
|
-- A job post can have more than one recruiter. current_recruiter_id stays the
|
|
-- first / primary pointer so existing joins and filters keep working;
|
|
-- current_recruiter_ids is the full JSONB list used by create / update / get.
|
|
-- Applied at startup by alembic_setup.run_manual_sql(). Needed because prod
|
|
-- boots with DB_AUTOGENERATE=false.
|
|
|
|
ALTER TABLE app.job_posts
|
|
ADD COLUMN IF NOT EXISTS current_recruiter_ids JSONB NOT NULL DEFAULT '[]'::jsonb;
|
|
|
|
UPDATE app.job_posts
|
|
SET current_recruiter_ids = jsonb_build_array(current_recruiter_id::text)
|
|
WHERE current_recruiter_id IS NOT NULL
|
|
AND (
|
|
current_recruiter_ids IS NULL
|
|
OR current_recruiter_ids = '[]'::jsonb
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_job_posts_current_recruiter_ids
|
|
ON app.job_posts USING GIN (current_recruiter_ids);
|