Skip to content
SQL

SQL Upsert: INSERT … ON CONFLICT and MERGE

Ihsan Ullah · 12 min read
SQL upsert, Lesson 18 of the SQL course: INSERT ... ON CONFLICT and MERGE

SQL course · Module 5: Creating and changing data · Lesson 18 of 40

Quick answer: An SQL upsert inserts a row, or updates it if it already exists. In PostgreSQL, write INSERT ... ON CONFLICT (column) DO NOTHING to skip duplicates, or ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col to update the existing row. ON CONFLICT only works on columns with a unique constraint or primary key. MERGE (PostgreSQL 15 and newer) compares a whole source table with a target table and can insert, update or delete in one statement.

In this lesson you will learn:

  • how ON CONFLICT DO NOTHING skips rows that already exist
  • how ON CONFLICT DO UPDATE and the EXCLUDED row update an existing row
  • why the conflict columns need a unique constraint, and the error you get without one
  • how MERGE applies a whole list of changes at once
  • how MySQL and SQLite write an upsert

Before you start: Lesson 16, SQL CREATE TABLE: Data Types and Constraints and Lesson 17, SQL INSERT, UPDATE and DELETE: Change Data Safely with RETURNING.

We ran every example on PostgreSQL 16.15 with the shop database, in order, and checked the current PostgreSQL 18 manual. This lesson changes the shop data, and new ids depend on it. Start psql in the folder that holds shop.sql from Lesson 3 and run \i shop.sql before you begin, so your ids match ours.

The problem an upsert solves

A new sign-up arrives with an email that is already in the table. email has a UNIQUE constraint, so a plain INSERT fails:

INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Ayesha M.', 'ayesha.malik@example.com', 'Lahore', 'Pakistan', '2026-11-05');
ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(ayesha.malik@example.com) already exists.

Often you want “add it if it is new, otherwise skip or update it” instead. That is an upsert (update + insert). A SELECT and then an INSERT is two statements, and another session could insert the same row in between (PostgreSQL docs: Transaction Isolation). An upsert is one.

ON CONFLICT DO NOTHING: skip existing rows

Add ON CONFLICT (email) DO NOTHING. The part in brackets is the conflict target: the columns whose unique constraint may clash.

INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Ayesha M.', 'ayesha.malik@example.com', 'Lahore', 'Pakistan', '2026-11-05')
ON CONFLICT (email) DO NOTHING;
INSERT 0 0

INSERT 0 0: no error, no row inserted. A new email goes in as usual:

INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Sara Khan', 'sara.khan@example.com', 'Islamabad', 'Pakistan', '2026-11-01')
ON CONFLICT (email) DO NOTHING
RETURNING id, name;
 id |   name    
----+-----------
 13 | Sara Khan
(1 row)

INSERT 0 1

Why 13 and not 11? The failed INSERT used 11 and the skipped one used 12. The manual explains that an INSERT with ON CONFLICT makes “any required nextval calls, before detecting any conflict” (PostgreSQL docs: Sequence Functions). Gaps in ids are normal; never rely on ids having no gaps.

ON CONFLICT DO UPDATE with EXCLUDED

Now a shopping cart. Order 22 is still pending:

SELECT * FROM order_items WHERE order_id = 22 ORDER BY product_id;
 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
       22 |          6 |        2 |      12.50
       22 |          8 |        1 |     149.00
(2 rows)

The customer adds one more Notebook Pack (product 6). The primary key allows one line per product, so we add to the quantity:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (22, 6, 1, 12.50)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity
RETURNING *;
 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
       22 |          6 |        3 |      12.50
(1 row)

INSERT 0 1

Inside DO UPDATE you can see two rows:

  • order_items.quantity is the row already in the table (2).
  • EXCLUDED.quantity is the row you tried to insert (1). The manual calls it “the row proposed for insertion” (PostgreSQL docs: INSERT).

So 2 + 1 = 3. The same statement for a product that is not in the order yet simply inserts it:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (22, 7, 1, 6.75)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity
RETURNING *;
 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
       22 |          7 |        1 |       6.75
