Upsert and RETURNING

“Update if it exists, insert if it doesn't” in one statement: `INSERT ... ON CONFLICT`, `excluded`, the danger of `INSERT OR REPLACE`, getting changed rows back with `RETURNING`, `MERGE` in PostgreSQL, `ON DUPLICATE KEY UPDATE` in MySQL, and loads you can safely re-run.

  • Advanced
  • 20 min
  • 12 questions
  • Lesson 11 of 28

In this lesson you will learn

  • Write upserts with ON CONFLICT ... DO NOTHING and DO UPDATE SET ... = excluded....
  • Explain why INSERT OR REPLACE is not an upsert
  • Get results from INSERT, UPDATE and DELETE with RETURNING
  • Recognise MERGE and ON DUPLICATE KEY UPDATE and build an idempotent load

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:

SQL
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:

SQL
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
The old Laptop is untouched, the Tablet was added.

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:

SQL
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
The Laptop kept its 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?”:

SQL
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

1) Add a condition to 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.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

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”:

SQL
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
The category and stock are lost, the 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:

SQL
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
The program gets the new ids without an extra query.
SQL
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).
SQL
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
Upsert + RETURNING: both the updated and the inserted row come back in their final state.
SQL
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)
PostgreSQL: old orders are moved to the archive in one statement.

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):

SQL
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)
The Headphones price is the same — it fell into no branch.

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.

SQL
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.

SQL
-- 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;
MySQL: the same upsert, without a conflict target.
TaskSQLitePostgreSQLMySQLSQL Server
Update if present, else insertON CONFLICT … DO UPDATEON CONFLICT … DO UPDATE, MERGE (15+)ON DUPLICATE KEY UPDATEMERGE
Skip duplicatesON CONFLICT DO NOTHINGON CONFLICT DO NOTHINGINSERT IGNORE (!)MERGE / NOT EXISTS
Return changed rowsRETURNING (3.35+)RETURNINGnoneOUTPUT
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.

  1. Staging table

    The supplier's file is loaded into price_feed first.

  2. Upsert

    INSERT ... SELECT ... WHERE true ON CONFLICT (name) DO UPDATE. In SQLite the WHERE after the SELECT matters: without it the word ON is read as a join's ON, and you get near "DO": syntax error.

  3. 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.

  4. Report and test

    RETURNING shows what changed; a second run must change 0 rows.

SQL
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
First run: the Laptop was updated, the Tablet inserted, the Headphones (same price) left alone.
SQL
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.

Exercise · SQL
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.

Exercise · SQL
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.
  • excluded is the row you tried to insert, the table name is the existing row; DO UPDATE ... WHERE cuts out useless updates.
  • INSERT OR REPLACE means delete + insert: a new id, lost columns, orphaned links.
  • RETURNING hands back the changed rows at once (SQLite 3.35+, PostgreSQL); SQL Server has OUTPUT, MySQL has none.
  • MERGE (PostgreSQL 15+, SQL Server, Oracle) and MySQL's ON DUPLICATE KEY UPDATE are other forms of the same need.
  • A good load is idempotent: a second run must change nothing.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. In ON CONFLICT (name) DO UPDATE SET price = excluded.price, what is excluded?