Incident brief
More than one row returned by a subquery used as an expression
A scalar subquery matched more than one row. PostgreSQL only allows a scalar subquery to return exactly one row, so it rejects the query.
In 10 seconds
- What
- More than one row returned by a subquery used as an expression
- What triggers it
- Create a table where more than one row can share the same lookup value.
- The fix
- Add a WHERE condition that narrows the subquery down to a single row, if one uniquely identifies the row you want.
- Proof
- Reproduced on PostgreSQL 16.14 → A scalar subquery that matched 2 rows (dept = 'eng') was rejected with SQLSTATE 21000. The underlying table data was never touched.
The fix
What to do right now
The immediate, application-level response to this error.
- Add a WHERE condition that narrows the subquery down to a single row, if one uniquely identifies the row you want.
- Otherwise, wrap the subquery in an aggregate (MIN, MAX, etc.) to guarantee exactly one value comes back.
- Avoid using LIMIT 1 alone as the fix — see the premium counterexample below for why.
-- an aggregate guarantees exactly one value comes back, no matter how many rows match
SELECT (SELECT MIN(id) FROM hr.employees WHERE dept = 'eng') AS one_id;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §4.2.11 Scalar Subqueries
It is an error to use a query that returns more than one row or more than one column as a scalar subquery.Read the full section on postgresql.org →
The scalar subquery that matches 2 rows
The subquery matched both id=1 and id=2, but a scalar subquery must return at most one row, so PostgreSQL rejected the query.Confirming the underlying rows are untouched
Both rows are still there, unchanged. The error came from how the query was shaped, not from bad data.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Create a table where more than one row can share the same lookup value.
- 2Use that lookup as a scalar subquery, e.g. SELECT (SELECT id FROM t WHERE ...).
- 3If the subquery matches 2 or more rows, PostgreSQL rejects the query with SQLSTATE 21000.
One client: 2 rows share dept = 'eng'; use that lookup as a scalar subquery.
DROP TABLE IF EXISTS hr.employees;
CREATE TABLE hr.employees (
id integer primary key,
dept text
);
INSERT INTO hr.employees (id, dept) VALUES
(1, 'eng'),
(2, 'eng'),
(3, 'sales');SELECT (SELECT id FROM hr.employees WHERE dept = 'eng') AS one_id;SELECT id, dept FROM hr.employees WHERE dept = 'eng' ORDER BY id;What PostgreSQL actually returned
DROP TABLE
CREATE TABLE
INSERT 0 3ERROR: more than one row returned by a subquery used as an expression id | dept
----+------
1 | eng
2 | eng
(2 rows)The manual's other cardinality rule was tested directly: a scalar subquery that matches zero rows does not error at all — it quietly returns NULL, which is a different behavior than the 'too many rows' case above.
Run the same scalar subquery pattern, but with a WHERE condition that matches no rows at all.
Without this
Above: 2 matching rows caused an error.
With this, tested
Below: 0 matching rows causes no error at all — just NULL.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-16 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed