more than one row returned by a subquery in PostgreSQL

ERROR: more than one row returned by a subquery used as an expression (SQLSTATE 21000, cardinality_violation) means a subquery that stands where one value is expected — in a SELECT list, after =, or in UPDATE … SET col = (…) — produced two or more rows. PostgreSQL does not pick one; it aborts the statement. The query need not be new: it works for as long as one row matches, because the error depends on the data, not on the SQL.

We measured every case below on PostgreSQL 17.11 and repeated the core cases on PostgreSQL 12.22, with the same messages and results (MERGE does not exist on 12).

Why does a query that worked yesterday fail today?

Nothing checks the subquery’s row count until it runs. With one pro row in plans, all three forms worked; after one more insert, all three failed:

insert into plans values ('pro', 59);  -- a second 'pro' row
select id, (select price from plans where code = 'pro') as price from accounts;
select * from accounts where plan_code = (select code from plans where price > 0);
update accounts set price = (select price from plans where code = 'pro') where id = 1;
-- each: ERROR:  21000: more than one row returned by a subquery used as an expression

There is no DETAIL or HINT, and nothing names the subquery or the key. The verbose LOCATION line does tell two kinds apart: ExecSetParamPlan for an uncorrelated subquery (run once), ExecScanSubPlan for a correlated one (run per outer row).

Does it fail for the whole query or only the bad rows?

The whole statement. In a correlated subquery only outer rows with several matches are a problem, but one is enough: with one of four accounts on a plan code that had two rows, psql showed no rows and the UPDATE changed nothing; through a cursor, account 1 arrived before the error on account 2.

So whether you see it depends on which rows the query reaches: excluding that account with a where, or order by id limit 1, made the same query succeed. An uncorrelated subquery with two rows did not fail against an empty table at all: it was never executed. A test on a small data set can pass while production fails.

Zero rows is not an error: the documentation says “the scalar result is taken to be null”, and an UPDATE … SET col = (select …) writes that NULL — an account with price 100 and a missing plan code got NULL. coalesce((select …), price) kept the 100.

Which fix returns which row?

They are not interchangeable. Measured with pro at 49 (created 1) and 59 (created 2):

LIMIT 1 without ORDER BY returned 49. Then we ran update plans set price = 49 where price = 49 — the same value — which put a new row version at the end of the table (ctid (0,3), behind the 59 at (0,2)), and the identical query returned 59.

Why does UPDATE … FROM not give this error?

A join is not a scalar subquery. With two pro rows, update accounts a set price = p.price from plans p where p.code = a.plan_code reported UPDATE 1 and wrote 49. After update plans set price = 49 where price = 49, which changed no value, the same statement wrote 59; with the two rows inserted in the opposite order, it wrote 59 too. The documentation: “only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.” It trades a loud failure for a silently wrong value. MERGE (PostgreSQL 15 and later) does refuse: MERGE command cannot affect row a second time, also 21000.

When the key should identify one row, the error is right and the data is wrong: add a unique constraint. While the duplicate existed it failed with could not create unique index "plans_code_key", DETAIL: Key (code)=(pro) is duplicated.; once it was in place, a second pro failed at the insert (23505), where the problem starts.

Why does it only fail once a second tenant exists?

In a multi-tenant table the key is unique per tenant, not globally. With unique (tenant_id, key) on settings, (select value from settings where key = 'currency') returned EUR for every invoice, including tenant 2’s, while only tenant 1 had a currency: the value was already wrong before the error. Once tenant 2 got USD — which the constraint allows — the same query failed. The fix is the tenant condition, correlated to the outer row (and s.tenant_id = i.tenant_id), which gave EUR and USD. More in a unique constraint per tenant.

With row-level security on settings, the missing filter hides itself. The same statement, with app.tenant_id = '1' set, gave:

Without app.tenant_id, the normal role and the owner under FORCE failed with unrecognized configuration parameter (42704), not 21000.

The application works; a job that connects as the owner or a role that bypasses RLS fails on identical SQL.

A policy can raise it too. With using (tenant_id = (select tenant_id from memberships where username = current_user)), a user with one membership saw their rows. After a second membership, every select, insert, update and delete on the table that reached a row failed with 21000 for that user — an error, not an empty result — while users with one membership were unaffected and a user with none got zero rows. With tenant_id in (select …) the user saw the rows of both tenants.

What does Django raise for this error?

With Django 5.2.18 and psycopg2 2.9.13, annotate(price=Subquery(Plan.objects.filter(code=OuterRef("plan_code")).values("price"))) worked with one plan row and raised django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression after the second, caused by psycopg2.errors.CardinalityViolation; except IntegrityError did not catch it. Django adds no LIMIT itself: .order_by("-price").values("price")[:1] produced ORDER BY 1 DESC LIMIT 1 and returned 59. Django 6.1.2, with psycopg2 and with psycopg 3.3.6, behaved the same.

What is the PL/pgSQL version of this error?

SELECT … INTO does not fail on several rows: a function with select value into v from settings where key = 'currency' returned EUR with two rows present, as did a LANGUAGE sql function. The documentation: the target “will be set to the first row returned by the command”, which “is not well-defined unless you’ve used ORDER BY”. INTO STRICT makes it an error:

select get_currency_strict();
-- ERROR:  P0003: query returned more than one row
-- HINT:  Make sure the query returns a single row, or use LIMIT 1.
-- CONTEXT:  PL/pgSQL function get_currency_strict() line 4 at SQL statement

set plpgsql.extra_errors = 'too_many_rows' made the non-strict function fail with P0003 too. An assignment v := (select …) is a scalar subquery and gave 21000. With a column default get_currency_strict(), an insert without that column failed with P0003. Other codes are in our SQLSTATE overview.

What we did not measure

Drivers and ORMs other than Django with psycopg2. Parallel plans, and whether a nested loop in UPDATE … FROM picks a different row than the merge and hash joins we saw. Row comparisons such as (a, b) = (select …).

Sources: PostgreSQL 17 documentation on scalar subqueries, UPDATE and PL/pgSQL statements, read on 22 October 2026. All measurements are our own, on PostgreSQL 17.11 and 12.22 in containers, with Django 5.2.18 and psycopg2 2.9.13, on 22 October 2026.