The same upsert on PostgreSQL, MySQL and SQLite

"Insert this row, or update it if it's already there" is one of the most common writes an app makes, and every engine spells it differently. PostgreSQL and SQLite have INSERT ... ON CONFLICT, MySQL has INSERT ... ON DUPLICATE KEY UPDATE, and PostgreSQL 15 added MERGE on top. They look interchangeable. They aren't: they disagree on what happens when a second unique key collides, on duplicate keys inside one batch, on how many ids they burn, on what they tell you afterwards, and on how they behave when two sessions race for the same new key.

We ran the same upserts on PostgreSQL 18.6, MySQL 8.4.10 and SQLite 3.50 and wrote down what each did. Everything below is copied from those runs. The table in all the examples is the same:

sql
CREATE TABLE users (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- AUTO_INCREMENT in MySQL, INTEGER PRIMARY KEY in SQLite
  email    text UNIQUE NOT NULL,
  username text UNIQUE NOT NULL,
  visits   int  NOT NULL DEFAULT 1
);
INSERT INTO users (email, username) VALUES ('ana@example.com', 'ana'), ('ben@example.com', 'ben');

The three spellings

sql
-- PostgreSQL and SQLite: name the unique key, refer to the new row as EXCLUDED
INSERT INTO users (email, username) VALUES ('ana@example.com', 'ana')
ON CONFLICT (email) DO UPDATE SET visits = users.visits + 1;

-- MySQL 8.0.19+: no key named; the new row gets an alias
INSERT INTO users (email, username) VALUES ('ana@example.com', 'ana') AS new
ON DUPLICATE KEY UPDATE visits = users.visits + 1, username = new.username;

-- PostgreSQL 15+: MERGE, a join between a source and the table
MERGE INTO users u
USING (VALUES ('ana@example.com', 'ana')) AS s(email, username) ON u.email = s.email
WHEN MATCHED THEN UPDATE SET visits = u.visits + 1
WHEN NOT MATCHED THEN INSERT (email, username) VALUES (s.email, s.username);

MySQL still accepts the older VALUES(username) form for the new row's value, but 8.4 answers it with warning 1287 ("'VALUES function' is deprecated and will be removed in a future release"). Use the row alias in new code.

A second unique key: an error, or the wrong row

Our table has two unique keys. Here's an insert with a new email but a username that already belongs to Ana: ('ana.new@example.com', 'ana').

PostgreSQL with ON CONFLICT (email) DO UPDATE raised an error: duplicate key value violates unique constraint "users_username_key". ON CONFLICT only handles the key you name (or, with DO NOTHING and no target, any key). That's the behaviour you want: the statement didn't guess. SQLite did the same: UNIQUE constraint failed: users.username.

MySQL has no way to name the key, so it took the row behind whichever unique key collided and updated that. We had written ON DUPLICATE KEY UPDATE email = new.email, visits = users.visits + 1, so Ana's row quietly got the new email address. Affected rows 2, no warning:

text
+----+---------------------+----------+--------+
| id | email               | username | visits |
+----+---------------------+----------+--------+
|  1 | ana.new@example.com | ana      |      3 |

It gets worse when the new row collides with two different existing rows. We inserted ('ben@example.com', 'cy'), which matches Ben by email and Cy by username. MySQL updated one of them (Ben: visits went from 1 to 101) and ignored the other. The MySQL manual advises against ON DUPLICATE KEY UPDATE on tables with more than one unique key for exactly this reason. If you can't avoid it, make sure the SET list only changes columns that are safe to change whichever row it lands on.

SQLite 3.35 and later lets you list several conflict clauses, one per key, which is the most explicit of the three:

sql
INSERT INTO users (email, username) VALUES ('ana.new@example.com', 'ana')
ON CONFLICT (email)    DO UPDATE SET visits = visits + 1
ON CONFLICT (username) DO UPDATE SET email = excluded.email
RETURNING id, email, username;
-- (1, 'ana.new@example.com', 'ana')

The same key twice in one batch

Batches built from a CSV or a queue often contain the same key twice. The engines split three ways:

text
INSERT INTO users (email, username) VALUES ('dee@example.com', 'dee'), ('dee@example.com', 'dee2')
ON CONFLICT (email) DO UPDATE SET visits = users.visits + 1;

PostgreSQL: ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
            HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
MERGE (PG): ERROR:  MERGE command cannot affect row a second time
MySQL:      3 rows affected, one row, visits = 2
SQLite:     2 changes, one row, visits = 2 (RETURNING lists the same id twice)

PostgreSQL refuses because the result would depend on the order of rows in the batch. If you get this error, deduplicate before the insert (SELECT DISTINCT ON (email) ... ORDER BY email, updated_at DESC keeps the newest per key). MySQL and SQLite apply both rows in order, so the last one wins, which is fine only if the batch is sorted the way you think it is.

Burned ids

An upsert that ends up updating still takes an id from the sequence first, and that value is gone. On PostgreSQL, after a handful of single-row upserts Cy's row had id 4 (3 went to Ana's update), and after 100 ON CONFLICT DO NOTHING inserts of an existing row the next new user got id 109. MySQL with the default innodb_autoinc_lock_mode = 2 burned one id for each single-row ON DUPLICATE KEY UPDATE that hit an existing row in the same way: Dee got id 8 after four updates. SQLite burns them too if the table uses AUTOINCREMENT (Cy got 4 again); a plain INTEGER PRIMARY KEY table doesn't, because its next id is simply the largest one plus one (after five conflicts the next row got id 2).

Gaps are harmless unless something assumes ids are dense ("we have 2 million users, the max id says so"). What can hurt is a 32-bit int id on a table that's upserted all day: with millions of upserts a day and most of them updating, int runs out years earlier than the row count suggests. Use bigint. One thing that does not burn ids: MERGE only evaluates the identity default for rows it actually inserts. In our run the matched row used nothing and the new one got the next id.

What you get back

To tell inserts from updates:

sql
-- PostgreSQL: xmax is 0 on a freshly inserted row version
INSERT INTO users (email, username) VALUES ('ana@example.com','ana'), ('cy@example.com','cy')
ON CONFLICT (email) DO UPDATE SET visits = users.visits + 1
RETURNING id, email, visits, (xmax = 0) AS inserted;
--  1 | ana@example.com | 2 | f
--  4 | cy@example.com  | 1 | t

-- PostgreSQL 17+: MERGE has RETURNING and merge_action()
MERGE INTO users u USING (VALUES ('ben@example.com','ben'), ('fay@example.com','fay')) s(email, username)
ON u.email = s.email
WHEN MATCHED THEN UPDATE SET visits = u.visits + 1
WHEN NOT MATCHED THEN INSERT (email, username) VALUES (s.email, s.username)
RETURNING merge_action(), u.id, u.email, u.visits;
--  UPDATE |   2 | ben@example.com | 2
--  INSERT | 111 | fay@example.com | 1

The xmax = 0 trick works but relies on an implementation detail; from PostgreSQL 17, MERGE ... RETURNING merge_action() says it outright. SQLite has RETURNING (3.35+) but nothing in it that tells the two apart. MySQL has no RETURNING: ROW_COUNT() counts 1 per inserted row, 2 per updated row and 0 if the update changed nothing, and LAST_INSERT_ID() only means something after an insert. A batch gives you the sum, so the 1,500,000 "affected rows" in our 1M-row batch below were 500,000 inserts plus 500,000 updates counted twice.

Updates that change nothing still write a new row version in PostgreSQL. Add a WHERE to DO UPDATE and unchanged rows are skipped (no new version, no WAL, nothing returned):

sql
INSERT INTO users (email, username, visits) VALUES ('ben@example.com', 'ben', 1)
ON CONFLICT (email) DO UPDATE SET visits = EXCLUDED.visits
WHERE users.visits IS DISTINCT FROM EXCLUDED.visits;

Two sessions, one new key: where MERGE is not an upsert

This is the difference that matters most in production. Two requests try to create the same user at the same moment. Session A runs the upsert and holds its transaction open for two seconds; session B runs the same statement 0.3 s later. At the default READ COMMITTED:

text
INSERT ... ON CONFLICT:  B waits for A, then takes the update path. 1 row, visits = 2. No error.
MERGE:                   B waits for A, then fails:
                         ERROR:  duplicate key value violates unique constraint "users_email_key"
MERGE at SERIALIZABLE:   ERROR:  could not serialize access due to read/write dependencies among transactions

MERGE decides "matched or not" from the snapshot it started with. B didn't see A's uncommitted row, chose the insert branch, and hit the unique index. ON CONFLICT is built on a speculative insert that checks the index and, on a conflict, switches to the update with the committed row, which is exactly what an upsert needs. The PostgreSQL manual says as much in its MERGE page: run alongside concurrent inserts, MERGE can fail with a unique violation, and INSERT ... ON CONFLICT is the alternative. So: ON CONFLICT for concurrent single-row upserts from the app; MERGE for batch jobs where it's the only writer (or where you're ready to retry), and for the things ON CONFLICT can't do, like deleting rows that disappeared from the source (WHEN NOT MATCHED BY SOURCE THEN DELETE, PostgreSQL 17+) or matching on a condition that isn't a unique key.

MySQL's ON DUPLICATE KEY UPDATE behaves like ON CONFLICT here: the second session waits and then updates. SQLite only has one writer at a time, so the race can't happen inside the database; the second connection waits for the lock (or gets database is locked if busy_timeout is 0).

A million rows: how fast is each?

A batch is a different job from a single row. We loaded 1M rows into items(id, sku UNIQUE, price, updated_at) and upserted a 1M-row staging table into it: half the SKUs existed (updates), half were new (inserts). Same laptop, Docker for the two servers; compare the methods within an engine, not the engines with each other.

text
PostgreSQL 18 (3 runs each, all within 2%)
  INSERT ... SELECT ... ON CONFLICT DO UPDATE       9.9 s   359 MB WAL   max id 2,000,000
  MERGE ... USING staging                           6.5 s   303 MB WAL   max id 1,500,000
  UPDATE ... FROM staging, then
  INSERT ... WHERE NOT EXISTS (one transaction)     6.9 s                max id 1,500,000
