Insert or update: ON CONFLICT

The problem

A supplier sends a daily price list. For each product, you want to add it if it is new and update its price if you already sell it. The obvious approach is two steps: check whether the product exists, then INSERT or UPDATE. That means two round trips, and code that has to handle a product appearing between the check and the write.

Without help, inserting a product that already exists is simply an error:

INSERT INTO products (id, name, category, price) VALUES (2, 'Wireless mouse', 'Accessories', 1399.00);

ERROR:  duplicate key value violates unique constraint "products_pkey"
DETAIL:  Key (id)=(2) already exists.

ON CONFLICT DO UPDATE

ON CONFLICT tells PostgreSQL what to do instead of raising that error. Inside DO UPDATE, the special name EXCLUDED means “the row I tried to insert”:

BEGIN;
INSERT INTO products (id, name, category, price)
VALUES (2, 'Wireless mouse', 'Accessories', 1299.00)
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price
RETURNING id, name, price;
ROLLBACK;

 id |      name      |  price
----+----------------+---------
  2 | Wireless mouse | 1299.00

Product 2 already existed, so its price changed from 1,200 to 1,299. Had it not existed, the same statement would have inserted it. Unlike check-then-write in application code, PostgreSQL guarantees one of those two outcomes even when many sessions run the same upsert at once.

(id) is the conflict target: the unique column or columns that decide whether a row “already exists”. It must match a primary key, a unique constraint or a unique index.

ON CONFLICT DO NOTHING

Sometimes the right answer to “it already exists” is to leave it alone:

BEGIN;
INSERT INTO customers (id, name, email, joined_on)
VALUES (10, 'Asha again', 'asha@example.com', '2025-10-18')
ON CONFLICT (email) DO NOTHING
RETURNING id;
ROLLBACK;

 id
----
(0 rows)

INSERT 0 0

asha@example.com is taken, so nothing was inserted. RETURNING came back empty and the count is 0: that is how your code can tell the row was skipped.

Check yourself

1. What does EXCLUDED.price refer to in ON CONFLICT … DO UPDATE SET price = EXCLUDED.price?

   Show answer

   The price in the row you tried to insert, as opposed to the price already stored in the table.

   I could explain this without looking

2. Why does ON CONFLICT (email) need email to be unique?

   Show answer

   The conflict target must match a primary key, unique constraint or unique index. Without one, PostgreSQL has no rule for deciding that two rows are “the same”.

   I could explain this without looking

3. How can your code tell whether ON CONFLICT DO NOTHING inserted the row or skipped it?

   Show answer

   The affected row count is 1 or 0, and RETURNING returns the inserted row or nothing.

   I could explain this without looking