Skip to content
Tutorials Center
My learning

Topics

Lesson 60 of 125 ยท Changing Data

Upserts with ON CONFLICT

Learn SQL upserts. Insert a row or update it when it already exists with ON CONFLICT DO UPDATE and DO NOTHING, avoid REPLACE pitfalls and compare MySQL and MERGE.

A very common job sounds simple: "save this row; if it's new, insert it, and if it already exists, update it." A shopping cart adds one more unit of a product, a sync job copies today's prices from a supplier, a page counter goes up by one. This combined insert-or-update is nicknamed an upsert.

You could write it as two steps: first SELECT to check whether the row exists, then INSERT or UPDATE. That code is longer, needs two round trips, and is subtly broken under load: two requests can both see "no row yet" and both try to insert. An upsert does the whole decision inside one statement, guarded by a unique constraint, so it's both shorter and safe. This lesson teaches SQLite's and PostgreSQL's ON CONFLICT clause, the older INSERT OR REPLACE and INSERT OR IGNORE, and how MySQL and SQL Server spell the same idea.

Conflicts come from unique constraints

An upsert reacts to a conflict: an insert that would create a duplicate in a PRIMARY KEY or UNIQUE column (or a unique index). Without such a constraint there is nothing to conflict with: naming a column that isn't unique in ON CONFLICT (...) is an error, and an upsert with no target would simply insert a new row every time. So the first step is always a key that defines "the same row": a product id, an email address, a (user, product) pair.

In the shop database, customers.email is UNIQUE. Inserting an existing email normally fails with an error, as you saw in the INSERT lesson. Adding ON CONFLICT ... DO NOTHING turns that error into a quiet skip:

SQL ON CONFLICT DO NOTHING ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The runner reports OK, 1 row affected.: Ines was inserted as customer 41, while the duplicate Yusuf row was skipped and the original Yusuf, who signed up in 2023, is untouched. (email) is the conflict target: it names the unique column (or columns) whose duplicates you want to handle.

ON CONFLICT DO UPDATE

To update the existing row instead, write DO UPDATE SET .... Inside it, the special table name excluded refers to the row you tried to insert, while the plain column names (or the table name) refer to the row that's already there.

This inventory table receives stock counts from a warehouse feed. Products already present get the new count; new products are inserted:

SQL ON CONFLICT DO UPDATE

Press Run (or Ctrl + Enter) to see the result here.

The mug's row now shows 35 units and today's date, the teapot is new, and the kettle wasn't in the feed so it kept its old values. Notice that the update copies only quantity and updated_on. Columns you don't mention in SET keep the existing row's values.

Combining old and new values

Because you can see both rows, the new value can be computed from both. That's how a shopping cart adds to a quantity instead of overwriting it, and how a counter increments:

SQL Counting with an upsert

Press Run (or Ctrl + Enter) to see the result here.

The first visit to a page inserts it with 1 view; every later visit adds one. /sql/upsert ends with 3 views and /sql/insert with 1. In views + 1, views means the existing row's value; excluded.views would be the 1 you tried to insert. The application never has to know whether the row existed.

Updating only when it makes sense

DO UPDATE can have its own WHERE clause. If the condition is false, the existing row is left alone and nothing is inserted either. A typical use is ignoring out-of-date data, such as a feed that delivers an older price after a newer one:

SQL DO UPDATE ... WHERE

Press Run (or Ctrl + Enter) to see the result here.

Product 1 takes the newer price, and product 2 keeps 18.50 because the incoming row is older. Here the existing row is written as prices.valid_from to make the comparison easy to read.

Upserting many rows from a query

An upsert can take its rows from a SELECT, just like INSERT ... SELECT. That's the standard way to merge a staging table into a main table. SQLite has one quirk here: when the SELECT has no WHERE clause, the parser can't tell whether ON CONFLICT belongs to a join, so you add a harmless WHERE true:

SQL Upsert from a staging table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two existing products got new stock (and the planner a lower price), while the balance board was added as product 27. RETURNING lists all three rows; note that the updated rows kept their original added_on, because SET doesn't touch it. PostgreSQL doesn't need the WHERE true.

One conflict per row and statement. If the incoming rows contain the same key twice, PostgreSQL refuses the statement ("ON CONFLICT DO UPDATE command cannot affect row a second time"), while SQLite applies them one after another. Deduplicate the source first, for example with GROUP BY or ROW_NUMBER.

Rules for the conflict target

  • The columns in ON CONFLICT (...) must exactly match a PRIMARY KEY, UNIQUE constraint or unique index. Otherwise SQLite reports ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint.
  • For a composite key, list all its columns: ON CONFLICT (user_id, product_id).
  • DO NOTHING may omit the target, meaning "any unique conflict". DO UPDATE needs a target in PostgreSQL; in SQLite only the last of several ON CONFLICT clauses may omit it.
  • SQLite (3.35+) allows several ON CONFLICT clauses in one statement for tables with more than one unique constraint. PostgreSQL allows one.
  • Upserts in SQLite need version 3.24 or later; RETURNING and multiple clauses need 3.35.

INSERT OR IGNORE and INSERT OR REPLACE

