Postgres Upsert: INSERT ... ON CONFLICT (and MERGE) Explained

An upsert means "insert this row — or, if it already exists, update it instead". Settings per user, counters, syncing data from another system, caching API results: all upserts.

Why not just check first?

const existing = await db.query('SELECT 1 FROM settings WHERE user_id = $1', [id])
if (existing.rows.length) await db.query('UPDATE ...')
else await db.query('INSERT ...')

Two requests running at the same time can both see "doesn't exist" and both insert — causing a duplicate or a unique constraint error. Postgres's ON CONFLICT does it atomically. (Database transactions)

ON CONFLICT DO NOTHING

Insert unless it already exists:

INSERT INTO newsletter_subscribers (email)
VALUES ('ada@example.com')
ON CONFLICT (email) DO NOTHING;

ON CONFLICT DO UPDATE

INSERT INTO user_settings (user_id, theme, updated_at)
VALUES (42, 'dark', now())
ON CONFLICT (user_id)
DO UPDATE SET theme = EXCLUDED.theme,
              updated_at = EXCLUDED.updated_at;

EXCLUDED is the row you tried to insert. So "set theme to the new value".

The conflict target needs a unique index

ON CONFLICT (user_id) only works if there's a unique constraint or unique index on user_id (or a primary key). Without one:

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

ALTER TABLE user_settings ADD CONSTRAINT user_settings_user_id_key UNIQUE (user_id);

Multi-column keys work: ON CONFLICT (user_id, key). For a partial unique index, repeat its condition: ON CONFLICT (email) WHERE deleted_at IS NULL.

Counters

INSERT INTO page_views (page, views)
VALUES ('/pricing', 1)
ON CONFLICT (page)
DO UPDATE SET views = page_views.views + 1;

page_views.views is the existing value; EXCLUDED.views the attempted one.

Only update when something changed

INSERT INTO products (sku, name, price)
VALUES ('LAMP-1', 'Desk Lamp', 39.00)
ON CONFLICT (sku)
DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price
WHERE (products.name, products.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price);

Skips writes (and dead row versions) when nothing changed — useful for syncs that run often. (Postgres VACUUM and bloat, NULL in SQL)

Get the row back

INSERT INTO user_settings (user_id, theme) VALUES (42, 'dark')
ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme
RETURNING *;

(With DO NOTHING, conflicting rows return nothing.)

Bulk upserts

INSERT INTO products (sku, name, price)
VALUES ('A', 'Lamp', 39), ('B', 'Chair', 120), ('C', 'Desk', 300)
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

One statement for many rows — far faster than one per row. If the same key appears twice in one statement, Postgres errors (ON CONFLICT DO UPDATE command cannot affect row a second time) — de-duplicate the input first. For large imports, load into a staging table, then upsert from it. (Import a CSV into Postgres)

MERGE

Postgres 15+ supports SQL-standard MERGE, more flexible when you need different actions by condition (including deletes):

MERGE INTO inventory i
USING incoming n ON i.sku = n.sku
WHEN MATCHED AND n.quantity = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET quantity = n.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (n.sku, n.quantity);

For a simple insert-or-update, ON CONFLICT is shorter and handles concurrent inserts more gracefully; MERGE can still hit unique violations under concurrency.

Sequence gaps

Every INSERT attempt consumes a value from an identity/serial sequence even when it ends up updating. Expect gaps in IDs — that's normal. (UUID vs auto-increment)

In ORMs

// Prisma
await prisma.userSettings.upsert({
  where: { userId: 42 },
  update: { theme: 'dark' },
  create: { userId: 42, theme: 'dark' },
})

// Drizzle
await db.insert(userSettings).values({ userId: 42, theme: 'dark' })
  .onConflictDoUpdate({ target: userSettings.userId, set: { theme: 'dark' } })

Prisma's upsert uses native ON CONFLICT when the query shape allows; otherwise it falls back to select-then-write, so keep where on a unique field.

---

EasySpawn gives each app its own Postgres database with Claude Code alongside, which can turn a racy select-then-insert into a proper atomic upsert. See how it works or join the waitlist.

Related: duplicate key value violates unique constraint · Database Transactions Explained · Postgres CTEs · Idempotency Keys