↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页

pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。

百科 / 错误代码 / Class 21 基数冲突

21000 cardinality_violation

基数冲突

ERROR 已实测 详解 实测通过

类别
Class 21 基数冲突
严重等级
ERROR
条件名
cardinality_violation
宏名称
ERRCODE_CARDINALITY_VIOLATION
启用版本
7.4
状态
活跃

版本覆盖

  1. 18
  2. 17
  3. 16
  4. 15
  5. 14
  1. 13
  2. 12
  3. 11
  4. 10
  1. 19 beta
  2. 20 dev

速览

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

含义

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

诊断

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

处理

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

可复现案例

在一次性实例上执行过的场景。其中 1 个附有可执行 SQL，正文相应小节里给出。

scalar_subquery_cardinality PG 10 / 18 有 SQL

前置条件

  • A disposable table contains two source rows
  • The scalar subquery is expected to return at most one row

触发

Use a two-row subquery in a scalar expression.

断言

  • SQLSTATE is 21000
  • The connection remains usable after the autocommit error
  • A one-row predicate succeeds afterward

处置

Make the scalar relation one row by predicate, aggregation, or an explicit application rule; do not add LIMIT without defining which row is correct.

清理

Drop the case schema with an owner connection.

实测诊断

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。

报文模板

源码里的格式串，不是某一次运行的输出。%s 之类是占位符，实际报文会填入对象名与取值。适用范围一栏是核验时留下的原始英文记录，未经翻译。

主消息 more than one row returned by a subquery used as an expression

来源：src/backend/executor/nodeSubplan.c @ REL_18_6

适用范围：This is the scalar-subquery template; other 21000 paths have different templates.

主消息 %s command cannot affect row a second time
HINT 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.

来源：src/backend/executor/nodeModifyTable.c @ REL_18_6

适用范围：The %s substitution is ON CONFLICT DO UPDATE or MERGE; the hint branch is command-specific.

代表案例

运行器从 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。

版本与边界

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

来源

证据

断言

每条断言都写明了是怎么核实的，以及它不覆盖什么。这一层是核验时留下的原始英文记录，照原样呈现，未经翻译。

  • 21000 is the cardinality_violation condition in Class 21.

    核实方式Read the fixed errcodes.txt row and macro mapping.

    不覆盖This identifies the protocol condition; it does not select one particular SQL operation.

    来源src/backend/utils/errcodes.txt

  • A scalar subquery used as an expression must yield at most one row; the executor reports 21000 when it produces a second row.

    核实方式Trace ERRCODE_CARDINALITY_VIOLATION and the fixed scalar-subquery documentation.

    不覆盖A scalar subquery returning zero rows yields NULL; the error is the more-than-one-row case.

    来源src/backend/executor/nodeSubplan.c · raw/calls/REL_18_6.jsonl · doc/src/sgml/syntax.sgml

  • ON CONFLICT DO UPDATE and MERGE can use 21000 when one command would affect the same target row a second time.

    核实方式Read the separate nodeModifyTable report groups and their hints.

    不覆盖This is a command-level cardinality conflict, not a generic duplicate-key error.

    来源src/backend/executor/nodeModifyTable.c · raw/calls/REL_18_6.jsonl

  • 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.

    核实方式Compare the selected summaries, raw outputs, and shared snippet registry.

    不覆盖The run covers one scalar-subquery path; it does not validate ON CONFLICT, MERGE, PL/pgSQL, dblink, or materialized-view paths.

运行记录

目标服务器版本结果覆盖案例
latest 18.6 (Homebrew) passed scalar_subquery_cardinality
pg10 10.21 (Debian 10.21-1.pgdg90+1) passed scalar_subquery_cardinality