PostgreSQL: 'null value in column violates not-null constraint' (23502)

ERROR: null value in column "email" of relation "patients" violates not-null constraint (SQLSTATE 23502) means an INSERT or UPDATE tried to store NULL in a column declared NOT NULL. Usually the column was simply left out of the INSERT and has no default.

Supply a value for the named column, or give the column a DEFAULT so an omitted value is filled in. The DETAIL line shows the whole failing row, which tells you exactly which values arrived.

The error

INSERT INTO patients (full_name) VALUES ('Maria Garcia');
psql:/tmp/p2.sql:9: ERROR:  null value in column "email" of relation "patients" violates not-null constraint
DETAIL:  Failing row contains (1, Maria Garcia, null, null).

Why it happens

A column you do not list in an INSERT gets its default, and a column without a DEFAULT clause defaults to NULL. The patients table declares email text NOT NULL with no default, so leaving it out produces a null and the constraint rejects the whole row. Passing NULL explicitly gives the identical message, so the error does not tell you which of the two happened; the DETAIL line does help, because it lists the row in column order: (1, Maria Garcia, null, null) is id, full_name, email and phone.

Two things in that row surprise people. The identity value 1 was already taken from the sequence, so the next successful insert got a higher id. And phone is also null, which is fine because phone allows nulls; the message names only the column that broke the rule.

The fix

INSERT INTO patients (full_name, email) VALUES ('Maria Garcia', 'maria.garcia@example.test');
INSERT 0 1

If a value can legitimately be unknown at insert time, a default keeps the NOT NULL guarantee and stops inserts from failing:

ALTER TABLE patients ALTER COLUMN email SET DEFAULT 'unknown@example.test';
INSERT INTO patients (full_name) VALUES ('David Chen');
INSERT 0 1
SELECT id, full_name, email FROM patients ORDER BY id;
 id |  full_name   |           email
----+--------------+---------------------------
  3 | Maria Garcia | maria.garcia@example.test
  4 | David Chen   | unknown@example.test
(2 rows)

Note the ids 3 and 4: the two failed inserts consumed 1 and 2. A default only helps when the column is omitted; an explicit NULL in the VALUES list still fails. Dropping the constraint is the last resort, not the fix. From .NET, Npgsql raises a PostgresException with SqlState 23502.

How it was reproduced

A postgres:18 container (PostgreSQL 18.4, Debian build 18.4-1.pgdg13+1) on Docker 29.9.2. The table was patients (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, full_name text NOT NULL, email text NOT NULL, phone text). One insert left email out and a second passed NULL explicitly; both failed with the same message. The script ran with psql -a -f inside the container, and a run with \set VERBOSITY verbose printed the code as ERROR: 23502 together with a COLUMN NAME: email field.

Frequently asked

What does 'null value in column violates not-null constraint' mean in PostgreSQL?

An INSERT or UPDATE tried to store NULL in a column declared NOT NULL. The message names the column and table, and the DETAIL line shows the full failing row in column order.

Why do I get a null when I did not pass NULL?

A column left out of an INSERT receives its default, and a column with no DEFAULT clause defaults to NULL. List the column with a value, or add a default with ALTER TABLE ... ALTER COLUMN ... SET DEFAULT.

Does a column default fix an explicit NULL?

No. A default is used only when the column is omitted from the INSERT. If your code sends NULL on purpose, the NOT NULL constraint still rejects it and you must send a real value.

More decoded errors in the Fixes category. For when to use NOT NULL and defaults in the first place, see constraints in Database Design for Developers.