← all rules

ADD CHECK without NOT VALID

Why it's unsafe

Adding a CHECK constraint without NOT VALID scans the whole table under an ACCESS EXCLUSIVE lock.

Safe rewrite

Add the CHECK with NOT VALID, then run VALIDATE CONSTRAINT separately (SHARE UPDATE EXCLUSIVE — concurrent reads and writes are allowed).

Unsafe

ALTER TABLE orders ADD CONSTRAINT chk_total CHECK (total > 0);

Safe

ALTER TABLE orders ADD CONSTRAINT chk_total CHECK (total > 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_total;

Related

• add-fk-without-not-valid — Adding a foreign key without NOT VALID scans and locks both tables.
• set-not-null — ALTER COLUMN ... SET NOT NULL scans the entire table under an ACCESS EXCLUSIVE lock.