UPSERT with ON CONFLICT

Insert or update in one statement

Lesson 16 of 20 · 10 min · Module 5: Change data safely

A delivery arrives: some products already exist and need more stock, others are new. Checking each one first and then choosing INSERT or UPDATE is slow and can go wrong. An upsert does both in one safe statement.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Insert or update with ON CONFLICT
Combine existing and new values with excluded
Write repeatable data loads

The idea

INSERT … ON CONFLICT (id) DO UPDATE SET … inserts new rows and updates rows whose key already exists. Inside the update, excluded.stock is the value you tried to insert, and stock is the value already stored.

INSERT INTO products (id, name, category, price_cents, stock)
VALUES (5, 'Notebook', 'Office', 650, 20)
ON CONFLICT (id) DO UPDATE SET stock = stock + excluded.stock;
SELECT id, stock FROM products WHERE id = 5;

01The conflict target

ON CONFLICT (id) says which unique key decides whether a row already exists. It must match a PRIMARY KEY or UNIQUE constraint.

02excluded is the incoming row

In SET stock = stock + excluded.stock, stock is the stored value and excluded.stock the delivered amount. Use DO UPDATE SET name = excluded.name to overwrite instead of adding.

03DO NOTHING for "insert if new"

ON CONFLICT DO NOTHING skips rows that already exist, which is handy for loading reference data repeatedly without errors.

INSERT INTO products (id, name, category, price_cents) VALUES (1, 'Ceramic Mug', 'Kitchen', 1200)
ON CONFLICT DO NOTHING;
SELECT COUNT(*) FROM products;

Output

Your turn

Record a delivery: 40 more Green Tea (id 3), 5 more Desk Lamp (id 6), and a new product id 9, Oat Milk, category Pantry, price 450 cents, 30 in stock. Use one upsert that adds delivered stock to existing products. Then select id, name, stock for ids 3, 6 and 9, sorted by id.

What the checks look for

Adds stock to Green Tea and Desk Lamp and creates Oat Milk
Leaves other products unchanged
Keeps the existing prices and names

Try it in the editor →

Take this with you

An upsert merges new data into existing rows in one atomic step.

Go a little further

Also update the price of existing products, but only when the delivered price is lower: DO UPDATE SET price_cents = MIN(price_cents, excluded.price_cents).