21000ERRORTier 2 — Caution✅ HIGH confidencecardinality violation
What this means
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.
Why it happens
- 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
How to reproduce
Scalar subquery returning multiple rows.
SELECT (SELECT name FROM employees WHERE department = 'Sales')
FROM departments; -- multiple rows returnedFix 1: Add LIMIT 1 to the scalar subquery
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 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 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.
Sources
📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html
🔧 Source ref: Class 21 — Cardinality Violation
Confidence assessment
✅ HIGH confidence
Standard SQLSTATE. Behaviour consistent and well-documented across all Postgres versions.
See also
🔗 Related errors
📄 Reference pages