Generador de upsert

Upsert en PostgreSQL: INSERT … ON CONFLICT DO UPDATE

Nombra tu tabla, clave y columnas y obtén la sentencia que PostgreSQL acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga PostgreSQL de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · 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";

Cómo funciona el upsert en 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 ejecutarla

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.

Preguntas frecuentes

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en PostgreSQL con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones