// PostgreSQL error reference

PostgreSQL 21000: more than one row returned by a subquery

A scalar subquery, used where a single value is expected, returned several rows. Add a filter, an aggregate, LIMIT 1, or use IN or a join.

SQLSTATE
21000
Name
cardinality_violation
Category
Cardinality Violation
Error message
more than one row returned by a subquery used as an expression

What it means

A subquery in the select list, in a comparison (= (SELECT ...)) or in a SET clause must produce at most one row. PostgreSQL only finds out at run time, when the subquery returns a second row, and then fails with 21000 (cardinality_violation).

The same code is used by ON CONFLICT DO UPDATE command cannot affect row a second time, when one INSERT proposes the same key twice.

Common causes

  • A lookup subquery on a column that isn't unique, such as matching customers by name.
  • A correlated subquery missing part of its join condition.
  • Data that used to be unique and no longer is.
  • An INSERT ... ON CONFLICT DO UPDATE whose input contains duplicate keys.

How to fix it

  1. If any of several values will do, or you want one of them deterministically, pick it explicitly.

    SELECT o.id,
           (SELECT c.email FROM contacts c
            WHERE c.customer_id = o.customer_id
            ORDER BY c.is_primary DESC, c.id
            LIMIT 1) AS email
    FROM orders o;
  2. If all matching rows count, use IN, ANY or a join instead of =.

    SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE name = 'Ada');
  3. Find the keys that return more than one row.

    SELECT customer_id, count(*) FROM contacts GROUP BY customer_id HAVING count(*) > 1;
  4. For ON CONFLICT, remove duplicate keys from the input first.

    INSERT INTO prices (sku, price)
    SELECT DISTINCT ON (sku) sku, price FROM staging ORDER BY sku, updated_at DESC
    ON CONFLICT (sku) DO UPDATE SET price = EXCLUDED.price;

Reproduce it safely

SELECT (SELECT x FROM (VALUES (1), (2)) v(x));  -- ERROR: more than one row returned by a subquery used as an expression

Related errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.