↑↓ 选择↵ 打开⌫ 切换范围完整搜索

PG.CENTER 连接 PostgreSQL 文档、百科与生态知识。由 Pigsty 维护。

Wiki / SQLSTATE

21000 — 基数冲突（cardinality_violation）

结果行数违反基数契约，如标量子查询返回多行。

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

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

8.18.28.38.49.09.19.29.39.49.59.61011121314151617181920
aliases
未知
class name
Cardinality Violation
condition name
cardinality_violation
lang
en
sqlstate
21000

版本定义 PG 18

21000 — 基数冲突

速览 {#at-a-glance}

21000 表示操作得到的行数不符合基数契约。最常见的是标量子查询返回多行；它与 23505 不同，不要求存在唯一索引冲突。

含义 {#meaning}

把标量子查询当作表达式时，最多只能返回一行；执行器看到第二行就报告 21000，零行则产生 NULL。同一错误也用于命令级基数冲突：ON CONFLICT DO UPDATE 的多个候选行可能再次命中同一目标行，MERGE 的多个源行也可能匹配同一目标行；这些路径有各自的报文和提示。

诊断 {#diagnosis}

先保存 sqlstate、message_primary、hint 和语句上下文。根据业务键增加确定性谓词，或在确实要把多行合成一个值时使用聚合；不要随意加 LIMIT 1，否则可能静默选择任意行。对 ON CONFLICT，按仲裁索引或唯一键去重候选源行；对 MERGE，保证源到目标的匹配对每个目标至多一行。应检查实际源行和键映射，不能把它泛化成普通重复键错误。

处理 {#response}

显式事务中先回滚失败事务，再执行修正后的完整操作。ON CONFLICT 或 MERGE 的多行来源必须先确定基数；原样重放同一批数据仍会重复触发确定性的冲突。自动提交下本案例错误后连接仍为 IDLE，这不能替代外层事务或 PL/pgSQL 处理器的边界。

实测诊断 {#messages}

18.6 (Homebrew) / latest：SQLSTATE 21000；primary more than one row returned by a subquery used as an expression；status_after_error IDLE。 10.21 (Debian 10.21-1.pgdg90+1) / pg10：SQLSTATE 21000；primary more than one row returned by a subquery used as an expression；status_after_error IDLE。

代表案例 {#case}

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

-- create
CREATE TABLE source_rows(id integer PRIMARY KEY);
-- seed
INSERT INTO source_rows VALUES (1), (2);
-- trigger
SELECT (SELECT id FROM source_rows ORDER BY id) AS only_id;
-- valid
SELECT (SELECT id FROM source_rows WHERE id = 1) AS only_id;

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

作者证据 ID：identity, scalar-subquery, dml-conflict, runtime。选定运行记录：runtime.21000-batch1-latest-20260909.latest, runtime.21000-batch1-pg10-20260909.pg10。

版本与边界 {#versions}

锁定目录在 7.4 已观察到该条件，并在列出的 9.0–18.6 正式快照中均存在。固定源码证据确认 18.6 的标量子查询、ON CONFLICT 和 MERGE 路径；选定运行只覆盖 18.6 与 10.21 的标量子查询。

相关 {#related}

对比 23505 唯一约束冲突、23503 外键冲突 和 23000 完整性约束总类。

来源 {#sources}

  • src.errcodes.18.6 (SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba)
  • src.calls.REL_18_6 (SHA-256 9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf)
  • src.nodeSubplan.18.6 (SHA-256 c356a9812691f875974c1f476efa987015ba19e70a4be833a43b692f3554dc47)
  • src.nodeModifyTable.18.6 (SHA-256 0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68)
  • doc.syntax.18.6 (SHA-256 449b0c500fccca068d0f8a1db2a5ebb5430eb83e33f12c6cb310a01a7437b635) · 官方文档

比较版本

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

cases · scalar_subquery_cardinality
{
  "assertions": [
    "SQLSTATE is 21000",
    "The connection remains usable after the autocommit error",
    "A one-row predicate succeeds afterward"
  ],
  "case_id": "scalar_subquery_cardinality",
  "cleanup": "Drop the case schema with an owner connection.",
  "has_snippet": true,
  "position": 0,
  "preconditions": [
    "A disposable table contains two source rows",
    "The scalar subquery is expected to return at most one row"
  ],
  "repair": "Make the scalar relation one row by predicate, aggregation, or an explicit application rule; do not add LIMIT without defining which row is correct.",
  "trigger": "Use a two-row subquery in a scalar expression.",
  "versions": [
    "10",
    "18"
  ]
}
claims · identity
{
  "claim_id": "identity",
  "limits": "This identifies the protocol condition; it does not select one particular SQL operation.",
  "method": "Read the fixed errcodes.txt row and macro mapping.",
  "position": 0,
  "runtime": [],
  "sources": [
    "src.errcodes.18.6"
  ],
  "statement": "21000 is the cardinality_violation condition in Class 21."
}
claims · scalar-subquery
{
  "claim_id": "scalar-subquery",
  "limits": "A scalar subquery returning zero rows yields NULL; the error is the more-than-one-row case.",
  "method": "Trace ERRCODE_CARDINALITY_VIOLATION and the fixed scalar-subquery documentation.",
  "position": 1,
  "runtime": [],
  "sources": [
    "src.nodeSubplan.18.6",
    "src.calls.REL_18_6",
    "doc.syntax.18.6"
  ],
  "statement": "A scalar subquery used as an expression must yield at most one row; the executor reports 21000 when it produces a second row."
}
claims · dml-conflict
{
  "claim_id": "dml-conflict",
  "limits": "This is a command-level cardinality conflict, not a generic duplicate-key error.",
  "method": "Read the separate nodeModifyTable report groups and their hints.",
  "position": 2,
  "runtime": [],
  "sources": [
    "src.nodeModifyTable.18.6",
    "src.calls.REL_18_6"
  ],
  "statement": "ON CONFLICT DO UPDATE and MERGE can use 21000 when one command would affect the same target row a second time."
}
claims · runtime
{
  "claim_id": "runtime",
  "limits": "The run covers one scalar-subquery path; it does not validate ON CONFLICT, MERGE, PL/pgSQL, dblink, or materialized-view paths.",
  "method": "Compare the selected summaries, raw outputs, and shared snippet registry.",
  "position": 3,
  "runtime": [],
  "sources": [
    "runtime.21000-batch1-latest-20260909.latest",
    "runtime.21000-batch1-pg10-20260909.pg10"
  ],
  "statement": "The selected scalar-subquery case returned 21000 on PostgreSQL 18.6 and 10.21, remained usable in autocommit mode, and succeeded after a one-row predicate."
}
messages · message.scalar-subquery
{
  "limits": "This is the scalar-subquery template; other 21000 paths have different templates.",
  "message_id": "message.scalar-subquery",
  "path": "",
  "position": 0,
  "raw": {
    "id": "message.scalar-subquery",
    "limits": "This is the scalar-subquery template; other 21000 paths have different templates.",
    "primary_template": "more than one row returned by a subquery used as an expression",
    "severity_source": "ERROR",
    "sources": [
      "src.nodeSubplan.18.6"
    ],
    "sqlstate": "21000"
  },
  "severity": "ERROR",
  "sources": [
    "src.nodeSubplan.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "more than one row returned by a subquery used as an expression",
      "position": 0,
      "role": "",
      "template": "more than one row returned by a subquery used as an expression"
    }
  ]
}
messages · dml-second-target
{
  "limits": "The %s substitution is ON CONFLICT DO UPDATE or MERGE; the hint branch is command-specific.",
  "message_id": "dml-second-target",
  "path": "",
  "position": 1,
  "raw": {
    "hint_template": "Ensure that no rows proposed for insertion within the same command have duplicate constrained values. / Ensure that not more than one source row matches any one target row.",
    "id": "dml-second-target",
    "limits": "The %s substitution is ON CONFLICT DO UPDATE or MERGE; the hint branch is command-specific.",
    "primary_template": "%s command cannot affect row a second time",
    "severity_source": "ERROR",
    "sources": [
      "src.nodeModifyTable.18.6"
    ],
    "sqlstate": "21000"
  },
  "severity": "ERROR",
  "sources": [
    "src.nodeModifyTable.18.6"
  ],
  "templates": [
    {
      "kind": "primary",
      "literal": "command cannot affect row a second time",
      "position": 0,
      "role": "",
      "template": "%s command cannot affect row a second time"
    },
    {
      "kind": "hint",
      "literal": "Ensure that no rows proposed for insertion within the same command have duplicate constrained values. / Ensure that not more than one source row matches any one target row.",
      "position": 1,
      "role": "",
      "template": "Ensure that no rows proposed for insertion within the same command have duplicate constrained values. / Ensure that not more than one source row matches any one target row."
    }
  ]
}
runtimes · runtime.21000-batch1-latest-20260909.latest · 18.6 (Homebrew) · passed
{
  "cases": [
    "scalar_subquery_cardinality"
  ],
  "limits": "",
  "position": 0,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/21000/cases.json",
      "sha256": "26525997f7d1220ec953f6451c63ddbbe788272d43050d71be9cfce2f23f17ca"
    },
    "cases": [
      "scalar_subquery_cardinality"
    ],
    "id": "runtime.21000-batch1-latest-20260909.latest",
    "observed": {
      "primary": "more than one row returned by a subquery used as an expression",
      "sqlstate": "21000",
      "status_after_error": "IDLE",
      "valid_result": 1
    },
    "raw": "verify/results/21000-batch1-latest-20260909/latest/raw.jsonl",
    "raw_sha256": "d2b3cff4ba74cebed39719769cc781555a43805354c0ed9eaa1727b654617c86",
    "run_id": "21000-batch1-latest-20260909",
    "server_version": "18.6 (Homebrew)",
    "server_version_num": 180006,
    "snippet_registry": {
      "path": "verify/cases/21000/snippets.json",
      "sha256": "6d820e94518ffca97f407956fdc2df104d47b14263d3117ff59dbc4409774dc2"
    },
    "status": "passed",
    "summary": "verify/results/21000-batch1-latest-20260909/latest/summary.json",
    "summary_sha256": "a0042f75764d6b803750aeacc2e4a1b94f5f9b61fd6a61dc30f77e72937fef9f",
    "target": "latest"
  },
  "run_id": "21000-batch1-latest-20260909",
  "runtime_id": "runtime.21000-batch1-latest-20260909.latest",
  "server_version": "18.6 (Homebrew)",
  "status": "passed",
  "target": "latest"
}
runtimes · runtime.21000-batch1-pg10-20260909.pg10 · 10.21 (Debian 10.21-1.pgdg90+1) · passed
{
  "cases": [
    "scalar_subquery_cardinality"
  ],
  "limits": "",
  "position": 1,
  "raw": {
    "case_manifest": {
      "path": "verify/cases/21000/cases.json",
      "sha256": "26525997f7d1220ec953f6451c63ddbbe788272d43050d71be9cfce2f23f17ca"
    },
    "cases": [
      "scalar_subquery_cardinality"
    ],
    "id": "runtime.21000-batch1-pg10-20260909.pg10",
    "observed": {
      "primary": "more than one row returned by a subquery used as an expression",
      "sqlstate": "21000",
      "status_after_error": "IDLE",
      "valid_result": 1
    },
    "raw": "verify/results/21000-batch1-pg10-20260909/pg10/raw.jsonl",
    "raw_sha256": "ec026f72b65e378fcfcb5b99ccc98055bc899aa3ad811a6cc52b4908c57e7c30",
    "run_id": "21000-batch1-pg10-20260909",
    "server_version": "10.21 (Debian 10.21-1.pgdg90+1)",
    "server_version_num": 100021,
    "snippet_registry": {
      "path": "verify/cases/21000/snippets.json",
      "sha256": "6d820e94518ffca97f407956fdc2df104d47b14263d3117ff59dbc4409774dc2"
    },
    "status": "passed",
    "summary": "verify/results/21000-batch1-pg10-20260909/pg10/summary.json",
    "summary_sha256": "87675457e4ff52f78f0c0075aab0be5e82e290554bafda5515cdbf8abf5f1979",
    "target": "pg10"
  },
  "run_id": "21000-batch1-pg10-20260909",
  "runtime_id": "runtime.21000-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.nodeSubplan.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/nodeSubplan.c",
  "position": 2,
  "sha256": "c356a9812691f875974c1f476efa987015ba19e70a4be833a43b692f3554dc47",
  "source_id": "src.nodeSubplan.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/nodeSubplan.c#L296-L298"
}
sources · src.nodeModifyTable.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "",
  "kind": "upstream_source",
  "location": "",
  "path": "src/backend/executor/nodeModifyTable.c",
  "position": 3,
  "sha256": "0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68",
  "source_id": "src.nodeModifyTable.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/nodeModifyTable.c#L2804-L2809"
}
sources · doc.syntax.18.6
{
  "commit": "724edf9bde9d356724ad384a2e196edc3c9f80f7",
  "docs_url": "https://www.postgresql.org/docs/18/functions-subquery.html",
  "kind": "upstream_source",
  "location": "",
  "path": "doc/src/sgml/syntax.sgml",
  "position": 4,
  "sha256": "449b0c500fccca068d0f8a1db2a5ebb5430eb83e33f12c6cb310a01a7437b635",
  "source_id": "doc.syntax.18.6",
  "tag": "REL_18_6",
  "url": "https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/syntax.sgml#L2232-L2238"
}

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · english-manuals:6d280944664fe6dd98d8c76dd9023318b6ac951be8b98a7dd2601a692472d193

正文语言: zh-Hans · english-manuals:6d280944664fe6dd98d8c76dd9023318b6ac951be8b98a7dd2601a692472d193