🚨 Postgres Error: 'more than one row returned by a subquery used as an expression' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL raises this error when a scalar subquery — one used where a single value is expected — returns more than one row. A scalar subquery must return exactly zero or one row; the moment it returns two, Postgres cannot collapse the result into a single value and aborts the statement.

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

The subquery is syntactically fine. The problem is the data: at the time the query ran, the inner query matched multiple rows for a spot where the outer query demanded one. That often means it worked in testing (one match) and broke in production (the data grew a second match).

Symptoms

• A query that ran fine for months suddenly fails as data volume grows.
• The failure is in a SELECT list expression, a WHERE col = (SELECT ...), or an UPDATE ... SET col = (SELECT ...).
• Re-running the outer query without the subquery works; isolating the subquery returns 2+ rows.
• Reproducible for specific keys only (the ones that now have duplicates).

UPDATE orders
SET customer_name = (SELECT name FROM customers WHERE customers.id = orders.customer_id)
WHERE id = 4192;

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

Here customers.id is not unique, so the correlated subquery returns more than one name.

Common Root Causes

1. A scalar subquery matches multiple rows

Any subquery in a position that expects one value — the SELECT list, = (...), or SET col = (...) — must return at most one row.

SELECT o.id,
       (SELECT c.email FROM customers c WHERE c.name = o.customer_name) AS email
FROM orders o;

If two customers share a name, the subquery yields two emails and the statement fails.

2. A non-unique join / lookup key

The subquery assumes the correlation column is unique when it is not. Missing unique constraint on customers.email, users.username, or a natural key lets duplicates slip in.

SELECT email, count(*)
FROM customers
GROUP BY email
HAVING count(*) > 1;

3. Data grew and the “one match” assumption broke

The query was written when each key had exactly one match. New rows introduced a second match for a handful of keys, so only some values fail.

4. A missing or too-loose WHERE filter

The subquery lacks a predicate that would have narrowed it to a single row (for example, no AND status = 'active' or no tenant filter).

How to diagnose

Step 1: Run the inner subquery alone

Pull the subquery out, substitute a failing key, and count what it returns.

SELECT name
FROM customers
WHERE id = 4192;

      name
----------------
 Ada Lovelace
 Ada L. Lovelace
(2 rows)

Two rows confirm the scalar subquery cannot resolve to one value.

Step 2: Find the duplicate keys driving it

SELECT id, count(*) AS n
FROM customers
GROUP BY id
HAVING count(*) > 1
ORDER BY n DESC
LIMIT 20;

Any row here is a key that will blow up the scalar subquery.

Step 3: Decide the correct single value

Look at the duplicate rows and decide which one is “right” (newest, active, highest priority). That decision drives whether you filter, order-and-limit, or aggregate.

SELECT id, name, updated_at, status
FROM customers
WHERE id = 4192
ORDER BY updated_at DESC;

Fixes

Force a single row with ORDER BY + LIMIT 1

When any one match is acceptable, make the choice deterministic:

SELECT o.id,
       (SELECT c.email
        FROM customers c
        WHERE c.name = o.customer_name
        ORDER BY c.updated_at DESC
        LIMIT 1) AS email
FROM orders o;

LIMIT 1 without ORDER BY picks an arbitrary row — always pair them.

Use IN or = ANY when you expect a set

If multiple matches are legitimate, stop treating the subquery as scalar:

SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE region = 'EU');

= ANY(ARRAY(...)) works the same way when you need array semantics.

Aggregate to collapse to one value

max, min, or array_agg turn many rows into a single result:

SELECT o.id,
       (SELECT max(c.updated_at)
        FROM customers c
        WHERE c.name = o.customer_name) AS last_seen
FROM orders o;

Tighten the subquery WHERE

Add the predicate that makes the match unique — the tenant, the active flag, the primary key:

SELECT (SELECT email FROM customers
        WHERE id = orders.customer_id AND status = 'active');

Rewrite as a JOIN

A LEFT JOIN is often clearer and lets you handle duplicates explicitly with DISTINCT ON or aggregation:

SELECT DISTINCT ON (o.id) o.id, c.email
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.id, c.updated_at DESC;

What to watch out for

• LIMIT 1 with no ORDER BY returns a non-deterministic row and hides the real duplication problem — fix the data or order explicitly.
• Adding a UNIQUE constraint on the lookup key prevents recurrence, but it will fail until you clean up existing duplicates.
• Switching = to IN changes semantics: a row now matches if any subquery row matches — make sure that is what you want.
• The error only surfaces for keys that currently have duplicates, so passing tests do not prove the query is safe at scale.
• UPDATE ... SET col = (SELECT ...) is a common trap; prefer UPDATE ... FROM with an explicit join and dedup.

Related

• Postgres Error: ‘relation does not exist’
• Postgres Error: ‘column does not exist’
• Postgres Error: ‘canceling statement due to statement timeout’