Postgres Upsert: INSERT ON CONFLICT in Practice

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

PostgreSQL doesn’t have an UPSERT keyword. It has INSERT ... ON CONFLICT, which has been the canonical way to “insert or update” since Postgres 9.5 in 2016. The mechanism is simple in theory and full of sharp edges in practice — wrong conflict target, missing partial-index predicate, accidental row locks on every insert, returning the wrong row when DO NOTHING skips. This post is the field guide for engineers who actually have to ship upserts against a real Postgres database.

In this post, we’ll cover:

• The core syntax — ON CONFLICT DO UPDATE vs DO NOTHING
• The conflict target — picking the right index, including partial uniques
• The EXCLUDED pseudo-table — referencing the would-be-inserted row
• Bulk upserts — multi-row inserts and unnest() patterns
• RETURNING with DO NOTHING — the common surprise
• Concurrency and locking — what ON CONFLICT actually locks
• Postgres 15+ MERGE — when (and why not) to switch
• Pitfalls — the mistakes I see in code review every week

The core syntax

A Postgres upsert is an INSERT with an ON CONFLICT clause. There are two variants: DO UPDATE (replace the existing row) and DO NOTHING (skip the insert silently).

-- DO UPDATE: insert if new, update if a unique constraint conflicts
INSERT INTO users (email, name, last_login_at)
VALUES ('jane@example.com', 'Jane', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  last_login_at = EXCLUDED.last_login_at;

-- DO NOTHING: insert if new, skip silently if it would conflict
INSERT INTO event_dedup (event_id, payload)
VALUES ('evt_abc123', '{"...":"..."}')
ON CONFLICT (event_id) DO NOTHING;

The ON CONFLICT (column) form names a column or list of columns that has a UNIQUE constraint or PRIMARY KEY. The DO UPDATE clause then runs as if it were a regular UPDATE statement, with one extra trick: the EXCLUDED pseudo-table, which holds the values that would have been inserted. This is how you write “set name to whatever the caller passed” without repeating the literal in the UPDATE clause.

DO NOTHING is what you reach for when you want idempotent inserts — webhook handlers, idempotency keys, deduplication tables. It produces no error, no row change, no side effect. It is much faster than DO UPDATE because Postgres can skip the update path entirely once it sees a conflict.

Picking the right conflict target

The conflict target is the part after ON CONFLICT. It tells Postgres which unique constraint or unique index to monitor. Get this wrong and your upsert silently turns into a regular INSERT that fails on the next conflict, or — worse — succeeds and produces duplicate rows.

There are three legal forms:

-- 1. By column name (must match a unique constraint or unique index exactly)
ON CONFLICT (email) DO UPDATE SET ...

-- 2. By multiple columns (composite unique key)
ON CONFLICT (tenant_id, email) DO UPDATE SET ...

-- 3. By constraint name
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET ...

You cannot write ON CONFLICT DO UPDATE without a target. (ON CONFLICT DO NOTHING is allowed without a target — it skips on any unique violation.) The columns named in the target must match a unique index exactly, including the index’s predicate if it’s partial.

Partial unique indexes

This is the trap that catches everyone the first time. Consider a soft-delete pattern:

-- Allow multiple deleted rows with the same email,
-- but only one active row per email.
CREATE UNIQUE INDEX users_email_active_idx
  ON users (email)
  WHERE deleted_at IS NULL;

To target this index in an upsert, you must repeat the predicate:

-- This works
INSERT INTO users (email, name) VALUES ('jane@example.com', 'Jane')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET name = EXCLUDED.name;

-- This fails: "there is no unique or exclusion constraint matching the ON CONFLICT specification"
INSERT INTO users (email, name) VALUES ('jane@example.com', 'Jane')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

Postgres needs the predicate to know which index you mean. The predicate in the ON CONFLICT clause must match the index definition, not the rows you happen to be inserting.

The EXCLUDED pseudo-table

EXCLUDED is the values you tried to insert. Inside the DO UPDATE SET clause it behaves like a one-row table with the same columns as the target. The original row is referenced by the table’s name (or its alias).

INSERT INTO products (sku, price, updated_at)
VALUES ('sku-1', 9.99, NOW())
ON CONFLICT (sku) DO UPDATE SET
  price = EXCLUDED.price,
  -- only update if the new price is actually different
  updated_at = CASE
    WHEN products.price <> EXCLUDED.price THEN EXCLUDED.updated_at
    ELSE products.updated_at
  END;

A useful pattern is the conditional update — update only when the new value is different, or only when the new value is “newer”. Without the condition you’ll touch the row (and the WAL, and replication, and triggers) on every upsert, even when nothing changed.

-- Last-write-wins by timestamp: only update if the new row is newer
INSERT INTO sensor_readings (sensor_id, value, observed_at)
VALUES ('s-1', 42.7, '2026-04-30T12:00:00Z')
ON CONFLICT (sensor_id) DO UPDATE SET
  value = EXCLUDED.value,
  observed_at = EXCLUDED.observed_at
WHERE sensor_readings.observed_at < EXCLUDED.observed_at;

The WHERE clause at the end of DO UPDATE is a row-level filter. If it evaluates to false, the conflicting row is left alone — but the insert still counts as “consumed” for RETURNING purposes, which we’ll come back to.

Bulk upserts

You can put more than one row in a single INSERT ... ON CONFLICT. This is usually what you want — one network round-trip, one statement-level lock acquisition, one pass through the index.

INSERT INTO products (sku, name, price)
VALUES
  ('sku-1', 'Widget', 9.99),
  ('sku-2', 'Gadget', 14.99),
  ('sku-3', 'Gizmo', 19.99)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price;

Two things to watch for at scale:

Duplicate keys inside a single statement are an error, not a conflict. If your batch has two rows with sku = 'sku-1', Postgres raises cannot affect row a second time. ON CONFLICT only resolves conflicts between the new rows and existing rows in the table, not between new rows in the same statement. Deduplicate the batch before sending it.

For very large batches, unnest() beats raw VALUES. Postgres has to parse each placeholder individually, and most drivers send each row as a separate parameter set. unnest() lets you pass column-typed arrays and avoid that overhead:

INSERT INTO products (sku, name, price)
SELECT * FROM unnest(
  $1::text[],
  $2::text[],
  $3::numeric[]
) AS t(sku, name, price)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price;

The driver sends three arrays instead of 3 * N scalars. For batches of a few thousand rows the difference is in the tens of milliseconds; for hundreds of thousands of rows it’s the difference between a 30-second batch and a 3-second one.

RETURNING with DO NOTHING

The single most common surprise in upsert code: RETURNING only returns rows that the statement actually inserted or updated. With DO NOTHING, that means rows that conflicted are not returned at all.

-- If the row already exists, this returns ZERO rows.
INSERT INTO users (email, name)
VALUES ('jane@example.com', 'Jane')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Code that does INSERT ... ON CONFLICT DO NOTHING RETURNING id and treats the result as “the id of the row, whether new or existing” is a bug waiting to happen. The fix depends on what you actually want:

-- Option 1: DO UPDATE with a no-op SET, so RETURNING always fires
-- (the trade-off is you touch the row's heap tuple and trigger any
-- ON UPDATE triggers, even when nothing logically changed)
INSERT INTO users (email, name)
VALUES ('jane@example.com', 'Jane')
ON CONFLICT (email) DO UPDATE SET email = users.email
RETURNING id;

-- Option 2: a CTE that falls back to a SELECT when DO NOTHING skipped
WITH ins AS (
  INSERT INTO users (email, name)
  VALUES ('jane@example.com', 'Jane')
  ON CONFLICT (email) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM users WHERE email = 'jane@example.com'
LIMIT 1;

Option 2 avoids the unnecessary write but requires Postgres to look the row up twice. For high-write paths option 1 is usually the right choice, scoped to a column whose self-assignment is harmless. Note that Postgres still writes a new tuple on DO UPDATE even with a no-op SET, so this isn’t free at scale — see the concurrency section below.

Concurrency and locking

INSERT ... ON CONFLICT is atomic per row. Either the new row is inserted, or the existing row is updated, in a single operation that another session cannot observe halfway through. Under the hood Postgres takes a row-level FOR UPDATE lock on the conflicting row before running DO UPDATE. That has two practical consequences worth understanding before you put upserts in a hot path.

First, DO UPDATE blocks if another transaction holds a conflicting row lock — even if your DO UPDATE would have been a no-op. Second, DO NOTHING does not take a row-level lock on the conflicting row. It simply walks away. That makes DO NOTHING substantially cheaper for idempotency-key-style writes where you only care that the row exists.

For high write rates against the same key, the right move is often to batch updates outside Postgres (debouncing, queuing) rather than to issue a stream of single-row upserts that serialize on the same FOR UPDATE lock. The Postgres documentation on row-level locks is the place to go for the precise semantics.

Postgres 15+ MERGE

Postgres 15 added the SQL-standard MERGE statement. MERGE lets you do INSERT, UPDATE, and DELETE against a target table in a single statement based on a WHEN MATCHED / WHEN NOT MATCHED join.

MERGE INTO products t
USING (VALUES ('sku-1', 9.99)) AS s(sku, price)
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET price = s.price
WHEN NOT MATCHED THEN INSERT (sku, price) VALUES (s.sku, s.price);

MERGE is more powerful than ON CONFLICT — it can delete on match, conditionally branch, and operate on rows joined from another table without first staging them as VALUES. But it has two real drawbacks for typical upsert workloads:

MERGE is not concurrency-safe the way INSERT ... ON CONFLICT is. If two transactions concurrently MERGE a row that doesn’t yet exist, both can hit the WHEN NOT MATCHED branch and both will try to insert. The second hits a unique-constraint violation and raises an error. ON CONFLICT was designed specifically to make this case work safely; MERGE was not. The Postgres committers have written about this — see Peter Eisentraut’s MERGE notes.

MERGE does not have a RETURNING clause. This was added in Postgres 17, so unless you’re on Postgres 17+ you cannot get the affected rows back from a MERGE.

For everyday “insert or update” against a table with a unique constraint, stick with INSERT ... ON CONFLICT. Reach for MERGE when you actually need its extra capabilities — joining to another table, deleting on match, or conditionally choosing among multiple WHEN branches.

Pitfalls

A handful of mistakes that show up over and over in upsert code:

Forgetting that DO UPDATE fires ON UPDATE triggers. If your table has audit triggers or updated_at set by a trigger, every conflict — even a no-op SET — runs the trigger. If your trigger is expensive or has side effects, scope the update with a WHERE clause that filters out unchanged rows.

Mixing up EXCLUDED and the target table inside DO UPDATE SET. EXCLUDED refers to the values the caller tried to insert; the target table name (or its alias) refers to the existing row. SET price = EXCLUDED.price overwrites with the new price; SET price = products.price is a no-op. Swap them by accident and you’ll silently keep the stale value. (EXCLUDED is only valid inside the SET and WHERE clauses of ON CONFLICT DO UPDATE — it cannot appear in RETURNING. RETURNING products.* gives you the row as it stands after the upsert.)

Targeting the wrong index. ON CONFLICT (lower(email)) looks valid but only works if you have a unique index on lower(email) — not just on email. Postgres matches the conflict target against indexes by their full definition, including expressions and predicates.

Default-value drift. INSERT ... ON CONFLICT DO UPDATE does not re-run column defaults on update. If your table has created_at DEFAULT NOW() and your upsert sets it from EXCLUDED.created_at, every conflict will overwrite the original created_at with the new one. Either omit created_at from the DO UPDATE SET list or explicitly preserve it: created_at = products.created_at.

Forgetting that sequences advance on conflicts. When INSERT fails the conflict path, the sequence has already been incremented. Heavy upsert traffic against tables with SERIAL or IDENTITY keys produces visible gaps in id sequences. This is by design, not a bug — sequences are not transactional. If you need gap-free ids, don’t use a sequence for the workload.

Try it on your own database

You can run every example in this post against a real Postgres database in QueryPlane’s SQL playground — no account or local install needed. For pre-flight checks on more complex upserts, paste the query into the SQL syntax checker to catch the most common parse and reference errors before they hit production. When you’re ready to run it against your own data, connect your Postgres database to QueryPlane and you can write the SQL with AI assistance, watch the affected rows in RETURNING, and turn the result into a shareable internal tool in the same session.

Frequently asked questions

What is the equivalent of UPSERT in PostgreSQL? PostgreSQL has no UPSERT keyword. The equivalent is INSERT ... ON CONFLICT (target) DO UPDATE SET ... for “insert or update,” and INSERT ... ON CONFLICT (target) DO NOTHING for “insert if not present.” Both have been available since Postgres 9.5 and are the recommended primitives for concurrent insert-or-update workloads.

What is the difference between ON CONFLICT DO UPDATE and ON CONFLICT DO NOTHING? DO UPDATE runs an UPDATE against the conflicting row using the values you specify, usually pulled from the EXCLUDED pseudo-table. DO NOTHING silently skips the row. The big practical difference is RETURNING: with DO NOTHING, conflicted rows are not returned, which means you cannot get the existing row’s id without a follow-up SELECT. DO UPDATE always returns the affected row, even if you set it to its existing value.

Does ON CONFLICT take a row-level lock? Yes — DO UPDATE acquires an exclusive row-level lock on the conflicting row before applying the update, the same lock a regular UPDATE would take. DO NOTHING also acquires a brief lock to evaluate the conflict but releases it immediately. Heavy upsert traffic against the same row will serialize on this lock and look like contention even though there is no explicit SELECT FOR UPDATE.

Can I use a partial unique index as the conflict target? Yes, but you must repeat the partial index predicate in the ON CONFLICT clause: ON CONFLICT (col) WHERE active = true DO UPDATE .... Without the WHERE clause Postgres cannot prove the partial index is the right index to use and the statement will fail with “no unique or exclusion constraint matching the ON CONFLICT specification.”

When should I use MERGE instead of INSERT ON CONFLICT? Use MERGE (Postgres 15+) when you need to combine INSERT, UPDATE, and DELETE against a target table from a single source query, especially for ETL and CDC merges where the operation depends on row-level conditions. Use INSERT ON CONFLICT for the common “insert or update on a unique key” pattern — it is concurrency-safe under concurrent writers in a way MERGE is not, because MERGE can still raise unique-violation errors under concurrent inserts.

Why does my SERIAL sequence have gaps after upserts? Sequences are not transactional. Every failed INSERT (including ones that hit the ON CONFLICT path) has already advanced the sequence before the conflict is detected, and the advance is not rolled back. Heavy upsert traffic produces visible id gaps. This is by design — if you need gap-free ids, do not use a sequence.

Wrapping up

Upsert in Postgres is INSERT ... ON CONFLICT. The mental model is small — pick a unique constraint as the conflict target, decide whether to DO UPDATE or DO NOTHING, reference the new values via EXCLUDED — but the practical details are where production bugs live. Partial unique indexes need their predicate in the ON CONFLICT clause. RETURNING is silent on DO NOTHING. DO UPDATE takes a row-level lock and fires triggers. Bulk upserts need deduplication and benefit from unnest(). MERGE is more flexible but is not the concurrency-safe primitive that ON CONFLICT is.

When you write upsert code, write it deliberately: pick the conflict target on purpose, write the WHERE clause that prevents no-op writes, and decide upfront whether you want the inserted row, the updated row, or both back from RETURNING. The syntax is fifteen years old at this point — most of the surprises come from the parts that look simpler than they are.