pgsafe_

← all rules

set-not-null

error

SET NOT NULL

Why it's unsafe

ALTER COLUMN ... SET NOT NULL scans the entire table under an ACCESS EXCLUSIVE lock.

Safe rewrite

On PG12+, first add CHECK (col IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, then SET NOT NULL (it reuses the validated check and skips the scan). Drop the helper CHECK afterward if you like.

Example

Try it in the playground →

Unsafe

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Safe

ALTER TABLE users ADD CONSTRAINT users_email_nn CHECK (email IS NOT NULL) NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_nn;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

Related