PostgreSQL Cardinality Violation (SQLSTATE 21000)

When a subquery that is expected to return a single value returns multiple rows, PostgreSQL raises ERROR: more than one row returned by a subquery used as an expression with SQLSTATE 21000 and condition name cardinality_violation. This error surfaces in psql, application logs, and driver exceptions whenever PostgreSQL evaluates a scalar subquery that yields more than one result row.

What This Error Means

SQLSTATE class 21 covers cardinality violations — situations where the number of rows returned by an expression does not match the expected cardinality. Class 21 has only one defined condition: 21000 cardinality_violation.

A scalar subquery is a subquery written in a position that expects exactly one row and one column — for example, in the SELECT list, in a SET clause of an UPDATE, or on either side of a comparison operator (=, <, >, etc.). PostgreSQL evaluates the subquery and, if it returns more than one row, immediately raises cardinality_violation rather than returning ambiguous results.

The transaction remains open after this error; only the current statement is aborted. If you are inside an explicit transaction block, the transaction is placed in an error state and you must issue ROLLBACK (or ROLLBACK TO SAVEPOINT) before executing further statements.

Common Causes

1. Scalar subquery in the SELECT list returning multiple rows. A correlated or uncorrelated subquery placed directly in the SELECT list is expected to yield at most one row. If the inner query can produce multiple rows for any outer row, PostgreSQL raises this error.

2. Scalar subquery on the right-hand side of an assignment or comparison. Using = (or <, >, etc.) with a subquery assumes single-row output. For multi-row results, IN, ANY, or ALL must be used instead.

3. UPDATE ... SET column = (subquery) where the subquery is not unique. A subquery in the SET clause of an UPDATE statement must return exactly one row. Missing a WHERE clause or joining on a non-unique key produces multiple rows and triggers this error.

4. Scalar subquery passed to a function expecting a single value. A subquery used as a function argument in a context that requires a single scalar value will fail if the subquery returns more than one row.

How to Fix cardinality_violation

1. Add a LIMIT 1 to the subquery when you only need one row and the order is deterministic.

   -- Failing query
   SELECT
     name,
     (SELECT email FROM user_emails WHERE user_id = u.id) AS email
   FROM users u;
   
   -- Fixed: limit to one row (add ORDER BY to make the result deterministic)
   SELECT
     name,
     (SELECT email FROM user_emails WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) AS email
   FROM users u;
   

2. Use an aggregate function to collapse multiple rows into one scalar.

   -- Instead of a bare scalar subquery that may return many rows:
   SELECT
     department_id,
     (SELECT salary FROM employees WHERE department_id = d.id) AS salary
   FROM departments d;
   
   -- Use MAX, MIN, AVG, or string_agg to reduce to one value:
   SELECT
     department_id,
     (SELECT MAX(salary) FROM employees WHERE department_id = d.id) AS max_salary
   FROM departments d;
   

3. Replace = with IN or = ANY(...) when comparing against a multi-row subquery.

   -- Failing: single-value comparison against multi-row subquery
   SELECT * FROM orders WHERE customer_id = (SELECT id FROM customers WHERE country = 'US');
   
   -- Fixed: use IN for multi-row subqueries
   SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');
   

4. Rewrite scalar subqueries in UPDATE SET clauses to guarantee uniqueness.

   -- Failing: subquery may return multiple rows if user_id is not unique in the source table
   UPDATE accounts
   SET last_login = (SELECT login_time FROM login_events WHERE user_id = accounts.id);
   
   -- Fixed: ensure the subquery returns one row per account
   UPDATE accounts
   SET last_login = (
     SELECT MAX(login_time) FROM login_events WHERE user_id = accounts.id
   );
   

5. Use a JOIN instead of a scalar subquery where the relationship is one-to-many.

   -- Rewrite as a lateral join to avoid scalar subquery limitations entirely
   SELECT u.name, le.email
   FROM users u
   JOIN LATERAL (
     SELECT email FROM user_emails WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1
   ) le ON true;
   

Additional Information

• SQLSTATE class 21 has existed in the SQL standard since SQL-92. PostgreSQL has mapped 21000 to cardinality_violation since at least PostgreSQL 7.4.
• The only condition in class 21 is 21000 itself — there are no sub-conditions.
• Related conditions in nearby classes: 02000 (no_data) is raised in PL/pgSQL when a SELECT INTO or FETCH returns no rows; the cardinality_violation counterpart for zero rows in a mandatory-single-row context.
• In PL/pgSQL, SELECT INTO with a query returning multiple rows raises cardinality_violation by default. The STRICT modifier on SELECT INTO or EXECUTE INTO explicitly enforces exactly-one-row semantics and raises either no_data_found (0 rows) or too_many_rows (>1 row — SQLSTATE P0003), which are easier to catch individually.
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, JDBC, node-postgres) surface this as a DatabaseError or ProgrammingError with the SQLSTATE 21000 accessible via the exception's pgcode or getSQLState() attribute.
• ORMs such as SQLAlchemy, Django ORM, and Hibernate do not rewrite subqueries to avoid this error; it propagates as a database-level exception.

Frequently Asked Questions

Why does PostgreSQL raise this error instead of just returning the first row? The SQL standard requires that scalar subqueries return exactly one row. Silently picking an arbitrary row would produce non-deterministic results and hide logic bugs. PostgreSQL enforces the standard strictly to ensure predictable behavior — if you want a single row from multiple candidates, you must explicitly specify which one using LIMIT 1 with an ORDER BY, or an aggregate like MAX/MIN.

What is the difference between cardinality_violation (21000) and too_many_rows (P0003)? 21000 is raised in plain SQL when a scalar subquery in a DML statement or query returns more than one row. P0003 too_many_rows is a PL/pgSQL-specific condition raised when SELECT INTO ... STRICT or EXECUTE ... INTO STRICT returns more than one row inside a PL/pgSQL function or procedure. Both mean "too many rows," but they occur in different execution contexts and must be caught separately in exception handlers.

Can I catch this error inside a PL/pgSQL exception block? Yes. Use the condition name directly:

BEGIN
  -- your statement
EXCEPTION
  WHEN cardinality_violation THEN
    -- handle or log the error
    RAISE NOTICE 'Subquery returned more than one row';
END;

Does adding an index help prevent this error? No. This is a query logic error, not a performance problem. An index will not change how many rows a subquery returns. The fix is always in the SQL itself — constrain the subquery to return at most one row via aggregation, LIMIT, or a structural rewrite using JOIN.