Before ON CONFLICT existed, SQLite offered conflict keywords right after INSERT. You'll still see them in a lot of code:

  • INSERT OR IGNORE skips rows that would break a constraint, much like ON CONFLICT DO NOTHING. It also ignores NOT NULL and CHECK failures, which can hide real bugs.
  • INSERT OR REPLACE (also written REPLACE INTO) deletes the conflicting row and inserts the new one.

"Delete and insert" is not the same as "update", and the difference bites. Any column you don't supply goes back to its default:

SQL The REPLACE pitfall

Press Run (or Ctrl + Enter) to see the result here.

Ana's name is fixed, but her 1,200 loyalty points are gone: the old row was deleted, and the new one got DEFAULT 0. A DELETE also fires delete triggers and can trigger ON DELETE CASCADE on child rows, and with a table's implicit rowid it can give the row a new id. Replace the INSERT OR REPLACE with ON CONFLICT (email) DO UPDATE SET name = excluded.name and run again: the points survive. Prefer ON CONFLICT ... DO UPDATE whenever you mean "update".

Upserts in other databases

Every major database supports upserts, with different syntax.

Database Insert or update Insert or skip
SQLite 3.24+, PostgreSQL 9.5+ ON CONFLICT (key) DO UPDATE SET c = excluded.c ON CONFLICT DO NOTHING
MySQL / MariaDB ON DUPLICATE KEY UPDATE c = new.c (row alias, MySQL 8.0.19+) or c = VALUES(c) INSERT IGNORE
SQL Server, Oracle MERGE MERGE without the update branch
PostgreSQL 15+ MERGE (in addition to ON CONFLICT) MERGE
SQLite, MySQL REPLACE INTO (delete + insert)

MySQL has no conflict target. ON DUPLICATE KEY UPDATE fires on a duplicate in any unique index:

SQL
-- MySQL 8.0.19+
INSERT INTO inventory (sku, name, quantity)
VALUES ('MG-200', 'Mug', 35) AS new
ON DUPLICATE KEY UPDATE quantity = new.quantity;

MERGE is the SQL standard's statement for synchronizing a target table with a source. It's more general than an upsert, because it can also delete rows, but it's wordier:

SQL
-- SQL Server
MERGE INTO inventory WITH (HOLDLOCK) AS target
USING (VALUES ('MG-200', 'Mug', 35)) AS source (sku, name, quantity)
  ON target.sku = source.sku
WHEN MATCHED THEN
  UPDATE SET quantity = source.quantity
WHEN NOT MATCHED THEN
  INSERT (sku, name, quantity)
  VALUES (source.sku, source.name, source.quantity);

MERGE isn't automatically race-free. In SQL Server, two concurrent MERGE statements can both decide "not matched" and collide on the unique key; the HOLDLOCK hint above prevents that. PostgreSQL's ON CONFLICT is designed to be safe under concurrency, while its MERGE can still fail with a unique violation. If you need concurrency-safe upserts in PostgreSQL, prefer ON CONFLICT. Isolation Levels and Locking explains why such races happen.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is "SELECT, then INSERT or UPDATE" in application code worse than a single upsert?

    The check and the write are separate steps, so another session can slip in between. An upsert decides inside one statement, guarded by the unique constraint.
  2. 2. In ON CONFLICT (sku) DO UPDATE SET quantity = excluded.quantity, what is excluded?

    excluded holds the values of the proposed row. Plain column names, or the table name, refer to the row already stored.
  3. 3. page_views has ('/home', 4). What is stored after INSERT INTO page_views (path, views) VALUES ('/home', 1) ON CONFLICT (path) DO UPDATE SET views = views + 1;?

    The insert conflicts on path, so the existing row is updated. views is the stored 4, giving 5.
  4. 4. A members row is ('[email protected]', 'Ana', points 1200), with points DEFAULT 0. What does INSERT OR REPLACE INTO members (email, name) VALUES ('[email protected]', 'Ana Lima'); leave in points?

    REPLACE deletes the old row and inserts a new one. points was not supplied, so it gets its default, 0.
  5. 5. Which statement is the MySQL equivalent of ON CONFLICT DO UPDATE?

    MySQL and MariaDB use ON DUPLICATE KEY UPDATE, which reacts to a duplicate in any unique index.

Summary

  • An upsert inserts a row or, if its key already exists, updates or skips it, in one safe statement.
  • It needs a PRIMARY KEY or UNIQUE constraint; the conflict target ON CONFLICT (columns) must match it.
  • DO NOTHING skips duplicates; DO UPDATE SET c = excluded.c updates the existing row, where excluded is the proposed row.
  • DO UPDATE ... WHERE updates only when a condition holds, such as newer data.
  • INSERT ... SELECT ... WHERE true ON CONFLICT merges a whole table in SQLite; RETURNING shows the result.
  • INSERT OR REPLACE deletes and re-inserts, resetting omitted columns. Prefer DO UPDATE.
  • MySQL uses ON DUPLICATE KEY UPDATE; SQL Server and Oracle use MERGE.

Next: Transactions, where you'll group several statements into one all-or-nothing unit.