SQL Upsert Explained: ON CONFLICT, MERGE, and More

An upsert is a single database operation that inserts a row if it doesn’t exist and updates it if it does. The name is a blend of “update” and “insert.” Instead of reading first and then deciding which statement to run, you hand the database both intentions at once and let it resolve the conflict atomically. Every major relational database supports it, though the syntax differs: PostgreSQL and SQLite use INSERT ... ON CONFLICT, MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and the SQL standard defines MERGE.

Why not just check first?

The obvious approach looks like this in application code:

row = SELECT * FROM users WHERE email = ?
if row exists:
    UPDATE users SET name = ? WHERE email = ?
else:
    INSERT INTO users (email, name) VALUES (?, ?)

It works on a single connection and fails under concurrency. Two requests can both run the SELECT, both see no row, and both attempt the INSERT. One succeeds and the other hits a unique-constraint violation — or, if there is no unique constraint, you silently end up with duplicate rows. Wrapping the logic in a transaction doesn’t automatically help: under the default isolation levels of most databases, neither transaction can see the other’s uncommitted insert.

An upsert closes that gap because the database checks for the conflict and performs the write as one step, coordinated through the unique index itself.

The foundation: a unique constraint

Every upsert form needs a way to define what “already exists” means, and that is almost always a primary key or unique constraint. If you haven’t modeled one, the database has nothing to conflict on. (For a refresher on the different kinds, see primary keys vs foreign keys vs unique constraints.)

CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT,
  login_count INT NOT NULL DEFAULT 0
);

Here email is the natural key we’ll upsert on.

PostgreSQL and SQLite: ON CONFLICT

PostgreSQL’s syntax names the conflict target and the action to take:

INSERT INTO users (email, name, login_count)
VALUES ('[email protected]', 'Ada', 1)
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  login_count = users.login_count + 1;

EXCLUDED is a special table reference holding the row you tried to insert. users refers to the existing row. That distinction lets you write both “overwrite with the new value” (EXCLUDED.name) and “combine with the old value” (users.login_count + 1).

Two useful variations:

• DO NOTHING skips the conflicting row entirely. It’s the right choice for “insert if absent” semantics, such as recording that an event was seen.
• A WHERE clause on DO UPDATE makes the update conditional, for example only overwriting when the incoming data is newer: DO UPDATE SET ... WHERE EXCLUDED.updated_at > users.updated_at.

Add RETURNING * to get the final row back, whether it was inserted or updated. SQLite supports essentially the same ON CONFLICT ... DO UPDATE and DO NOTHING syntax, including excluded.

MySQL: ON DUPLICATE KEY UPDATE

MySQL and MariaDB take a different shape:

INSERT INTO users (email, name, login_count)
VALUES ('[email protected]', 'Ada', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  login_count = login_count + 1;

There’s no explicit conflict target: the update fires if the insert would violate any primary key or unique index on the table. On a table with several unique indexes, that can update a row you didn’t expect. Newer MySQL versions prefer a row alias (VALUES (...) AS new then new.name) over the VALUES() function, which is deprecated.

MySQL also has INSERT IGNORE and REPLACE. Be careful with REPLACE: it deletes the conflicting row and inserts a new one, which fires delete triggers, can cascade through foreign keys, and resets any column you didn’t supply to its default.

The SQL standard: MERGE

MERGE is the standard’s general-purpose tool. It joins a source against a target and lets you specify what happens for matched and unmatched rows:

MERGE INTO users AS t
USING (VALUES ('[email protected]', 'Ada')) AS s(email, name)
ON t.email = s.email
WHEN MATCHED THEN
  UPDATE SET name = s.name
WHEN NOT MATCHED THEN
  INSERT (email, name) VALUES (s.email, s.name);

It’s available in SQL Server, Oracle, and recent PostgreSQL versions, and it shines for bulk synchronization — merging a staging table into a production one, with deletes for rows that disappeared from the source. It is not, however, a drop-in concurrency-safe upsert everywhere. Depending on the database, two concurrent MERGE statements can both take the NOT MATCHED branch and one will fail on the unique constraint. For single-row, high-concurrency writes in PostgreSQL, ON CONFLICT is the safer choice.

Syntax at a glance

	PostgreSQL	SQLite	MySQL / MariaDB	SQL Server / Oracle
Main form	ON CONFLICT ... DO UPDATE	ON CONFLICT ... DO UPDATE	ON DUPLICATE KEY UPDATE	MERGE
Insert-if-absent	DO NOTHING	DO NOTHING	INSERT IGNORE	MERGE with only NOT MATCHED
Explicit conflict target	Yes	Yes	No (any unique index)	Via the ON join condition
New-row reference	EXCLUDED	excluded	VALUES() or row alias	Source alias

Common pitfalls

Burned sequence values. In PostgreSQL, an INSERT ... ON CONFLICT that ends up updating still consumes a value from the table’s sequence. IDs will have gaps. That’s harmless unless something wrongly assumes IDs are contiguous.

Affecting the same row twice. A multi-row INSERT ... ON CONFLICT DO UPDATE fails if two rows in the same statement conflict with each other on the target key. Deduplicate the batch before sending it.

Partial unique indexes. If the uniqueness is defined by a partial index (WHERE deleted_at IS NULL), the ON CONFLICT clause must repeat that predicate so PostgreSQL can infer which index to use.

Lock contention on hot keys. An upsert takes a row lock on conflict. A counter that thousands of requests increment at once will serialize on that row. Consider batching or sharding the counter if it becomes a bottleneck.

Triggers and side effects. Both insert and update triggers may fire depending on the path taken. Know which ones your schema defines.

Where upserts fit in system design

Upserts are a natural building block for idempotent operations: replaying the same “set this user’s profile to X” request produces the same final state no matter how many times it runs. That makes them ideal for webhook handlers, sync jobs, and message consumers that may see duplicate deliveries. They also inherit the guarantees of the surrounding ACID transaction, so you can combine an upsert with other writes and commit or roll back everything together.

The takeaway

An upsert lets the database resolve “insert or update” atomically, using a unique constraint as the arbiter, which eliminates the race in check-then-write code. Use INSERT ... ON CONFLICT in PostgreSQL and SQLite, ON DUPLICATE KEY UPDATE in MySQL, and MERGE for bulk synchronization. Always define the unique key first, be explicit about which columns get overwritten, and remember that the update path still takes locks and fires triggers.