Adding a NOT NULL column to a table that already has rows
ERROR: column "zipcode" of relation "distributors" contains null valuesShort answer
ADD COLUMN ... NOT NULL fails on a table with rows because existing rows get NULL. Add the column with a constant DEFAULT, which is stored in metadata and does not rewrite the table, then drop the default if you want. Or add it nullable, backfill, then SET NOT NULL.
Fix it fast
1Find out which cause you have
Check whether the table has rows. An empty table accepts the plain command; a table with rows needs one of the routes below.
SELECT count(*) FROM distributors;2Match what you see, then jump to its fix
- 1
ALTER TABLE ... ADD COLUMN ... NOT NULL on a non-empty tableNo default was given, so existing rows would be NULLFix - 2
Each existing row needs a value computed from other data, or the default is volatile such as clock_timestamp()The right value for each row is not a constantFix - 3
The backfill is done but the final SET NOT NULL takes too long on a busy tableSET NOT NULL scans a very large table while it holds a lockFix
- 1
What does the error look like?
ERROR: column "zipcode" of relation "distributors" contains null values
Cause 1 of 3
No default was given, so existing rows would be NULL
1Check
The manual says that with no column constraints the default is NULL, so every existing row would get NULL and break the NOT NULL rule.
ALTER TABLE distributors ADD COLUMN zipcode varchar(5) NOT NULL;2Fix
Give the column a constant default. A non-volatile default is evaluated once and kept in table metadata, so the statement is fast even on large tables. Drop the default afterwards if new rows must supply a value themselves.
ALTER TABLE distributors ADD COLUMN zipcode varchar(5) NOT NULL DEFAULT '00000';
ALTER TABLE distributors ALTER COLUMN zipcode DROP DEFAULT;3Verify
Every row has a value and the column is NOT NULL.
SELECT count(*) FROM distributors WHERE zipcode IS NULL;Confirmed by postgresql.org
Cause 2 of 3
The right value for each row is not a constant
1Check
A volatile default, an identity column or a stored generated column rewrites the whole table and its indexes,.
SELECT pg_size_pretty(pg_total_relation_size('distributors'));2Fix
Add the column as nullable, fill it with an UPDATE (in batches on big tables), then switch NOT NULL on.
ALTER TABLE distributors ADD COLUMN zipcode varchar(5);
UPDATE distributors SET zipcode = '00000' WHERE zipcode IS NULL;
ALTER TABLE distributors ALTER COLUMN zipcode SET NOT NULL;3Verify
SET NOT NULL succeeds only when no NULLs remain, so the last statement is the proof.
SELECT count(*) FROM distributors WHERE zipcode IS NULL;Confirmed by postgresql.org
Cause 3 of 3
SET NOT NULL scans a very large table while it holds a lock
1Check
SET NOT NULL normally checks by scanning the whole table. If a valid CHECK constraint already proves no NULL can exist, the scan is skipped.
\d distributors2Fix
Add a CHECK constraint as NOT VALID, validate it, then set NOT NULL and drop the CHECK. Validation takes a weaker lock that lets reads and writes continue.
ALTER TABLE distributors
ADD CONSTRAINT zipcode_nn CHECK (zipcode IS NOT NULL) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT zipcode_nn;
ALTER TABLE distributors ALTER COLUMN zipcode SET NOT NULL;
ALTER TABLE distributors DROP CONSTRAINT zipcode_nn;3Verify
The column now reports NOT NULL.
\d distributorsConfirmed by postgresql.org
How do I stop it coming back?
- For new required columns on live tables, ship the constant-default form first, then tighten later if needed.
- Backfill in batches of a few thousand rows so one UPDATE does not hold locks for long.
- Test the migration on a copy of production-sized data to see whether it rewrites the table.
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
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 fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix