PostgreSQL Error 21000

SQLSTATE

21000

Condition name

cardinality_violation

Product

PostgreSQL

Versions

PostgreSQL 13 to 18; MERGE from PostgreSQL 15 (examples run on PostgreSQL 18.6 in PGlite)

Category

Types & Expressions

Last reviewed

24 October 2026

What it means

SQLSTATE 21000 means a statement needed at most one row but got several: a scalar subquery returned more than one row, or an upsert or MERGE tried to change the same target row twice.

A subquery used as a value, in a SELECT list, after =, < and similar operators, or in UPDATE ... SET, must return a single value. If it returns no rows the value is NULL, but if it returns two or more PostgreSQL cannot pick one and raises 21000 (cardinality_violation). The error is raised while the query runs, so it often appears only for some data: the query works until a customer places a second order or a lookup table gains a duplicate.

The same SQLSTATE protects upserts. INSERT ... ON CONFLICT DO UPDATE refuses to update one existing row twice in a single statement, which happens when the inserted rows contain the same key more than once; MERGE (PostgreSQL 15 and later) refuses when several source rows match the same target row. In both cases the result would depend on row order, so PostgreSQL stops instead.

Common causes

• A correlated subquery in the SELECT list, such as the order total per customer, returns several rows once a customer has more than one order.
• A filter uses = (SELECT ...) where the subquery can match several rows; IN or EXISTS was meant.
• UPDATE ... SET column = (SELECT ...) is missing the condition that ties the subquery to the row being updated.
• A batch upsert (INSERT ... VALUES list or INSERT ... SELECT with ON CONFLICT DO UPDATE) contains the same key twice.
• MERGE uses a source with duplicate join keys.

Diagnose it

Read-only These queries only read the system catalogs (pg_catalog, information_schema) and statistics views such as pg_stat_activity and pg_locks. They do not change data, roles or settings, so they are safe to run first.

SELECT customer_id, count(*) AS matching_rows
FROM orders
GROUP BY customer_id
HAVING count(*) > 1
ORDER BY matching_rows DESC;

SELECT sku, count(*) AS copies
FROM staging_stock
GROUP BY sku
HAVING count(*) > 1;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

SELECT *
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 10);

SELECT c.name,
       (SELECT o.total
        FROM orders AS o
        WHERE o.customer_id = c.id
        ORDER BY o.id DESC
        LIMIT 1) AS last_total
FROM customers AS c;

INSERT INTO stock (sku, qty)
SELECT sku, sum(qty)
FROM staging_stock
GROUP BY sku
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

ALTER TABLE exchange_rates
    ADD CONSTRAINT exchange_rates_currency_key UNIQUE (currency);

Examples

A correlated subquery that returns several orders

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric(10,2));
INSERT INTO customers VALUES (1, 'Sara'), (2, 'Ali');
INSERT INTO orders VALUES (10, 1, 20), (11, 1, 35), (12, 2, 15);

SELECT c.name,
       (SELECT o.total FROM orders o WHERE o.customer_id = c.id) AS last_total
FROM customers c;

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

= (SELECT ...) where IN was meant

CREATE TABLE customers (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric(10,2));
INSERT INTO customers VALUES (1, 'Sara'), (2, 'Ali');
INSERT INTO orders VALUES (10, 1, 20), (11, 1, 35), (12, 2, 15);

SELECT * FROM customers
WHERE id = (SELECT customer_id FROM orders WHERE total > 10);

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

An upsert batch with the same key twice

The table can be empty: the first A1 row is inserted and the second would have to update it in the same statement, which PostgreSQL does not allow.

CREATE TABLE stock (sku text PRIMARY KEY, qty int);

INSERT INTO stock (sku, qty)
VALUES ('A1', 5), ('A1', 7)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

MERGE with two source rows for one target row

CREATE TABLE stock (sku text PRIMARY KEY, qty int);
INSERT INTO stock VALUES ('A1', 0);

MERGE INTO stock s
USING (VALUES ('A1', 1), ('A1', 2)) AS v(sku, qty)
ON s.sku = v.sku
WHEN MATCHED THEN UPDATE SET qty = v.qty;

ERROR:  MERGE command cannot affect row a second time
HINT:  Ensure that not more than one source row matches any one target row.

How to prevent it

Write a scalar subquery only when the data model guarantees one row (a primary key or unique constraint on the lookup), or make it explicit with an aggregate or ORDER BY ... LIMIT 1. Test queries with data that has several matching rows, not just one.

Deduplicate batches before an upsert or MERGE, in the application or with GROUP BY or DISTINCT ON in the source query, so each key appears once per statement.

Common questions

Learn more on CodeWithSQL

• SQL subqueries vs joins
• Practice: first order per customer

Official documentation

• PostgreSQL error codes (Appendix A)
• Value expressions: scalar subqueries
• INSERT ... ON CONFLICT
• MERGE

Last technically reviewed 24 October 2026; examples and diagnostics run on PostgreSQL 18.6 (PGlite 0.5.11). SQL-level examples were run in PGlite (PostgreSQL compiled to WebAssembly); examples that need a running server are shown as documented in the PostgreSQL documentation.