UPSERT Patterns: ON CONFLICT and Idempotent Writes

To write "insert if it doesn't exist, update if it does" logic, the beginner approach is to SELECT first to check, INSERT if absent, and UPDATE if present. Three statements, two round trips, and a concurrency race condition hiding in it — two requests SELECT at the same time, both say "not present", then both INSERT, and the unique constraint blows up in your face. To fix this pitfall you either wrap it in a transaction with locks, or write a pile of retries. Actually, one SQL statement can solve it — that's UPSERT.

UPSERT (UPDATE or INSERT) is a write pattern every backend engineer should master. It fuses "check + insert + update" into a single atomic statement, eliminating races and naturally supporting idempotency — retry as many times as you like, the result is always the same. This article covers PostgreSQL's INSERT ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, the design of idempotent scenarios, and the practical pitfalls (partial indexes, multi-column conflicts, concurrent lock behavior) all at once.

1. What Is UPSERT: The Atomic Union of Insert and Update

UPSERT isn't a SQL keyword, it's a semantic: given a row, update it if there's a conflict, insert it if there isn't. A "conflict" means violating a unique constraint (primary key or UNIQUE).

First, look at how annoying the problem is. Suppose there's a user_stats table and the business wants to "increment the login count by user_id":

-- Naive approach (anti-pattern)
SELECT count FROM user_stats WHERE user_id = 42;
-- Application-layer decision: UPDATE if found, INSERT if not

This logic has three problems:

1. Non-atomic: There's a time window between SELECT and INSERT/UPDATE. Under concurrency, two requests both "find nothing", both INSERT, and the second one hits the unique constraint and errors.
2. One extra round trip: A single operation needs at least two DB round trips, doubling latency.
3. Retry isn't idempotent: A retry on network jitter may INSERT an extra dirty row.

UPSERT solves all three at once. It tells the database: "you check whether there's a conflict for me; if there is, update according to the plan I give you; if not, insert. The whole thing happens in one statement, one atomic operation." The database handles conflict detection itself, so there's no "time window between check and insert" at all — the race condition vanishes at the root.

Implementations across mainstream databases:

PostgreSQL's ON CONFLICT is the cleanest design of the bunch — it explicitly tells you "which column to check for conflict on", while MySQL's ON DUPLICATE KEY UPDATE is implicit (it automatically finds the unique key that matched), which is one of the pitfalls covered later. Let's start with PG.

2. PostgreSQL: The INSERT ON CONFLICT Three-Part Form

PG's UPSERT syntax looks like this:

INSERT INTO user_stats (user_id, login_count, last_login, updated_at)
VALUES (42, 1, now(), now())
ON CONFLICT (user_id)          -- conflict judgment column
DO UPDATE SET                  -- what to do on conflict
    login_count = user_stats.login_count + 1,
    last_login = now(),
    updated_at = now();

You can read it in three parts:

1. INSERT INTO ... VALUES (...): A normal insert statement; write it however you'd normally write it.
2. ON CONFLICT (user_id): Specifies "which column/constraint to use for conflict judgment". Here it tells PG: if a unique constraint conflict occurs on user_id during insert (i.e., a row with user_id=42 already exists), don't error out — take the branch below.
3. DO UPDATE SET ...: The update action on conflict. Here you can reference two special tables: EXCLUDED represents "the row being inserted" (i.e., the new value from VALUES), while user_stats (the table name) represents "the existing row" (the old value).

Understanding the EXCLUDED pseudo-table is the key to writing PG UPSERT well. It is exactly "the INSERT data that triggered the conflict". Look at this comparison:

-- To overwrite the old value with the new one: reference EXCLUDED
INSERT INTO config (key, value, updated_at)
VALUES ('timeout', '30s', now())
ON CONFLICT (key)
DO UPDATE SET
    value = EXCLUDED.value,          -- use the new value
    updated_at = EXCLUDED.updated_at;

-- To accumulate/merge: reference the table itself (old value)
INSERT INTO user_stats (user_id, login_count)
VALUES (42, 1)
ON CONFLICT (user_id)
DO UPDATE SET
    login_count = user_stats.login_count + EXCLUDED.login_count;
-- old value + new value

A common pitfall: you can't reference the VALUES(1) literal inside DO UPDATE SET — you have to get it via EXCLUDED.column_name. Many people instinctively write login_count = login_count + 1 (wanting to reference the 1 in the INSERT), and PG will report column reference is ambiguous or simply resolve it to the column in the table. Remember: in the DO UPDATE clause, the table name = the old row, EXCLUDED = the new row.

Another key point: the column in ON CONFLICT (user_id) must have a unique constraint or unique index (UNIQUE or PRIMARY KEY both work). If you specify a column that has no unique constraint, PG will outright error with there is no unique or exclusion constraint matching the ON CONFLICT specification. This is a hard PG requirement — it needs to know what to judge conflicts against.

3. DO NOTHING: The "Silent" Idempotent Version

DO UPDATE means "change it on conflict". Sometimes you don't need to change anything — you just need "don't error". For example, when recording an idempotent webhook event — the same event_id arriving ten times should be persisted once, and the rest ignored. Use DO NOTHING here:

INSERT INTO webhook_events (event_id, payload, received_at)
VALUES ('evt_123', '{"type":"ping"}', now())
ON CONFLICT (event_id) DO NOTHING;

DO NOTHING means literally: on conflict, do nothing (neither error nor update) and just discard this INSERT. It's equivalent to "this event is already in the database, ignore this one".

This is much cleaner than DO UPDATE SET col = col (pretending to update) — DO NOTHING doesn't fire triggers, doesn't update updated_at, doesn't produce a WAL log write, and doesn't let logical replication sync this change to replicas. It is a true "silent ignore".

The most classic use of DO NOTHING is idempotent consumption. Message queues, webhooks, callbacks — these channels that inherently redeliver — paired with an idempotency key + ON CONFLICT DO NOTHING, can guarantee "multiple processing = one processing". This is one of the cheapest and most reliable idempotency schemes in distributed systems, far less hassle than maintaining a Redis dedup set in the application layer.

Note one detail: ON CONFLICT DO NOTHING without a column means "ignore any unique constraint conflict". In practice I prefer to specify the column explicitly (ON CONFLICT (event_id) DO NOTHING) to make the intent clear — otherwise, if the table has multiple unique constraints, a hit on any of them will be ignored, which can mask bugs.

4. MySQL: ON DUPLICATE KEY UPDATE

MySQL's UPSERT syntax is different:

INSERT INTO user_stats (user_id, login_count, last_login, updated_at)
VALUES (42, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    updated_at = NOW();

The semantics are similar to PG's — on insert, if any UNIQUE/PRIMARY KEY conflict is triggered, perform the UPDATE. But there are several key differences to watch for:

1. Conflict judgment is implicit. MySQL doesn't let you specify "which column to judge by"; it automatically matches "the unique key that caused the conflict". If the table has multiple unique keys, whichever one hits is the one used. This is the biggest design flaw of MySQL's syntax — the behavior is non-deterministic. PG's ON CONFLICT (col) is explicit, with strong determinism. The pitfalls section below expands on this.

2. Referencing the new value uses the VALUES() function (before MySQL 8.0.19) or AS new_row_name (8.0.20+). This is the concept corresponding to PG's EXCLUDED, but the syntax is convoluted:

-- MySQL 8.0.19 and earlier: use the VALUES() function to reference the newly inserted value
INSERT INTO config (key, value) VALUES ('timeout', '30s')
ON DUPLICATE KEY UPDATE value = VALUES(value);   -- overwrite with the new value

-- MySQL 8.0.20+: the VALUES() function is deprecated, aliasing is recommended
INSERT INTO config (key, value) VALUES ('timeout', '30s') AS new
ON DUPLICATE KEY UPDATE value = new.value;

Here's a big pitfall: MySQL 8.0.20 marked the VALUES() function as deprecated, and starting in 8.0.21 it warns in some scenarios. If your project still uses the old form col = VALUES(col), watch out when upgrading MySQL. New projects should use the AS new alias syntax directly — it's clearer and aligns with PG's EXCLUDED approach.

3. By default it fires all the side effects of INSERT. Even when taking the UPDATE branch, MySQL may still fire BEFORE/AFTER INSERT triggers (and then the UPDATE triggers), and the auto_increment counter advances in some versions — this causes ID gaps. This is a quirk of MySQL's implementation; PG doesn't behave this way.

5. Idempotent Scenarios: Why UPSERT Is a Retry's Lifesaver

The most valuable thing about UPSERT isn't saving two lines of code; it's that it makes "retry" safe.

In distributed systems, "retry on failure" is everyday life: an HTTP request times out so you retry once, a message queue consumption fails so it's redelivered, an ETL job crashes so you rerun it, a scheduled task is mistakenly fired twice. The premise of retry is that the operation is idempotent — running it once and running it ten times leaves the same final state. Otherwise retry just equals "double charge / double order / duplicate message".

UPSERT naturally provides row-level idempotency. Typical scenarios:

Scenario 1: Data sync (external system → our database). Sync the upstream user table every day. Syncing the same user_id ten times should produce the same result as syncing it once:

INSERT INTO users (id, name, email, synced_at)
VALUES (:id, :name, :email, now())
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email,
    synced_at = now();

A full sync job can be rerun any number of times safely — insert if absent, overwrite with the latest value if present. This is far safer than "DELETE then INSERT" (a crash after DELETE loses data) and far more efficient than "SELECT then decide" (one SQL statement does it).

Scenario 2: The idempotency key for webhooks / message consumption. Stripe, GitHub, and WeCom callbacks may all redeliver the same event. Give each event a unique event_id and use ON CONFLICT DO NOTHING on insert:

INSERT INTO received_events (event_id, source, payload, created_at)
VALUES (:event_id, :source, :payload, now())
ON CONFLICT (event_id) DO NOTHING;
-- Application layer checks the affected row count: 1 means first time, 0 means duplicate → skip duplicates

This is the standard pattern for handling "at-least-once delivery" messaging systems. It's more durable than using Redis SETNX for dedup in the application layer (Redis loses data on restart) and far less prone to races.

Scenario 3: Resumable ETL / batch jobs. An import job that crashed halfway through can't re-insert the rows it already imported when rerun. UPSERT by the business primary key makes a rerun naturally an overwrite, with no need to manually maintain a "how far have I processed" checkpoint.

Scenario 4: Counters and statistical aggregation. Aggregate telemetry data once a minute into a stats table. Because the upstream may replay, use UPSERT to ensure multiple aggregations produce consistent results:

INSERT INTO daily_stats (stat_date, metric, value)
VALUES ('2026-07-22', 'login_count', 1234)
ON CONFLICT (stat_date, metric) DO UPDATE SET
    value = EXCLUDED.value;   -- overwrite with the latest full value, not accumulate

Note that whether to "overwrite" or "accumulate" depends on upstream semantics: if the upstream always sends the full value (final state), overwrite; if the upstream sends deltas, accumulate (value = daily_stats.value + EXCLUDED.value). UPSERT itself doesn't guarantee idempotency; what guarantees idempotency is "how you use EXCLUDED". An accumulating UPSERT that's rerun will accumulate again — that's not idempotency, that's a bug.

One-line summary of this section: for any write that needs to be "retryable", the first reflex should be UPSERT, not SELECT-then-INSERT. The former pushes idempotency down into the database; the latter leaves the race risk in the application layer.

6. Where ON CONFLICT's Constraints Come From: UNIQUE, PK, and Partial Indexes

PG's ON CONFLICT can specify more than just column names — it can also leverage some less obvious constraint forms. Understanding its "constraint matching" rules unlocks more advanced usage.

Form 1: Specify the column directly. Most common; requires a UNIQUE or PRIMARY KEY on that column:

ON CONFLICT (user_id)

Form 2: Specify the constraint name. Directly name a specific constraint, more explicit:

ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE SET ...

This is useful when your table has multiple unique constraints and you want precise control over which one is used.

Form 3: Specify composite columns. Multi-column unique constraint:

CREATE TABLE favorites (user_id int, article_id int, PRIMARY KEY (user_id, article_id));

INSERT INTO favorites (user_id, article_id) VALUES (42, 7)
ON CONFLICT (user_id, article_id) DO NOTHING;

Note that ON CONFLICT (a, b) must match a (a, b) unique constraint — it's not "conflict on a or b", it's "conflict on the (a, b) combination".

Form 4: Partial Index — the killer feature most easily overlooked. PG allows ON CONFLICT on a partial index that has a WHERE:

-- Each user can have only one "active" subscription, but multiple historical records are allowed
CREATE UNIQUE INDEX active_subscription_unique
ON subscriptions (user_id)
WHERE status = 'active';

INSERT INTO subscriptions (user_id, status, plan) VALUES (42, 'active', 'pro')
ON CONFLICT (user_id) WHERE status = 'active'
DO UPDATE SET plan = EXCLUDED.plan, updated_at = now();

This form is extremely powerful: it enforces uniqueness only on rows where status = 'active', while rows in other states don't affect each other. UPSERT also matches only this partial index. For implementing a "soft unique constraint" (conditional uniqueness), this is the only clean solution — a hundred times more elegant than locking in the application layer.

Pitfall: the target of ON CONFLICT must exactly match some index/constraint, including the WHERE clause. You can't write ON CONFLICT (user_id) (missing the WHERE) against a partial index on (user_id) WHERE status='active' — PG will error saying it can't find a matching constraint. You must write ON CONFLICT (user_id) WHERE status = 'active', aligned verbatim.

If you're unsure which index a UPSERT uses, run EXPLAIN to look at the execution plan — an On Conflict node and the conflict-arbitration index it chose will appear, clear at a glance.

7. Pitfalls: Multi-Column Conflicts, RETURNING Detection, and Concurrent Lock Behavior

UPSERT looks simple, but there are pitfalls you don't feel until you've stepped on them. This section is the most valuable part of the whole article.

Pitfall 1: MySQL with Multiple Unique Keys Has Non-Deterministic Conflict Behavior

PG's ON CONFLICT (col) explicitly specifies the conflict column, with strong determinism. MySQL's ON DUPLICATE KEY UPDATE is implicit — it finds "the first unique key that hits". If the table has both a PRIMARY KEY and another UNIQUE KEY, and your INSERT data violates both constraints at once, MySQL will only take the UPDATE branch on whichever one it internally decides, and the other is "incidentally" ignored.

CREATE TABLE t (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  name VARCHAR(100)
);

-- Assume id=1, email='[email protected]' already exists
INSERT INTO t (id, email, name) VALUES (1, '[email protected]', 'Bob')
ON DUPLICATE KEY UPDATE name = 'Bob';
-- Both the primary key and email conflict; MySQL only takes one UPDATE branch

In this "two keys conflict at once" situation, your UPDATE might be based on "primary key conflict" or "email conflict", and the business semantics become ambiguous. Engineering countermeasure: leave only one "semantically authoritative" unique key per table for UPSERT; if other unique keys are unavoidable, ensure they aren't simultaneously hit in business terms. Or just switch to PG and spell out the conflict column.

Pitfall 2: Detecting INSERT vs. UPDATE with RETURNING

The RETURNING clause is convenient for returning data after a UPSERT, but by default it doesn't tell you whether this was an insert or an update:

INSERT INTO user_stats (user_id, login_count) VALUES (42, 1)
ON CONFLICT (user_id) DO UPDATE SET login_count = user_stats.login_count + 1
RETURNING user_id, login_count;
-- Whether it was an insert or an update, it returns one row; you can't tell which

The application layer often needs to know "was this an insert or an update" (for example, to decide whether to send a "new user registered" notification). PG offers an extremely elegant solution — the xmax system column:

INSERT INTO user_stats (user_id, login_count) VALUES (42, 1)
ON CONFLICT (user_id) DO UPDATE SET
    login_count = user_stats.login_count + 1,
    updated_at = now()
RETURNING
    user_id,
    (xmax = 0) AS inserted;   -- true = this was an INSERT, false = it was an UPDATE

How it works: xmax is PG's internal transaction ID field. A newly inserted row has xmax = 0 (not yet modified by any transaction), while an old row hit by UPDATE has xmax set to the current transaction ID that initiated the update (non-zero). So xmax = 0 precisely determines "newly inserted". This is a widely used idiom in the PG community; remembering it saves you a pile of conditional logic.

MySQL has no RETURNING (only 8.0.21+, and with limited functionality); detecting insert vs. update relies on the application layer looking at affected_rows — MySQL's affected_rows has special semantics in UPSERT: an actual INSERT counts as 1, an actual UPDATE counts as 2 (not 1), and an UPDATE with no changes counts as 0. This "UPDATE = 2" counting is a historical legacy; relying on it is counterintuitive, so use it cautiously.

Pitfall 3: Locks and Deadlocks in Concurrent UPSERT

UPSERT itself is atomic, but concurrently executing multiple UPSERTs can still deadlock. Typical scenario: two transactions simultaneously UPSERT different subsets of the same batch of rows, in different orders.

-- Transaction A
INSERT INTO t (id, val) VALUES (1, 'a'), (2, 'b') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;
-- Transaction B
INSERT INTO t (id, val) VALUES (2, 'x'), (1, 'y') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

A locks id=1 first then id=2; B locks id=2 first then id=1 — a classic deadlock. PG will abort one of the transactions when it detects the deadlock (deadlock detected). The countermeasure is simple: when batch UPSERTing, sort by the conflict column to ensure all transactions acquire locks in the same order:

-- Sort by id in the application layer before assembling
INSERT INTO t (id, val) VALUES (1, ...), (2, ...) ON CONFLICT (id) DO UPDATE SET ...;

Also, PG's UPSERT, on conflict, needs to "insert" first and then "discover the conflict", then handle it with the "speculative insertion" mechanism — it first tries to insert a row version with a speculative marker, commits it only if no conflict is found, and rolls back the speculative insertion to do an UPDATE if there is a conflict. This mechanism ensures UPSERT doesn't miss conflicts due to races under high concurrency, but the cost is that it's somewhat slower than a plain INSERT. If in your scenario "the vast majority are new inserts", UPSERT's overhead is slightly higher than a raw INSERT — this is trading universality for safety.

Practical advice: For hotspot rows (the same row being high-frequency UPSERTed), consider using application-layer queuing or SELECT FOR UPDATE to explicitly serialize — more efficient than letting the database repeatedly handle speculative insertion. For batch sync of cold data, UPSERT is the best choice.

8. In Practice: Batch UPSERT, the Swiss Army Knife of ETL

Single-row UPSERT is the foundation; what actually moves data in production is batch UPSERT — one statement processing hundreds or thousands of rows, the workhorse of ETL and data sync.

PG Batch UPSERT

INSERT INTO products (sku, name, price, stock, updated_at)
VALUES
    ('A001', 'Mouse', 29.9, 100, now()),
    ('A002', 'Keyboard', 99.0, 50, now()),
    ('A003', 'Monitor', 1299.0, 20, now()),
    ('A004', 'Webcam', 199.0, 0, now())
ON CONFLICT (sku) DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = EXCLUDED.stock,
    updated_at = EXCLUDED.updated_at
WHERE products.price IS DISTINCT FROM EXCLUDED.price
   OR products.stock IS DISTINCT FROM EXCLUDED.stock
   OR products.name IS DISTINCT FROM EXCLUDED.name;

Two advanced techniques here are worth covering:

Technique 1: EXCLUDED automatically corresponds to each row in the batch scenario. Each row's data in VALUES is paired with the old row it triggered, with no effort on your part. If 10 rows come in — 3 new, 7 updates — PG handles them separately automatically, all in one statement.

Technique 2: The WHERE clause implements "update only when changed". By default, DO UPDATE executes an update on all conflicting rows, even if the old and new values are identical. This brings side effects: updated_at gets refreshed, triggers fire, WAL logs get written, and logical replication syncs this "no-change update" to replicas. Adding a WHERE products.column IS DISTINCT FROM EXCLUDED.column condition updates only when something actually changed. IS DISTINCT FROM is safer than <> — it includes NULL in the comparison (NULL <> NULL is NULL, a missed judgment; NULL IS DISTINCT FROM NULL is false, correct).

This small optimization pays off enormously on large tables. I took over a sync job that full-UPSERTed 500,000 rows a day; after adding WHERE ... IS DISTINCT FROM, the actual number of updated rows dropped from 500,000 to an average of 2,000 (the vast majority of data was unchanged), execution time fell from 8 minutes to 30 seconds, and replica lag disappeared.

MySQL Batch UPSERT

INSERT INTO products (sku, name, price, stock, updated_at)
VALUES
    ('A001', 'Mouse', 29.9, 100, NOW()),
    ('A002', 'Keyboard', 99.0, 50, NOW())
ON DUPLICATE KEY UPDATE
    name  = VALUES(name),
    price = VALUES(price),
    stock = VALUES(stock),
    updated_at = NOW();

MySQL doesn't support a WHERE filter for "don't update if unchanged", so every conflict results in a real change — another hard flaw of MySQL's syntax. Workarounds are using triggers or comparing in the application layer, neither elegant. If this overhead matters to you, seriously consider migrating to PG.

Batching and Transaction Boundaries

In practice, don't cram tens of thousands of rows into one UPSERT. A single SQL statement that's too large will blow up max_allowed_packet (MySQL) or cause a single transaction to run too long and hold locks too long (PG). Batch it, 500-2000 rows per batch:

# Pseudocode
BATCH = 1000
for i in range(0, len(rows), BATCH):
    chunk = rows[i:i+BATCH]
    db.execute(build_upsert_sql(chunk))

Whether each batch is its own transaction depends on the scenario: if a single batch failure should roll back the whole batch, use a transaction; if partial success is acceptable (an idempotent rerun will fill in), you can skip the transaction. For idempotent UPSERTs, I lean toward not opening large transactions, executing batches raw — a rerun can fill in anyway, a single failed batch can just be retried, and you avoid long transactions holding locks.

UPSERT in ORMs

Most ORMs wrap UPSERT, but the interfaces vary:

• Prisma: prisma.user.upsert({ where: { id }, create: {...}, update: {...} }). Note that its single-row upsert internally does a SELECT first then an INSERT/UPDATE — not a native ON CONFLICT — so its concurrency and performance are worse than native SQL. For batch scenarios, use $executeRaw to write SQL directly.
• SQLAlchemy: session.sync_insert(...) or dialect.insert(...).on_conflict_do_update(), which generates native ON CONFLICT.
• TypeORM: repository.upsert(...) (newer versions) uses native syntax.

One principle: for single-row, low-frequency, performance-insensitive UPSERTs, ORM methods are fine; for batch, high-frequency, performance-sensitive scenarios, write raw SQL directly. Much of UPSERT's value lies in SQL-level atomicity; wrapping it in an ORM dilutes that.

Summary

1. UPSERT = the atomic union of Insert + Update: One statement completes "insert if absent, update if present", eliminating the race and extra round trips of SELECT-then-INSERT, and is the first choice for retryable writes.
2. PG's ON CONFLICT is a cleaner design than MySQL's ON DUPLICATE KEY: PG explicitly specifies the conflict column, with high determinism and support for partial indexes; MySQL implicitly decides among multiple unique keys with non-deterministic behavior, and doesn't support "don't update if unchanged".
3. EXCLUDED (PG) / VALUES() (MySQL) is the key to referencing the new value: accumulate with table.col + EXCLUDED.col, overwrite with EXCLUDED.col; DO NOTHING is a true silent ignore — it doesn't fire triggers or write WAL, making it best for idempotent consumption.
4. Idempotency is designed, not given away by UPSERT: an overwrite-style UPSERT is safe to rerun; an accumulate-style UPSERT re-accumulates on rerun — pick the wrong semantics and UPSERT works against you.
5. Watch the three big pitfalls: MySQL's non-deterministic behavior with multiple unique keys, using (xmax = 0) in RETURNING to detect insert vs. update, and sorting batch UPSERTs by the conflict column to avoid deadlocks.

One-line summary: using SELECT to decide between INSERT/UPDATE leaves the race risk with yourself; UPSERT hands atomicity to the database. If "update if present, insert if not" can be solved in one SQL statement, don't split it into three.