Batches

Write many rows at once: split them under Postgres's parameter limit, skip the ones already there, add up counts, keep rows with different keys from erasing what's stored, and get back only what you need. It assumes you know insert() and upsert().

Insert an array

Give insert() an array, and every row goes in one query:

TSYou write Open in playground

const tags = await db.insert('tags', [
  { film_id: 1000, tag: 'orchards' },
  { film_id: 1000, tag: 'slow cinema' },
  { film_id: 1002, tag: 'maps' },
]).run(pool);

SQLPostgres runs

INSERT INTO "tags" ("film_id", "tag")
VALUES ($1, $2), ($3, $4), ($5, $6)
RETURNING to_jsonb("tags".*) AS result

values [1000, "orchards", 1000, "slow cinema", 1002, "maps"]

::TypeScript knows

const tags: tags.JSONSelectable[]
// that is
const tags: {
  film_id: number;
  tag: string;
}[]

Each cell is a parameter: three rows of two columns, VALUES ($1, $2), ($3, $4), ($5, $6) RETURNING. An empty array sends nothing and resolves with [].

Split under the parameter limit

Postgres takes at most 65,535 parameters in a query. Past that, it refuses the query as a protocol violation, 08P01, with a message that varies with the count and doesn't say why, such as bind message supplies 0 parameters, but prepared statement "" requires 65536.

A query's parameters, to count before you send it:

• one per cell: rows × columns;
• none for a cell that is DEFAULT, a key missing from its row or db.Default; a sql fragment sends its own;
• one per column of returning and per key of extras, and the parameters of updateValues, once.

Or ask the query itself: .compile().values.length of a one-row insert, as below, when every row has the same keys.

Then send the rows in chunks of a fixed size, well under the limit. In one transaction, every chunk goes in or none does:

const perRow = db.insert('tags', tags.slice(0, 1)).compile().values.length;  // 2: film_id and tag
const chunkSize = Math.floor(10_000 / perRow);  // 10,000 parameters a query

await db.readCommitted(pool, async txnClient => {
  for (let i = 0; i < tags.length; i += chunkSize) {
    await db.insert('tags', tags.slice(i, i + chunkSize)).run(txnClient);
  }
});

START TRANSACTION ISOLATION LEVEL READ COMMITTED
-- the insert() of each chunk: 20 of them for 100,000 tags
COMMIT

• If a chunk fails, readCommitted() sends ROLLBACK and rejects with its error: no tag is left in. A deadlock (40P01) runs the chunks again first (Retries).
• On the pool, without the transaction, each chunk commits as it goes: the chunks before the one that failed stay.
• upsert() splits the same way.

Skip the rows already there

Give upsert() the rows and updateColumns: db.doNothing: Postgres inserts the new ones and leaves the others as they are.

TSYou write Open in playground

const added = await db.upsert('tags', [
  { film_id: 1000, tag: 'orchards' },
  { film_id: 1003, tag: 'lighthouses' },
], ['film_id', 'tag'], { updateColumns: db.doNothing }).run(pool);

SQLPostgres runs

INSERT INTO "tags" ("film_id", "tag")
VALUES ($1, $2), ($3, $4)
ON CONFLICT ("film_id", "tag") DO NOTHING
RETURNING to_jsonb("tags".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values [1000, "orchards", 1003, "lighthouses"]

::TypeScript knows

const added: db.UpsertReturnableForTable<"tags", db.ColumnsOption<"tags">, db.ExtrasOption<"tags">, "suppress" | undefined>[]
// that is
const added: {
  film_id: number;
  tag: string;
  $action: "INSERT" | "UPDATE";
}[]

• Only the rows inserted come back (doNothing): with orchards already tagged, added holds lighthouses alone.
• A row given twice in the array is inserted once.

Add up counts

Except with doNothing, an upsert refuses an array that holds the same key twice: 21000, ON CONFLICT DO UPDATE command cannot affect row a second time. So sum the ballots first, one row per film_id and stars:

const ballots = [
  { film_id: 1002, stars: 4 },
  { film_id: 1002, stars: 4 },
  { film_id: 1002, stars: 5 },
  { film_id: 1000, stars: 5 },
  { film_id: 1000, stars: 5 },
  { film_id: 1000, stars: 5 },
];

const counts = new Map<string, { film_id: number; stars: number; votes: number }>();
for (const { film_id, stars } of ballots) {
  const key = `${film_id}:${stars}`;
  const row = counts.get(key) ?? { film_id, stars, votes: 0 };
  row.votes += 1;
  counts.set(key, row);
}
const rows = [...counts.values()];  // the three rows below

Then add each row's votes to those stored: set votes in updateValues from both, the stored row by its table's name, the new one as EXCLUDED.

TSYou write Open in playground

const ratings = await db.upsert('ratings', [
  { film_id: 1002, stars: 4, votes: 2 },
  { film_id: 1002, stars: 5, votes: 1 },
  { film_id: 1000, stars: 5, votes: 3 },
], ['film_id', 'stars'], {
  updateValues: { votes: db.sql`${'ratings'}.${'votes'} + EXCLUDED.${'votes'}` },
}).run(pool);

SQLPostgres runs

INSERT INTO "ratings" ("film_id", "stars", "votes")
VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9)
ON CONFLICT ("film_id", "stars") DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", "ratings"."votes" + EXCLUDED."votes")
RETURNING to_jsonb("ratings".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values [1002, 4, 2, 1002, 5, 1, 1000, 5, 3]

::TypeScript knows

const ratings: db.UpsertReturnableForTable<"ratings", db.ColumnsOption<"ratings">, db.ExtrasOption<"ratings">, "suppress" | undefined>[]
// that is
const ratings: {
  film_id: number;
  stars: number;
  votes: number;
  $action: "INSERT" | "UPDATE";
}[]

• The first run inserts the three rows. The second updates them, to 4, 2 and 6 votes, each with $action: 'UPDATE'.
• An undefined in updateValues doesn't leave votes alone (An undefined value, #168).

Rows with different keys

pgcast inserts a key that some rows lack as DEFAULT in those rows. Without updateColumns, an upsert then sets every column from EXCLUDED, that DEFAULT included. Say you correct the names on two bookings, and only one of them has its booked_at:

• the second row is (DEFAULT, $5, $6, $7), and "booked_at" is set from EXCLUDED."booked_at": F8's booking time becomes the time of the upsert, now(), the column's default;
• pgcast warns once per process, PGCAST_DEP001.

Name the columns to update instead, with updateColumns:

TSYou write Open in playground

const bookings = await db.upsert('seat_bookings', [
  { screening_id: 1000, seat: 'F7', customer: 'Ines Calloway', booked_at: '2030-02-20T09:15:00Z' },
  { screening_id: 1000, seat: 'F8', customer: 'Barnaby Quill-Hart' },
], ['screening_id', 'seat'], { updateColumns: 'customer' }).run(pool);

SQLPostgres runs

INSERT INTO "seat_bookings" ("booked_at", "customer", "screening_id", "seat")
VALUES ($1, $2, $3, $4), (DEFAULT, $5, $6, $7)
ON CONFLICT ("screening_id", "seat") DO UPDATE SET ("customer") = ROW(EXCLUDED."customer")
RETURNING to_jsonb("seat_bookings".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values ["2030-02-20T09:15:00Z", "Ines Calloway", 1000, "F7", "Barnaby Quill-Hart", 1000, "F8"]

::TypeScript knows

const bookings: db.UpsertReturnableForTable<"seat_bookings", db.ColumnsOption<"seat_bookings">, db.ExtrasOption<"seat_bookings">, "suppress" | undefined>[]
// that is
const bookings: {
  booked_at: db.TimestampTzString;
  customer: string;
  screening_id: number;
  seat: PgSeat_label;
  $action: "INSERT" | "UPDATE";
}[]

• The update is DO UPDATE SET ("customer") = ROW(EXCLUDED."customer"): F8 keeps its booking time. A new row still takes the booked_at given, or the default.
• Or give every row the same keys: upsert the rows with a booked_at and those without in two calls.
• Not every key counts as missing (Rows with different keys).

Opt-in

With db.setConfig({ upsertRequiresSameKeys: true }) (Config.upsertRequiresSameKeys), such an upsert throws before it sends anything.

An undefined in a row

Rows built from input often hold an undefined, such as a booked_at the form left empty. By default it's sent as NULL, as in Zapatos 6.6.1.

booked_at is NOT NULL, so Postgres refuses the whole batch, 23502, null value in column "booked_at" of relation "seat_bookings" violates not-null constraint, and pgcast warns once, PGCAST_DEP002.

Opt-in

With db.setConfig({ undefinedMeansMissing: true }) (Config.undefinedMeansMissing), an undefined is a missing key: F8 with booked_at: undefined is (DEFAULT, $5, $6, $7), a row with different keys as above (An undefined value).

Choose what comes back

Each row comes back whole by default, and from an upsert with its $action. For a big batch, ask for less:

TSYou write Open in playground

const bookings = await db.upsert('seat_bookings', [
  { screening_id: 1000, seat: 'F7', customer: 'Ines Calloway', booked_at: '2030-02-20T09:15:00Z' },
  { screening_id: 1000, seat: 'F8', customer: 'Barnaby Quill-Hart' },
], ['screening_id', 'seat'], { updateColumns: 'customer', returning: ['seat'] }).run(pool);

const newSeats = bookings.filter(b => b.$action === 'INSERT').map(b => b.seat);

SQLPostgres runs

INSERT INTO "seat_bookings" ("booked_at", "customer", "screening_id", "seat")
VALUES ($1, $2, $3, $4), (DEFAULT, $5, $6, $7)
ON CONFLICT ("screening_id", "seat") DO UPDATE SET ("customer") = ROW(EXCLUDED."customer")
RETURNING jsonb_build_object($8::text, "seat") ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result

values ["2030-02-20T09:15:00Z", "Ines Calloway", 1000, "F7", "Barnaby Quill-Hart", 1000, "F8", "seat"]

::TypeScript knows

const bookings: db.UpsertReturnableForTable<"seat_bookings", "seat"[], db.ExtrasOption<"seat_bookings">, "suppress" | undefined>[]
// that is
const bookings: {
  seat: PgSeat_label;
  $action: "INSERT" | "UPDATE";
}[]

• returning: ['seat'] sends jsonb_build_object($8::text, "seat") in place of to_jsonb("seat_bookings".*).
• $action tells the new rows from the updated ones: newSeats is ['F8'] when F7 was already booked.
• reportAction: 'suppress' leaves $action out of the SQL and of the type, for a batch that doesn't need it.
• Both narrow the type only with strictNullChecks on (Options).

See also

• insert() and upsert(): every option, the SQL it adds.
• transaction(): its levels, retries and errors.
• Versions: the opt-ins and their warnings.