23502 — not_null_violation

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.

English manual coverage begins with PostgreSQL 8.1. Source history and runtime observations have their own recorded version boundaries.

Reading PG 18·Select a recorded version

8.1 8.2 8.3 8.4 9.0 9.1 9.2 9.3 9.4 9.5 9.6 10 11 12 13 14 15 16 17 18 19 20

aliases: Unknown
class name: Integrity Constraint Violation
condition name: not_null_violation
lang: en
sqlstate: 23502

Definition PG 18

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.

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.

-- 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 {#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, 23503 foreign-key violation, and 23505 unique violation.

Sources {#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

Compare versions

SQLSTATE evidence and recorded runtime versions

cases · not_null_insert

{
  "assertions": [
    "SQLSTATE is 23502",
    "The column and relation are identified",
    "A valid row can be inserted after the error"
  ],
  "case_id": "not_null_insert",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "The target column is declared NOT NULL",
    "The insert supplies a NULL value"
  ],
  "repair": "Provide a valid value or change the schema only after confirming the data contract; do not silently coerce a missing value without business meaning.",
  "trigger": "Insert a row with NULL in the NOT NULL column.",
  "versions": [
    "10",
    "18"
  ]
}

claims · identity

{
  "claim_id": "identity",
  "limits": "The code says that a NOT NULL rule failed; it does not by itself distinguish DML from schema validation.",
  "method": "Read the fixed errcodes row.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "23502 is not_null_violation in Class 23."
}

claims · dml-path

{
  "claim_id": "dml-path",
  "limits": "The DETAIL can be omitted or vary with row-description settings; use structured column/table diagnostics.",
  "method": "Trace ReportNotNullViolationError and its errtablecol metadata.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.execMain.18.6",
    "src.calls.REL_18_6"
  ],
  "statement": "The executor reports 23502 when a tuple has a NULL value for a NOT NULL column, with column/relation fields and an optional failing-row DETAIL."
}

claims · schema-path

{
  "claim_id": "schema-path",
  "limits": "A repair must satisfy the actual schema rule; adding a default or relaxing NOT NULL is a migration decision, not a generic retry.",
  "method": "Compare the tablecmds and executor report groups.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.tablecmds.18.6",
    "src.calls.REL_18_6",
    "src.typecmds.18.6"
  ],
  "statement": "ALTER TABLE validation and domain NOT NULL validation use separate 23502 source templates; domain CHECK validation is a 23514 path."
}

claims · runtime

{
  "claim_id": "runtime",
  "limits": "The case covers one INSERT path and not ALTER TABLE, domains, partitions, or trigger-generated NULLs.",
  "method": "Read the selected summaries and raw diagnostics.",
  "position": 3,
  "runtime": [],
  "sources": [
    "runtime.23502-batch1-latest-20260909.latest",
    "runtime.23502-batch1-pg10-20260909.pg10"
  ],
  "statement": "The selected insert case returned 23502 on PG18.6 and PG10.21, identified label/items, stayed IDLE in autocommit mode, and then inserted a valid row."
}

messages · insert

{
  "limits": "The %s substitutions are the column, relation, and optional row description.",
  "message_id": "insert",
  "path": "",
  "position": 0,
  "raw": {
    "detail_template": "Failing row contains %s.",
    "id": "insert",
    "limits": "The %s substitutions are the column, relation, and optional row description.",
    "primary_template": "null value in column \"%s\" of relation \"%s\" violates not-null constraint",
    "severity_source": "ERROR",
    "sources": [
      "src.execMain.18.6"
    ],
    "sqlstate": "23502"
  },
  "severity": "ERROR",
  "sources": [
    "src.execMain.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "null value in column \" \" of relation \" \" violates not-null constraint",
      "position": 0,
      "role": "",
      "template": "null value in column \"%s\" of relation \"%s\" violates not-null constraint"
    },
    {
      "kind": "detail",
      "literal": "Failing row contains .",
      "position": 1,
      "role": "",
      "template": "Failing row contains %s."
    }
  ]
}

messages · schema-not-null-validation

{
  "limits": "This validation template reports existing rows while a relation schema is being validated.",
  "message_id": "schema-not-null-validation",
  "path": "",
  "position": 1,
  "raw": {
    "id": "schema-not-null-validation",
    "limits": "This validation template reports existing rows while a relation schema is being validated.",
    "primary_template": "column \"%s\" of relation \"%s\" contains null values",
    "severity_source": "ERROR",
    "sources": [
      "src.tablecmds.18.6"
    ],
    "sqlstate": "23502"
  },
  "severity": "ERROR",
  "sources": [
    "src.tablecmds.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" of relation \" \" contains null values",
      "position": 0,
      "role": "",
      "template": "column \"%s\" of relation \"%s\" contains null values"
    }
  ]
}

messages · domain-not-null-validation

{
  "limits": "This template is used while validating a domain NOT NULL rule against columns that use the domain.",
  "message_id": "domain-not-null-validation",
  "path": "",
  "position": 2,
  "raw": {
    "id": "domain-not-null-validation",
    "limits": "This template is used while validating a domain NOT NULL rule against columns that use the domain.",
    "primary_template": "column \"%s\" of table \"%s\" contains null values",
    "severity_source": "ERROR",
    "sources": [
      "src.typecmds.18.6"
    ],
    "sqlstate": "23502"
  },
  "severity": "ERROR",
  "sources": [
    "src.typecmds.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "column \" \" of table \" \" contains null values",
      "position": 0,
      "role": "",
      "template": "column \"%s\" of table \"%s\" contains null values"
    }
  ]
}

runtimes · runtime.23502-batch1-latest-20260909.latest · 18.6 (Homebrew) · passed

{
  "cases": [
    "not_null_insert"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23502/cases.json",
      "sha256": "2a097af0bb10072da2300cf9a5626d13027ed5773bad917fd333b489f4b4f109"
    },
    "cases": [
      "not_null_insert"
    ],
    "id": "runtime.23502-batch1-latest-20260909.latest",
    "observed": {
      "column": "label",
      "detail": "Failing row contains (2, null).",
      "primary": "null value in column \"label\" of relation \"items\" violates not-null constraint",
      "rows": [
        [
          1,
          "seed"
        ],
        [
          2,
          "valid"
        ]
      ],
      "sqlstate": "23502",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23502-batch1-latest-20260909/latest/raw.jsonl",
    "raw_sha256": "52cde4d2906d14030040c5034ac22d1331109e60ddc32bc7331ab11666986787",
    "run_id": "23502-batch1-latest-20260909",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/23502/snippets.json",
      "sha256": "f701e32a0e9214d6c88deabcda99981c288cae0f176d1dcaf383863c3942f8e0"
    },
    "status": "passed",
    "summary": "verify/results/23502-batch1-latest-20260909/latest/summary.json",
    "summary_sha256": "a224ad3252f2d40d8f1685a490fe4efe6cd8deb95515b5533c9002323387576a",
    "target": "latest"
  },
  "run_id": "23502-batch1-latest-20260909",
  "runtime_id": "runtime.23502-batch1-latest-20260909.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}

runtimes · runtime.23502-batch1-pg10-20260909.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed

{
  "cases": [
    "not_null_insert"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/23502/cases.json",
      "sha256": "2a097af0bb10072da2300cf9a5626d13027ed5773bad917fd333b489f4b4f109"
    },
    "cases": [
      "not_null_insert"
    ],
    "id": "runtime.23502-batch1-pg10-20260909.pg10",
    "observed": {
      "column": "label",
      "detail": "Failing row contains (2, null).",
      "primary": "null value in column \"label\" violates not-null constraint",
      "rows": [
        [
          1,
          "seed"
        ],
        [
          2,
          "valid"
        ]
      ],
      "sqlstate": "23502",
      "status_after_error": "IDLE",
      "table": "items"
    },
    "raw": "verify/results/23502-batch1-pg10-20260909/pg10/raw.jsonl",
    "raw_sha256": "c1c8501748a78a9e9553ff3c7887b9f60c7a78973efefc6cc952374ec19f007c",
    "run_id": "23502-batch1-pg10-20260909",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/23502/snippets.json",
      "sha256": "f701e32a0e9214d6c88deabcda99981c288cae0f176d1dcaf383863c3942f8e0"
    },
    "status": "passed",
    "summary": "verify/results/23502-batch1-pg10-20260909/pg10/summary.json",
    "summary_sha256": "1449b9682de2708ea83657d8356bf28b716f5a6f27c52f26e12b2eb9e63a6bbc",
    "target": "pg10"
  },
  "run_id": "23502-batch1-pg10-20260909",
  "runtime_id": "runtime.23502-batch1-pg10-20260909.pg10",
  "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
  "status": "passed",
  "target": "pg10"
}

sources · src.errcodes.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/utils/errcodes.txt",
  "position": 0,
  "sha256": "6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba",
  "source_id": "src.errcodes.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt"
}

sources · src.calls.REL_18_6

{
  "commit": "",
  "docs_url": "",
  "kind": "local_artifact",
  "location": "",
  "path": "raw/calls/REL_18_6.jsonl",
  "position": 1,
  "sha256": "9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf",
  "source_id": "src.calls.REL_18_6",
  "tag": "",
  "url": ""
}

sources · src.execMain.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/execMain.c",
  "position": 2,
  "sha256": "33b97337fa23a649c5e7a092e1bd405a54e8503529236c62c9d5bb93a1774a8d",
  "source_id": "src.execMain.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/execMain.c#L2213-L2219"
}

sources · src.tablecmds.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/tablecmds.c",
  "position": 3,
  "sha256": "422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9",
  "source_id": "src.tablecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L6465-L6490"
}

sources · doc.ddl-constraints.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/ddl-constraints.html",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/ddl.sgml",
  "position": 4,
  "sha256": "ce1919d9236f2e71672660e1a347146472e966e4d19b77fde5ae345dd1db6ec7",
  "source_id": "doc.ddl-constraints.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/ddl.sgml"
}

sources · src.typecmds.18.6

{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/commands/typecmds.c",
  "position": 5,
  "sha256": "60d1e9754646e100f6b74aa367ad8c2c52386c400df721707423d329209c47eb",
  "source_id": "src.typecmds.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/typecmds.c#L3199-L3204"
}

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:274ab2a34b4d8447f04f036014ce9f12f487201ce18570f4ef5a76e70aae5fe5

Text language: en · english-manuals:274ab2a34b4d8447f04f036014ce9f12f487201ce18570f4ef5a76e70aae5fe5