# Batches

Source: https://pgcast.dev/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()`](https://pgcast.dev/insert).

## Insert an array

Give [`insert()`](https://pgcast.dev/insert#insert) an array, and every row goes in one query:

You write:

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

Postgres runs:

```sql
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:

```ts
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`](https://pgcast.dev/sql-template#Default); a `sql` fragment sends its own;
- one per column of `returning` and per key of [`extras`](https://pgcast.dev/insert#insert-options), and the parameters of `updateValues`, once.

Or ask the query itself: [`.compile()`](https://pgcast.dev/sql-template#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:

```ts
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);
  }
});
```

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

- If a chunk fails, [`readCommitted()`](https://pgcast.dev/transaction#readCommitted) sends `ROLLBACK` and rejects with its error: no tag is left in. A deadlock (40P01) runs the chunks again first ([Retries](https://pgcast.dev/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()`](https://pgcast.dev/insert#upsert) the rows and [`updateColumns: db.doNothing`](https://pgcast.dev/insert#doNothing): Postgres inserts the new ones and leaves the others as they are.

You write:

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

Postgres runs:

```sql
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:

```ts
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`](https://pgcast.dev/insert#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`:

```ts
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`](https://pgcast.dev/insert#upsert-options) from both, the stored row by its table's name, the new one as `EXCLUDED`.

You write:

```ts
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);
```

Postgres runs:

```sql
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:

```ts
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](https://pgcast.dev/insert#undefined-values), [#168](https://github.com/AlexandreDemartin/pgcast/issues/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`](https://pgcast.dev/insert#upsert-options):

You write:

```ts
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);
```

Postgres runs:

```sql
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:

```ts
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](https://pgcast.dev/insert#upsert-different-keys)).

> **Opt-in**
>
> With `db.setConfig({ upsertRequiresSameKeys: true })` ([`Config.upsertRequiresSameKeys`](https://pgcast.dev/runtime-config#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`](https://pgcast.dev/runtime-config#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](https://pgcast.dev/insert#undefined-values)).

## 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:

You write:

```ts
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);
```

Postgres runs:

```sql
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:

```ts
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']`](https://pgcast.dev/insert#insert-options) 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'`](https://pgcast.dev/insert#upsert-options) 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](https://pgcast.dev/insert#insert-options)).

## See also

- [`insert()`](https://pgcast.dev/insert#insert) and [`upsert()`](https://pgcast.dev/insert#upsert): every option, the SQL it adds.
- [`transaction()`](https://pgcast.dev/transaction#transaction): its levels, retries and errors.
- [Versions](https://pgcast.dev/versions): the opt-ins and their warnings.