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

What it means

A subquery in brackets used as a value, in the select list, after =, or in UPDATE … SET col =, is a scalar subquery. It must return one row with one column, or no rows (which gives NULL). This one returned more, and PostgreSQL won’t guess which row you meant, so the statement stopped.

The error is raised while the query runs, not when it’s parsed. So a query that has worked for months can start failing the day the data gains a second matching row, such as a customer’s second invoice or a duplicated setting. The message has no LINE pointer; look for the subquery that sits where one value belongs.

Common causes

1. A one-to-many relationship treated as one-to-one: “the invoice total for each customer” when customers can have several invoices.
2. = where IN was meant: WHERE id = (SELECT customer_id FROM …).
3. An UPDATE … SET col = (SELECT …) where some target rows match several source rows.
4. Duplicate data in a table that was assumed to have one row per key, because no unique constraint enforces it.

How to fix it

Testing membership: use IN, ANY or EXISTS

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM invoices WHERE total > 60);

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM invoices i WHERE i.customer_id = c.id AND i.total > 60);

= ANY (SELECT …) means the same as IN (SELECT …).

Several values that belong together: aggregate them

SELECT c.name,
       (SELECT sum(i.total) FROM invoices i WHERE i.customer_id = c.id) AS total
FROM customers c;

count(), max(), array_agg() and string_agg() all return one row however many they read.

One specific row: ORDER BY and LIMIT 1

Say which row wins, then take it:

SELECT c.name,
       (SELECT i.total FROM invoices i
        WHERE i.customer_id = c.id
        ORDER BY i.created_at DESC
        LIMIT 1) AS latest
FROM customers c;

LIMIT 1 without ORDER BY also stops the error, but returns whichever row the server reads first, which can change from one run to the next.

Several columns from that row: a LATERAL join

A scalar subquery can return only one column. To get several from the same row, join to it:

SELECT c.name, i.total, i.created_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT total, created_at FROM invoices
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) i ON true;

Find the duplicates

If there should only ever be one matching row, find the keys that have more:

SELECT customer_id, count(*) FROM invoices GROUP BY customer_id HAVING count(*) > 1;

If one per key is the rule, clean them up and add a unique constraint so it can’t happen again; see duplicate key value.

Watch out for UPDATE … FROM

Rewriting SET col = (SELECT …) as UPDATE … FROM makes the error go away but not the problem. When a target row joins to several source rows, PostgreSQL uses one of them, and the documentation says which one “is not readily predictable”. Make the source one row per key first, with DISTINCT ON or an aggregate.

Reproduce it

On PostgreSQL 18.6, with Ada having two invoices, Grace one and Linus none:

CREATE TABLE customers (id int PRIMARY KEY, name text, email text);
CREATE TABLE invoices (id int PRIMARY KEY, customer_id int REFERENCES customers, total numeric, created_at date);
INSERT INTO customers VALUES (1, 'Ada', 'ada@example.com'), (2, 'Grace', 'grace@example.com'), (3, 'Linus', 'linus@example.com');
INSERT INTO invoices VALUES (1, 1, 100, '2026-09-01'), (2, 1, 50, '2026-10-01'), (3, 2, 70, '2026-10-02');

SELECT c.name, (SELECT i.total FROM invoices i WHERE i.customer_id = c.id) AS total FROM customers c ORDER BY c.id;

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

The same query limited to Grace and Linus works: 70 for Grace, and NULL (shown empty) for Linus, who has no invoices:

 name  | total 
-------+-------
 Grace |    70
 Linus |      
(2 rows)

WHERE id = (SELECT customer_id FROM invoices WHERE total > 60) and UPDATE customers c SET last_total = (SELECT i.total FROM invoices i WHERE i.customer_id = c.id) fail with the same message. The ORDER BY … LIMIT 1 version returns 50, 70 and NULL; the UPDATE … FROM version runs without error and gave Ada 100, one of her two totals, chosen by the server.

Asking a scalar subquery for two columns is a different error, subquery must return only one column. PostgreSQL 14, 15, 16 and 17 print the same messages.

The same error code, 21000, also covers ON CONFLICT DO UPDATE command cannot affect row a second time: one INSERT … ON CONFLICT proposed the same key twice. Remove the duplicates from the input; see duplicate key value.

In Inlet

When a statement fails, Inlet shows the server’s error with a hint for common ones. With your own Anthropic API key, Ask Claude (⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows, so it can see the one-to-many relationship in the schema but not your data.