23502 — not_null_violation

At a glance

23502 means a NULL reached a NOT NULL rule. The selected case identifies column label and relation items; the exact PG18 and PG10 primary messages differ slightly in relation wording, while the SQLSTATE and structured object identity agree.

Meaning

The executor checks the tuple against the relation’s NOT NULL attributes and reports 23502 with the column and relation. PostgreSQL also uses the code for schema validation and domain NOT NULL validation, with different source templates; a failing domain CHECK is a 23514 path instead. A default, an explicit value, a generated expression, or a schema change may be the correct repair depending on why the NULL was produced.

Diagnosis

Capture the exact column_name, table_name, and optional failing-row detail. The selected PG18 primary includes of relation "items", while PG10 omits that phrase; use structured fields rather than matching the full English string. Trace the value through application parameters, casts, generated columns, triggers, and INSERT ... SELECT; do not infer the source from the final row alone. The selected autocommit case remained IDLE after the failed insert, but an explicit transaction must be rolled back or handled before reuse.

Response

Supply a value that is valid for the column, intentionally apply a default, or change the constraint only after checking existing rows and downstream readers. Do not drop NOT NULL or replace it with a weaker CHECK merely to silence a missing-value error; make NULLability a deliberate data-contract decision. Retry the insert with the corrected value. For a migration that adds NOT NULL, validate existing data separately and keep the schema change’s transaction boundary explicit.

Observed diagnostics

18.6 (Homebrew) / latest:SQLSTATE 23502; primary null value in column "label" of relation "items" violates not-null constraint; DETAIL Failing row contains (2, null).; status_after_error IDLE. 10.21 (Debian 10.21-1.pgdg90+1) / pg10:SQLSTATE 23502; primary null value in column "label" violates not-null constraint; DETAIL Failing row contains (2, null).; status_after_error IDLE.

Representative case

The runner reads these statements from verify/cases/23502/snippets.json (SHA-256 f701e32a0e9214d6c88deabcda99981c288cae0f176d1dcaf383863c3942f8e0) and qualifies the temporary table names; the complete setup, assertions, and cleanup are in the case export.

-- create
CREATE TABLE items(id integer PRIMARY KEY, label text NOT NULL);
-- seed
INSERT INTO items VALUES (1, 'seed');
-- trigger
INSERT INTO items VALUES (2, NULL);
-- repair
INSERT INTO items VALUES (2, 'valid');
-- verify
SELECT id, label FROM items ORDER BY id;

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the shared registry; structured evidence · case export.

Authored evidence IDs: identity, dml-path, schema-path, runtime. Selected runtime records: runtime.23502-batch1-latest-20260909.latest, runtime.23502-batch1-pg10-20260909.pg10.

Versions

The locked catalogue observes the condition by 7.4 and in all listed formal snapshots. The selected runtime is one ordinary INSERT on 18.6 and 10.21; it does not cover ALTER TABLE validation, domains, partition routing, or trigger-generated NULLs.

Related

Compare 23514 CHECK violation, 23503 foreign-key violation, and 23505 unique violation.

Sources

• src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
• src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
• src.execMain.18.6 (SHA-256 33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d)
• src.tablecmds.18.6 (SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9)
• src.typecmds.18.6 (SHA-256 60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb)
• doc.ddl-constraints.18.6 (SHA-256 ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7) · official documentation