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

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

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

Related & next steps

• SQLSTATE 21000 — Cardinality Violation (full reference)
• Message: query returned more than one row
• Glossary: Subquery

Reference: PostgreSQL 18 Section 9.24 “Subquery Expressions”.