Postgres Upsert with ON CONFLICT: Examples and Gotchas

StitchDB team8 min read

A Postgres upsert is INSERT ... ON CONFLICT (column) DO UPDATE SET ...: Postgres tries the insert, and if it would violate the unique index or constraint on that column, it updates the existing row instead. Use DO NOTHING to skip duplicates silently. The special excluded table holds the values you tried to insert, so set name = excluded.name copies the new value over the old one, and the whole operation is atomic even under heavy concurrency.

SQL
insert into public.users (email, name)
values ('[email protected]', 'Ada')
on conflict (email) do update set name = excluded.name;

That covers most cases. The rest of this article works through the questions that come up right after: why the statement errors, how to upsert many rows, how to tell inserts from updates, when to use MERGE instead, and how upserts interact with Row Level Security and the REST API.

#The parts of ON CONFLICT

SQL
insert into table_name (columns...) values (...)
on conflict [conflict_target] do nothing
on conflict  conflict_target  do update set col = expr [, ...] [where condition]
  • conflict_target names the unique index Postgres should check: a column list (email), an expression ((lower(email))), optionally with where for partial indexes, or on constraint constraint_name.
  • DO NOTHING skips the row. The target is optional here; without it, any unique violation is skipped.
  • DO UPDATE requires a target. excluded.col is the proposed value; the plain table name (or alias) is the existing row.
  • WHERE on DO UPDATE limits which conflicting rows actually get updated.

The INSERT reference has the full grammar.

#Why do I get "there is no unique or exclusion constraint matching the ON CONFLICT specification"?

Because ON CONFLICT (cols) must match an existing unique index or constraint on exactly those columns, in any order. A plain (non-unique) index does not count.

SQL
-- fails if email has no unique index
insert into public.users (email, name) values ('[email protected]', 'Ada')
on conflict (email) do nothing;

-- fix
alter table public.users add constraint users_email_key unique (email);

On a large live table, build the index first without blocking writes, then attach it:

SQL
create unique index concurrently users_email_key on public.users (email);
alter table public.users add constraint users_email_key unique using index users_email_key;

For multi-column keys the target must list all columns of the index:

SQL
create unique index memberships_uniq on public.memberships (team_id, user_id);

insert into public.memberships (team_id, user_id, role)
values ($1, $2, 'member')
on conflict (team_id, user_id) do update set role = excluded.role;

#How do I upsert on a case-insensitive email?

Use an expression index and the same expression as the target:

SQL
create unique index users_email_lower on public.users (lower(email));

insert into public.users (email, name) values ('[email protected]', 'Ada')
on conflict ((lower(email))) do update set name = excluded.name;

Note the double parentheses: the outer pair is the target list, the inner pair marks an expression. The citext extension is the alternative: a citext unique column compares case-insensitively and works with a plain on conflict (email).

#How do I upsert with a partial unique index?

A partial index enforces uniqueness only for some rows, such as "one active subscription per user". Repeat its where clause in the target:

SQL
create unique index subs_one_active on public.subscriptions (user_id) where status = 'active';

insert into public.subscriptions (user_id, plan, status)
values ($1, 'pro', 'active')
on conflict (user_id) where status = 'active'
do update set plan = excluded.plan;

#How do I update only when something changed?

Add where to DO UPDATE. Rows that fail the condition are left alone (they are still locked for the statement), and they are not returned by returning.

SQL
insert into public.products as p (sku, price, updated_at)
values ('A-1', 19.90, now())
on conflict (sku) do update
set price = excluded.price, updated_at = excluded.updated_at
where p.price is distinct from excluded.price;

This avoids writing a new row version, firing update triggers and sending realtime events when nothing changed. is distinct from treats two NULL values as equal, unlike <>.

#How do I increment a counter?

Reference the existing row in the expression:

SQL
insert into public.page_views as pv (path, day, views)
values ('/pricing', current_date, 1)
on conflict (path, day) do update set views = pv.views + 1;

This is safe under concurrency: two requests at the same moment produce views = 2, not a lost update.

#How do I upsert many rows at once?

Pass multiple values rows, or arrays with unnest, which keeps the statement size fixed for any batch size:

SQL
insert into public.products (sku, name, price)
select * from unnest(
  array['A-1', 'B-7', 'C-3']::text[],
  array['Mug', 'Shirt', 'Cap']::text[],
  array[9.90, 24.00, 15.00]::numeric[]
)
on conflict (sku) do update
set name = excluded.name, price = excluded.price;

#"ON CONFLICT DO UPDATE command cannot affect row a second time"

You get this when the same batch contains two rows with the same key. Postgres refuses to update one row twice in one statement because the result would depend on order. Remove duplicates before the insert:

SQL
insert into public.products (sku, name, price)
select distinct on (sku) sku, name, price
from staging_products
order by sku, updated_at desc   -- keep the newest per sku
on conflict (sku) do update set name = excluded.name, price = excluded.price;

#How do I return the id whether it was inserted or not?

DO NOTHING ... RETURNING returns nothing for rows that already existed. Two common patterns:

SQL
-- 1. no-op update: always returns the row, but writes a new row version
insert into public.tags (name) values ('postgres')
on conflict (name) do update set name = excluded.name
returning id;

-- 2. insert or select
with ins as (
  insert into public.tags (name) values ('postgres')
  on conflict (name) do nothing
  returning id
)
select id from ins
union all
select id from public.tags where name = 'postgres'
limit 1;

