A subquery used as a single value returns several rows
ERROR: more than one row returned by a subquery used as an expressionShort answer
PostgreSQL only accepts a subquery as a single value when it returns one row and one column. Run the subquery on its own to see the extra rows. Then change the comparison to IN or ANY, make the subquery return one row on purpose, or add the missing join condition.
Fix it fast
1Find out which cause you have
Copy the subquery out of the parentheses and run it alone. If it prints more than one row, count them per key to see which values repeat.
-- replace the table and column names with your own SELECT customer_id, count(*) FROM orders GROUP BY customer_id HAVING count(*) > 1;2Match what you see, then jump to its fix
- 1
WHERE col = (SELECT ...) or SET col = (SELECT ...) on a column that is not uniqueA subquery after = or <> returns more than one matchFix - 2
SELECT col, (SELECT ... ) FROM t, or UPDATE ... SET col = (SELECT ...) where several child rows exist per parentYou need exactly one value, but the data has several candidatesFix - 3
The inner query has no condition tying it to the outer table, so it returns every row on each passThe correlated subquery lost its link to the outer rowFix
- 1
What does the error look like?
ERROR: more than one row returned by a subquery used as an expression
Cause 1 of 3
A subquery after = or <> returns more than one match
1Check
A subquery in parentheses used as one value is a scalar subquery. The manual says it must return exactly one row with one column, and returning more is an error. Zero rows is not an error: the value becomes null.
SELECT id FROM customers WHERE country = 'IE';2Fix
If you meant 'any of these', compare with IN or = ANY. Both are documented as equivalent.
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'IE');3Verify
The statement runs and returns the rows of every matching customer, not an error.
SELECT count(*)
FROM orders
WHERE customer_id = ANY (SELECT id FROM customers WHERE country = 'IE');Confirmed by postgresql.org
Cause 2 of 3
You need exactly one value, but the data has several candidates
1Check
Look for duplicates in the table the subquery reads. A one-to-many relationship (many payments per order) gives many rows per outer row.
SELECT order_id, count(*)
FROM payments
GROUP BY order_id
HAVING count(*) > 1;2Fix
State which single row you want. Use an aggregate such as max(), or sort and take the first row. LIMIT without ORDER BY returns an unpredictable row, so always sort first.
-- the latest payment amount per order
SELECT o.id,
(SELECT p.amount
FROM payments p
WHERE p.order_id = o.id
ORDER BY p.paid_at DESC
LIMIT 1) AS last_amount
FROM orders o;3Verify
Run the query again. Every outer row gets one value, and the error is gone.
SELECT count(*) FROM orders;Confirmed by postgresql.org
Cause 3 of 3
The correlated subquery lost its link to the outer row
1Check
The manual says a subquery can refer to variables of the surrounding query, which act as constants for each evaluation. If the inner WHERE never mentions the outer alias, it is not correlated and always returns the full set.
-- broken: no link to o
SELECT o.id, (SELECT p.amount FROM payments p) FROM orders o;2Fix
Add the join condition that uses the outer alias, then make sure that condition identifies one row (see the cause above).
SELECT o.id,
(SELECT max(p.amount) FROM payments p WHERE p.order_id = o.id)
FROM orders o;3Verify
The query returns one row per order with a single value in the second column.
SELECT count(*) FROM orders;Confirmed by postgresql.org
How do I stop it coming back?
- Put a unique constraint on any column you use to look up a single value, so the database can never hold two candidates.
- When a subquery sits in a comparison, ask whether you mean one value or any of several, and write = or IN to match.
- Never use LIMIT 1 without ORDER BY: it hides the problem and returns a different row from run to run.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix