Subquery Returned More Than One Row
A SELECT, UPDATE, or INSERT that embeds a scalar subquery -- (SELECT ...) used where one value is expected, such as a SET clause, a target-list column, or...
In 10 seconds
A nightly job fills orders.unit_price from a price_book lookup with SET unit_price = (SELECT price FROM price_book WHERE sku = orders.sku). It ran cleanly for a year, then started aborting with 'ERROR: more than one row returned by a subquery used as an expression'. Nothing changed in the job -- the data did. The price_book is a price history, and a handful of SKUs just received a second, newer price row. The scalar subquery, written as if each SKU maps to exactly one price, now finds two and refuses to pick. The fix is to make the subquery return one row deterministically (newest price wins), and the prevention is to read prices through a single-valued view.
Why This Happens
A scalar subquery is contractually allowed to return zero rows (yielding NULL) or one row (yielding that value); returning two or more is undefined, because PostgreSQL cannot know which value you meant. The executor enforces this at run time: as soon as the inner query produces a second row it raises 21000. Here the price_book legitimately holds price history, so the SKUs with a new price have two rows; the subquery that assumed one price per SKU is simply incomplete -- it never said which of the historical prices to use.
Diagnose
Free Tier -- Basic Approach
Find the SKUs the scalar subquery cannot resolve: the ones with more than one price row.
SELECT sku, count(*) AS price_rows FROM price_book GROUP BY sku HAVING count(*) > 1 ORDER BY sku;
sku | price_rows ----------+------------ SKU-0996 | 2 SKU-0997 | 2 SKU-0998 | 2 SKU-0999 | 2 SKU-1000 | 2 (5 rows)
SELECT sku, count(*) AS price_rows FROM price_book GROUP BY sku HAVING count(*) > 1 ORDER BY sku. Five SKUs (SKU-0996 through SKU-1000) each have 2 rows -- the exact keys for which the subquery returns more than one value.
Fix
Make the subquery return exactly one row. ORDER BY effective_date DESC LIMIT 1 picks the newest price per SKU deterministically, so every order resolves to a single correct value and the UPDATE completes.
UPDATE orders o SET unit_price = (SELECT price FROM price_book b WHERE b.sku = o.sku ORDER BY b.effective_date DESC LIMIT 1);
UPDATE 1000
UPDATE orders o SET unit_price = (SELECT price FROM price_book b WHERE b.sku = o.sku ORDER BY b.effective_date DESC LIMIT 1). The statement now finishes with UPDATE 1000 -- the LIMIT 1 guarantees one row and the ORDER BY guarantees it is the current price, not an arbitrary one.
Verify
Confirm every order is priced, the previously-ambiguous SKUs carry the NEWEST price (not the stale 2024 one), and the bare scalar subquery still raises 21000 -- proving the fix restructured the query rather than hiding a real ambiguity.
SELECT count(*) AS priced_rows, count(*) FILTER (WHERE unit_price IS NULL) AS unpriced_rows FROM orders;
priced_rows | unpriced_rows
-------------+---------------
1000 | 0
(1 row)
SELECT sku, unit_price FROM orders WHERE sku IN ('SKU-0996','SKU-0997','SKU-0998','SKU-0999','SKU-1000') ORDER BY sku;
sku | unit_price ----------+------------ SKU-0996 | 146.00 SKU-0997 | 147.00 SKU-0998 | 148.00 SKU-0999 | 149.00 SKU-1000 | 100.00 (5 rows)
UPDATE orders o SET unit_price = (SELECT price FROM price_book b WHERE b.sku = o.sku);
ERROR: 21000: more than one row returned by a subquery used as an expression LOCATION: ExecScanSubPlan, nodeSubplan.c:304
SELECT count(*) AS priced_rows, count(*) FILTER (WHERE unit_price IS NULL) AS unpriced_rows -- expect 1000 and 0. Check SKU-0996..SKU-1000 show the new prices (e.g.
SKU-1000 = 100.00, not the old 10.00). Re-running the original UPDATE orders o SET unit_price = (SELECT price ...) still fails with 'more than one row returned by a subquery used as an expression', confirming the ambiguity was real and only the query changed.
⏪ Rollback
The aborted UPDATE writes nothing -- it rolls back before committing, so unit_price stays exactly as it was (all NULL here). The fix UPDATE is an ordinary write you can reverse with UPDATE orders SET unit_price = NULL if needed. Creating the prevention view is non-destructive and reversible with DROP VIEW current_price. No schema change touches the underlying tables, and there is no lock beyond the row locks of the single UPDATE.
📦 Version Matrix (PG 14--18)
Run the broken scalar subquery and the single-row fix on PostgreSQL 14 through 18. Every version raises the same cardinality violation and accepts the same ORDER BY ... LIMIT 1 rewrite.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=more than one row returned by a subquery used as an expression | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=more than one row returned by a subquery used as an expression | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=more than one row returned by a subquery used as an expression | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=more than one row returned by a subquery used as an expression | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=more than one row returned by a subquery used as an expression | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why did this break with no code change?
The query relied on an assumption about the data -- one price row per SKU -- that the data stopped satisfying. price_book is a history, and the affected SKUs gained a second (newer) row. A scalar subquery raises 21000 the instant its inner query returns more than one row, so the failure surfaced only when the duplicate appeared, not when the query was written.
Should I fix the data or the query?
Fix the query when the multiple rows are legitimate (price history is). Make the subquery return exactly one row with ORDER BY effective_date DESC LIMIT 1 (newest wins) or an appropriate aggregate. Only deduplicate the table if the extra rows are genuinely erroneous; here they are valid history, so deleting them would lose information.
Is LIMIT 1 enough on its own?
No. LIMIT 1 without ORDER BY returns whichever row the plan happens to emit first, which can change between runs or versions, giving you a silently wrong (and unstable) price. Add ORDER BY effective_date DESC so 'one row' always means the same, correct row.
How do I keep this from recurring?
Stop reading a one-value lookup directly from a many-row table. Build a view that is single-valued per key -- SELECT DISTINCT ON (sku) sku, price ... ORDER BY sku, effective_date DESC -- and read prices from it, so any query can treat the result as a scalar. Add a check that the view returns at most one row per SKU as a standing guard.