add-domain-not-null
errorALTER DOMAIN ADD/SET NOT NULL
Why it's unsafe
ALTER DOMAIN … SET NOT NULL (and the PG17+ ADD [CONSTRAINT] NOT NULL) checks that no existing value of the domain type is null across every dependent table, scanning them and holding a lock that blocks writes for the scan's duration. Unlike a CHECK, a domain NOT NULL cannot be added NOT VALID.
Safe rewrite
Add it while the domain has few or no dependent rows, or drop the domain-level NOT NULL and set NOT NULL on each dependent column via the safe two-step (CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, then SET NOT NULL).
Example
Try it in the playground →Unsafe
ALTER DOMAIN us_postal SET NOT NULL;