23502ERRORTier 1 — Safe✅ HIGH confidence

null value in column violates not-null constraint

Category: Integrity Constraint ViolationVersions: All Postgres versions

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

1Explicitly inserting NULL into a NOT NULL column
2Omitting a NOT NULL column from the INSERT column list without a DEFAULT value
3Updating a NOT NULL column to NULL
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

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL
);

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

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 the value is known at insert time.

fix

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 a sensible default value exists for the column.

fix

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

Why it's wrong: NULLs in critical columns propagate through JOINs and calculations in unintuitive ways; provide a meaningful default instead.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-NOT-NULL

🔧 Source ref: src/backend/executor/execMain.c — ExecConstraints()

📖 Further reading: Not-Null Constraints

Confidence assessment

✅ HIGH confidence

Stable and well-documented. NOT NULL enforcement is fundamental and has not changed. Edge case: a NOT NULL column with a DEFAULT will silently use the default if the column is omitted from INSERT; 23502 only fires when the column is explicitly NULL or the default evaluates to NULL.