# 23502 — not_null_violation

> PostgreSQL SQLSTATE 23502: not_null_violation with column-aware diagnosis and repair boundaries.
---

# 23502 — not_null_violation

## At a glance {#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.

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `23502` |
| Condition | `not_null_violation` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_NOT_NULL_VIOLATION` |
| Aliases | `—` |

<!-- source facts: data/errcodes/23502.json -->
<!-- END SQLSTATE FACTS -->

## Meaning {#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 {#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 {#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 {#messages}

`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 {#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](../data/cases/23502.json).

<!-- BEGIN SQLSTATE SNIPPET: not_null_insert -->
```sql
-- 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;
```
<!-- END SQLSTATE SNIPPET -->

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the shared registry; [structured evidence](../data/evidence/23502.json) · [case export](../data/cases/23502.json).

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 {#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 {#related}

Compare [23514 CHECK violation](../23514/), [23503 foreign-key violation](../23503/), and [23505 unique violation](../23505/).

## Sources {#sources}

- [`src.errcodes.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt) (SHA-256 `6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba`)
- `src.calls.REL_18_6` (SHA-256 `9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf`)
- [`src.execMain.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execMain.c#L2213-L2219) (SHA-256 `33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d`)
- [`src.tablecmds.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L6465-L6490) (SHA-256 `422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9`)
- [`src.typecmds.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/typecmds.c#L3199-L3204) (SHA-256 `60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb`)
- [`doc.ddl-constraints.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/ddl.sgml) (SHA-256 `ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7`) · [official documentation](https://www.postgresql.org/docs/18/ddl-constraints.html)