(1 row)

INSERT 0 1

Both runs printed INSERT 0 1: the number counts rows “inserted or updated”, so RETURNING is how you see the result. The manual promises “an atomic INSERT or UPDATE outcome”, even with many sessions at once.

SQL upsert flow for Lesson 18: INSERT ... ON CONFLICT checks the unique key; a new key such as (22, 7) is inserted, an existing key such as (22, 6) is skipped with DO NOTHING or updated with DO UPDATE to quantity 2 + EXCLUDED 1 = 3.
One proposed row, three possible outcomes. ON CONFLICT reacts only when the unique key already exists; EXCLUDED holds the row you tried to insert. Real values from the shop database (PostgreSQL 16.15).

Update only when something changed

Add a WHERE to DO UPDATE to skip updates that change nothing. Ayesha moved to Islamabad:

INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Ayesha Malik', 'ayesha.malik@example.com', 'Islamabad', 'Pakistan', '2025-11-03')
ON CONFLICT (email) DO UPDATE
SET city = EXCLUDED.city
WHERE customers.city IS DISTINCT FROM EXCLUDED.city
RETURNING id, name, city;
 id |     name     |   city    
----+--------------+-----------
  1 | Ayesha Malik | Islamabad
(1 row)

INSERT 0 1

We ran exactly the same statement a second time:

 id | name | city 
----+------+------
(0 rows)

INSERT 0 0

The city was already Islamabad, so nothing was written. IS DISTINCT FROM treats two NULLs as equal (Lesson 6).

ON CONFLICT needs a unique constraint

Try to upsert a product by its name:

INSERT INTO products (name, category, price)
VALUES ('Desk Lamp', 'Home Office', 29.00)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

ON CONFLICT does not search for an equal value. It reacts only when a unique index or constraint (an “arbiter index” in the manual) rejects the row, and products.name has none. Add one with ALTER TABLE (mentioned in Lesson 16):

ALTER TABLE products ADD CONSTRAINT products_name_key UNIQUE (name);
ALTER TABLE

Now the same upsert works:

INSERT INTO products (name, category, price)
VALUES ('Desk Lamp', 'Home Office', 29.00)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price
RETURNING id, name, price;
 id |   name    | price 
----+-----------+-------
  5 | Desk Lamp | 29.00
(1 row)

INSERT 0 1

The Desk Lamp kept id 5; its price went from 32.00 to 29.00.

SQL MERGE: apply a whole list of changes

A supplier sends a new price list. We load it into a small table:

CREATE TABLE price_list (
    name     text,
    category text,
    price    numeric(10,2)
);

INSERT INTO price_list (name, category, price) VALUES
    ('USB-C Hub',     'Electronics', 36.00),
    ('Notebook Pack', 'Stationery',  12.50),
    ('Monitor Arm',   'Accessories', 64.00);
CREATE TABLE
INSERT 0 3

MERGE joins the source (price_list) to the target (products) and runs a WHEN clause for each row; it “can conditionally INSERT, UPDATE or DELETE rows” (PostgreSQL docs: MERGE). It arrived in PostgreSQL 15 (PostgreSQL 15 release notes).

MERGE INTO products p
USING price_list s
ON p.name = s.name
WHEN MATCHED AND p.price <> s.price THEN
    UPDATE SET price = s.price
WHEN NOT MATCHED THEN
    INSERT (name, category, price) VALUES (s.name, s.category, s.price);
MERGE 2

Read it like this:

  • ON p.name = s.name decides which rows match.
  • WHEN MATCHED AND p.price <> s.price updates products whose price changed.
  • WHEN NOT MATCHED inserts products that are new.
  • A matched row with the same price fits no WHEN clause and is left alone.

MERGE 2 is the number of rows changed:

SELECT id, name, category, price
FROM products
WHERE name IN ('USB-C Hub', 'Notebook Pack', 'Monitor Arm')
ORDER BY id;
 id |     name      |  category   | price 
