Upsert generator

Upsert into Neon: INSERT … ON CONFLICT DO UPDATE

Name your table, key and columns and get the statement Neon 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 Neon 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 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.

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

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.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Neon 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