Adding NOT NULL and a default to a column without a long table lock
ERROR: column "status" of relation "orders" contains null valuesShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
After ALTER COLUMN ... SET DEFAULT the old rows are still NULL.A default was set, but it does not apply to old rowsFix - 2
You are adding a column and plan to backfill it afterwards.The column is new, so the update is not needed at allFix - 3
The 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
- 1
What does the error look like?
ERROR: column "status" of relation "orders" contains null valuesALTER 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 remain3Verify
No NULLs remain.
SELECT count(*) FROM orders WHERE status IS NULL;Confirmed by postgresql.org
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
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
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
Databases
Adding a NOT NULL column to a table that already has rows
ERROR: column "zipcode" of relation "distributors" contains null values
ADD COLUMN ..
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix