id is the primary key of products. An INSERT with an id that exists fails:
text
INSERT INTO products (id, name, price, stock)
VALUES (7, 'Linux Sticker Pack', 6.00, 350);
ERROR: duplicate key value violates unique constraint "products_pkey"A supplier sends a list of products and stock levels. Some ids are new, some exist. You want to insert the new rows and update the others. That is an upsert (update or insert).
sql
INSERT INTO products (id, name, price, stock)
VALUES (7, 'Linux Sticker Pack', 6.00, 350)
ON CONFLICT (id)
DO UPDATE SET stock = EXCLUDED.stock;INSERT … VALUES (…)the row to addON CONFLICT (id)when this key existsDO UPDATE SET stock = EXCLUDED.stockchange the existing row instead
ON CONFLICT (id)names the column of a PRIMARY KEY or UNIQUE constraint.EXCLUDEDis the row that you tried to insert.EXCLUDED.stockis 350.DO UPDATE SETworks like the SET of an UPDATE. It changes only the existing row with that key.
ON CONFLICT decides row by row:
| The key of a row you insert | What happens |
|---|---|
| is new | the row is inserted |
| exists | DO UPDATE changes the existing row |
Keep the existing row
DO NOTHING skips every row whose key exists. The other rows are inserted, and there is no error:
sql
INSERT INTO newsletter (email, name)
VALUES ('liam.p@example.com', 'Liam Patel')
ON CONFLICT (email) DO NOTHING;