Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Adding a NOT NULL column to a table that already has rows

ErrorERROR: column "zipcode" of relation "distributors" contains null values

Short 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.

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

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1ALTER TABLE ... ADD COLUMN ... NOT NULL on a non-empty tableNo default was given, so existing rows would be NULLFix
    2. 2Each 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. 3The 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

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

Did this fix it?

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

Did this fix it?

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 distributors

2Fix

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 distributors

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

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