Generador de upsert

Upsert en Neon: INSERT … ON CONFLICT DO UPDATE

Nombra tu tabla, clave y columnas y obtén la sentencia que Neon 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 Neon 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 Neon

Neon runs standard Postgres, so INSERT … ON CONFLICT DO UPDATE works unchanged, with the unique-index requirement and the EXCLUDED row. The difference is operational: compute is separate from storage and suspends after a period of inactivity, so the first statement of a sync pays a cold start, and each connection to a scaled-to-zero endpoint waits for compute to come back.

Batch accordingly. One upsert statement from a staging table per entity is cheap; thousands of single-row upserts spread over an hour keep compute awake and bill for it. Use the pooled connection string (PgBouncer in transaction mode) for many short workers, and the direct string for one long COPY session, because transaction-mode pooling does not support session-level prepared statements.

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

Does scale-to-zero break a long-running upsert?

No, an active connection keeps compute running. It only suspends when idle, so a sync that connects, upserts and disconnects is the pattern that costs least.

Can I test the load on a branch?

Yes. Create a branch from production, run the generated statement there, inspect the result, then run it on main. Branches share storage, so the copy is instant.

Pooled or direct connection for the sync?

Direct for one bulk session with COPY; pooled for many short-lived workers. Prepared statements and session settings do not survive transaction-mode pooling.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Neon 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