Not Null Constraint Add Column Contains Null Values

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.

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.

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.

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.

📦 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).

Frequently Asked Questions

References

• ALTER TABLE -- SET NOT NULL
• Constraints -- Not-Null Constraints
• PostgreSQL Error Codes (Class 23 -- Integrity Constraint Violation, 23502 not_null_violation)
• Populating a Database / DDL change strategies (CHECK ... NOT VALID then SET NOT NULL)