MySQL 8.4
  INSERT ... SELECT ... ON DUPLICATE KEY UPDATE    14.1 s   1,500,000 affected rows
  UPDATE ... JOIN staging, then
  INSERT ... LEFT JOIN ... WHERE id IS NULL        13.1 s
SQLite 3.50 (file, WAL)
  INSERT ... SELECT ... WHERE true ON CONFLICT      1.3 s
  UPDATE ... FROM staging, then
  INSERT ... WHERE NOT EXISTS                       5.1 s

On PostgreSQL, ON CONFLICT was the slowest way to run a big batch: it handles rows one at a time (try the insert, hit the index, take the update path) and burned 500,000 identity values doing it. MERGE plans the whole thing as one join and was 35% faster with less WAL. For a nightly sync with no other writers on the table, that's the better tool, and the concurrency problem above doesn't apply when nobody else is inserting. The two-statement version was about as fast as MERGE, with the same caveat about concurrent inserts.

On MySQL the two methods were within 10% of each other. The max id after both was 1,548,560, not 1,500,000: with INSERT ... SELECT, MySQL reserves ids in growing blocks because it can't know the row count up front, and the unused end of the last block is lost. On SQLite, ON CONFLICT was by far the fastest, four times quicker than the two statements.

SQLite's parser trap

INSERT ... SELECT ... FROM staging ON CONFLICT is a syntax error in SQLite (near "DO": syntax error): the parser reads ON as the start of a join condition. Put WHERE true before ON CONFLICT and it works. The SQLite docs mention it; most people find out from the error.

REPLACE is not an upsert

MySQL's REPLACE INTO and SQLite's INSERT OR REPLACE look like a short way to write an upsert. They delete the old row and insert a new one. In MySQL, Eve's id went from 10 to 11. In SQLite with foreign keys on, Ben's two rows in logins (ON DELETE CASCADE) were deleted along with him, and his row came back with a new id and visits reset to the default. If anything references the table, or you want to keep columns the statement doesn't mention, use ON CONFLICT / ON DUPLICATE KEY UPDATE instead.

Translating between them

Moving an app between engines means rewriting every upsert. Until this week our own SQL translator, sqlTranslate (built on sqlglot), passed them through as written, producing things like ON DUPLICATE KEY UPDATE SET for PostgreSQL. It now rewrites them: ON DUPLICATE KEY UPDATE to ON CONFLICT ... DO UPDATE, new.col and VALUES(col) to EXCLUDED.col, INSERT IGNORE to DO NOTHING, INSERT OR REPLACE to REPLACE INTO, and a DO UPDATE ... WHERE to a CASE per column for MySQL. What it can't carry over goes in comments above the statement. The one thing it can't know is which unique key a MySQL upsert means, so the PostgreSQL output says ON CONFLICT (unique_key_columns) for you to fill in:

sqlTranslate turning a MySQL INSERT ... AS new ON DUPLICATE KEY UPDATE into PostgreSQL INSERT ... ON CONFLICT (unique_key_columns) DO UPDATE SET visits = users.visits + 1, username = EXCLUDED.username, with two comment lines about the key and RETURNING

We ran every translated statement on its target engine (PostgreSQL 18, MySQL 8.4, SQLite) with the key filled in; all of them ran and left the rows we expected. Read the comment lines before you paste: dropping the conflict target on the way to MySQL is where the "wrong row" behaviour above comes from.

Paste an upsert, pick the two engines, and get the rewrite with notes on what changed. Free, no sign-up.

Open sqlTranslate

Which one to use

PostgreSQL: INSERT ... ON CONFLICT (key) DO UPDATE for app writes, with a WHERE to skip no-op updates and RETURNING to know what happened. MERGE for single-writer batch syncs, especially when it should also delete. Deduplicate batches first. MySQL: INSERT ... AS new ON DUPLICATE KEY UPDATE, only on tables where just one unique key can collide, or with a SET list that's safe on any row; never REPLACE on a table with children. SQLite: ON CONFLICT with one clause per key, WHERE true after a SELECT, and no INSERT OR REPLACE when foreign keys point at the table. All three: bigint ids.

Related on this site: the deadlocks article has the batch upsert that deadlocks when two jobs insert the same keys in a different order (sort the batch), and MySQL to PostgreSQL: what a SQL translator fixes covers the rest of a port.

Run the same upsert against PostgreSQL, MySQL and SQLite from one window: Pilotbase connects to all three, and more. Desktop for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Sources

PostgreSQL documentation: INSERT and ON CONFLICT, MERGE (including the note on concurrent inserts). MySQL 8.4 manual: INSERT ... ON DUPLICATE KEY UPDATE, AUTO_INCREMENT handling in InnoDB, REPLACE. SQLite: UPSERT (the parsing ambiguity), ON CONFLICT and REPLACE, AUTOINCREMENT. Measured on PostgreSQL 18.6 and MySQL 8.4.10 (Docker images, defaults except shared_buffers 512MB on PostgreSQL) and SQLite 3.50.4, October 2026.