----+---------------+-------------+-------
  3 | USB-C Hub     | Electronics | 36.00
  6 | Notebook Pack | Stationery  | 12.50
 12 | Monitor Arm   | Accessories | 64.00
(3 rows)

USB-C Hub went from 39.50 to 36.00, Monitor Arm is new (id 12; the Desk Lamp upsert used 11), and Notebook Pack did not change. Running the same MERGE again printed MERGE 0.

MERGE or ON CONFLICT?

MERGE matches rows with ON, so it needs no unique constraint (our test without one worked), and it can also delete. But for rows other sessions may insert at the same moment, the manual points to INSERT ... ON CONFLICT, which can “run an UPDATE if a concurrent INSERT occurs”. Use ON CONFLICT for single rows and live traffic, MERGE for syncing tables.

PostgreSQL 17 added RETURNING and WHEN NOT MATCHED BY SOURCE to MERGE (PostgreSQL 17 release notes). On PostgreSQL 16 they are syntax errors:

MERGE INTO products p
USING price_list s
ON p.name = s.name
WHEN MATCHED THEN UPDATE SET price = s.price
RETURNING p.id, p.name;
ERROR:  syntax error at or near "RETURNING"
LINE 5: RETURNING p.id, p.name;
        ^

Upsert in MySQL and SQLite

MySQL has no MERGE statement (MySQL 8.4: Data Manipulation Statements), and its INSERT syntax has no ON CONFLICT. It uses INSERT ... ON DUPLICATE KEY UPDATE c=c+1, with no conflict target: any UNIQUE index or PRIMARY KEY triggers it, so avoid it “on tables with multiple unique indexes”. For the new values, alias the row (VALUES (...) AS new ... c = new.a+new.b); the old VALUES() function “is deprecated” (MySQL 8.4: INSERT … ON DUPLICATE KEY UPDATE). INSERT IGNORE skips duplicates. We did not run MySQL; this comes from the MySQL 8.4 manual.

SQLite UPSERT “follows the syntax established by PostgreSQL” and arrived in version 3.24.0 (SQLite docs: UPSERT). In our SQLite 3.53.1 test, the cart upsert above worked unchanged. SQLite also accepted quantity without the table name and DO UPDATE without a conflict target; MERGE gave near "MERGE": syntax error. Lesson 2 compares these databases.

Try it yourself

Do the exercises right after the examples, before you reset the data; the ids in the solutions assume that order.

Exercise 1: Skip existing customers

In one INSERT, add Lucas Martin (lucas.martin@example.com, Toronto, Canada, 2025-12-14) and Zara Ali (zara.ali@example.com, Peshawar, Pakistan, 2026-11-06). Skip anyone whose email exists, and return the id and name of the rows really inserted.

Exercise 2: Add to the cart twice

Add 2 USB-C Hubs (product 3) at 36.00 to order 22 with an upsert that adds to the quantity. Run the statement twice. What is the quantity after each run?

Exercise 3: A bigger price list

Add Gel Pens 10-Pack (Stationery, 7.25) and Desk Mat (Accessories, 19.99) to price_list, run the same MERGE again, and check both products.

Solutions

Solution 1

INSERT INTO customers (name, email, city, country, signup_date)
VALUES ('Lucas Martin', 'lucas.martin@example.com', 'Toronto',  'Canada',   '2025-12-14'),
       ('Zara Ali',     'zara.ali@example.com',     'Peshawar', 'Pakistan', '2026-11-06')
ON CONFLICT (email) DO NOTHING
RETURNING id, name;
 id |   name   
----+----------
 17 | Zara Ali
(1 row)

INSERT 0 1

Lucas was skipped, so RETURNING lists only Zara. Her id is 17 because skipped and failed inserts also used ids.

Solution 2

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (22, 3, 2, 36.00)
ON CONFLICT (order_id, product_id)
DO UPDATE SET quantity = order_items.quantity + EXCLUDED.quantity
RETURNING *;
 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
       22 |          3 |        2 |      36.00
