Postgres UPSERT Generator

Write a correct PostgreSQL upsert in a few clicks. Give it a table and a column list, pick the conflict target (columns or a named constraint) and whether a duplicate should update the existing row or be skipped, and the generator produces INSERT ... ON CONFLICT DO UPDATE SET col = EXCLUDED.col with an optional WHERE guard and RETURNING clause. It also emits the multi-row bulk form, the PostgreSQL 15+ MERGE equivalent, and the unique constraint your conflict target requires — plus warnings about the errors upserts fail with in practice. Runs entirely in your browser.

TableOn conflictConflict target

Insert columns (comma or newline separated)

Conflict columnsNever overwrite

Extra WHERE guardSkip write when nothing changed

RETURNING clauseSource is a SELECT (not VALUES)

Generated UPSERT

INSERT INTO public.products (sku, name, price, stock, updated_at)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  stock = EXCLUDED.stock,
  updated_at = EXCLUDED.updated_at
RETURNING sku, name, price, stock, updated_at;

Bulk multi-row form

INSERT INTO public.products (sku, name, price, stock, updated_at)
VALUES
  ($1, $2, $3, $4, $5),
  ($6, $7, $8, $9, $10),
  ($11, $12, $13, $14, $15)
ON CONFLICT (sku) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  stock = EXCLUDED.stock,
  updated_at = EXCLUDED.updated_at
RETURNING sku, name, price, stock, updated_at;

MERGE equivalent (PG15+)

-- PostgreSQL 15+ equivalent
MERGE INTO public.products AS t
USING (VALUES ($1, $2, $3, $4, $5)) AS s (sku, name, price, stock, updated_at)
   ON t.sku = s.sku
WHEN MATCHED THEN
  UPDATE SET
    name = s.name,
    price = s.price,
    stock = s.stock,
    updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (sku, name, price, stock, updated_at)
  VALUES (s.sku, s.name, s.price, s.stock, s.updated_at);

Required unique constraint

-- ON CONFLICT (sku) needs a unique index or constraint on exactly those columns:
ALTER TABLE public.products
  ADD CONSTRAINT products_sku_key
  UNIQUE (sku);

-- Or, for a partial unique index, use the inferred form with the same predicate:
-- CREATE UNIQUE INDEX ... ON public.products (sku) WHERE deleted_at IS NULL;
-- INSERT ... ON CONFLICT (sku) WHERE deleted_at IS NULL DO UPDATE ...

Notes

• ON CONFLICT only fires for a unique index or exclusion constraint. Without one on the conflict target you get: there is no unique or exclusion constraint matching the ON CONFLICT specification.
• EXCLUDED is the pseudo-table holding the row you tried to insert. Reference the table name (or its alias) for the existing row: SET stock = products.stock + EXCLUDED.stock.
• DO UPDATE consumes a sequence value even when it updates instead of inserting, so serial/identity IDs will show gaps. That is expected and harmless.
• Upserts take a row lock on conflict, so concurrent upserts on the same key serialise. Insert rows in a consistent key order in batch jobs to avoid deadlocks.

Do more than postgres upsert generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the target table and the columns you insert, then choose the conflict target: the unique columns, or a named constraint.
Pick DO UPDATE or DO NOTHING, list any columns that must not be overwritten, and optionally add a WHERE guard or skip writes when nothing changed.
Copy the generated INSERT ... ON CONFLICT statement, the bulk form or the MERGE version, and make sure the unique index in the prerequisite box exists.

Frequently asked questions

How do I write an UPSERT in PostgreSQL?

Use INSERT ... ON CONFLICT. For example: INSERT INTO products (sku, name, price) VALUES ($1, $2, $3) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price. The conflict target (sku) must be covered by a unique index or unique constraint. EXCLUDED refers to the row you tried to insert, so EXCLUDED.price is the new value and products.price is the value already stored. Add RETURNING * if you need the resulting row back in one round trip.

Why do I get "there is no unique or exclusion constraint matching the ON CONFLICT specification"?

PostgreSQL infers which index to use from the columns in ON CONFLICT (...), and it only accepts a unique index or an exclusion constraint on exactly those columns. A plain (non-unique) index, a primary key on different columns, or a unique index whose column order or expression does not match will all produce this error. Create the matching constraint — ALTER TABLE products ADD CONSTRAINT products_sku_key UNIQUE (sku) — or, if the index is partial, repeat its predicate in the statement: ON CONFLICT (sku) WHERE deleted_at IS NULL DO UPDATE ...

Should I use ON CONFLICT or MERGE in PostgreSQL?

ON CONFLICT is the right default: it is available in every supported version, it is concurrency-safe under high contention, and it handles the plain 'insert or update this row' case in one line. MERGE (PostgreSQL 15+) is the SQL-standard statement and is better when you need several branches — update some matched rows, delete others, insert the rest — or when you are porting code from Oracle or SQL Server. Note that MERGE is not an atomic upsert under concurrent writers: it can raise a unique violation where ON CONFLICT would not. To run either against a live database with schema browsing and AI-assisted SQL, Chat2DB is a free client — download it at https://chat2db.ai/download or use https://app.chat2db.ai.