pgsafe_

← all rules

add-column-not-null-no-default

error

ADD COLUMN NOT NULL without a default

Why it's unsafe

ADD COLUMN ... NOT NULL with no DEFAULT fails immediately on any non-empty table — it cannot fill existing rows.

Safe rewrite

Add the column nullable, backfill in batches, then enforce NOT NULL via CHECK (col IS NOT NULL) NOT VALID + VALIDATE CONSTRAINT, then SET NOT NULL (PG12+ reuses the validated check and skips the scan).

Example

Try it in the playground →

Unsafe

ALTER TABLE users ADD COLUMN status text NOT NULL;

Safe

ALTER TABLE users ADD COLUMN status text;
-- backfill in batches, then add NOT NULL via the safe two-step

Related