21000 — cardinality_violation

21000 means that an operation received the wrong number of rows for its cardinality contract. The common user-visible case is a scalar subquery that returns more than one row. It is different from 23505: no unique index conflict is required.

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: Cardinality Violation
condition name: cardinality_violation
lang: en
sqlstate: 21000

Definition PG 18

21000 — cardinality_violation

At a glance {#at-a-glance}

21000 means that an operation received the wrong number of rows for its cardinality contract. The common user-visible case is a scalar subquery that returns more than one row. It is different from 23505: no unique index conflict is required.

Meaning {#meaning}

A scalar subquery used as an expression must return at most one row. PostgreSQL reports 21000 when the executor sees a second row; zero rows produce NULL. The same condition also covers command-level cases where multiple proposed rows in ON CONFLICT DO UPDATE revisit one target row, or multiple MERGE source rows match one target row. Those paths have different messages and hints, so branch on the SQLSTATE and diagnostic fields rather than on a single English string.

Diagnosis {#diagnosis}

Capture sqlstate, message_primary, hint, and the statement context. For the scalar path, inspect the subquery without discarding rows: add a predicate that reflects the intended key, or use an aggregate when “many rows become one value” is the business rule. Do not add LIMIT 1 merely to silence the error; it can choose an arbitrary row. For ON CONFLICT, deduplicate the proposed source rows under the arbiter or unique key; for MERGE, make the source-to-target join produce at most one source row per target. Inspect the actual source rows and key mapping rather than treating this as a generic duplicate-key error.

Response {#response}

In an explicit transaction, roll back the failed statement’s transaction state before issuing the corrected statement. Then make the cardinality rule explicit and retry the complete operation. For a multi-row ON CONFLICT or MERGE, retry only after the source cardinality is deterministic; repeating the same batch will reproduce the deterministic conflict. In autocommit mode the failed scalar statement left the selected sessions IDLE, but that is a property of the boundary, not a promise for a surrounding transaction or PL/pgSQL block.

Observed diagnostics {#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.

Representative case {#case}

The runner reads these statements from verify/cases/21000/snippets.json (SHA-256 6d820e94518ffca97f407956fdc2df104d47b14263d3117ff59dbc4409774dc2) and qualifies the temporary table names; the complete setup, assertions, and cleanup are in the case export.

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

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, scalar-subquery, dml-conflict, runtime. Selected runtime records: runtime.21000-batch1-latest-20260909.latest, runtime.21000-batch1-pg10-20260909.pg10.

Versions {#versions}

The locked catalogue observes the condition by 7.4 and in every listed 9.0–18.6 formal snapshot. The fixed source evidence confirms the 18.6 scalar, ON CONFLICT, and MERGE paths; the selected runtime case covers only a scalar subquery on 18.6 and 10.21.

Related {#related}

Compare 23505 unique violation, 23503 foreign-key violation, and 23000 integrity-constraint umbrella.

Sources {#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) · official documentation

Compare versions

SQLSTATE evidence and recorded runtime versions

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

Source references

Original manual reference

Full definition and evidence JSON

Definition sources

center · PostgreSQL 18 · english-manuals:6d280944664fe6dd98d8c76dd9023318b6ac951be8b98a7dd2601a692472d193

Text language: en · english-manuals:6d280944664fe6dd98d8c76dd9023318b6ac951be8b98a7dd2601a692472d193