PostgreSQL Error 23502

SQLSTATE

23502

Condition name

not_null_violation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18 (examples run on PostgreSQL 18.6 in PGlite)

Category

Integrity Constraints

Last reviewed

24 October 2026

What it means

SQLSTATE 23502 means a row would store NULL in a column declared NOT NULL (including every primary key column), so PostgreSQL rejected the statement.

A NOT NULL constraint says the column must always have a value. When an INSERT, UPDATE, COPY or a cascading foreign key action would leave it NULL, PostgreSQL stops the statement and raises 23502 (not_null_violation). The message names the column and the table, and DETAIL prints the whole failing row in column order, with null where the value is missing, so you can see exactly what was sent. Primary key columns are NOT NULL automatically.

Two details catch people out. A column default is used only when the column is left out of the INSERT or given the keyword DEFAULT; an explicit NULL, which many ORMs send for unset fields, is stored as NULL and fails. And the check runs on the row PostgreSQL proposes to insert, so INSERT ... ON CONFLICT DO UPDATE fails here when the VALUES list omits a NOT NULL column, even if the row already exists. In PostgreSQL 18 NOT NULL constraints are also stored in pg_constraint with names such as customers_email_not_null, but the error message still names the column, not the constraint.

Common causes

• The INSERT leaves out a NOT NULL column that has no default.
• The application or ORM sends an explicit NULL for a field it did not set, which bypasses the column default.
• An UPDATE sets the column to NULL, directly or through an expression or subquery that returns NULL (for example a lookup that finds no row).
• An INSERT ... SELECT or COPY brings in source rows with empty values, or a LEFT JOIN in the SELECT produces NULLs.
• ALTER TABLE ... SET NOT NULL, ADD COLUMN ... NOT NULL without a default, or ADD PRIMARY KEY runs on a table where some rows are NULL ("contains null values").
• A foreign key with ON DELETE SET NULL points from a column that is also NOT NULL, so deleting the parent fails.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT column_name, is_nullable, column_default, is_identity, is_generated
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'customers'
ORDER BY ordinal_position;

SELECT conname, pg_get_constraintdef(oid) AS definition, convalidated
FROM pg_constraint
WHERE conrelid = 'customers'::regclass
  AND contype = 'n'
ORDER BY conname;

SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE code IS NULL) AS null_rows
FROM products;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO customers (email, name) VALUES ('sara@example.com', 'Sara');

-- or, for a column with a default
INSERT INTO events (id, created_at) VALUES (1, DEFAULT);

ALTER TABLE events ALTER COLUMN created_at SET DEFAULT now();

UPDATE products SET code = 'UNKNOWN' WHERE code IS NULL;
ALTER TABLE products ALTER COLUMN code SET NOT NULL;

-- a new required column on a table that already has rows
ALTER TABLE customers ADD COLUMN status text NOT NULL DEFAULT 'active';

INSERT INTO accounts (id, owner, note)
VALUES (1, 'Sara', 'updated')
ON CONFLICT (id) DO UPDATE SET note = EXCLUDED.note;

Examples

Leaving out a required column

CREATE TABLE customers (
    id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL,
    name  text
);

INSERT INTO customers (name) VALUES ('Sara');

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

An explicit NULL does not use the column default

CREATE TABLE events (
    id         int,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (id, created_at) VALUES (1, DEFAULT);  -- works
INSERT INTO events (id, created_at) VALUES (2, NULL);     -- fails

ERROR:  null value in column "created_at" of relation "events" violates not-null constraint
DETAIL:  Failing row contains (2, null).

An upsert that omits a NOT NULL column

CREATE TABLE accounts (
    id    int PRIMARY KEY,
    owner text NOT NULL,
    note  text
);
INSERT INTO accounts VALUES (1, 'Sara', 'first');

-- only meant to update note, but the proposed row has owner = NULL
INSERT INTO accounts (id, note) VALUES (1, 'updated')
ON CONFLICT (id) DO UPDATE SET note = EXCLUDED.note;

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

Setting NOT NULL on a column that already has NULLs

This form has no DETAIL line, so it does not say which rows are NULL; use the counting query above. ADD PRIMARY KEY on the column and ADD COLUMN ... NOT NULL without a default on a table with rows give the same message.

CREATE TABLE products (id int, code text);
INSERT INTO products VALUES (1, 'A-1'), (2, NULL);

ALTER TABLE products ALTER COLUMN code SET NOT NULL;

ERROR:  column "code" of relation "products" contains null values

How to prevent it

Declare NOT NULL on every column that must have a value, with a default where one makes sense, and keep the application from sending NULL for fields it does not set. Validate required fields before the INSERT so users get a clear message, and still handle SQLSTATE 23502 in code, since the database is the final check.

Before tightening a column in a migration, count the NULLs, backfill them and only then add NOT NULL. Do not combine ON DELETE SET NULL with a NOT NULL column, and test imports with a few rows first so empty source values show up early.

Common questions

Learn more on CodeWithSQL

• SQL NULL values explained
• SQL constraints explained

Official documentation

• PostgreSQL error codes (Appendix A)
• Constraints: not-null constraints
• Default values
• ALTER TABLE (SET NOT NULL, ADD COLUMN)

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.