Not Null Constraint Add Column Contains Null Values
ALTER TABLE ... ALTER COLUMN ... SET NOT NULL fails with SQLSTATE 23502 and the message "column ... of relation ... contains null values". The same...
In 10 seconds
A migration is supposed to lock down a column that should never have been nullable: accounts.region must become NOT NULL. It passes in dev and staging, then fails in production with ERROR: column "region" of relation "accounts" contains null values. While the column was nullable, rows slipped in with no region -- an old import, an API path that did not set it, a default that was never applied. ALTER TABLE ... ALTER COLUMN region SET NOT NULL scans every row to prove none is NULL, and the first NULL it meets aborts the whole ALTER. This is the third sibling of adding a constraint to a table that already violates it: the foreign key meets orphans, the unique index meets duplicates, and here the NOT NULL meets missing values -- same failure shape, a different cleanup (backfill the gaps).
Why This Happens
A NOT NULL constraint is a promise that every row has a value in the column. To add it, PostgreSQL must verify the promise against the current contents, so SET NOT NULL scans the whole column; the first row that is NULL violates the promise and the ALTER aborts with not_null_violation (SQLSTATE 23502). The constraint is never recorded.
The NULLs are there precisely because nothing required a value while those rows were written -- a nullable column plus an application or import path that omitted it. There are two faces to 23502 worth keeping straight: the ADD-constraint form here ("contains null values", raised while validating existing rows) and the run-time form ("null value in column ... violates not-null constraint", with a DETAIL naming the failing row) that the constraint raises afterward on a bad INSERT. The first tells you the table is dirty; the second proves the constraint is enforcing once it exists. The first tells you the table is dirty; the second proves the constraint is enforcing once it exists.
Diagnose
Free Tier -- Basic Approach
The error says NULLs exist but not how many. A single count of NULL rows sizes the backfill standing between you and the constraint.
SELECT count(*) AS null_region_rows FROM accounts WHERE region IS NULL;
null_region_rows
------------------
5
(1 row)
Count NULLs in the target column: null_region_rows = 5 here. On a real table the number tells you whether this is a five-row fix or a million-row backfill that needs batching.
Fix
Give every NULL row a value, then the SET NOT NULL that failed now succeeds because the scan finds no NULLs. The lab uses a placeholder; in production prefer a derived value.
UPDATE accounts SET region = 'unknown' WHERE region IS NULL; ALTER TABLE accounts ALTER COLUMN region SET NOT NULL;
UPDATE 5 ALTER TABLE
UPDATE accounts SET region = 'unknown' WHERE region IS NULL fills the 5 gaps (UPDATE 5). ALTER TABLE accounts ALTER COLUMN region SET NOT NULL now returns ALTER TABLE.
On a large table, do the CHECK ... NOT VALID -> VALIDATE -> SET NOT NULL dance instead of a bare scan.
Verify
Healthy state is three facts together: the column is flagged NOT NULL in the catalog, zero NULLs remain, and a NULL write is rejected -- this last error is the OTHER 23502 form (a failing INSERT), the run-time enforcement the constraint now guarantees.
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'accounts'::regclass AND attname = 'region';
attnotnull ------------ t (1 row)
SELECT count(*) AS null_region_rows FROM accounts WHERE region IS NULL;
null_region_rows
------------------
0
(1 row)
INSERT INTO accounts(owner, region) VALUES ('newbie', NULL);
ERROR: null value in column "region" of relation "accounts" violates not-null constraint DETAIL: Failing row contains (1001, newbie, null).
pg_attribute reports attnotnull = t for region. The NULL-row count is 0.
A NULL INSERT ('newbie', NULL) is rejected with null value in column "region" of relation "accounts" violates not-null constraint and DETAIL Failing row contains (1001, newbie, null) -- proof the constraint enforces new writes.
⏪ Rollback
Nothing to undo from the failed SET NOT NULL -- the constraint is never added and the table is unchanged. The remediation changes data: the UPDATE gives every NULL row a value. Choose that value deliberately. A literal placeholder ('unknown') is fine when the value is genuinely unknown and the application can treat it as such, but often the right fill is derived (copy from a related column, look up a default per account, or use a business rule). If a NULL row should not exist at all, deleting it may be more correct than inventing a value. Run the audit first so you see exactly which rows are affected and what real values look like before you backfill. Backfilling does not lose data; it only fills gaps. If you decide not to enforce NOT NULL after all, simply do not run the SET NOT NULL.
📦 Version Matrix (PG 14--18)
Same mechanism on PostgreSQL 14, 15, 16, 17 and 18: with NULLs present SET NOT NULL fails with "contains null values"; after the NULLs are backfilled the identical SET NOT NULL succeeds (ALTER TABLE).
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=contains null values | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=contains null values | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=contains null values | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=contains null values | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=contains null values | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
The error did not name a row. How do I find what is NULL?
Count and list them: SELECT count(*) FROM accounts WHERE region IS NULL gives the size of the problem, and SELECT id, owner, region FROM accounts WHERE region IS NULL lists the exact rows to fix. The SET NOT NULL message only reports that NULLs exist, not which rows.
What value should I backfill?
Whatever makes the row correct. Prefer a derived value (from a related column, a lookup, or a business default) over a literal placeholder. Use the premium distribution query to see what real values look like before choosing. A placeholder like 'unknown' is acceptable only when 'not known' is a meaningful, app-understood state -- otherwise it pollutes downstream queries.
Is SET NOT NULL safe on a big table?
The change is safe but the bare form scans the whole column under a strong lock. To avoid a long blocking scan, add CHECK (col IS NOT NULL) NOT VALID first (instant), VALIDATE CONSTRAINT (scans under a weaker SHARE UPDATE EXCLUSIVE lock that does not block normal traffic), then SET NOT NULL -- on PostgreSQL 12 and later the validated CHECK lets SET NOT NULL skip re-scanning. You can drop the helper CHECK afterward.
Why are there two different 'not-null' errors?
One is raised while ADDING the constraint to existing data ('column ... contains null values') and one is raised at run time when a write would violate the existing constraint ('null value in column ... violates not-null constraint', with the failing row in the DETAIL). The first is a data-cleanup problem; the second is a bad statement to fix. Seeing the second after the fix is good -- it proves the constraint is enforcing.
How do I keep NULLs from coming back?
Keep the NOT NULL in place so the database rejects NULL writes, fix the application/import path that produced the NULLs, and add a standing guard: SELECT count(*) FROM accounts WHERE region IS NULL, which must always be 0. Declaring the column NOT NULL (often with a DEFAULT) at table-creation time prevents the gap from ever opening.