Gerador de upsert

Upsert no Neon: INSERT … ON CONFLICT DO UPDATE

Nomeie tabela, chave e colunas e receba a instrução que o Neon 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 Neon 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 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 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

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Neon 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