23502 — 非空约束冲突（not_null_violation）

向 NOT NULL 列写入了 NULL 值。

英文手册覆盖始于 PostgreSQL 8.1. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

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: 未知
class name: Integrity Constraint Violation
condition name: not_null_violation
lang: en
sqlstate: 23502

版本定义 PG 18

23502 — 非空约束冲突

速览 {#at-a-glance}

23502 表示 NULL 到达了 NOT NULL 规则。本案例识别出列 label 和关系 items；18.6 与 10.21 的英文主报文关系措辞略有差异，但 SQLSTATE 和结构化对象一致。

含义 {#meaning}

执行器检查元组的 NOT NULL 属性并带出列、关系及可选的失败行 DETAIL。模式校验和域的 NOT NULL 校验也会使用该码，但源码模板不同；域 CHECK 失败属于 23514 路径。默认值、显式值、生成表达式或模式变更的选择取决于 NULL 的来源。

诊断 {#diagnosis}

保存 column_name、table_name 和 DETAIL。选定案例中，18.6 主报文包含 of relation "items"，10.21 则省略该短语；应使用结构化字段，不要匹配完整英文报文。沿参数、类型转换、生成列、触发器和 INSERT ... SELECT 追踪值。自动提交案例错误后仍为 IDLE；外层显式事务需要回滚或处理器。

处理 {#response}

填入合法值、明确采用默认值，或在检查既有数据和下游读取方后调整约束。不要为了掩盖缺失值而删除 NOT NULL，或把它换成更弱的 CHECK；NULL 能否出现必须是有意的数据契约。新增 NOT NULL 的迁移要先校验既有数据，保持模式变更事务边界显式。

实测诊断 {#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。

代表案例 {#case}

运行器从 verify/cases/23502/snippets.json（SHA-256 f701e32a0e9214d6c88deabcda99981c288cae0f176d1dcaf383863c3942f8e0）读取下列片段，并为临时 schema 替换表名；完整 setup、断言与清理见案例导出。

-- 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;

本案例对应的 SQLSTATE、诊断、事务状态和修复断言均来自上述共享 registry；结构化证据 · 案例导出。

作者证据 ID：identity, dml-path, schema-path, runtime。选定运行记录：runtime.23502-batch1-latest-20260909.latest, runtime.23502-batch1-pg10-20260909.pg10。

版本与边界 {#versions}

锁定目录在 7.4 已观察到该条件，并在列出的正式快照中均存在。选定运行只覆盖 18.6 与 10.21 的普通 INSERT，不覆盖 ALTER TABLE 校验、域、分区或触发器生成 NULL。

来源 {#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) · 官方文档

比较版本

SQLSTATE 证据与已记录的运行版本

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"
}

来源引用

原始手册引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · english-manuals:274ab2a34b4d8447f04f036014ce9f12f487201ce18570f4ef5a76e70aae5fe5

正文语言: zh-Hans · english-manuals:274ab2a34b4d8447f04f036014ce9f12f487201ce18570f4ef5a76e70aae5fe5