Why ADD COLUMN NOT NULL fails on a full table, and the migration that does not

This one works on your laptop and fails in production, which is the worst kind of migration there is.

ALTER TABLE orders ADD COLUMN status text NOT NULL;
-- ERROR:  column "status" of relation "orders" contains null values

On a dev database the table is empty, so there are no rows to violate the constraint and the statement succeeds. On the real table every existing row would need a value for the new column, none is given, and Postgres refuses. The migration stops half way through the deploy, the framework marks it as failed, and someone gets paged to work out whether the earlier statements in the same file were applied or rolled back.

What the server is actually doing

ADD COLUMN takes an ACCESS EXCLUSIVE lock on the table. That is the strongest lock Postgres has: it blocks every read and every write until the statement finishes. For a plain nullable column that is fine, because adding a nullable column with no default is a catalog change. No row is touched, the lock is held for a few milliseconds, and nobody notices.

NOT NULL changes that. A NOT NULL column has to hold a value in every row, so Postgres has to know what the value is. With a default it can use the default. Without one there is nothing to use, and the only honest answer is the error above. The check is not a scan in the usual sense, it is Postgres declining to invent data.

What changed in Postgres 11

Before Postgres 11, adding a column with a default rewrote the whole table to write the default into every row, under that same exclusive lock. On a large table that was minutes of downtime, so the folk wisdom became "never add a column with a default". Postgres 11 fixed it: a constant default is now stored in the catalog and returned on read for rows that predate the column, so ADD COLUMN status text NOT NULL DEFAULT 'open' is instant on any table size.

The word that matters is constant. DEFAULT now() or DEFAULT gen_random_uuid() is volatile, every row needs its own value, and Postgres still rewrites the table. Same syntax, completely different cost, and the only way to tell them apart is to know the rule.

The migration that works

When there is a sensible constant default, use it and you are done, on any modern Postgres. When there is not, which is the normal case for a column that must be backfilled from other data, do it in three steps.

-- 1. add the column nullable; a catalog change, milliseconds
ALTER TABLE orders ADD COLUMN status text;

-- 2. backfill in batches, outside the migration transaction
UPDATE orders SET status = 'open'
 WHERE status IS NULL AND id BETWEEN 1 AND 50000;
-- ... repeat by id range until no rows remain

-- 3. add the constraint without a full-table scan under lock
ALTER TABLE orders ADD CONSTRAINT orders_status_not_null
  CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_not_null;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_not_null;

Step three looks like ceremony, and it is there for a reason. A bare SET NOT NULL scans the whole table to prove no null exists, and it does that scan while holding ACCESS EXCLUSIVE. On a big table that is the outage everyone was trying to avoid in step one. A CHECK ... NOT VALID constraint is added instantly, VALIDATE CONSTRAINT does its scan under a much weaker lock that lets reads and writes continue, and from Postgres 12 onward SET NOT NULL notices the validated check constraint and skips its own scan. Then the helper constraint can go.

The two rules that catch it

The first statement in this post is BV002, and the bare SET NOT NULL is BV011. Both are in the free CLI, which is worth knowing because both are exactly the kind of mistake that looks fine in review. The SQL is syntactically perfect. It is the table it will run against that makes it wrong, and a reviewer can not see the table from the diff.

If you use SQLite instead, the rule is stricter and the failure is earlier: SQLite refuses a NOT NULL column without a non-null default outright, in every enviroment including the empty one. That is SL001.