add-domain-constraint-without-not-valid
errorALTER DOMAIN ADD CONSTRAINT without NOT VALID
Why it's unsafe
ALTER DOMAIN … ADD CONSTRAINT without NOT VALID validates the new CHECK against every existing value of the domain type across all dependent tables, scanning and locking them.
Safe rewrite
Add the constraint with NOT VALID, then run ALTER DOMAIN … VALIDATE CONSTRAINT separately.
Example
Try it in the playground →Unsafe
ALTER DOMAIN us_postal ADD CONSTRAINT fmt CHECK (VALUE ~ '^[0-9]{5}$');Safe
ALTER DOMAIN us_postal ADD CONSTRAINT fmt CHECK (VALUE ~ '^[0-9]{5}$') NOT VALID;
ALTER DOMAIN us_postal VALIDATE CONSTRAINT fmt;