How to Upsert (Merge, Insert on Duplicate Update) in PostgreSQL

An upsert describes a database operation that inserts a row when its key is new and handles the row when that key already exists. PostgreSQL provides this behavior with INSERT ... ON CONFLICT. The examples below are SQL syntax illustrations; no PostgreSQL server or execution evidence is supplied for this article.

What UPSERT Means in PostgreSQL

UPSERT combines the ideas of “update” and “insert,” but it is not a PostgreSQL SQL keyword. PostgreSQL uses the INSERT ... ON CONFLICT statement to handle a conflict as part of an insert. A DO UPDATE action must use a usable unique conflict target; an exclusion constraint cannot arbitrate DO UPDATE. A DO NOTHING action may omit its conflict target to ignore conflicts covered by usable unique or exclusion constraints.

Suppose car has an integer id primary key and a name column. It currently contains (1, 'Nissan GTR'), (2, 'Toyota Corolla'), and (3, 'Mercedes Benz'). A plain INSERT of (2, 'Toyota Vigo') violates the primary key because id 2 is already present. An upsert can instead replace the name for id 2, or leave that row as it is, depending on the conflict action you choose.

Use INSERT ... ON CONFLICT for a PostgreSQL Upsert

Use ON CONFLICT in the same statement as the insert. For example, inserting (2, 'Toyota Vigo') with ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name updates the existing id 2 row. The resulting row is (2, 'Toyota Vigo'); the other rows are unchanged.

The conflict target (id) identifies the unique key whose conflict should activate the action. It can name columns backed by a unique index or constraint. When a named constraint is clearer, PostgreSQL also supports the ON CONSTRAINT constraint_name form. Choose a target that matches the rule defining duplicates in your data. If the statement omits a target for DO NOTHING, it can ignore conflicts against any usable unique or exclusion constraint; DO UPDATE requires a conflict target.

Choose DO UPDATE or DO NOTHING

Choose DO UPDATE when a conflicting row should be refreshed or merged. Its SET clause lists only the columns to change, so the primary key can remain untouched. Choose DO NOTHING when the existing row should win and the attempted insert can be discarded. For example, INSERT INTO car (id, name) VALUES (2, 'Toyota Vigo') ON CONFLICT (id) DO NOTHING leaves (2, 'Toyota Corolla') unchanged.

These actions express different data policies: updating may overwrite newer or user-edited values, while doing nothing may leave stale values. Decide which behavior is correct for the application instead of treating either action as universally safe. An ON CONFLICT DO UPDATE statement also cannot update the same existing row more than once in a single command. If a multi-row insert contains duplicate values for the chosen conflict key, deduplicate the input before sending it.

Use EXCLUDED and Choose the Conflict Target

Inside DO UPDATE, EXCLUDED refers to the row proposed for insertion that encountered the conflict. It is not a table that stores pending rows. In SET name = EXCLUDED.name, PostgreSQL takes the incoming name; a reference such as car.name refers to the value already stored in the conflicting row. You can combine both values, for example by updating a counter from its stored value plus an incoming increment.

A DO UPDATE action can include a WHERE condition to avoid changing a row unless an application rule is met. PostgreSQL still locks a conflicting row even when that condition prevents the update. For example, a version check can update only when the incoming version is newer, but the caller should account for the possibility that no row was updated. Select the unique conflict target and update condition together: the target defines which rows count as duplicates, while the condition defines when a duplicate is changed.

Why Custom Functions, Triggers, Sequential Statements, and CTEs Are Usually Not the Default

A custom function or trigger can centralize specialized rules, but it adds code paths that callers must understand and maintain. Triggers also run as a side effect of inserts or updates, which can make behavior less visible. Prefer the built-in conflict clause for ordinary insert-or-update behavior; use custom logic when it supplies a specific rule that the statement cannot express clearly.

A check-then-insert sequence, such as querying whether id 2 exists and inserting only when it does not, has a race: another transaction can insert that id after the check. Wrapping the two statements in a transaction does not by itself make the check and insert atomic against concurrent sessions. A unique constraint protects the data, and INSERT ... ON CONFLICT lets PostgreSQL resolve the competing insert at the constraint. If a multi-step workflow needs stronger guarantees, choose an appropriate locking or isolation strategy and handle serialization or uniqueness errors as part of that design.

A common table expression (CTE) is scoped to the single SQL statement that begins with WITH. It can organize one statement, but a CTE name is not available to a separately issued INSERT or later statement. Therefore, a WITH clause followed by an update CTE and then a separate insert cannot be split across calls as if the CTE were a temporary table. For the usual insert-or-update task, keep conflict handling in one INSERT ... ON CONFLICT statement. Use a CTE only when it makes a genuinely multi-part single statement easier to read.