UPSERT (ON CONFLICT)

Overview

UPSERT is a combination of 'update' and 'insert'. In Postgres, it's written as INSERT ... ON CONFLICT. If a row with the same unique key already exists, instead of throwing an error, the database can either DO NOTHING or DO UPDATE to modify the existing row. When using DO UPDATE, you use the special `EXCLUDED` keyword to reference the values that were proposed for insertion.

It safely handles race conditions where multiple requests might try to create the same record simultaneously, and it simplifies application logic by merging check-and-create into a single database operation.

Where used: incrementing page views, saving drafts, idempotent background jobs

Why learn this

• You can write idempotent inserts that never fail on duplicate key errors.
• You avoid the inefficient 'SELECT to check, then INSERT or UPDATE' pattern which is vulnerable to race conditions.

Query walkthrough

INSERT INTO products (id, name, category, price) VALUES (2, 'Mouse V2', 'Electronics', 24.99) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price RETURNING id, name, price;

Focus: The conflict on id=2 triggers an update, merging the EXCLUDED.name and EXCLUDED.price into the existing row.

Common mistakes

• Missing unique constraint: ON CONFLICT requires a unique constraint or index on the column(s) specified. Without it, the database doesn't know what constitutes a 'conflict'.
• Referencing the old row instead of EXCLUDED: When doing an ON CONFLICT DO UPDATE, you must use the special EXCLUDED table to refer to the values that were proposed for insertion.

Glossary

idempotent

An operation that produces the same result no matter how many times it is run.

vulnerable

Exposed to the possibility of being attacked or harmed; in code, a weakness that can cause bugs or errors.

constitutes

To make up, form, or be considered as something, like defining what counts as a duplicate record.

Recall questions

• What keyword is used in the DO UPDATE clause to refer to the new values that failed to insert?

Understanding checks

Assume a customer with id = 1 already exists. What rows does this query return (assuming 'id' is a primary key)?

id: 1, name: 'Alice V2'

Customer id 1 already exists, causing a conflict. The DO UPDATE clause triggers, updating the name to 'Alice V2'.

Assume a product with id = 1 already exists. What rows does this query return?

(No rows)

Product id 1 exists. DO NOTHING silently ignores the insert, and since no row was inserted or updated, RETURNING yields nothing.

Practice tasks

Upsert an employee salary

Given this starter code, modify it to update the employee's salary to the new value if the employee id already exists.

Challenge

Upsert an order

Write an upsert query that tries to insert order id 8 for customer 2 on '2023-08-01' with status 'shipped'. If order id 8 already exists, update its status to 'shipped'. Return the id and status.

Continue learning

Previous: INSERT & Returning

Previous: Constraints & defaults

Return to SQL Roadmap