Upsert in SQL: INSERT ON DUPLICATE KEY vs ON CONFLICT Explained

An upsert is a single database operation that inserts a new row or updates an existing one, depending on whether a conflict is found on a primary key or unique index. The name is just "update" and "insert" stuck together. Most SQL databases support the pattern natively, but the syntax differs: MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, PostgreSQL uses INSERT ... ON CONFLICT, and SQL Server uses MERGE.

The basic idea is the same everywhere, but the edge cases are not. Each implementation behaves differently when many users write at the same time, or on tables with multiple unique indexes. This guide walks through the syntax for each database, the pitfalls worth knowing before you ship, and how upserts interact with migrations.

What is upsert and when to use it#

An upsert is useful whenever you need to write data without first checking whether a row already exists. A few common cases:

• Syncing external data: you pull records from an API and write them to a local table, updating the row if it's already there and inserting it if not.
• Safe retries: if a write fails and you retry it, an upsert produces the same result whether it runs once or ten times.
• Counters: increment a value if the row exists, or start it from scratch if it doesn't.
• Seed data in migrations: write default rows that should always exist, without failing if they're already there.

Without native upsert support, the usual workaround is SELECT-then-INSERT/UPDATE: two separate queries with a gap in between where two users can race to insert the same row. Upsert collapses that into one step, which is why it's worth using even when it isn't strictly necessary.

MySQL: INSERT ON DUPLICATE KEY UPDATE#

MySQL's upsert syntax:

INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON DUPLICATE KEY UPDATE col2 = VALUES(col2), ...;

When the row you're inserting would conflict with an existing primary key or unique index, MySQL runs the UPDATE clause instead of failing. To reference the new values inside that UPDATE clause, you use the VALUES() function on MySQL 5.x, or column aliases on MySQL 8.0.19 and later:

-- MySQL 8.0.19+ (preferred)
INSERT INTO page_views (page_id, view_count)
VALUES (42, 1) AS new_vals
ON DUPLICATE KEY UPDATE view_count = view_count + new_vals.view_count;