Pattern 2 avoids the extra write. In a rare race, where another transaction inserts the same name at the same moment, the second select may not see that row yet (it uses the statement's snapshot) and returns nothing; retry in that case. Pattern 1 always returns a row.

#How do I know if a row was inserted or updated?

A widely used trick is returning (xmax = 0) as inserted: a freshly inserted row has xmax zero. It relies on an internal system column rather than documented behavior, so treat it as a convenience, not a contract. If you need a reliable answer, use MERGE with merge_action(), below.

#Does the sequence skip numbers on conflict?

Yes. With an identity or serial id, the default value is computed before Postgres detects the conflict, so every attempted insert consumes a number. Gaps are normal and harmless; never rely on ids being consecutive. The UUID vs bigint guide covers key choice in more depth.

#Should I use MERGE instead?

Postgres 15 added MERGE, and Postgres 17 added RETURNING with merge_action() and WHEN NOT MATCHED BY SOURCE:

SQL
merge into public.products p
using staging_products s on s.sku = p.sku
when matched and s.discontinued then delete
when matched then update set price = s.price, name = s.name
when not matched then insert (sku, name, price) values (s.sku, s.name, s.price)
returning merge_action(), p.sku;

MERGE is better for syncing a table from a source: it can insert, update and delete in one statement, and with when not matched by source it can delete rows that disappeared from the source. But it does not have the concurrency guarantee of ON CONFLICT. Two sessions merging the same new key at the same moment can both decide to insert, and one fails with a unique violation. The Postgres docs point to INSERT ... ON CONFLICT when you need an update if a concurrent insert happens. Rule of thumb: ON CONFLICT for app writes, MERGE for batch syncs.

#Triggers and upserts

BEFORE INSERT triggers always fire, because Postgres tries the insert first; their changes appear in excluded. If the row conflicts and is updated, BEFORE UPDATE and AFTER UPDATE triggers fire as well. Keep that in mind for audit triggers that should run once.

#Upserts with Row Level Security

With RLS enabled, an upsert that may update needs permission for both paths: an insert policy whose with check accepts the new row, and an update policy whose using matches the existing row. Postgres also checks select policies on the existing row on the update path. A common symptom of a missing update or select policy is an upsert that works the first time and fails on the second.

SQL
alter table public.profiles enable row level security;

create policy "insert own profile" on public.profiles
  for insert to authenticated with check (id = auth.uid());

create policy "read own profile" on public.profiles
  for select to authenticated using (id = auth.uid());

create policy "update own profile" on public.profiles
  for update to authenticated using (id = auth.uid()) with check (id = auth.uid());

More on policies in Row Level Security in Postgres.

#Upserts on StitchDB: SQL, SDK and REST

In SQL, everything above runs as written in the Studio SQL Editor. From your app, the REST API and SDK expose upsert directly (Writing data):

JavaScript
import { createClient } from 'stitchdb-js'

const stitchdb = createClient('https://<project-ref>.p.stitchdb.com', '<anon-key>')

// insert or update by primary key (or another unique column)
const { data, error } = await stitchdb
  .from('profiles')
  .upsert({ id: userId, username: 'ada' }, { onConflict: 'id' })
  .select()

Over HTTP it is a POST with on_conflict and a Prefer header:

Terminal
curl -X POST 'https://<project-ref>.p.stitchdb.com/rest/v1/profiles?on_conflict=id' \
  -H "apikey: $STITCHDB_ANON_KEY" \
  -H "Authorization: Bearer $USER_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates" \
  -d '{"id":"<user-id>","username":"ada"}'

The same RLS rules apply, since the request runs as the authenticated role. For upserts that need expressions (counters, conditional updates), write a SQL function and call it with rpc() (Call functions from the API). Because StitchDB speaks the same REST API as the open-source Supabase stack, existing supabase-js upsert calls work after changing the project URL and keys (compatibility).

#FAQ

#What is the difference between ON CONFLICT DO NOTHING and DO UPDATE?

DO NOTHING skips rows that would violate a unique index. DO UPDATE changes the existing row instead, using excluded for the new values. DO UPDATE requires a conflict target; DO NOTHING does not.

#Is Postgres upsert atomic?

Yes. INSERT ... ON CONFLICT DO UPDATE guarantees an insert or an update outcome for each row, even with many concurrent sessions, unless some other error occurs.

#Can I upsert without a unique constraint?

No. ON CONFLICT needs a unique index or constraint to detect the conflict. Add one on the columns that identify a row.

#Does upsert work with the Supabase-style JavaScript client?

Yes. .upsert(row, { onConflict: 'col' }) sends an upsert to the REST API. On StitchDB the same call works with the StitchDB SDK or supabase-js.

#Should I use MERGE or ON CONFLICT?

Use ON CONFLICT for concurrent app writes; it handles races safely. Use MERGE for batch syncs that need inserts, updates and deletes in one statement.

Try upserts from SQL and the SDK on a real Postgres database: create a free StitchDB project.

Related articles

Postgres7 min read

UUID vs Bigint Primary Keys in Postgres (and UUIDv7)

Postgres UUID primary key vs bigint identity: size, index locality, security and offline IDs, plus UUIDv7 on Postgres 17 and the built-in uuidv7() in 18.

 Postgres6 min read

Postgres Row Level Security: A Practical Guide

Postgres row level security explained with real policies: USING vs WITH CHECK, permissive vs restrictive, teams, performance tips, testing and common mistakes.

 Postgres6 min read

Should You Disable Row Level Security? (And Safer Options)

How to disable row level security in Supabase-style Postgres, what it exposes, when it is fine, and safer fixes for the reasons people turn RLS off.