PostgreSQL Upsert with ON CONFLICT: Examples and Pitfalls

To upsert in PostgreSQL, add an ON CONFLICT clause to a normal INSERT. ON CONFLICT (key) DO UPDATE SET ... updates the row that already exists, and ON CONFLICT (key) DO NOTHING quietly skips it. The whole thing runs as one atomic statement, so two sessions running the same upsert at the same time will not create duplicates.

This guide walks through every form you will use in real code: single and multi-column keys, the EXCLUDED row, conditional updates, partial and expression indexes, telling inserts from updates, and when MERGE is the better fit. Every example below was run on PostgreSQL 16, and the output shown is the real output.

How INSERT ON CONFLICT works

PostgreSQL tries to insert each row. If the row would violate a unique constraint or unique index named in the conflict target, PostgreSQL does not raise an error. It takes the alternative action instead. The documentation calls the chosen index the arbiter index.

The general shape is:

INSERT INTO table_name (columns...)
VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = EXCLUDED.column [, ...]
   [WHERE condition]
-- or
ON CONFLICT (conflict_target) DO NOTHING;

Three pieces matter:

• Conflict target: the column list (or expression, or ON CONSTRAINT name) that identifies the unique index to check. It is required for DO UPDATE and optional for DO NOTHING.
• EXCLUDED: a special row that holds the values you tried to insert. The existing row is referenced by the table name or its alias.
• Atomicity: the docs state that DO UPDATE "guarantees an atomic INSERT or UPDATE outcome". You do not need a separate SELECT, and you do not need to catch duplicate key errors in a retry loop.

ON CONFLICT has been available since PostgreSQL 9.5. It is a PostgreSQL extension, not standard SQL. The standard alternative is MERGE, covered later.

Sample setup

Create a small product catalogue to follow along. The primary key on product_id gives us a unique index to conflict on.