-- Older syntax using VALUES() function
INSERT INTO page_views (page_id, view_count)
VALUES (42, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + VALUES(view_count);

Affected rows behavior

MySQL's affected-rows count is not what you'd guess:

This matters if your code checks the affected-row count to work out what happened. Most ORMs hide the detail, but raw database clients hand you the raw number, so it's worth knowing the convention.

The multiple unique index caveat

If a table has more than one unique or primary key index, and the row you're inserting matches more than one of them, MySQL only runs the update for whichever index it finds first. MySQL's own documentation warns against using ON DUPLICATE KEY UPDATE on tables like this, and for good reason: the result depends on internal index ordering and isn't predictable.

For tables with a single primary key, which is the common case, the behavior is straightforward. One thing to know: for AUTO_INCREMENT primary keys, the counter still ticks up even when a conflict happens and no row is inserted, so you can end up with gaps in your IDs.

REPLACE INTO: skip this

MySQL also has REPLACE INTO, which handles conflicts by deleting the old row and inserting a fresh one. That sounds equivalent, but it isn't. It resets the primary key on auto-increment tables, fires DELETE plus INSERT triggers instead of UPDATE triggers, and can break foreign key relationships that point at the deleted row. Stick with ON DUPLICATE KEY UPDATE.

PostgreSQL: INSERT ... ON CONFLICT#

PostgreSQL added INSERT ... ON CONFLICT in version 9.5. The syntax:

INSERT INTO table (col1, col2, ...)
VALUES (val1, val2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET col2 = EXCLUDED.col2, ...;

EXCLUDED is the special name PostgreSQL gives to the row you tried to insert. You can reference it anywhere in the SET clause, which reads more cleanly than MySQL's VALUES() approach:

INSERT INTO page_views (page_id, view_count, last_seen)
VALUES (42, 1, NOW())
ON CONFLICT (page_id)
DO UPDATE SET
    view_count = page_views.view_count + EXCLUDED.view_count,
    last_seen = EXCLUDED.last_seen;

DO NOTHING

When you want to silently skip conflicts rather than update:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;

DO NOTHING is handy for seed data you're safe to re-run, or any pipeline where a duplicate just means the work was already done.

Conflict target options

PostgreSQL requires a conflict target when you use DO UPDATE:

-- By column (most common; infers the unique index)
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name

-- By named constraint
ON CONFLICT ON CONSTRAINT users_email_key DO NOTHING

-- By partial unique index
ON CONFLICT (user_id) WHERE is_active = true DO UPDATE SET ...

Targeting by column name is usually safer than hard-coding a constraint name. ON CONFLICT (email) keeps working if you drop and recreate the unique constraint, or if PostgreSQL rebuilds the underlying index, whereas ON CONFLICT ON CONSTRAINT users_email_key breaks the moment that specific constraint name no longer exists.

Safe under concurrent writes

INSERT ... ON CONFLICT is safe when multiple users or processes write to the same table at the same time. PostgreSQL guarantees that exactly one insert or update fires per row, so two sessions can't both win on the same row. This is the part the SELECT-then-INSERT pattern gets wrong, where a second user can slip in between your SELECT and your INSERT.

SQL Server and other databases#

SQL Server: MERGE

SQL Server uses MERGE, which is also the SQL standard syntax (SQL:2003):

MERGE INTO target_table AS target
USING (VALUES (42, 1)) AS source (page_id, view_count)
ON target.page_id = source.page_id
WHEN MATCHED THEN
    UPDATE SET target.view_count = target.view_count + source.view_count
WHEN NOT MATCHED THEN
    INSERT (page_id, view_count) VALUES (source.page_id, source.view_count);

MERGE requires a semicolon terminator; leave it off and you get Error 10713.

It's more verbose than the MySQL or PostgreSQL upsert, but it's also more general. You can handle MATCHED, NOT MATCHED BY TARGET, and NOT MATCHED BY SOURCE in one statement, which makes it a good fit for full table synchronization rather than just single-row writes. MySQL vs SQL Server covers more syntax differences between the two.

SQLite

SQLite gives you two options. INSERT OR REPLACE has the same delete-then-insert problem as MySQL's REPLACE INTO, so prefer the cleaner INSERT ... ON CONFLICT syntax added in version 3.24.0 (released June 2018), which closely mirrors PostgreSQL. If you're on an older SQLite version, upgrade before relying on ON CONFLICT DO UPDATE.

INSERT INTO page_views (page_id, view_count)
VALUES (42, 1)
ON CONFLICT(page_id) DO UPDATE SET view_count = view_count + excluded.view_count;

Note: SQLite spells excluded in lowercase; PostgreSQL accepts both cases.

Oracle

Oracle uses MERGE with syntax similar to SQL Server. There's no INSERT ... ON CONFLICT shorthand.

Performance and locking considerations#

Upserts save a round trip compared to SELECT-then-INSERT/UPDATE, but they aren't free. They carry some locking overhead, and how much depends on the database.

MySQL locking

ON DUPLICATE KEY UPDATE locks the conflicting row even when the update doesn't change any values. On tables with heavy write traffic and frequent conflicts, this can push up wait times. For workloads where most writes are new inserts and conflicts are rare, it can actually be faster to catch the duplicate-key error in your application code and retry as an UPDATE. That's only worth doing if you handle the exception carefully, though.

PostgreSQL

INSERT ... ON CONFLICT locks the conflicting row only for the duration of that statement, then releases it. Multiple users can safely upsert to the same table at the same time without stepping on each other.

Deadlocks in batch upserts

When two users upsert batches of rows at the same time, they can get stuck waiting for each other if their batches overlap in opposite orders: user A holds a lock on row 1 and waits for row 2, while user B holds row 2 and waits for row 1. The remedy is simple. Sort your rows by primary key before batching, so every session processes them in the same order:

-- Sort by primary key before batching
INSERT INTO page_views (page_id, view_count)
VALUES (1, 5), (2, 3), (7, 1)
ON CONFLICT (page_id)
DO UPDATE SET view_count = page_views.view_count + EXCLUDED.view_count;

Checking what happened (PostgreSQL)

PostgreSQL's RETURNING clause lets you see whether the row was inserted or updated:

INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING xmax;
-- xmax = 0 → row was inserted; non-zero → row was updated

Upsert in application code and migrations#

ORM support

Most ORMs support upsert natively, though the method name varies:

Upserts in migration scripts

Upsert-based seed data is safe to run more than once: running the migration twice produces the same result. For reference tables such as countries, currencies, status codes, or permission names, this is much safer than a plain INSERT, which fails if the rows are already there:

-- Safe to run multiple times
INSERT INTO currencies (code, name)
VALUES ('USD', 'US Dollar'), ('EUR', 'Euro')
ON CONFLICT (code) DO NOTHING;

One thing to watch for: if you add a UNIQUE constraint to a column as part of a migration, any duplicate values already in that column will make the ALTER TABLE fail before your upsert even runs. Clean up the existing duplicates first. What is Database Schema Migration? covers safe migration sequencing in more detail.

Which conflicts an upsert detects depends entirely on which primary keys and unique indexes exist on the table. Primary key vs foreign key explains the difference if you're trying to work out why a particular upsert is or isn't triggering the update path.

Bytebase and safe upsert migrations#

Upsert patterns interact with schema changes in ways that aren't always obvious. Adding or removing a unique index changes which conflicts the upsert detects. Renaming a constraint in PostgreSQL breaks any ON CONFLICT ON CONSTRAINT references in your application code. Changing a column's data type can quietly affect whether two rows are considered equal during the conflict check.

Bytebase reviews schema migrations before they reach production. Its SQL review engine enforces rules like requiring indexes on columns used in WHERE clauses, blocking DROP INDEX without an explicit override, and flagging tables that lack a primary key, which are the same constraints that upsert logic depends on. For teams running PostgreSQL and MySQL side by side, Bytebase runs the same review workflow against both databases regardless of which upsert dialect each one uses.

FAQ#

What is the difference between upsert and MERGE? MERGE is the SQL standard way to do an upsert, used by SQL Server and Oracle. MySQL and PostgreSQL have their own shorter syntax that covers the insert-or-update case. MERGE can also handle DELETE in the same statement, which makes it useful for syncing a whole table rather than individual rows.

Can I use ON CONFLICT with a two-column unique index in PostgreSQL? Yes. List both columns: ON CONFLICT (col1, col2) DO UPDATE SET .... PostgreSQL finds the unique index that covers exactly those columns. If no such index exists, you get an error at runtime.

Why does MySQL ON DUPLICATE KEY UPDATE return 2 affected rows on an update? It's MySQL's convention: 1 means the row was inserted, 2 means an existing row was updated, and 0 means the row matched but nothing changed. It's intentional and documented. Most ORMs hide it, but raw database clients return the raw number.

Is REPLACE INTO the same as upsert? No. REPLACE INTO deletes the old row and inserts a brand new one. That resets the primary key on auto-increment tables, fires DELETE triggers instead of UPDATE triggers, and can break foreign keys that point at the deleted row. Use INSERT ... ON DUPLICATE KEY UPDATE instead.

Does PostgreSQL ON CONFLICT work with partial indexes? Yes. If your unique index was created with a WHERE clause, include the same condition in the conflict target: ON CONFLICT (col) WHERE is_active = true DO UPDATE SET .... PostgreSQL only uses the partial index when the row being inserted matches that condition.