EC
23502
PostgreSQLERRORCommonIntegrity Constraint ViolationHIGH confidence

null value in column violates not-null constraint

What this means

An INSERT or UPDATE attempted to store NULL in a column that has a NOT NULL constraint. Postgres checks NOT NULL constraints in the executor before writing the tuple to the heap.

Why it happens
  1. 1Explicitly inserting NULL into a NOT NULL column
  2. 2Omitting a NOT NULL column from the INSERT column list without a DEFAULT value
  3. 3Updating a NOT NULL column to NULL
  4. 4A DEFAULT was expected but the column has no DEFAULT clause defined
How to reproduce

An INSERT omits a required NOT NULL column that has no DEFAULT.

trigger — this will error
trigger — this will error
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO users DEFAULT VALUES; -- email has no default, triggers 23502

expected output

ERROR:  null value in column "email" of relation "users" violates not-null constraint
DETAIL:  Failing row contains (1, null).

Fix 1

Provide the required value in the INSERT

WHEN When the value is known at insert time.

Provide the required value in the INSERT
INSERT INTO users (email) VALUES ('alice@example.com');

Why this works

The executor builds the new tuple from the provided column values. When all NOT NULL columns receive a non-NULL value, the NOT NULL check in ExecConstraints() passes and the tuple is written to the heap.

Fix 2

Add a DEFAULT to the column

WHEN When a sensible default value exists for the column.

Add a DEFAULT to the column
ALTER TABLE users ALTER COLUMN email SET DEFAULT 'unknown@placeholder.com';

-- Or use a generated column (Postgres 12+):
ALTER TABLE users ADD COLUMN display_name TEXT NOT NULL
  GENERATED ALWAYS AS (COALESCE(email, 'unknown')) STORED;

Why this works

A DEFAULT clause stores the expression in pg_attrdef. When a column is omitted from an INSERT, the executor evaluates the default expression and substitutes it for the missing value, satisfying the NOT NULL constraint.

What not to do

✕ Remove the NOT NULL constraint to allow NULLs

NULLs in critical columns propagate through JOINs and calculations in unintuitive ways; provide a meaningful default instead.

Sources
Official documentation ↗

src/backend/executor/execMain.c — ExecConstraints()

Not-Null Constraints ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors