Upsert with ON CONFLICT

“Insert this row, unless it already exists, in which case update it” is one of the most common write patterns there is. Doing it as a SELECT followed by an INSERT or UPDATE is a race condition: two requests can both see “not there yet” and both insert. PostgreSQL solves it atomically with upsert.

Use ON CONFLICT against a real unique constraint:

INSERT INTO settings (user_id, theme)
VALUES (1, 'dark')
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme;

If no row with user_id = 1 exists, this is a plain insert. If one exists, the unique constraint on user_id fires, and instead of an error PostgreSQL runs the DO UPDATE against the existing row.

EXCLUDED is the row you tried to insert. EXCLUDED.theme means “the theme value from my VALUES clause”, so the same statement works no matter which value you pass in.

The conflict target is not optional decoration

(user_id) names which uniqueness rule you are handling, and PostgreSQL checks that a matching unique index or constraint really exists. Without one you get:

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

That error is telling you something real: upsert is only meaningful against an actual constraint. If user_id is not unique, “the existing row” is not a well-defined idea, and the fix is to add the constraint, not to fight the syntax.

Name the conflict target. Do not silently swallow every possible error.

DO NOTHING, used narrowly

Sometimes the right reaction to a duplicate is to skip it, for example when replaying events that may already be recorded:

INSERT INTO events (event_id, payload)
VALUES ('evt_9f2c', '{"type":"signup"}')
ON CONFLICT (event_id) DO NOTHING;

Keep the explicit target even here. A bare ON CONFLICT DO NOTHING ignores conflicts on any constraint, which quietly discards rows for reasons you never anticipated.

Verify what an upsert actually did by adding RETURNING id, theme to the statement, or by checking the row count: INSERT 0 1 means one row was written, INSERT 0 0 after DO NOTHING means the row was skipped.