DataSQL

Bulk upsert in Postgres with ON CONFLICT

Insert or update many rows in one round trip, skip writes when nothing changed, and find out which rows were new. Useful for syncing data from CRMs, APIs or CSV imports.

#postgres#sql#upsert#sync

upsert_contacts.sql

-- Table shape this query assumes:
-- CREATE TABLE contacts (
--   id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
--   external_id text NOT NULL UNIQUE,  -- the conflict target must be unique
--   email       text NOT NULL,
--   full_name   text,
--   updated_at  timestamptz NOT NULL DEFAULT now()
-- );

INSERT INTO contacts (external_id, email, full_name, updated_at)
SELECT external_id, lower(email), full_name, now()
FROM unnest(
  $1::text[],  -- external ids
  $2::text[],  -- emails
  $3::text[]   -- full names
) AS input(external_id, email, full_name)
ON CONFLICT (external_id) DO UPDATE
SET email      = EXCLUDED.email,
    full_name  = EXCLUDED.full_name,
    updated_at = EXCLUDED.updated_at
-- Skip the write entirely when nothing changed: no dead tuple, no trigger
WHERE (contacts.email, contacts.full_name)
      IS DISTINCT FROM (EXCLUDED.email, EXCLUDED.full_name)
RETURNING id, external_id, (xmax = 0) AS inserted;

Usage

TypeScript

import { readFileSync } from "node:fs";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const sql = readFileSync("upsert_contacts.sql", "utf8");

type Contact = { externalId: string; email: string; fullName: string };

export async function upsertContacts(contacts: Contact[]) {
  const { rows } = await pool.query<{ id: string; external_id: string; inserted: boolean }>(sql, [
    contacts.map((c) => c.externalId),
    contacts.map((c) => c.email),
    contacts.map((c) => c.fullName),
  ]);
  const created = rows.filter((r) => r.inserted).length;
  return { created, updated: rows.length - created };
}

How it works & gotchas

01unnest over parallel arrays sends thousands of rows with three parameters, instead of building a giant VALUES list with thousands of placeholders.
02The WHERE ... IS DISTINCT FROM clause makes unchanged rows a no-op. They also drop out of RETURNING, so the result lists only rows that were inserted or actually changed.
03xmax = 0 is a well-known trick to tell inserts from updates. It relies on Postgres internals, so treat it as reporting, not business logic.
04If the same external_id appears twice in one batch, Postgres raises ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate before sending.
05Keep batches to a few thousand rows so a single statement doesn't hold locks for too long.

Need this wired into a real system?

We build and harden production software end to end.

Talk to an engineer

More code helpers

TypeScript

Keyset (cursor) pagination for Postgres

Paginate large tables with stable, fast queries that don't slow down on deep pages and don't skip or repeat rows when new data arrives, unlike OFFSET.

TypeScript

Stream LLM responses from a Next.js route handler

Send tokens to the browser as the model produces them instead of making users wait for the full completion. Uses a plain ReadableStream, so it works with any client that can read a fetch body.

TypeScript

Retry LLM calls with exponential backoff and jitter

LLM APIs return 429s and 5xx errors under load. This wrapper retries only the failures worth retrying, honours Retry-After and spreads retries out so many workers don't hit the API in lockstep.