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

SQLSTATE 21000 condition cardinality_violation class 21 — Cardinality Violation severity ERROR

Reproduced & verified on PostgreSQL 14.23, 15.18, 16.14, 17.10 and 18.4 — identical message on every version.

Last reviewed 30 May 2025 · Reproduced live with the SQL on this page.

Symptoms

A scalar subquery (used where a single value is expected) returned more than one row. PostgreSQL raises SQLSTATE 21000 (cardinality_violation).

A subquery in a scalar context returned multiple rows.
Common in SELECT lists or comparisons expecting one value.
The subquery’s predicate isn’t unique.

What the server log shows

ERROR:  more than one row returned by a subquery used as an expression

Why PostgreSQL raises this — what the manual says

As Section 9.24 Subquery Expressions explains:

A subquery used as a scalar expression must return at most one row; it returned more than one, so PostgreSQL cannot reduce it to a single value — add a LIMIT 1, aggregate the result, or tighten the WHERE clause so the subquery yields exactly one row.

A scalar subquery must yield a single value. When it returns multiple rows, there is no single value to substitute, so PostgreSQL reports 21000.

Common causes

A non-unique WHERE clause in the subquery.
Missing aggregation or LIMIT where one row is expected.
Data that has more matches than assumed.

How to fix it

Make the subquery return one row (tighten the predicate or use a unique key).
Aggregate (e.g. MAX/MIN) or add LIMIT 1 with an explicit ORDER BY.
Use IN/EXISTS or a join if multiple matches are valid.

Related & next steps

Reference: PostgreSQL 18 Section 9.24 “Subquery Expressions”.