PostgreSQL 23502: null value violates not-null constraint
An INSERT or UPDATE left a NOT NULL column empty, often because the column was omitted and has no default.
- SQLSTATE
23502- Name
not_null_violation- Category
- Integrity Constraint Violation
What it means
A NOT NULL column must have a value in every row. 23502 means the new or updated row had NULL there, either explicitly or because the column was left out of an INSERT and its default is NULL.
The DETAIL line shows the whole failing row. PostgreSQL 13 and later name the table in the message; older versions say only null value in column "email" violates not-null constraint.
Common causes
- The
INSERTdoesn't list the column and the column has noDEFAULT. - The application sent
NULL, for example an unset field or a failed lookup. INSERT ... SELECTor anUPDATEwith a subquery that returned no row, producingNULL.- A column added with
NOT NULLthat existing code doesn't know about.
How to fix it
-
Supply the value, or give the column a default.
INSERT INTO users (id, name, email) VALUES (7, 'Ada', '[email protected]'); ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
-
Use
COALESCEwhen a lookup or expression can returnNULL.UPDATE orders o SET region = COALESCE((SELECT region FROM customers c WHERE c.id = o.customer_id), 'unknown');
-
If the column should allow missing values, drop the constraint.
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;
Reproduce it safely
CREATE TEMP TABLE u (id int, email text NOT NULL); INSERT INTO u (id) VALUES (1); -- ERROR: null value in column "email" of relation "u" violates not-null constraint
Related errors
23505duplicate key violates unique constraint23503foreign key constraint violation23514check constraint violation515SQL Server: Cannot insert the value NULL into column1048MySQL: Column cannot be null1364MySQL: Field doesn't have a default valueORA-01400Oracle: cannot insert NULL into columnSQLITE_CONSTRAINT_NOTNULLSQLite: NOT NULL constraint failed
More integrity constraint violation errors
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.