Upsert with ON CONFLICT, and MERGE

Part of the free LogicWiz SQL & Databases course, Module 8: Changing data. The queries and practice run in the app on real Postgres.

Every evening the kirana shop's supplier sends a stock sheet. Some items are new, some already exist and need their quantity added. Insert the new, update the old, in one statement that cannot go wrong when two sheets arrive at once.

You'll learn to

Skip duplicates with ON CONFLICT DO NOTHING
Insert-or-update with ON CONFLICT DO UPDATE and EXCLUDED
Merge a whole table of changes with MERGE
Explain why "check, then insert" is not safe

Learn

Why not just check first?

The obvious way: SELECT to see if the row exists, then INSERT or UPDATE. On a busy app this is a race: two requests both check, both see nothing, both insert, and one fails with a duplicate key (or, with no key, you get two rows).

An upsert (update + insert) does the check and the write as one statement, so the database settles who wins. In Postgres it is INSERT … ON CONFLICT.

ON CONFLICT DO NOTHING

INSERT INTO customers (customer_id, name, city, signup_date) VALUES
  (1, 'Aarav Sharma', 'Bengaluru', '2025-01-05'),
  (13, 'Neha Verma', 'Pune', '2025-07-15')
ON CONFLICT (customer_id) DO NOTHING
RETURNING customer_id, name;

Customer 1 already exists, so that row is quietly skipped; 13 is new and goes in. RETURNING shows only the row that was actually inserted.

ON CONFLICT DO UPDATE and EXCLUDED

INSERT INTO menu_items (item_id, restaurant_id, name, category, price, is_veg) VALUES
  (4, 2, 'Masala Dosa', 'Main', 130, true),
  (19, 2, 'Pongal', 'Main', 110, true)
ON CONFLICT (item_id) DO UPDATE
SET price = EXCLUDED.price
RETURNING item_id, name, price;

EXCLUDED is the row you tried to insert. For item 4 (it exists) only the price is updated, to 130. Item 19 is new, so it is inserted in full. The old row is named by the table: menu_items.price.

A counter that adds up

CREATE TABLE daily_sales (
  item_id  INT,
  day      DATE,
  qty      INT NOT NULL,
  PRIMARY KEY (item_id, day)
);

INSERT INTO daily_sales VALUES (4, '2025-07-01', 3)
ON CONFLICT (item_id, day) DO UPDATE SET qty = daily_sales.qty + EXCLUDED.qty;

INSERT INTO daily_sales VALUES (4, '2025-07-01', 2)
ON CONFLICT (item_id, day) DO UPDATE SET qty = daily_sales.qty + EXCLUDED.qty;

SELECT * FROM daily_sales;

The first INSERT creates the row with 3; the second conflicts and adds 2, giving 5. daily_sales.qty is the old value, EXCLUDED.qty the new one.

ON CONFLICT needs a PRIMARY KEY or UNIQUE constraint on exactly the columns you name; without one Postgres has nothing to conflict with and raises an error. And one INSERT cannot hit the same row twice: two rows for the same key in one VALUES list give ON CONFLICT DO UPDATE command cannot affect row a second time.

No unique constraint, no upsert

INSERT INTO menu_items (item_id, restaurant_id, name, category, price, is_veg)
VALUES (30, 2, 'Masala Dosa', 'Main', 130, true)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;

name is not UNIQUE in menu_items (two restaurants may both sell a Masala Dosa), so Postgres refuses: there is no unique or exclusion constraint matching the ON CONFLICT specification.

MERGE: a whole sheet of changes

MERGE INTO menu_items AS m
USING price_sheet AS p
  ON m.item_id = p.item_id
WHEN MATCHED AND m.price <> p.price THEN
  UPDATE SET price = p.price
WHEN NOT MATCHED THEN
  INSERT (item_id, restaurant_id, name, category, price, is_veg)
  VALUES (p.item_id, p.restaurant_id, p.name, p.category, p.price, p.is_veg);

SELECT item_id, name, price FROM menu_items WHERE restaurant_id = 2 ORDER BY item_id;

MERGE (SQL standard; Postgres 15+, Oracle, SQL Server) compares a source table with a target and says what to do when a row matches and when it does not. Coffee's price is unchanged, so it is left alone. MERGE can also DELETE matched rows.

MySQL: INSERT … ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty) (8.0.20+ prefers a row alias: VALUES (…) AS new ON DUPLICATE KEY UPDATE qty = qty + new.qty). INSERT IGNORE is like DO NOTHING. Beware REPLACE INTO: on a conflict it deletes the old row and inserts a new one, which fires delete cascades.

Interview favourites: "How do you insert a row only if it does not exist?" (ON CONFLICT DO NOTHING, not SELECT-then-INSERT), "What is EXCLUDED?", and "ON CONFLICT vs MERGE": ON CONFLICT is atomic per row and race-safe; MERGE handles a whole source table with update, insert and delete branches.

In one line

INSERT … ON CONFLICT (key) DO NOTHING | DO UPDATE SET col = EXCLUDED.col is an atomic upsert; MERGE applies a whole source table.

Interview corner

How do you insert a row only if it does not already exist?

Use INSERT … ON CONFLICT (key) DO NOTHING in Postgres (MySQL: INSERT IGNORE), backed by a PRIMARY KEY or UNIQUE constraint. SELECT-then-INSERT is a race: two sessions can both see "not there" and both insert.

What is an upsert, and how is it written in Postgres and MySQL?

Insert the row, or update it if the key already exists, in one atomic statement. Postgres: INSERT … ON CONFLICT (id) DO UPDATE SET qty = t.qty + EXCLUDED.qty. MySQL: INSERT … ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty).

ON CONFLICT vs MERGE?

ON CONFLICT is Postgres's insert-or-update for rows you are inserting, keyed on a unique constraint, and is safe under concurrency. MERGE is standard SQL that joins a whole source to a target, with WHEN MATCHED (update or delete) and WHEN NOT MATCHED (insert) branches; it needs no unique constraint but can still hit duplicate errors under concurrent inserts.

Why is REPLACE INTO in MySQL dangerous?

On a duplicate key it deletes the old row and inserts a new one. Columns you did not supply are reset to defaults, delete triggers and ON DELETE CASCADE fire (removing child rows), and auto-increment ids can change. Prefer ON DUPLICATE KEY UPDATE.