28 lines
531 B
PL/PgSQL
28 lines
531 B
PL/PgSQL
BEGIN;
|
|
|
|
ALTER TABLE roles
|
|
ADD COLUMN IF NOT EXISTS role_code TEXT;
|
|
|
|
UPDATE roles
|
|
SET role_code = name
|
|
WHERE role_code IS NULL OR btrim(role_code) = '';
|
|
|
|
ALTER TABLE roles
|
|
ALTER COLUMN role_code SET NOT NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM pg_constraint
|
|
WHERE conname = 'uq_roles_system_role_code'
|
|
) THEN
|
|
ALTER TABLE roles
|
|
ADD CONSTRAINT uq_roles_system_role_code UNIQUE (system_id, role_code);
|
|
END IF;
|
|
END$$;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_roles_role_code ON roles(role_code);
|
|
|
|
COMMIT;
|