21000 — cardinality_violation

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

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

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

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

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

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

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

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

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