PostgreSQL ON CONFLICT: upsert syntax and examples

Quick answer

Insert a row, or update it if it already exists:

INSERT INTO inventory (sku, qty)
VALUES ('WIDGET-1', 99)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

Insert a row, or do nothing if it already exists:

INSERT INTO inventory (sku, qty)
VALUES ('WIDGET-1', 99)
ON CONFLICT (sku) DO NOTHING;

This is PostgreSQL's upsert. There is no UPSERT keyword, and MERGE (added in PostgreSQL 15) is a different, more general tool.

Setting up

Every example below uses this table:

CREATE TABLE inventory (
  sku        text PRIMARY KEY,
  qty        int,
  updated_at timestamptz DEFAULT now()
);

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 10);

ON CONFLICT DO NOTHING

The simplest form. If the insert would violate a constraint, skip it:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 99)
ON CONFLICT (sku) DO NOTHING;

INSERT 0 0

Note the 0 0. Nothing was inserted, and the existing row was left alone:

SELECT sku, qty FROM inventory;

   sku    | qty
----------+-----
 WIDGET-1 |  10
(1 row)

You can omit the conflict target entirely, which catches a conflict on any constraint:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 99)
ON CONFLICT DO NOTHING;

That is convenient and usually a mistake. It will silently swallow a conflict on a constraint you were not thinking about, including one added months later. Name the target unless you genuinely mean "any conflict at all".

ON CONFLICT DO UPDATE

The real upsert. When a conflict happens, update the existing row instead:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 99)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

INSERT 0 1

   sku    | qty
----------+-----
 WIDGET-1 |  99
(1 row)

Note the rowcount is 1 even though this was an update, not an insert. PostgreSQL reports it as an INSERT because that is the statement you ran. You cannot tell insert from update by the rowcount alone. See the RETURNING section for how to find out.

The EXCLUDED table

EXCLUDED is the row you tried to insert. It is not a real table; it is a reference PostgreSQL makes available inside the DO UPDATE clause.

That gives you two sets of values to work with:

• EXCLUDED.column is the incoming value
• inventory.column (the table name) is the existing value

Which means you can do arithmetic across both. To add to a running total rather than replacing it:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 5)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

   sku    | qty
----------+-----
 WIDGET-1 | 104
(1 row)

99 + 5. This is the pattern for counters, stock levels, and anything additive.

Qualify the column with the table name when you mean the existing value. Bare qty inside SET is ambiguous to read even where it parses, and SET qty = qty + 1 does not mean what a reader expects.

Updating a timestamp on upsert

A common shape, updating a modified time only when the row actually changes:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 42)
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty,
    updated_at = now();

Conditional updates with WHERE

DO UPDATE accepts a WHERE clause, so you can decline the update:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 3)
ON CONFLICT (sku) DO UPDATE
SET qty = EXCLUDED.qty
WHERE inventory.qty < EXCLUDED.qty;

That only overwrites when the incoming quantity is higher. When the WHERE is false, nothing happens and the rowcount is 0, exactly like DO NOTHING.

This is useful for last-write-wins guards:

ON CONFLICT (id) DO UPDATE
SET data = EXCLUDED.data, version = EXCLUDED.version
WHERE inventory.version < EXCLUDED.version;

The conflict target needs a unique constraint

ON CONFLICT works by detecting a unique violation, so the column you name must actually have a unique index or constraint behind it:

CREATE TABLE noconstraint (a int, b int);
INSERT INTO noconstraint VALUES (1,1) ON CONFLICT (a) DO NOTHING;

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

The fix is to add the constraint, not to change the query:

ALTER TABLE noconstraint ADD CONSTRAINT noconstraint_a_key UNIQUE (a);

You can also name a constraint directly instead of its columns:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 7)
ON CONFLICT ON CONSTRAINT inventory_pkey DO UPDATE SET qty = EXCLUDED.qty;

Naming columns is usually better, because it survives a constraint being renamed.

Composite and partial targets

For a multi-column unique constraint, list all the columns:

ON CONFLICT (tenant_id, sku) DO UPDATE SET qty = EXCLUDED.qty

If the unique index is partial, you have to repeat its predicate so PostgreSQL knows which index you mean:

CREATE UNIQUE INDEX ON inventory (sku) WHERE deleted_at IS NULL;

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 7)
ON CONFLICT (sku) WHERE deleted_at IS NULL
DO UPDATE SET qty = EXCLUDED.qty;

Omit the WHERE and you get the same "no unique or exclusion constraint matching" error, which is confusing when you can see the index right there.

RETURNING, and the trap in it

RETURNING works, and is the clean way to get the row back:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-2', 5)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
RETURNING sku, qty;

But RETURNING with DO NOTHING returns nothing when a conflict happens:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 1)
ON CONFLICT (sku) DO NOTHING RETURNING sku;

 sku
-----
(0 rows)

This catches people out constantly. Code that does INSERT ... ON CONFLICT DO NOTHING RETURNING id and then reads the id gets nothing back on the exact path where the row already exists, which is the path you were guarding against. If you need the id either way, use DO UPDATE with a no-op:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 1)
ON CONFLICT (sku) DO UPDATE SET sku = inventory.sku
RETURNING sku, qty;

That always returns a row. It costs a pointless write, which is a real trade-off on a hot table, but it is predictable.

Telling insert from update

xmax is a system column that is zero for a fresh insert and non-zero for an updated row:

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-3', 1)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty
RETURNING sku, (xmax = 0) AS was_inserted;

This works and is widely used, but it leans on an implementation detail rather than a documented contract. Fine for logging or metrics. Do not build correctness on it.

Bulk upserts

ON CONFLICT handles multi-row inserts, which is the fastest way to sync a batch:

INSERT INTO inventory (sku, qty) VALUES
  ('WIDGET-1', 10),
  ('WIDGET-2', 20),
  ('WIDGET-3', 30)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

One caveat that produces a genuinely confusing error: a single statement cannot update the same row twice.

INSERT INTO inventory (sku, qty) VALUES ('WIDGET-1', 1), ('WIDGET-1', 2)
ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty;

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

De-duplicate your batch before sending it. The hint is unusually clear about what to do.

ON CONFLICT vs MERGE

PostgreSQL 15 added MERGE, which is the SQL-standard statement for this kind of work:

The important row is the concurrency one. ON CONFLICT is built on unique-violation detection, so it is safe under concurrent inserts by design. MERGE matches on a join condition and can still raise a unique violation if another transaction inserts between the match and the write.

For plain upserts, use ON CONFLICT. Reach for MERGE when you need conditional deletes or genuinely portable SQL.

Common errors

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

The columns you named have no unique index or constraint. Add one. If the index is partial, repeat its WHERE clause in the ON CONFLICT target.

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Your batch contains duplicate keys. De-duplicate before inserting.

ERROR: column reference "qty" is ambiguous

Inside DO UPDATE SET, qualify the existing value with the table name (inventory.qty) and the incoming value with EXCLUDED.qty.

Quick reference