Gerador de upsert

Upsert no PostgreSQL: INSERT … ON CONFLICT DO UPDATE

Nomeie tabela, chave e colunas e receba a instrução que o PostgreSQL realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o PostgreSQL de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · 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";

Como o upsert funciona no 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.

Antes de executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no PostgreSQL com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações