cardinality violation
SQLSTATE 21000 is raised when an operation expects a single row but a subquery or scalar expression returns more than one row. It also fires when a scalar subquery used as a column expression returns multiple rows.
- 1A scalar subquery in a SELECT list or WHERE clause returns more than one row
- 2SELECT INTO in PL/pgSQL receives more than one row (with STRICT)
- 3An assignment expects a single value but the right-hand side produces multiple rows
Scalar subquery returning multiple rows.
SELECT (SELECT name FROM employees WHERE department = 'Sales') FROM departments; -- multiple rows returned
expected output
ERROR: more than one row returned by a subquery used as an expression
Fix 1
Add LIMIT 1 to the scalar subquery
WHEN When only one representative value is needed.
SELECT (SELECT name FROM employees WHERE department = 'Sales' LIMIT 1) FROM departments;
Why this works
LIMIT 1 guarantees at most one row from the subquery, satisfying the scalar expectation.
Fix 2
Rewrite as a JOIN instead of a subquery
WHEN When a multi-row relationship needs to be preserved properly.
SELECT d.name, e.name FROM departments d JOIN employees e ON e.department = d.name;
Why this works
A JOIN explicitly handles the one-to-many relationship and produces the correct number of output rows.
Fix 3
Use an aggregate to reduce to a single value
WHEN When the scalar value should be an aggregate of all matching rows.
SELECT (SELECT MAX(salary) FROM employees WHERE department = 'Sales') FROM departments;
Why this works
Aggregate functions (MAX, MIN, COUNT, etc.) always return exactly one row from a subquery.
Class 21 — Cardinality Violation
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev