Stop duplicate rows in Postgres with INSERT ON CONFLICT
Short answer: If your app sometimes creates two customer records, two subscriptions or two settings rows for the same thing, the usual cause is a "check, then insert" pattern racing with itself. Add a unique constraint on the column that should be unique, then write the save as one statement: INSERT ... ON CONFLICT DO NOTHING or ON CONFLICT ... DO UPDATE. PostgreSQL guarantees that DO UPDATE ends in either an insert or an update, even under heavy concurrency.
How duplicates sneak in
Most apps start with code like this:
- Look for a row with this email (or product code, or user and date).
- If none is found, insert one.
- If one is found, update it.
It works in testing. In production, two requests can arrive a few milliseconds apart, for example a double-clicked button, a retried webhook or two browser tabs. Both run step 1, both see nothing, both insert. Now you have two rows, and later code that expects one will pick the wrong one or fail.
Typical places this shows up:
- Newsletter signups saved twice from a double submit.
- Webhook handlers creating duplicate orders when the sender retries.
- Daily summary rows (one per store per day) appearing twice after a busy period.
- "Last seen" or settings tables getting a second row per user.
Adding more checks in application code doesn't fix it, because the gap between reading and writing is still there.
Step 1: let the database enforce uniqueness
Decide what "the same thing" means and say so in the schema. That might be:
- A unique constraint on
emailin a subscribers table. - A composite unique constraint on
(store_id, day)for daily summaries. - A unique constraint on the external event ID that a webhook sender provides.
With the constraint in place, the second insert in a race fails with a unique violation instead of silently creating a duplicate. That alone protects your data. Before adding the constraint to an existing table, find and merge any duplicates already there, or the constraint creation will fail.
For case-insensitive values like emails, decide how you'll normalise them (for example, store a lower-case copy) so the constraint matches what you mean by "same".
Step 2: make the write a single statement
PostgreSQL's INSERT command has an ON CONFLICT clause for exactly this situation. Its documentation describes two choices:
ON CONFLICT DO NOTHINGsimply avoids inserting a row when a conflicting one exists. Use it when the first write wins and later ones can be ignored, such as recording that an event was processed.ON CONFLICT DO UPDATEupdates the existing row that conflicts with the proposed one. Use it when the latest data should win, such as saving a user's preferences.
In DO UPDATE, the special table name excluded refers to the row you tried to insert, so you can write something like: insert a subscriber with name and email, on conflict on email do update set name to excluded.name and updated time to now.
The key promise, in the documentation's words, is that ON CONFLICT DO UPDATE guarantees an atomic insert or update outcome; provided there's no independent error, one of the two happens, even under high concurrency. This is what people mean by "upsert".
Choosing the conflict target
You tell PostgreSQL which uniqueness rule to watch, either by listing the columns or naming the constraint. A few tips:
- Match an existing unique index or constraint. The conflict target has to correspond to one, or the statement errors.
- Be specific. If a table has several unique constraints, naming the one you care about avoids surprises.
- Add a WHERE clause on the update if you only want to overwrite under some conditions, for example only when the incoming data is newer than what's stored.
Getting the result back
Add RETURNING to get the row's ID and values after the statement. With DO UPDATE, you get the row whether it was inserted or updated. With DO NOTHING, nothing is returned when a conflict occurred, so your code should then select the existing row if it needs it.
Testing the fix
Race conditions rarely show up when you click through an app by hand, so test them on purpose:
- Write a small script that sends the same request twenty times in parallel and then counts rows.
- Replay a webhook several times quickly from your provider's dashboard or command-line tool on a staging copy.
- Watch your logs for unique-violation errors after deploying; a few are expected if any old code paths remain.
What upsert doesn't solve
- It's per statement. If your logic spans several tables, wrap it in a transaction and think about which constraints protect each step.
- It doesn't replace idempotency for payments. For charging cards or calling external services, use idempotency keys with the payment provider as well, so a retry doesn't charge twice.
- ORMs vary. Many ORMs expose upsert helpers that generate
ON CONFLICTfor you; check the generated SQL to be sure it targets the right constraint. - Other databases differ. MySQL and SQLite have their own upsert syntax. The idea is the same, the details aren't.
A worked example
Consider a hypothetical Austin meal-kit startup whose webhook handler created an order whenever the payment provider sent a "payment succeeded" event. On busy evenings, the provider's retries occasionally produced two orders for one payment, and two boxes were packed. The team added a unique constraint on the provider's event ID in a processed-events table, merged the existing duplicates by hand, and changed the handler to insert the event ID with ON CONFLICT DO NOTHING first, creating an order only when that insert actually returned a row.
FAQ
Is ON CONFLICT slower than a normal insert?
The difference is small for typical app workloads, and it replaces a separate select, so total work is often similar or lower.
Can I use it without a unique constraint?
No. The conflict detection relies on a unique index or constraint, which is also what protects your data.
What about deleting duplicates already in my table?
Clean them up first, keeping the right row in each group, then add the constraint.
Does it work for bulk inserts?
Yes, it applies to each row in a multi-row insert, though a single statement can't update the same row twice.
If duplicate records keep appearing in your app's database, send us a short description of where they show up.