CREATE TABLE products (
    product_id  integer PRIMARY KEY,
    name        text    NOT NULL,
    price       numeric(10,2) NOT NULL,
    stock       integer NOT NULL DEFAULT 0,
    updated_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO products (product_id, name, price, stock) VALUES
    (101, 'USB-C Cable',      299.00, 50),
    (102, 'Wireless Mouse',   799.00, 20),
    (103, 'Laptop Stand',    1499.00,  8);

First, see the problem upsert solves. A plain insert of an existing key fails:

INSERT INTO products (product_id, name, price, stock)
VALUES (101, 'USB-C Cable', 279.00, 60);

ERROR:  duplicate key value violates unique constraint "products_pkey"
DETAIL:  Key (product_id)=(101) already exists.

Inside a transaction, that error also aborts everything else you did. ON CONFLICT avoids it.

ON CONFLICT DO NOTHING: skip duplicates

Use DO NOTHING when an existing row should win and new duplicates can be ignored, for example when loading events that may be delivered twice.

INSERT INTO products (product_id, name, price, stock)
VALUES (101, 'USB-C Cable', 279.00, 60)
ON CONFLICT (product_id) DO NOTHING;

INSERT 0 0

The command tag INSERT 0 0 means zero rows were inserted, and no error was raised. The existing row for 101 is unchanged. With DO NOTHING you may leave out the conflict target entirely; PostgreSQL then skips conflicts on any unique constraint of the table.

ON CONFLICT DO UPDATE: insert or update

Step 1: overwrite with the new values

This is the classic upsert. Row 101 exists, so it is updated. Row 104 is new, so it is inserted. Both happen in one statement.

INSERT INTO products (product_id, name, price, stock)
VALUES (101, 'USB-C Cable', 279.00, 60),
       (104, 'HDMI Adapter', 649.00, 15)
ON CONFLICT (product_id) DO UPDATE
SET price      = EXCLUDED.price,
    stock      = EXCLUDED.stock,
    updated_at = now();

SELECT product_id, name, price, stock FROM products ORDER BY product_id;

INSERT 0 2

Notice the command tag says INSERT 0 2 even though one row was updated. PostgreSQL counts both inserted and updated rows. EXCLUDED.price is 279.00, the value from the VALUES list, so the old price of 299.00 was replaced.

Step 2: combine old and new values

You can reference the existing row too. Give the table an alias with AS so the expression reads clearly. Here, incoming stock is added to the current stock instead of replacing it:

INSERT INTO products AS p (product_id, name, price, stock)
VALUES (102, 'Wireless Mouse', 799.00, 5)
ON CONFLICT (product_id) DO UPDATE
SET stock = p.stock + EXCLUDED.stock
RETURNING product_id, stock;

The old stock was 20, the incoming value was 5, and the result is 25. This pattern is common for counters, daily totals and inventory. Because the existing row is locked before it is updated, concurrent upserts on the same key add up correctly under the default READ COMMITTED isolation level instead of losing increments. Under REPEATABLE READ or SERIALIZABLE, a concurrent change can instead raise a serialization error that your code must retry.

Step 3: update only when something changed

DO UPDATE accepts a WHERE clause. Rows that fail the condition are not updated. This avoids writing a new row version (and firing update triggers) when the data is identical.

INSERT INTO products AS p (product_id, name, price, stock)
VALUES (103, 'Laptop Stand', 1499.00, 8),
       (104, 'HDMI Adapter', 599.00, 15)
ON CONFLICT (product_id) DO UPDATE
SET price = EXCLUDED.price, updated_at = now()
WHERE p.price IS DISTINCT FROM EXCLUDED.price
RETURNING product_id, price;

INSERT 0 1

Product 103 already had the price 1499.00, so it was skipped and does not appear in RETURNING. Only 104 changed. IS DISTINCT FROM is used instead of <> because it treats NULLs as comparable values.

Upsert on multiple columns

Conflict targets can have several columns, as long as a unique constraint or unique index covers exactly those columns. A daily sales summary per store is a typical case.

CREATE TABLE daily_sales (
    store_id   integer NOT NULL,
    sale_date  date    NOT NULL,
    amount     numeric(12,2) NOT NULL
);

INSERT INTO daily_sales (store_id, sale_date, amount)
VALUES (1, '2026-10-15', 5000)
ON CONFLICT (store_id, sale_date) DO UPDATE
SET amount = daily_sales.amount + EXCLUDED.amount;

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

This is the most common ON CONFLICT error. PostgreSQL needs a unique index to detect the conflict, and the table has none. Add one and run the upsert again:

ALTER TABLE daily_sales
    ADD CONSTRAINT daily_sales_store_date_uk UNIQUE (store_id, sale_date);

INSERT INTO daily_sales (store_id, sale_date, amount)
VALUES (1, '2026-10-15', 5000),
       (2, '2026-10-15', 3200)
ON CONFLICT (store_id, sale_date) DO UPDATE
SET amount = daily_sales.amount + EXCLUDED.amount;

INSERT INTO daily_sales (store_id, sale_date, amount)
VALUES (1, '2026-10-15', 1250)
ON CONFLICT (store_id, sale_date) DO UPDATE
SET amount = daily_sales.amount + EXCLUDED.amount;

SELECT * FROM daily_sales ORDER BY store_id;

Store 1 now has 5000 + 1250 = 6250. The order of columns in the conflict target does not have to match the index. ON CONFLICT (sale_date, store_id) works the same way, because PostgreSQL matches the set of columns "without regard to order".

Partial and expression unique indexes

Partial index: repeat the WHERE predicate

A soft-delete design often allows one active row per email but keeps deleted copies. That needs a partial unique index:

CREATE TABLE customers (
    customer_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        text NOT NULL,
    full_name    text NOT NULL,
    deleted_at   timestamptz
);

CREATE UNIQUE INDEX customers_email_active_uk
    ON customers (email)
    WHERE deleted_at IS NULL;

INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha Rao');

INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha R. Rao')
ON CONFLICT (email) DO UPDATE SET full_name = EXCLUDED.full_name;

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

The index exists, but PostgreSQL will not pick a partial index unless your conflict target includes a predicate that matches it. Add the index's WHERE clause after the column list:

INSERT INTO customers (email, full_name)
VALUES ('asha@example.com', 'Asha R. Rao')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET full_name = EXCLUDED.full_name
RETURNING customer_id, email, full_name;

Expression index: wrap the expression in parentheses

For case-insensitive uniqueness you might index lower(email). The conflict target must use the same expression, inside its own parentheses:

CREATE TABLE subscribers (email text NOT NULL, name text);
CREATE UNIQUE INDEX subscribers_lower_email_uk ON subscribers (lower(email));
INSERT INTO subscribers VALUES ('Ravi@Example.com', 'Ravi');

INSERT INTO subscribers (email, name)
VALUES ('ravi@example.com', 'Ravi Kumar')
ON CONFLICT ((lower(email))) DO UPDATE SET name = EXCLUDED.name
RETURNING *;

The incoming email differs in case, but lower() of both is the same, so the existing row was updated. Its stored email keeps the original spelling because SET did not touch it.

How to tell if a row was inserted or updated

RETURNING gives you the final row in both cases. To know which action happened on PostgreSQL 17 or earlier, a widely used trick is to check the system column xmax. A freshly inserted row has xmax = 0; a row updated through ON CONFLICT does not.

INSERT INTO products (product_id, name, price, stock)
VALUES (104, 'HDMI Adapter', 549.00, 15),
       (105, 'Webcam', 2499.00, 4)
ON CONFLICT (product_id) DO UPDATE
SET price = EXCLUDED.price
RETURNING product_id, (xmax = 0) AS inserted;

Treat this as a practical trick, not a documented API. It relies on internal row header behaviour. On PostgreSQL 18 and later, use the documented old and new aliases in RETURNING instead: old.product_id is NULL when the row was inserted and non-NULL when an existing row was updated.

ON CONFLICT vs MERGE

PostgreSQL 15 added the SQL standard MERGE statement. It can do the same insert-or-update job and more, such as deleting matched rows:

MERGE INTO products AS p
USING (VALUES (101, 'USB-C Cable', 259.00, 70),
              (107, 'Monitor Arm', 3299.00, 3))
      AS s(product_id, name, price, stock)
ON p.product_id = s.product_id
WHEN MATCHED THEN
    UPDATE SET price = s.price, stock = s.stock
WHEN NOT MATCHED THEN
    INSERT (product_id, name, price, stock)
    VALUES (s.product_id, s.name, s.price, s.stock);

MERGE 2

Product 101 was updated to 259.00 and product 107 was inserted. So which should you use?

The concurrency row is the deciding factor for most applications. The MERGE documentation itself suggests INSERT ... ON CONFLICT "as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs". Use ON CONFLICT for application upserts where many sessions write the same keys. Use MERGE for batch synchronisation from a staging table, especially when you also need deletes. If you are moving code from Oracle, MERGE will feel familiar; see the Oracle MERGE statement guide for comparison.

Common mistakes

1. Duplicate keys inside the same statement

If the rows you insert contain the same key twice, DO UPDATE cannot decide which one should win:

INSERT INTO products (product_id, name, price, stock)
VALUES (106, 'Keyboard', 999.00, 10),
       (106, 'Keyboard', 949.00, 12)
ON CONFLICT (product_id) DO UPDATE
SET price = EXCLUDED.price;

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.

This happens a lot with INSERT ... SELECT from a staging table. Remove duplicates in the source first, for example with SELECT DISTINCT ON (product_id) ... ORDER BY product_id, loaded_at DESC to keep the latest row per key.

2. Leaving out the conflict target with DO UPDATE

INSERT INTO products (product_id, name, price, stock)
VALUES (101, 'USB-C Cable', 279.00, 60)
ON CONFLICT DO UPDATE SET price = EXCLUDED.price;

ERROR:  ON CONFLICT DO UPDATE requires inference specification or constraint name
LINE 3: ON CONFLICT DO UPDATE SET price = EXCLUDED.price;
        ^
HINT:  For example, ON CONFLICT (column_name).

Only DO NOTHING may omit the target. Add (product_id).

3. Expecting a conflict on a column with no unique index

ON CONFLICT does not search for matching rows. It only reacts to unique index violations. If the columns are not covered by a unique constraint or unique index (including the predicate for a partial index), you get the "no unique or exclusion constraint matching" error shown earlier.

4. Being surprised by gaps in identity or serial values

Look at the customers table after one more insert:

INSERT INTO customers (email, full_name)
VALUES ('vikram@example.com', 'Vikram Shah')
RETURNING customer_id;

The new customer got id 3, not 2. The earlier upsert for asha@example.com evaluated the identity default before discovering the conflict, so value 2 was consumed and thrown away. This is normal sequence behaviour. Never rely on identity or serial columns being gap-free.

5. Expecting RETURNING to show skipped rows

RETURNING returns only rows that were inserted or updated. With DO NOTHING, or with a WHERE clause that filters a row out, that row is missing from the result. If you need the id of an existing row either way, a common workaround is DO UPDATE SET email = EXCLUDED.email (a no-op update), which always returns the row but writes a new row version each time.

Performance tips

• Batch your rows. One INSERT with hundreds of rows in VALUES, or an INSERT ... SELECT from a staging table, is far cheaper than one round trip per row.
• Skip no-op updates with WHERE ... IS DISTINCT FROM EXCLUDED.... Every DO UPDATE creates a new row version, which later needs VACUUM.
• Keep the arbiter index lean. Each upsert probes it, so a narrow unique index on the real business key is ideal.
• Avoid ON CONFLICT during CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY on the same unique index. The docs warn that upserts may unexpectedly fail with a unique violation while that runs.
• Sort large batches by key when several sessions upsert overlapping keys. Taking row locks in the same order lowers the chance of deadlocks.

Frequently asked questions

Does PostgreSQL have an UPSERT statement?

Not as a keyword. Upsert in PostgreSQL is INSERT ... ON CONFLICT DO UPDATE, available since version 9.5. From version 15 you can also use MERGE.

Can I upsert without a unique constraint?

Not with ON CONFLICT; it needs a unique constraint or unique index to detect conflicts. MERGE works with any join condition, but without a unique index nothing stops two concurrent sessions from inserting the same row. In practice, add the unique constraint.

How do I use ON CONFLICT with multiple columns?

List them all in the target, for example ON CONFLICT (store_id, sale_date). A unique constraint or index must cover exactly those columns, in any order.

What is EXCLUDED in PostgreSQL?

EXCLUDED is a special row in the DO UPDATE clause holding the values that were proposed for insertion. Use EXCLUDED.column for the new value and the table name or alias for the current value.

Is INSERT ON CONFLICT faster than checking with SELECT first?

Yes in most cases, because it is one statement and one round trip instead of two. More importantly, SELECT-then-INSERT is not safe under concurrency: two sessions can both see no row and both try to insert.

Summary

• Use ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col to insert or update in one atomic statement.
• Use ON CONFLICT DO NOTHING to skip duplicates silently.
• Make sure a unique constraint or unique index covers exactly the conflict target columns.
• Repeat the WHERE predicate for partial indexes and wrap expressions in double parentheses.
• Add a WHERE clause to DO UPDATE to skip updates when nothing changed.
• Remove duplicate keys from the source rows before a multi-row upsert.
• Prefer ON CONFLICT for concurrent application writes; use MERGE (PostgreSQL 15+) for batch syncs and deletes.