Using UPSERT Operations
1. Understanding INSERT ON CONFLICT
| Concept | Detail |
|---|---|
| Conflict target | Unique/exclusion constraint or column list |
| DO NOTHING | Skip conflicting rows |
| DO UPDATE | Apply updates to existing row |
| excluded.col | Refers to candidate row's value |
2. Using DO NOTHING
INSERT INTO customers (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO NOTHING;3. Using DO UPDATE SET
INSERT INTO customers (email, full_name, updated_at)
VALUES ('alice@example.com', 'Alice W.', now())
ON CONFLICT (email) DO UPDATE
SET full_name = excluded.full_name,
updated_at = excluded.updated_at;4. Specifying Conflict Target
| Form | Example |
|---|---|
| Column list | ON CONFLICT (email) |
| Constraint name | ON CONFLICT ON CONSTRAINT customers_email_key |
| Expression index | ON CONFLICT (lower(email)) |
| Partial index | Must match WHERE predicate |
5. Using WHERE Clause in DO UPDATE
INSERT INTO inventory (sku, qty, updated_at)
VALUES ('A100', 5, now())
ON CONFLICT (sku) DO UPDATE
SET qty = excluded.qty, updated_at = excluded.updated_at
WHERE inventory.qty < excluded.qty; -- only bump if larger6. Using excluded.*
INSERT INTO counters (key, value, updated_at)
VALUES ('hits', 1, now())
ON CONFLICT (key) DO UPDATE
SET value = counters.value + excluded.value,
updated_at = excluded.updated_at;| Alias | Refers To |
|---|---|
| excluded | Row proposed by INSERT |
| table_name | Existing conflicting row |
7. Using RETURNING with UPSERT
INSERT INTO customers (email, full_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET full_name = excluded.full_name
RETURNING id, (xmax = 0) AS was_inserted;Note:
xmax = 0 identifies whether the row was newly inserted vs updated.8. Handling Unique Constraint Violations
| Pattern | When |
|---|---|
| ON CONFLICT DO NOTHING | Idempotent inserts |
| ON CONFLICT DO UPDATE | Last-write-wins merge |
| Savepoint + retry | Multi-constraint cases |
9. Handling Primary Key Conflicts
INSERT INTO sessions (id, user_id, last_seen)
VALUES ('abc', 42, now())
ON CONFLICT (id) DO UPDATE SET last_seen = excluded.last_seen;10. Using UPSERT with Multiple Columns
INSERT INTO daily_stats (date, metric, value)
VALUES (current_date, 'signups', 5)
ON CONFLICT (date, metric) DO UPDATE
SET value = daily_stats.value + excluded.value;| Required | Detail |
|---|---|
| Unique constraint | Must exist on (date, metric) |
| Order matters | Column list must match an existing unique/exclusion constraint |