PostgreSQL 21000: more than one row returned by a subquery
A scalar subquery, used where a single value is expected, returned several rows. Add a filter, an aggregate, LIMIT 1, or use IN or a join.
- SQLSTATE
21000- Name
cardinality_violation- Category
- Cardinality Violation
What it means
A subquery in the select list, in a comparison (= (SELECT ...)) or in a SET clause must produce at most one row. PostgreSQL only finds out at run time, when the subquery returns a second row, and then fails with 21000 (cardinality_violation).
The same code is used by ON CONFLICT DO UPDATE command cannot affect row a second time, when one INSERT proposes the same key twice.
Common causes
- A lookup subquery on a column that isn't unique, such as matching customers by name.
- A correlated subquery missing part of its join condition.
- Data that used to be unique and no longer is.
- An
INSERT ... ON CONFLICT DO UPDATEwhose input contains duplicate keys.
How to fix it
-
If any of several values will do, or you want one of them deterministically, pick it explicitly.
SELECT o.id, (SELECT c.email FROM contacts c WHERE c.customer_id = o.customer_id ORDER BY c.is_primary DESC, c.id LIMIT 1) AS email FROM orders o; -
If all matching rows count, use
IN,ANYor a join instead of=.SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE name = 'Ada');
-
Find the keys that return more than one row.
SELECT customer_id, count(*) FROM contacts GROUP BY customer_id HAVING count(*) > 1;
-
For
ON CONFLICT, remove duplicate keys from the input first.INSERT INTO prices (sku, price) SELECT DISTINCT ON (sku) sku, price FROM staging ORDER BY sku, updated_at DESC ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;
Reproduce it safely
SELECT (SELECT x FROM (VALUES (1), (2)) v(x)); -- ERROR: more than one row returned by a subquery used as an expression
Related errors
42803column must appear in the GROUP BY clause23505duplicate key violates unique constraint512SQL Server: Subquery returned more than 1 value1242MySQL: Subquery returns more than 1 rowORA-01427Oracle: single-row subquery returns more than one row
The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.
Work on PostgreSQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.