# 21000 — cardinality_violation

> PostgreSQL SQLSTATE 21000: cardinality_violation, with source-backed scalar-subquery and command-level diagnostics.
---

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

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `21000` |
| Condition | `cardinality_violation` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_CARDINALITY_VIOLATION` |
| Aliases | `—` |

<!-- source facts: data/errcodes/21000.json -->
<!-- END SQLSTATE FACTS -->

## 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](../data/cases/21000.json).

<!-- BEGIN SQLSTATE SNIPPET: scalar_subquery_cardinality -->
```sql
-- 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;
```
<!-- END SQLSTATE SNIPPET -->

The SQLSTATE, diagnostic, transaction-state, and repair assertions for this excerpt are produced from the shared registry; [structured evidence](../data/evidence/21000.json) · [case export](../data/cases/21000.json).

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](../23505/), [23503 foreign-key violation](../23503/), and [23000 integrity-constraint umbrella](../23000/).

## Sources {#sources}

- [`src.errcodes.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt) (SHA-256 `6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba`)
- `src.calls.REL_18_6` (SHA-256 `9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf`)
- [`src.nodeSubplan.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/nodeSubplan.c#L296-L298) (SHA-256 `c356a9812691f875974c1f476efa987015ba19e70a4be833a43b692f3554dc47`)
- [`src.nodeModifyTable.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/executor/nodeModifyTable.c#L2804-L2809) (SHA-256 `0fc3cb180b3443f216142954e43d8ed5f84713d75094ce8cd2785ad8f604bd68`)
- [`doc.syntax.18.6`](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/doc/src/sgml/syntax.sgml#L2232-L2238) (SHA-256 `449b0c500fccca068d0f8a1db2a5ebb5430eb83e33f12c6cb310a01a7437b635`) · [official documentation](https://www.postgresql.org/docs/18/functions-subquery.html)