PostgreSQL error: more than one row returned by a subquery

SQLSTATE 21000 (cardinality_violation) · Updated July 9, 2026

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

A subquery used where a single value belongs — after =, in a SELECT list, in an assignment — returned more than one row. Half the time the query is wrong (you meant a set, not a value); the other half the query is right and the data has duplicates nobody knew about.

What this error means

A scalar subquery context (WHERE x = (SELECT ...), SELECT (SELECT ...) AS y, SET col = (SELECT ...)) requires zero rows (→ NULL) or one row. Two or more is an error at execution time — which means it can lurk in production for months and fire the first day the data makes the subquery multi-row.

Common causes

• You meant a set: the natural reading was "any of these" — that is IN, not =.
• Missing correlation or filter: the subquery should have been tied to the outer row (WHERE sub.user_id = outer.id) and was not.
• Unexpected duplicates in data assumed unique — the subquery was right, the invariant broke.

How to diagnose it

-- Esegui la subquery da sola: quante righe? quali?
SELECT id FROM payments WHERE order_id = 42;

-- Se "non dovrebbero esserci duplicati", trovali:
SELECT order_id, count(*)
FROM payments
GROUP BY order_id
HAVING count(*) > 1;

How to fix it

• Set intended → WHERE x IN (SELECT ...), or EXISTS for pure existence checks.
• One specific row intended → make the choice explicit: (SELECT ... ORDER BY created_at DESC LIMIT 1). A bare LIMIT 1 without ORDER BY hides the bug instead of fixing it.
• Broken invariant → clean up the duplicates, then add the unique constraint that makes the assumption enforceable, so this class of error cannot return.