Upsert: ON CONFLICT & MERGE

A supplier sends a price list every morning. Some products are new, most already exist and only need a new price. Writing "check if the row exists, then insert or update" in application code is slow and breaks when two processes run at once. This pattern is called an upsert (update + insert), and PostgreSQL has two built-in tools for it.

In this lesson you will use INSERT ... ON CONFLICT for single-table upserts, and MERGE for synchronising a table with another set of rows, including deleting rows that disappeared from the source.

INSERT ... ON CONFLICT DO UPDATE

ON CONFLICT tells PostgreSQL what to do when an insert would break a unique or primary key. In brackets you name the column(s) of that key, called the conflict target. In the DO UPDATE part, the special table EXCLUDED holds the row you tried to insert:

INSERT INTO products (product_id, product_name, category, price, stock)
VALUES (5, 'Laptop Stand', 'Accessories', 1199.00, 0),
       (13, 'Wireless Earbuds', 'Audio', 1999.00, 60)
ON CONFLICT (product_id)
DO UPDATE SET price = EXCLUDED.price
RETURNING product_id, product_name, price, stock;

product_id | product_name     | price   | stock
-----------+------------------+---------+------
5          | Laptop Stand     | 1199.00 | 75
13         | Wireless Earbuds | 1999.00 | 60
(2 rows)

Product 5 already existed, so only its price changed and its stock of 75 stayed as it was, because stock is not in the SET list. Product 13 was new, so it was inserted with all its values. One statement, two different outcomes, and no gap in which another session could insert the same product between your check and your insert.

The conflict target must match a unique index or constraint exactly. ON CONFLICT (product_name) fails here, because product_name has no unique constraint.

Updating only when something changed

You can add a WHERE to DO UPDATE. A common use is to skip rows whose values are already the same, which saves write work, or to keep a value from going backwards:

CREATE TABLE daily_rates (
  product_id int PRIMARY KEY,
  rate numeric(10,2) NOT NULL,
  updated_on date NOT NULL
);

INSERT INTO daily_rates VALUES (1, 799.00, '2025-06-01'), (2, 3499.00, '2025-06-01');

INSERT INTO daily_rates AS d
VALUES (1, 799.00, '2025-06-02'), (2, 3299.00, '2025-06-02')
ON CONFLICT (product_id)
DO UPDATE SET rate = EXCLUDED.rate, updated_on = EXCLUDED.updated_on
WHERE d.rate IS DISTINCT FROM EXCLUDED.rate
RETURNING product_id, rate, updated_on;

Query OK
Query OK, 2 rows affected
product_id | rate    | updated_on
-----------+---------+-----------
2          | 3299.00 | 2025-06-02
(1 row)

Only product 2 is returned: its rate really changed. Product 1 had the same rate, so the WHERE skipped it. IS DISTINCT FROM is a NULL-safe "not equal": it treats two NULLs as equal and NULL versus a value as different. The table alias AS d lets the WHERE refer to the existing row.

DO NOTHING

Sometimes you just want "insert it if it is not there yet". ON CONFLICT DO NOTHING skips conflicting rows without an error:

CREATE TABLE newsletter (email text PRIMARY KEY, joined_on date NOT NULL);

INSERT INTO newsletter VALUES ('meera.nair@example.com', '2025-05-01');

INSERT INTO newsletter
VALUES ('meera.nair@example.com', '2025-06-10'),
       ('kabir.singh@example.com', '2025-06-10')
ON CONFLICT DO NOTHING
RETURNING email;

SELECT * FROM newsletter ORDER BY email;

Query OK
Query OK, 1 row affected
email
-----------------------
kabir.singh@example.com
(1 row)

email                   | joined_on
------------------------+-----------
kabir.singh@example.com | 2025-06-10
meera.nair@example.com  | 2025-05-01
(2 rows)

Meera's original sign-up date is kept, and only Kabir's row comes back from RETURNING, because skipped rows are not returned. Without a conflict target, DO NOTHING skips a conflict on any unique constraint.

MERGE

MERGE (added in PostgreSQL 15, from the SQL standard) compares a target table with a source of rows and runs different actions for matched and unmatched rows. Think of the morning delivery: products the shop knows get more stock, and new products are added.

MERGE INTO products AS p
USING (VALUES (8, 'HD Webcam', 'Accessories', 2199.00, 20),
              (12, 'Graphics Tablet', 'Accessories', 4599.00, 5),
              (15, 'USB Microphone', 'Audio', 3299.00, 15))
      AS delivery (product_id, product_name, category, price, qty)
ON p.product_id = delivery.product_id
WHEN MATCHED THEN
  UPDATE SET stock = p.stock + delivery.qty
WHEN NOT MATCHED THEN
  INSERT (product_id, product_name, category, price, stock)
  VALUES (delivery.product_id, delivery.product_name, delivery.category, delivery.price, delivery.qty)
RETURNING merge_action() AS action, p.product_id, p.product_name, p.stock;

action | product_id | product_name    | stock
-------+------------+-----------------+------
UPDATE | 8          | HD Webcam       | 20
UPDATE | 12         | Graphics Tablet | 17
INSERT | 15         | USB Microphone  | 15
(3 rows)

The source here is a VALUES list, but it can be any table or query, such as a staging table loaded from a CSV file. merge_action() (PostgreSQL 17 and later) tells you which action produced each returned row.

MERGE can do things ON CONFLICT cannot: delete matched rows, use different conditions per branch (WHEN MATCHED AND p.stock = 0 THEN ...), and work without a unique constraint. Since PostgreSQL 17 it can also act on target rows that are missing from the source with WHEN NOT MATCHED BY SOURCE, which turns it into a full synchronisation:

MERGE INTO featured_products AS f
USING todays_picks AS t
ON f.product_id = t.product_id
WHEN NOT MATCHED THEN INSERT (product_id) VALUES (t.product_id)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

After this runs, featured_products contains exactly the products in todays_picks: new picks are added and yesterday's picks that are gone are deleted.

Which one should you use?

ON CONFLICT is designed for concurrency: it guarantees an insert or an update even when another session inserts the same key at the same moment. MERGE does not give that guarantee; two sessions merging the same new key at once can still hit a unique-key error, so it suits batch jobs better than busy web requests.

Common mistakes

• A conflict target with no unique index. ON CONFLICT (col) needs a unique constraint or index on exactly those columns.
• Forgetting EXCLUDED. SET price = price keeps the old value. The new value is EXCLUDED.price.
• Expecting DO NOTHING rows in RETURNING. Skipped rows are not returned.
• One target row matched by two source rows in MERGE. PostgreSQL raises an error; remove duplicates from the source first.

Key points

• An upsert inserts new rows and updates existing ones in one atomic statement.
• INSERT ... ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col is PostgreSQL's concurrent-safe upsert.
• DO UPDATE ... WHERE can skip unchanged rows; DO NOTHING skips conflicts silently.
• MERGE compares a target with a source and can update, insert or delete per branch.
• PostgreSQL 17 added RETURNING, merge_action() and WHEN NOT MATCHED BY SOURCE to MERGE.

CREATE TABLE stock_counts (product_id int PRIMARY KEY, counted int NOT NULL);
INSERT INTO stock_counts VALUES (1, 118), (2, 44);

INSERT INTO stock_counts VALUES (2, 43), (3, 18)
ON CONFLICT (product_id) DO UPDATE SET counted = EXCLUDED.counted
RETURNING *;