How do you upsert in Postgres (INSERT ... ON CONFLICT)?
An upsert writes a row if it does not exist and updates it if it does, in a single statement instead of a separate check-then-insert-or-update. Postgres has no UPSERT keyword; the syntax is INSERT ... ON CONFLICT (conflict_columns) DO UPDATE SET ... or DO NOTHING. The conflict_columns must have a unique index or constraint on them — that is what Postgres uses to detect the conflict in the first place.
1.Basic upsert: INSERT ... ON CONFLICT DO UPDATE
Name the column (or columns) with the unique constraint in ON CONFLICT, then set the columns to update using the special excluded table, which holds the row that would have been inserted.
-- requires: create unique index on users (email);
insert into users (email, name, last_seen_at)
values ('a@example.com', 'Ada', now())
on conflict (email)
do update set name = excluded.name, last_seen_at = excluded.last_seen_at;2.Upsert many rows at once
VALUES accepts multiple rows, and ON CONFLICT applies to each one, so a bulk upsert is one round trip instead of N. Each conflicting key in the input must appear only once per statement (Postgres cannot update the same row twice in one command — deduplicate the input first if it might repeat a key).
insert into inventory (sku, quantity, updated_at)
values
('SKU-1', 42, now()),
('SKU-2', 7, now()),
('SKU-3', 0, now())
on conflict (sku)
do update set quantity = excluded.quantity, updated_at = excluded.updated_at;3.DO NOTHING, conditional updates, and partial-index conflicts
Use DO NOTHING to silently skip a row that already exists (no error, no change). Add a WHERE clause after DO UPDATE SET to only apply the update under a condition, such as never overwriting with older data. When the unique constraint is a partial or expression index, repeat the same expression or predicate in ON CONFLICT so Postgres can match it to that index.
-- skip duplicates silently
insert into events (idempotency_key, payload) values ('evt_123', '{}')
on conflict (idempotency_key) do nothing;
-- only overwrite with newer data
insert into prices (sku, cents, updated_at)
values ('SKU-1', 999, now())
on conflict (sku) do update set cents = excluded.cents, updated_at = excluded.updated_at
where excluded.updated_at > prices.updated_at;
-- expression index: create unique index on users (lower(email));
insert into users (email) values ('A@Example.com')
on conflict ((lower(email))) do nothing;4.Upsert from a client library
The supabase-js client sends ON CONFLICT for you through .upsert(). Pass onConflict when the unique key is not the primary key, since without it the client assumes the primary key is the conflict target and raises the "no unique or exclusion constraint matching the ON CONFLICT specification" error if that column has no unique index.
const { error } = await client
.from('users')
.upsert({ email: 'a@example.com', name: 'Ada' }, { onConflict: 'email' })
// bulk upsert: pass an array, same onConflict option
await client.from('inventory').upsert(rows, { onConflict: 'sku' })Common questions
Does Postgres have an UPSERT keyword?
No. "Upsert" describes the behavior of INSERT ... ON CONFLICT (columns) DO UPDATE SET ... (or DO NOTHING) — insert if the row is new, update if the conflict columns already exist. There is no separate UPSERT statement.
Why do I get "no unique or exclusion constraint matching the ON CONFLICT specification"?
ON CONFLICT names columns that must have a unique index or constraint backing them. This error means the columns you named have no such index — create one (create unique index on table (column)) or name the columns that already have one.
What does excluded mean in ON CONFLICT DO UPDATE?
excluded is a pseudo-table holding the row values that were proposed for insertion, before the conflict was detected. SET name = excluded.name takes the new value from the incoming row rather than keeping the existing row's value.
Can I upsert multiple rows with the same conflict key in one statement?
No — Postgres raises "ON CONFLICT DO UPDATE command cannot affect row a second time" if the input contains the same conflict key twice. Deduplicate the input first, keeping whichever row should win, typically with DISTINCT ON.
Ready to try it?
Start a free project and follow this guide against a real backend, or move an existing project in with a dry run first.