Upserts and INSERT ON CONFLICT

~12 min · transactions, upsert

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Insert-or-update without a race

The classic anti-pattern: "check if a row exists; if so UPDATE, else INSERT." Two of those running concurrently both see "doesn't exist," both INSERT, and one fails on the unique constraint. PostgreSQL's ON CONFLICT clause makes the whole operation atomic.

Three shapes you'll use

ON CONFLICT (col) DO NOTHING — silent dedup; useful for "insert if not present."
ON CONFLICT (col) DO UPDATE SET ... — true upsert; insert or update.
ON CONFLICT ON CONSTRAINT name DO ... — when you want to target a named constraint instead of guessing the column.

EXCLUDED — the row that tried to insert

Inside DO UPDATE, the keyword EXCLUDED refers to the row from the INSERT. Without prefix, column names refer to the existing row. This lets you choose: keep the old value, take the new one, or compute from both.

Code

DO NOTHING (dedup)·sql

INSERT INTO tags (name) VALUES ('postgres'), ('sql'), ('postgres')
ON CONFLICT (name) DO NOTHING;
-- Two rows inserted; the duplicate 'postgres' silently skipped.

DO UPDATE (upsert)·sql

INSERT INTO users (email, name, login_count, last_login)
VALUES ('alice@example.com', 'Alice', 1, now())
ON CONFLICT (email) DO UPDATE
SET name = EXCLUDED.name,
    login_count = users.login_count + 1,
    last_login = EXCLUDED.last_login
RETURNING *;

WHERE in DO UPDATE·sql

-- Only update if the new value is actually newer
INSERT INTO sensor_readings (id, value, recorded_at)
VALUES (?, ?, ?)
ON CONFLICT (id) DO UPDATE
SET value = EXCLUDED.value,
    recorded_at = EXCLUDED.recorded_at
WHERE EXCLUDED.recorded_at > sensor_readings.recorded_at;

External links

PostgreSQL INSERT ... ON CONFLICT

Exercise

Replace any 'check then insert/update' code in your project with INSERT ... ON CONFLICT. Verify it handles concurrent writes by running the upsert from two clients at once.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousDeadlocks: How They Happen Next →Idempotency in Database Operations

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.