SQLSTATE 21000Severity lowLab verified

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.

What lands in your log

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

Reproduced on PostgreSQL 16.14Verified 2026-07-16 (Docker lab, PostgreSQL 16.14)Verified against PostgreSQL 16.14 in an isolated lab environment

In 10 seconds

What triggers it: Create a table where more than one row can share the same lookup value.
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.

Fix

What to do right now

Application-level steps for 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.

Fix SQL

-- 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;

For this error

See this error live on the server

Run these against the affected instance to confirm the diagnosis before you act.

More than one row returned by a subquery used as an expression (21000) is statement-local: PostgreSQL does not retain the rejected value after the statement ends. Capture the exact bound value and statement position in application/server logs, then run this targeted validation before retrying.

Count rows before using a scalar subquery

Validate the specific value/shape that can raise this SQLSTATE.

-- Replace the inner query, keeping it read-only.
SELECT count(*) AS rows_returned
FROM (<subquery_that_failed>) AS candidate;

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.

Schema setup

CREATE SCHEMA IF NOT EXISTS hr;
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');

The scalar subquery that matches 2 rows

SELECT (SELECT id FROM hr.employees WHERE dept = 'eng') AS one_id;

Confirming the underlying rows are untouched

SELECT id, dept FROM hr.employees WHERE dept = 'eng' ORDER BY id;

What PostgreSQL actually returned

Schema setup output

DROP TABLE
CREATE TABLE
INSERT 0 3

The scalar subquery that matches 2 rows output

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

Confirming the underlying rows are untouched output

 id | dept 
----+------
  1 | eng
  2 | eng
(2 rows)

Verification

PG 16.14

Last verified: 2026-07-16 (Docker lab, PostgreSQL 16.14)
Verification scope: Verified against PostgreSQL 16.14 in an isolated lab environment
Audit status: reviewed

ShareLinkedIn X

Went further?

Pro unlocks the second lab proof

Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.

See Pro on pricing Sign in

FollowSubstack LinkedInnew errors · lab notes · hiring loops

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

What to do right now

See this error live on the server

Count rows before using a scalar subquery

What PostgreSQL is telling you

A real, single-session PostgreSQL reproduction

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.

Pro unlocks the second lab proof