(1 row)

INSERT 0 1

The second run of the same statement:

 order_id | product_id | quantity | unit_price 
----------+------------+----------+------------
       22 |          3 |        4 |      36.00
(1 row)

INSERT 0 1

The first run inserted the line; the second found it and added 2 + 2.

Solution 3

INSERT INTO price_list (name, category, price) VALUES
    ('Gel Pens 10-Pack', 'Stationery',  7.25),
    ('Desk Mat',         'Accessories', 19.99);

MERGE INTO products p
USING price_list s
ON p.name = s.name
WHEN MATCHED AND p.price <> s.price THEN
    UPDATE SET price = s.price
WHEN NOT MATCHED THEN
    INSERT (name, category, price) VALUES (s.name, s.category, s.price);

SELECT id, name, price FROM products WHERE name IN ('Gel Pens 10-Pack', 'Desk Mat') ORDER BY id;
INSERT 0 2
MERGE 2
 id |       name       | price 
----+------------------+-------
  7 | Gel Pens 10-Pack |  7.25
 13 | Desk Mat         | 19.99
(2 rows)

The Gel Pens price changed from 6.75 and the Desk Mat was added; the three older rows needed no change.

When you finish, run DROP TABLE price_list; and then \i shop.sql in psql. The reset also removes products_name_key, because shop.sql builds the tables fresh.

Common mistakes

  • No unique constraint on the conflict columns. You get there is no unique or exclusion constraint matching the ON CONFLICT specification. Worse, ON CONFLICT DO NOTHING with no target just inserts: without the constraint, our test added a second Desk Lamp.
  • Leaving out the conflict target with DO UPDATE. ON CONFLICT DO UPDATE fails with ON CONFLICT DO UPDATE requires inference specification or constraint name, with the hint For example, ON CONFLICT (column_name).
  • Writing the column name alone in DO UPDATE. SET quantity = quantity + EXCLUDED.quantity fails with column reference "quantity" is ambiguous. Write order_items.quantity.
  • The same key twice in one statement. VALUES (22, 7, 1, 6.75), (22, 7, 2, 6.75) failed with ON CONFLICT DO UPDATE command cannot affect row a second time. Combine the rows first. In MERGE, two price-list rows for USB-C Hub gave MERGE command cannot affect row a second time.

Frequently asked questions

What is the difference between UPSERT and MERGE?

“Upsert” is a name for insert-or-update, not a command: INSERT ... ON CONFLICT in PostgreSQL and SQLite, INSERT ... ON DUPLICATE KEY UPDATE in MySQL. MERGE is a standard SQL command that compares a source with a target and can insert, update and delete in one pass; PostgreSQL has it since version 15.

Does ON CONFLICT work without a primary key?

It needs a primary key, UNIQUE constraint or unique index on exactly the columns in the conflict target. Without one, you get the “no unique or exclusion constraint” error shown above. Remove any duplicates, then add the constraint.

Why do my ids have gaps after an upsert?

The next id is taken before PostgreSQL detects the conflict, and it is not given back. That is why Sara got id 13.

Summary

  • An SQL upsert inserts a new row or handles the existing one in one statement.
  • ON CONFLICT (cols) DO NOTHING skips rows that already exist; DO UPDATE SET ... EXCLUDED.col updates them with the new values.
  • The conflict columns need a primary key, UNIQUE constraint or unique index.
  • MERGE (PostgreSQL 15+) syncs a target table with a source table; RETURNING on MERGE needs PostgreSQL 17.
  • MySQL uses ON DUPLICATE KEY UPDATE; SQLite uses the same ON CONFLICT syntax as PostgreSQL.

Upserts make data loads safe to repeat: a record loaded twice stays one row. Try ON CONFLICT in Project 2 of our data engineering projects, which stores scheduled results in PostgreSQL.

Next lesson

Next: Lesson 19, SQL Transactions: BEGIN, COMMIT, ROLLBACK and SAVEPOINT.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *