Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A subquery used as a single value returns several rows

ErrorERROR: more than one row returned by a subquery used as an expression

Short 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.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1WHERE col = (SELECT ...) or SET col = (SELECT ...) on a column that is not uniqueA subquery after = or <> returns more than one matchFix
    2. 2SELECT 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. 3The 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

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.