UPSERT in PostgreSQL: INSERT ... ON CONFLICT in Practice

UPSERT means "insert a row, or update it if it already exists" in one atomic statement. In PostgreSQL that job belongs to INSERT ... ON CONFLICT. It closes the classic race where you SELECT first, find nothing, INSERT — and then hit a unique-violation because a concurrent transaction inserted the same row in between. ON CONFLICT handles that inside the engine, with no explicit locking and no "try insert, catch error, update" loop.

Here is a working schema and copy-paste-ready examples for psql.

Basic syntax: DO NOTHING and DO UPDATE

Start with a users table that has a unique email:

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text NOT NULL UNIQUE,
    name       text NOT NULL,
    visits     int  NOT NULL DEFAULT 0,
    updated_at timestamptz NOT NULL DEFAULT now()
);

DO NOTHING silently skips a conflict instead of erroring out:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;

DO UPDATE updates the existing row instead. One key detail: after ON CONFLICT you specify the conflict target — the column(s) backed by a unique index or constraint that PostgreSQL uses to detect the collision:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name,
              updated_at = now();

• The conflict target must match a real unique index or PK. Without a matching index, PostgreSQL rejects the statement.
• You can target by constraint name instead: ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....

EXCLUDED: the values from the rejected insert

EXCLUDED is a pseudo-table holding the row you tried to insert but couldn't because of the conflict. Inside DO UPDATE you pull the "new" values from it instead of retyping them. That is especially handy for batch inserts:

INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann'),
       ('bob@example.com', 'Bob')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

You can blend old and new — for example, only overwrite when a non-empty name arrives:

INSERT INTO users (email, name)
VALUES ('ann@example.com', NULL)
ON CONFLICT (email)
DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);

Here users.name is the current value in the table and EXCLUDED.name is what you tried to write. That gives you fine-grained control over which columns to overwrite and which to keep.

Idempotent inserts and WHERE in DO UPDATE

Idempotency means running the same statement again doesn't change the outcome. That matters for at-least-once message delivery, retries, and re-runnable imports. The simplest form is DO NOTHING on a natural key:

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id) DO NOTHING;

Run it ten times and the row appears exactly once. If you do want updates but only when the data actually changed, add a WHERE to DO UPDATE — this avoids dead writes and stops triggers from firing for nothing:

INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'shipped')
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status,
              updated_at = now()
WHERE orders.status IS DISTINCT FROM EXCLUDED.status;

IS DISTINCT FROM compares values safely even when NULL is involved. If the status hasn't changed, no update happens at all.

Atomic counters

A classic task: count visits or page views without races. Instead of SELECT ... + UPDATE, do the increment right inside DO UPDATE, referencing the row's current value:

INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
              updated_at = now();

The first call creates the row with visits = 1; every subsequent call atomically bumps the counter by one. Concurrent transactions queue up at the row level, so no updates are lost. Add RETURNING to read the new value back immediately:

... DO UPDATE SET visits = users.visits + 1
RETURNING visits;

Gotchas and the MySQL difference

• Multiple conflicts in one statement. If a row violates two different unique indexes at once, ON CONFLICT only handles the target you named — the other index still throws an error.
• Duplicates inside one VALUES list. You cannot update the same target row twice in a single statement: ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate your input before inserting.
• Partitioned tables. The conflict target must include the partition key.

MySQL's equivalent is INSERT ... ON DUPLICATE KEY UPDATE. It fires on any unique key (no target is named), and instead of EXCLUDED you use the VALUES() function (a row alias in newer versions):

-- MySQL
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    visits = visits + 1;

ClickHouse has no real UPSERT: the engine doesn't enforce uniqueness on the fly. The usual patterns are ReplacingMergeTree (dedup happens in the background during merges) or plain INSERT followed by filtering at read time. If you need strict idempotent inserts and atomic counters right now, that's PostgreSQL and MySQL territory — not analytical columnar stores.