Upserts (ON CONFLICT)

An "upsert" is a single statement that inserts a row if it does not already exist, or updates it if it does — the name is a blend of insert and update. PostgreSQL's idiomatic way to do this is INSERT ... ON CONFLICT, which avoids the classic race condition of first checking whether a row exists and then deciding whether to insert or update it in a second statement.

The basic pattern

ON CONFLICT is attached to an INSERT and names the column (or columns) that might collide with an existing row, followed by what to do about it: DO NOTHING to silently skip the insert, or DO UPDATE SET to update the existing row instead.

Insert a product, or update its stock if the SKU already exists

SQL

INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES ('SKU-1001', 'Wireless Mouse', 24.99, 150)
ON CONFLICT (sku)
DO UPDATE SET stock_qty = products.stock_qty + EXCLUDED.stock_qty;

INSERT 0 1

Running that exact statement again — say, as part of restocking the same SKU a second time — does not fail with a duplicate-key error. Instead, because a row with sku = 'SKU-1001' now exists, PostgreSQL runs the DO UPDATE SET instead of the INSERT, adding the new shipment's quantity onto the existing stock.

Understanding EXCLUDED

EXCLUDED is a special, temporary row name available only inside the DO UPDATE SET clause. It refers to the row that would have been inserted — that is, the values from the VALUES clause of the INSERT — as opposed to the row that already exists in the table (which is referred to by the table's own name or an alias).

EXCLUDED vs. the table name — two different rows, both in scope

Inside DO UPDATE SET, two versions of the row are visible at once: the existing row (referenced normally, e.g. products.stock_qty) and the incoming row that triggered the conflict (referenced via EXCLUDED, e.g. EXCLUDED.stock_qty). In the example above, products.stock_qty + EXCLUDED.stock_qty adds the newly shipped quantity onto whatever was already in stock, rather than overwriting it — a plain EXCLUDED.stock_qty on its own would instead replace the existing quantity with the new one.

Replacing vs. accumulating a value with EXCLUDED

SQL

-- Replace the price outright with whatever was just supplied
INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES ('SKU-1002', 'Mechanical Keyboard', 79.00, 40)
ON CONFLICT (sku)
DO UPDATE SET unit_price = EXCLUDED.unit_price;

-- Accumulate onto the existing stock quantity instead of replacing it
INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES ('SKU-1002', 'Mechanical Keyboard', 79.00, 40)
ON CONFLICT (sku)
DO UPDATE SET stock_qty = products.stock_qty + EXCLUDED.stock_qty;

DO NOTHING

When a conflicting row should simply be left alone rather than updated, DO NOTHING skips the insert entirely for that row, without raising an error.

Silently skip duplicates

SQL

INSERT INTO products (sku, name, unit_price, stock_qty)
VALUES ('SKU-1001', 'Wireless Mouse', 24.99, 150)
ON CONFLICT (sku) DO NOTHING;

ON CONFLICT needs a unique or exclusion constraint on the target column(s)

ON CONFLICT (sku) only works because sku has a UNIQUE constraint — that constraint is what defines what counts as a "conflict" in the first place. Trying to use ON CONFLICT on a column with no unique or exclusion constraint raises an error, because PostgreSQL has no way to detect a conflict on that column without one.

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

ON CONFLICT can also target a constraint by name

Instead of listing columns, ON CONFLICT ON CONSTRAINT constraint_name can target a named unique constraint directly — useful when the constraint covers multiple columns or already has a descriptive name.

INSERT ... ON CONFLICT (column) DO UPDATE SET ... / DO NOTHING is PostgreSQL's upsert.
EXCLUDED refers to the row that would have been inserted, distinct from the existing row already in the table.
DO NOTHING silently skips the insert on conflict; DO UPDATE SET modifies the existing row instead.
The conflict target column(s) must be covered by a unique or exclusion constraint.