Postgres error 21000: cardinality violation

Common causes

• A scalar subquery (one used as a value) matched more than one row.
• An INSERT ... ON CONFLICT DO UPDATE contains two input rows with the same conflict key, so the same target row would be updated twice in one statement.

How to fix it

• Make the subquery return one row: add a stricter WHERE, LIMIT 1 with an ORDER BY, or aggregate.
• Deduplicate the input of an upsert before sending it, keeping the last value per key.

insert into prices (sku, cents) values ('A', 100), ('A', 120)
on conflict (sku) do update set cents = excluded.cents;
-- ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

-- fix: one row per key
insert into prices (sku, cents)
select distinct on (sku) sku, cents from (values ('A', 100, 1), ('A', 120, 2)) v(sku, cents, n)
order by sku, n desc
on conflict (sku) do update set cents = excluded.cents;

Through a REST API

PostgREST, the REST layer behind supabase-js and Scribase, answers HTTP 400 for this error and returns the SQLSTATE in the code field of the JSON error body. A bulk upsert from a client library hits this when the array you send contains the same key twice.

Questions