Alter check constraint on Postgres domain

user_312962 0
Viewed 1493

Given an existing Postgres DOMAIN with CHECK constraint:

CREATE DOMAIN code as CHAR(1) 
CHECK (value IN ('A', 'B', 'C'));

I need to alter the CHECK constraint to add D as a member to the valid set A, B and C.

Since the new constraint is a superset of the first it seemed acceptable to first DROP the existing constraint and then ADD it again; i.e:

ALTER DOMAIN code 
DROP CONSTRAINT code_check; -- constraint is implicitly named `code_check`

ALTER DOMAIN code 
ADD CONSTRAINT code_check 
CHECK (value IN ('A', 'B', 'C', 'D')); -- new constraint

Are there possible issues with this approach, or is there a better method?

postgresql
1 Answers

Theoretically, it is possible that a concurrent session will insert a row that violates the constraint at the moment the domain has no constraint. Reversing the order of commands will prevent this possibility. Since the new constraint is a superset of the old one you can suppress checking values in existing columns using the domain:

ALTER DOMAIN code 
ADD CONSTRAINT code_check_new 
CHECK (value IN ('A', 'B', 'C', 'D')) NOT VALID;

ALTER DOMAIN code 
DROP CONSTRAINT code_check;

-- if for some reason the constraint name is important:
ALTER DOMAIN code 
RENAME CONSTRAINT code_check_new TO code_check;

Per the documentation:

Newly inserted or updated rows are always checked against all constraints, even those marked NOT VALID.

user_19957380
Related