// PostgreSQL error reference

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
Error message
null value in column "email" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (7, Ada, null).

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 INSERT doesn't list the column and the column has no DEFAULT.
  • The application sent NULL, for example an unset field or a failed lookup.
  • INSERT ... SELECT or an UPDATE with a subquery that returned no row, producing NULL.
  • A column added with NOT NULL that existing code doesn't know about.

How to fix it

  1. 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();
  2. Use COALESCE when a lookup or expression can return NULL.

    UPDATE orders o
    SET region = COALESCE((SELECT region FROM customers c WHERE c.id = o.customer_id), 'unknown');
  3. 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

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.

// fewer errors, faster fixes

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.