Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Adding NOT NULL and a default to a column without a long table lock

ErrorERROR: column "status" of relation "orders" contains null values

Short answer

SET DEFAULT never changes rows already in the table, so setting a default does not remove the NULLs. A new column with a non-volatile default is fast and needs no rewrite. For an existing column with NULLs, fill them in batches, add a NOT VALID check, validate it, then SET NOT NULL, which skips its scan.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Count the NULLs. Zero means SET NOT NULL can go ahead; anything else must be filled first.

    SELECT count(*) FILTER (WHERE status IS NULL) AS nulls, count(*) AS total FROM orders;
  2. 2Match what you see, then jump to its fix

    1. 1After ALTER COLUMN ... SET DEFAULT the old rows are still NULL.A default was set, but it does not apply to old rowsFix
    2. 2You are adding a column and plan to backfill it afterwards.The column is new, so the update is not needed at allFix
    3. 3The data is already clean but the statement is slow on a large table.SET NOT NULL scans the whole table while it holds a lockFix

What does the error look like?

  • ERROR: column "status" of relation "orders" contains null values
  • ALTER TABLE ... SET NOT NULL runs for minutes and blocks writes

Cause 1 of 3

A default was set, but it does not apply to old rows

1Check

The page for ALTER TABLE says a new default only applies to later INSERT or UPDATE commands. Existing rows are untouched.

SELECT count(*) FROM orders WHERE status IS NULL;

2Fix

Update the old rows. On a big table do it in batches so one transaction does not hold locks for long.

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'new';
UPDATE orders SET status = 'new'
WHERE status IS NULL AND id BETWEEN 1 AND 10000;
-- repeat with the next id range until no NULLs remain

3Verify

No NULLs remain.

SELECT count(*) FROM orders WHERE status IS NULL;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The column is new, so the update is not needed at all

1Check

With ADD COLUMN and a non-volatile DEFAULT, the value is stored in the table's metadata and returned for existing rows, with no table rewrite. A volatile default such as clock_timestamp() does rewrite the table.

SELECT column_name, column_default FROM information_schema.columns WHERE table_name = 'orders';

2Fix

Add the column with its default and NOT NULL in one statement.

ALTER TABLE orders ADD COLUMN status text NOT NULL DEFAULT 'new';

3Verify

Old rows already read the default.

SELECT status, count(*) FROM orders GROUP BY status;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

SET NOT NULL scans the whole table while it holds a lock

1Check

SET NOT NULL normally scans the entire table to prove there are no NULLs. The scan is skipped when a valid CHECK constraint already proves it.

SELECT conname, convalidated FROM pg_constraint WHERE conrelid = 'orders'::regclass AND contype = 'c';

2Fix

Add the check as NOT VALID (no scan), validate it (a scan with a lighter lock), then SET NOT NULL, which now skips the scan. Drop the helper check afterwards.

ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;
ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

3Verify

The column now shows as not null.

SELECT is_nullable FROM information_schema.columns WHERE table_name = 'orders' AND column_name = 'status';

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Add new columns with their default and NOT NULL together while the table is still cheap to change.
  • Run migrations on a copy of production-sized data to see lock time before the real run.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.