Upsert generator

Upsert into PostgreSQL: INSERT … ON CONFLICT DO UPDATE

Name your table, key and columns and get the statement PostgreSQL actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads PostgreSQL incrementally.

Generate the statement

Statement · INSERT … ON CONFLICT DO UPDATE

INSERT INTO "deals" ("id", "name", "stage", "amount", "owner_id", "updated_at")
SELECT "id", "name", "stage", "amount", "owner_id", "updated_at"
FROM "deals_staging"
ON CONFLICT ("id") DO UPDATE SET
  "name" = EXCLUDED."name",
  "stage" = EXCLUDED."stage",
  "amount" = EXCLUDED."amount",
  "owner_id" = EXCLUDED."owner_id",
  "updated_at" = EXCLUDED."updated_at"
WHERE "deals"."updated_at" IS NULL
   OR "deals"."updated_at" < EXCLUDED."updated_at";

How the upsert works in PostgreSQL

PostgreSQL has had a native upsert since 9.5: INSERT … ON CONFLICT DO UPDATE. The conflict target must match a unique index exactly, and the special EXCLUDED row exposes the values that failed to insert, so the DO UPDATE branch can copy just the columns that changed. The whole statement is atomic per row, which is why it stays correct when two sync workers write the same key at once.

The generated statement adds a WHERE on the DO UPDATE branch: a row is only overwritten when the incoming updated-at is newer. That guard is what turns a plain upsert into a watermark load, and it costs nothing because the row is already locked. Load the batch into an UNLOGGED staging table with COPY, upsert from it, then TRUNCATE the staging table.

Before you run it

The conflict target needs a UNIQUE index or constraint on exactly those columns; without one Postgres raises "there is no unique or exclusion constraint matching the ON CONFLICT specification".
EXCLUDED is the row that would have been inserted. The WHERE after DO UPDATE runs per conflicting row, so a stale row (older updated-at) is skipped rather than overwriting fresher data.
PostgreSQL 15 added MERGE for multi-branch logic (WHEN NOT MATCHED BY SOURCE … DELETE), but MERGE is not concurrency-safe under two writers; ON CONFLICT is the atomic upsert.

Questions people ask

ON CONFLICT or MERGE in PostgreSQL 15+?

ON CONFLICT for upserts: it is atomic and handles concurrent inserts. MERGE is for multi-branch logic such as deleting rows missing from the source, and it can fail with a unique violation under concurrent writers.

Why does Postgres say there is no unique constraint matching the ON CONFLICT specification?

The columns in ON CONFLICT (…) must be covered by a UNIQUE index or constraint with exactly those columns. A primary key on (id) satisfies ON CONFLICT (id); a composite index does not satisfy a single-column target.

How do I stop an older row from overwriting a newer one?

Keep the WHERE after DO UPDATE: it compares the stored updated-at with EXCLUDED.updated-at and skips the update when the incoming row is stale.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into PostgreSQL with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog