# Upserts With ON CONFLICT — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/a-upsert

> Insert or update in one statement.

## Atomic, idempotent writes

`INSERT ... ON CONFLICT (key) DO UPDATE` inserts new rows and updates existing ones atomically, using the special `EXCLUDED` row for the proposed values. `DO NOTHING` skips conflicting rows. It requires a unique constraint or index on the conflict target. Upserts make imports, counters and synchronisation jobs safe to retry. PostgreSQL 15 also added the SQL-standard `MERGE` statement for more complex cases.

## Adding stock with an upsert, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. Product 1 already had 50 units, so the conflict branch adds 5 to make 55; product 2 is new and inserted with 7. RETURNING shows both results.

```sql
CREATE TABLE stock (product_id bigint PRIMARY KEY REFERENCES products(id), qty int NOT NULL);
INSERT INTO stock VALUES (1, 50);

INSERT INTO stock (product_id, qty) VALUES (1, 5), (2, 7)
ON CONFLICT (product_id) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING product_id, qty;
```

Output:

```
 product_id | qty 
------------+-----
          1 |  55
          2 |   7
(2 rows)
```

## Make jobs idempotent

Upserts let a failed import be re-run without creating duplicates.

**Quiz:** What does EXCLUDED refer to in ON CONFLICT DO UPDATE?

- [ ] The deleted row
- [x] The row that was proposed for insertion
- [ ] A table of excluded users
- [ ] The previous transaction

*Answer:* The row that was proposed for insertion. Combine existing and proposed values.