Every night a supplier sends a price list: some products are already in the shop, some are new. The naive way takes two steps: check with SELECT, then INSERT or UPDATE. That is two round trips to the database and, worse, a race condition: two processes see “not there” at the same moment, both try to insert, and one gets a UNIQUE error. An upsert (update + insert) makes this decision inside the database, in one atomic statement. The lesson «INSERT, UPDATE, DELETE: changing data» mentioned it in a table; now we learn it properly, together with RETURNING, which hands the changed rows straight back.
ON CONFLICT: DO NOTHING and DO UPDATE
UpsertDefinition
An INSERT that says what to do when the new row collides (conflicts) with a unique key: nothing (DO NOTHING) or update the existing row (DO UPDATE). A conflict can only happen on a PRIMARY KEY or UNIQUE constraint.
In the practice database products.name is not unique, so first we create a unique index. Now a second Laptop fails with an ordinary INSERT:
CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5);Expected output
Error: UNIQUE constraint failed: products.name
With ON CONFLICT (name) DO NOTHING the colliding row is silently dropped and the others are inserted. The columns in brackets are the conflict target and must match an existing unique constraint:
CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO NOTHING;
SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;Expected output
id | name | price | stock 1 | Laptop | 1450 | 8 11 | Tablet | 520 | 10
Two rows are visible inside DO UPDATE SET. **excluded is the row we tried to insert, which was “left out” because of the conflict; the table name (or a column without a prefix) is the row already in the database. Below, the price is replaced with the new one, while the stock is added** to the old one:
CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price,
stock = products.stock + excluded.stock;
SELECT id, name, price, stock FROM products
WHERE name IN ('Laptop', 'Tablet') ORDER BY id;Expected output
id | name | price | stock 1 | Laptop | 1399 | 13 11 | Tablet | 520 | 10
id: price 1399, stock 8 + 5 = 13.A classic use is a counter: the first visit to a page creates the row, after that only the number grows. The program never has to ask “is the row there yet?”:
CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER NOT NULL);
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/python', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
INSERT INTO page_views VALUES ('/sql', 1)
ON CONFLICT (page) DO UPDATE SET views = views + 1;
SELECT * FROM page_views ORDER BY page;Expected output
page | views /python | 1 /sql | 3
Don't let old data overwrite new data
Problem
A warehouse system sends stock messages, but they sometimes arrive out of order: first the 10:05 message (stock 7), then the 10:00 one (stock 9). How do you always keep the newest stock in stock_levels(sku PRIMARY KEY, qty, updated_at)?
Show solutionHide solution
Solution
DO UPDATE: ON CONFLICT (sku) DO UPDATE SET qty = excluded.qty, updated_at = excluded.updated_at WHERE excluded.updated_at > stock_levels.updated_at.2) When the 10:05 message arrives the row is created or updated: stock 7.
3) When the 10:00 message arrives the
WHERE is false (10:00 is not later than 10:05) — the row doesn't change.4) Result: the stock stays 7. Without the
WHERE, the message that arrived last (9) would win.INSERT OR REPLACE is not an upsert
SQLite (and MySQL's REPLACE INTO) has a shorter form, but it does something else: on a conflict it deletes the old row and inserts the new one. Columns not listed become NULL (or DEFAULT), the row gets a new id, and the orders that pointed to it become “orphans”:
CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT OR REPLACE INTO products (name, price)
VALUES ('Laptop', 1399);
SELECT p.id, p.name, p.category, p.price, p.stock,
(SELECT COUNT(*) FROM orders AS o WHERE o.product_id = p.id) AS orders
FROM products AS p
WHERE p.name = 'Laptop';Expected output
id | name | category | price | stock | orders 11 | Laptop | NULL | 1399 | NULL | 0
id changed from 1 to 11, and order 1 now points to a product that no longer exists.RETURNING: getting the changed rows back
The new row's id, a value computed by the database, the rows that were deleted — usually you write a second SELECT for these. RETURNING (since SQLite 3.35; in PostgreSQL for a long time) goes at the end of INSERT, UPDATE and DELETE and returns the listed columns for every changed row — like a SELECT list, expressions and aliases included:
INSERT INTO students (first_name, last_name, age, grade, city)
VALUES ('Nərmin', 'Səlimova', 15, 9, 'Bakı'),
('Rauf', 'Məlikov', 16, 10, 'Gəncə')
RETURNING id, first_name || ' ' || last_name AS student;Expected output
id | student 13 | Nərmin Səlimova 14 | Rauf Məlikov
ids without an extra query.UPDATE products
SET price = ROUND(price * 0.9, 2)
WHERE category = 'Electronics' AND stock > 0
RETURNING name, price AS new_price;Expected output
name | new_price Laptop | 1305 Smartphone | 809.99 Headphones | 108.45
RETURNING shows the values after the change (the Monitor, with 0 in stock, was not touched).CREATE UNIQUE INDEX idx_products_name ON products (name);
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
('Tablet', 'Electronics', 520, 10)
ON CONFLICT (name) DO UPDATE
SET price = excluded.price, stock = products.stock + excluded.stock
RETURNING id, name, price, stock;Expected output
id | name | price | stock 1 | Laptop | 1399 | 13 11 | Tablet | 520 | 10
RETURNING: both the updated and the inserted row come back in their final state.CREATE TABLE orders_archive (LIKE orders);
WITH moved AS (
DELETE FROM orders
WHERE order_date < '2025-02-01'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved
RETURNING id, order_date;Expected output
id | order_date ----+------------ 1 | 2025-01-15 2 | 2025-01-15 (2 rows)
MERGE and ON DUPLICATE KEY UPDATE
MERGE is a command from the SQL standard: it compares a target table with a source and spells out what to do in each case: WHEN MATCHED [AND condition] THEN UPDATE (or DELETE), WHEN NOT MATCHED THEN INSERT. SQL Server and Oracle have had it for years, PostgreSQL since version 15; version 17 added RETURNING, merge_action() and WHEN NOT MATCHED BY SOURCE (rows missing from the source). The result was checked in PGlite (PostgreSQL 18):
MERGE INTO products AS p
USING price_feed AS f
ON p.name = f.name
WHEN MATCHED AND p.price <> f.price THEN
UPDATE SET price = f.price
WHEN NOT MATCHED THEN
INSERT (name, price) VALUES (f.name, f.price)
RETURNING merge_action() AS action, p.id, p.name, p.price;Expected output
action | id | name | price --------+----+--------+--------- UPDATE | 1 | Laptop | 1399.00 INSERT | 11 | Tablet | 520.00 (2 rows)
One subtlety: in PostgreSQL a single INSERT ... ON CONFLICT DO UPDATE can't change the same row twice. If the source contains the same key twice, the statement fails — merge the duplicates first (GROUP BY). SQLite runs such a statement, and the second row updates the first. Also, MERGE can stop with a unique violation under concurrent INSERTs; for a simple “upsert by key” the PostgreSQL documentation recommends INSERT ... ON CONFLICT.
INSERT INTO products (name, price, stock)
VALUES ('Tablet', 520, 5),
('Tablet', 499, 3)
ON CONFLICT (name) DO UPDATE
SET stock = products.stock + excluded.stock;Expected output
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.
MySQL has its own syntax: ON DUPLICATE KEY UPDATE. There is no conflict target — any PRIMARY KEY or UNIQUE index triggers it. Since 8.0.19 the proposed row is referred to by an alias (AS new); the old VALUES(column) function is deprecated since 8.0.20. MySQL reports 1 “affected row” for an insert and 2 for an update.
-- MySQL 8.0.19+
INSERT INTO products (name, category, price, stock)
VALUES ('Laptop', 'Electronics', 1399, 5),
('Tablet', 'Electronics', 520, 10) AS new
ON DUPLICATE KEY UPDATE
price = new.price,
stock = products.stock + new.stock;| Task | SQLite | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|---|
| Update if present, else insert | ON CONFLICT … DO UPDATE | ON CONFLICT … DO UPDATE, MERGE (15+) | ON DUPLICATE KEY UPDATE | MERGE |
| Skip duplicates | ON CONFLICT DO NOTHING | ON CONFLICT DO NOTHING | INSERT IGNORE (!) | MERGE / NOT EXISTS |
| Return changed rows | RETURNING (3.35+) | RETURNING | none | OUTPUT |
INSERT IGNORE turns not only duplicates but many other errors into warnings — use it with care.Mini project: a nightly load you can re-run
Idempotent operationDefinition
An operation that gives the same result whether it runs once or ten times. When a load breaks halfway, you simply start it again — with no duplicates and nothing counted twice.
Staging table
The supplier's file is loaded into
price_feedfirst.Upsert
INSERT ... SELECT ... WHERE true ON CONFLICT (name) DO UPDATE. In SQLite theWHEREafter theSELECTmatters: without it the wordONis read as a join'sON, and you getnear "DO": syntax error.Update only what changed
WHERE products.price IS NOT excluded.price— an equal price isn't rewritten, so logs and triggers don't fire for nothing.Report and test
RETURNINGshows what changed; a second run must change 0 rows.
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
('Laptop', 'Electronics', 1399),
('Headphones', 'Electronics', 120.5),
('Tablet', 'Electronics', 520);
INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price
RETURNING id, name, price;Expected output
id | name | price 1 | Laptop | 1399 11 | Tablet | 520
CREATE UNIQUE INDEX idx_products_name ON products (name);
CREATE TABLE price_feed (name TEXT, category TEXT, price REAL);
INSERT INTO price_feed VALUES
('Laptop', 'Electronics', 1399),
('Headphones', 'Electronics', 120.5),
('Tablet', 'Electronics', 520);
INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price; -- first run
INSERT INTO products (name, category, price, stock)
SELECT name, category, price, 0 FROM price_feed WHERE true
ON CONFLICT (name) DO UPDATE
SET price = excluded.price
WHERE products.price IS NOT excluded.price; -- second run, the same file
SELECT changes() AS rows_changed_by_second_run,
(SELECT COUNT(*) FROM products) AS products;Expected output
rows_changed_by_second_run | products 0 | 11
changes() is the number of rows changed by the last statement: the second run changed nothing, and there are still 11 products.Exercise
A visit counter: create the table daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL). Write an upsert that records one visit to a page (creating it with 1 if missing, adding 1 if present) and run it for the pages /home, /sql, /home in turn. Show the table sorted by page.
CREATE TABLE daily_visits (page TEXT PRIMARY KEY, visits INTEGER NOT NULL);
-- three upserts: /home, /sql, /home
SELECT page, visits FROM daily_visits ORDER BY page;Expected output
page | visits /home | 2 /sql | 1
Exercise
A delivery has arrived: 200 *Notebook*s, 50 *Pen set*s and 10 units of a new product, Tablet (category Electronics, price 520). With one upsert, increase the stock of existing products and add the new one (the unique index on products.name is in the starter code). Then show name and stock of these three products, sorted by name.
CREATE UNIQUE INDEX idx_products_name ON products (name);
-- one INSERT ... ON CONFLICT here
SELECT name, stock FROM products
WHERE name IN ('Notebook', 'Pen set', 'Tablet')
ORDER BY name;Expected output
name | stock Notebook | 700 Pen set | 350 Tablet | 10
Remember
- An upsert is one atomic statement:
INSERT ... ON CONFLICT (key) DO NOTHING | DO UPDATE SET ...; the target must match a unique constraint. excludedis the row you tried to insert, the table name is the existing row;DO UPDATE ... WHEREcuts out useless updates.INSERT OR REPLACEmeans delete + insert: a newid, lost columns, orphaned links.RETURNINGhands back the changed rows at once (SQLite 3.35+, PostgreSQL); SQL Server hasOUTPUT, MySQL has none.MERGE(PostgreSQL 15+, SQL Server, Oracle) and MySQL'sON DUPLICATE KEY UPDATEare other forms of the same need.- A good load is idempotent: a second run must change nothing.