24 lines
775 B
PL/PgSQL
24 lines
775 B
PL/PgSQL
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS committee_member (
|
|
committee_id BIGINT NOT NULL REFERENCES committee(id) ON DELETE CASCADE,
|
|
user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by BIGINT NULL REFERENCES app_user(id) ON DELETE SET NULL,
|
|
PRIMARY KEY (committee_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_committee_member_user_id
|
|
ON committee_member (user_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_committee_member_committee_id
|
|
ON committee_member (committee_id);
|
|
|
|
INSERT INTO committee_member (committee_id, user_id, created_at, created_by)
|
|
SELECT u.committee_id, u.id, NOW(), NULL
|
|
FROM app_user u
|
|
WHERE u.committee_id IS NOT NULL
|
|
ON CONFLICT (committee_id, user_id) DO NOTHING;
|
|
|
|
COMMIT;
|