Fix "more than one row returned by a subquery" in PostgreSQL
ERROR: more than one row returned by a subquery used as an expression
You put a subquery where Postgres expects one single value, like after = or inside the SELECT list. For at least one row, that subquery came back with several rows, and Postgres can't fit several values into one spot.
Why it happens
- A subquery in SELECT that finds several matching rows, like all of a customer's orders.
- WHERE column = (SELECT ...) where the subquery returns a list, not one value.
- A subquery with GROUP BY, which returns one row per group.
How to fix it
1. Aggregate the subquery to one value
Fails
SELECT c.name, (SELECT o.amount FROM orders o WHERE o.customer_id = c.id) AS amount FROM customers c;
Works
SELECT c.name, (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id) AS total_spent FROM customers c;
Most customers have more than one order. SUM(), MAX() or COUNT() turn them into one value per customer. Customers with no orders get NULL.
2. Use IN for a list of values
Fails
SELECT id, customer_id, amount FROM orders WHERE customer_id = (SELECT id FROM customers WHERE country = 'Canada');
Works
SELECT id, customer_id, amount FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'Canada');
= compares with one value, IN checks against a whole list. It may have worked before because only one row matched at the time.
3. Match each group with a row comparison
Fails
SELECT name, department, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees GROUP BY department);
Works
SELECT name, department, salary FROM employees WHERE (department, salary) IN ( SELECT department, MAX(salary) FROM employees GROUP BY department );
The subquery returns one top salary per department, so three rows. Comparing the (department, salary) pair makes sure each person is checked against their own department's top salary.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.