# Writes

Source: https://pgcast.dev/insert

The shortcuts that write: `insert()`, `upsert()`, `update()`, `deletes()` and `truncate()`. For each, its parameters, every option with the SQL it adds and its effect on the type, and what it throws.

What every write shares:

- It returns a `SQLFragment`: [`.run(pool)`](https://pgcast.dev/sql-template#run) sends it, `.compile()` gives its `text` and `values` without sending anything.
- A value is sent as a parameter (`$1`), never written into the SQL; a `sql` fragment is SQL.
- Rows come back through `to_jsonb()`, as a table's `JSONSelectable`: a `timestamptz` is a string ([The catalog](https://pgcast.dev/catalog-types)).
- An error from Postgres rejects `.run()` with a `DatabaseError` ([`isDatabaseError()`](https://pgcast.dev/transaction#isDatabaseError)). The errors below are thrown by pgcast as it builds the query, before anything is sent.

## `insert()`

```ts
db.insert(table, values, options?)
```

Insert one row or several, and return them as Postgres stored them, defaults and serials filled in.

- `table`: a table of the schema.
- `values`: an `Insertable`, or an array of them. A `sql` fragment is written as SQL (`` db.sql`now()` ``), `db.Default` as `DEFAULT`; an `undefined` value is `NULL` ([An `undefined` value](https://pgcast.dev/insert#undefined-values)).
  - `raw()`, `cols()` and `vals()` are no values here: they throw while compiling, before any query ([`raw()`](https://pgcast.dev/sql-template#raw) gives the message), and pgcast's type refuses them, where Zapatos 6.6.1's took them. For SQL, write a `sql` fragment; in an array or a `jsonb` object, they are part of the value, sent as JSON.
  - `db.parent()` throws "The 'parent' table alias has no meaning here": an `INSERT` has no parent query. The type refuses it.
  - A column typed `any`, as a `zapatos/custom` placeholder is, passes the type, not the run time. Zapatos 6.6.1 sent each as one parameter, as JSON: `{ tagline: db.raw("'x'") }` stored `{"value":"'x'"}`, `db.parent('id')` `{"value":"id"}` ([#259](https://github.com/AlexandreDemartin/pgcast/issues/259)).
- `options`: `returning` and `extras` ([Options](https://pgcast.dev/insert#insert-options)).

You write:

```ts
const films = await db.insert('films', [{
  studio_id: 1001,
  title: 'The Tidewater Ledger',
  release_year: 2031,
  runtime_minutes: 117,
  tagline: 'Every debt comes in with the tide.',
}, {
  studio_id: 1002,
  title: 'Moths over Harrowgate',
  release_year: 2031,
  runtime_minutes: 95,
}]).run(pool);
```

Postgres runs:

```sql
INSERT INTO "films" ("release_year", "runtime_minutes", "studio_id", "tagline", "title")
VALUES ($1, $2, $3, $4, $5), ($6, $7, $8, DEFAULT, $9)
RETURNING to_jsonb("films".*) AS result
```

Values: `[2031, 117, 1001, "Every debt comes in with the tide.", "The Tidewater Ledger", 2031, 95, 1002, "Moths over Harrowgate"]`

TypeScript knows:

```ts
const films: films.JSONSelectable[]
// that is
const films: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}[]
```

- One row gives one result; an array gives an array, in the order Postgres returns them, in practice the order given.
- The columns are the keys of every row; a key missing from a row is `DEFAULT` in that row, as `tagline` above.
- An empty array is a no-op: `.run(pool)` resolves with `[]` and sends nothing, unless forced with `.run(pool, true)`. Its SQL, `INSERT INTO "films" SELECT null WHERE false`, is marked as a no-op.
- Postgres takes at most 65,535 parameters in a query: split a bigger insert ([Batches](https://pgcast.dev/batches)).

### Options

`returning` and `extras` are the same for `upsert()`, `update()` and `deletes()` ([`ReturningOptionsForTable`](https://pgcast.dev/insert#ReturningOptionsForTable)).

You write:

```ts
const film = await db.insert('films', {
  studio_id: 1000,
  title: 'Winter at Fennick Lock',
  release_year: 2032,
  runtime_minutes: 108,
}, {
  returning: ['id'],
  extras: {
    name: 'title',
    shouted: db.sql<s.films.SQL, string>`upper(${'title'})`,
  },
}).run(pool);
```

Postgres runs:

```sql
INSERT INTO "films" ("release_year", "runtime_minutes", "studio_id", "title")
VALUES ($1, $2, $3, $4)
RETURNING jsonb_build_object($5::text, "id") ||
  jsonb_build_object($6::text, "title", $7::text, upper("title")) AS result
```

Values: `[2032, 108, 1000, "Winter at Fennick Lock", "id", "name", "shouted"]`

TypeScript knows:

```ts
const film: db.ReturningTypeForTable<"films", "id"[], {
  name: "title";
  shouted: db.SQLFragment<string, never>;
}>
// that is
const film: {
  id: number;
  name: string;
  shouted: string;
}
```

| Option | SQL it adds | Effect on the type |
|---|---|---|
| none | `RETURNING to_jsonb("films".*) AS result` | `films.JSONSelectable` |
| `returning: ['id']` | `jsonb_build_object($5::text, "id")` in place of `to_jsonb("films".*)` | `Pick<films.JSONSelectable, 'id'>` |
| `extras: { name: 'title', shouted: … }`, as above | `\|\| jsonb_build_object($6::text, "title", $7::text, upper("title"))` | adds `name`, of the column's type, and `shouted`, of the fragment's `RunResult`: `string`, from `db.sql<s.films.SQL, string>` |

- A `readonly` array in `returning` (`['id'] as const`) gives the same SQL and the same type as a mutable one ([`columns`](https://pgcast.dev/select#columns)).
- Both need `strictNullChecks` for their types.

## `upsert()`

```ts
db.upsert(table, values, conflictTarget, options?)
```

`INSERT … ON CONFLICT … DO UPDATE`: insert each row, or update the existing row it conflicts with.

- `table`, `values`: as for [`insert()`](https://pgcast.dev/insert#insert); `raw()`, `cols()`, `vals()` and `parent()` throw there too, on a conflicting row or a new one.
- `conflictTarget`: the arbiter of the conflict: a column, an array of columns, or the name of a unique or primary-key constraint wrapped in [`constraint()`](https://pgcast.dev/insert#constraint).
- `options`: `updateColumns`, `noNullUpdateColumns`, `updateValues` and `reportAction` ([Options](https://pgcast.dev/insert#upsert-options)); `returning` and `extras`, as for [`insert()`](https://pgcast.dev/insert#insert-options).

You write:

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

Postgres runs:

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

Values: `[1002, 4]`

TypeScript knows:

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

- The update sets every column inserted, from `EXCLUDED`, and each key of `updateValues`.
- Each result has `$action`, `'INSERT'` or `'UPDATE'` ([`UpsertAction`](https://pgcast.dev/insert#UpsertAction)).
- One row gives one result; an array gives an array, without the rows that do nothing ([`doNothing`](https://pgcast.dev/insert#doNothing)).
- An empty array is a no-op, as for `insert()`.

### Options

Each SQL replaces the `DO UPDATE SET …` of the plain upsert, on `ratings` (on `films` for `noNullUpdateColumns`), or adds to it:

| Option | SQL it adds | Effect on the type |
|---|---|---|
| none | `DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", EXCLUDED."votes")` | `ratings.JSONSelectable & UpsertAction` |
| `updateColumns: 'votes'` | `DO UPDATE SET ("votes") = ROW(EXCLUDED."votes")` | none |
| `updateColumns: db.doNothing` | `DO NOTHING` | `\| undefined`, for one row |
| `noNullUpdateColumns: 'tagline'` | `CASE WHEN EXCLUDED."tagline" IS NULL THEN "films"."tagline" ELSE EXCLUDED."tagline" END` in place of `EXCLUDED."tagline"` | none |
| `updateValues: { votes: 10 }` | `ROW(EXCLUDED."film_id", EXCLUDED."stars", $3)`: `$3` in place of `EXCLUDED."votes"` | none |
| ``updateValues: { votes: db.sql`${'ratings'}.${'votes'} + 1` }`` | `"ratings"."votes" + 1` in place of `EXCLUDED."votes"` | none |
| `reportAction: 'suppress'` | no `\|\| jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END)` | no `UpsertAction` |

- `updateColumns` takes a column or an array; `noNullUpdateColumns` a column, an array, or `db.all` for every column. [Batches](https://pgcast.dev/batches#rows-with-different-keys) has a panel of `updateColumns`: a booking keeps its time.
- `updateValues` is one set of values for every row updated. Its keys are always updated, even when `updateColumns` leaves them out, or is `doNothing` (then the SQL is `DO UPDATE SET`, not `DO NOTHING`).
- In `updateValues`, a plain value is a parameter. A value that holds a fragment, `param()`, `raw()`, `cols()`, `vals()`, `parent()`, `Default` or `self`, at any depth of its arrays and plain objects, is compiled as in a `sql` template.
- To read a column there, name the table for the existing row, `${'ratings'}.${'votes'}`, or `EXCLUDED` for the new one, `` db.sql`EXCLUDED.${'votes'}` ``.
- There, `db.self`, or a fragment or condition that uses it (`` db.sql`${db.self} + 1` ``, [`conditions.add()`](https://pgcast.dev/conditions#conditions.add)), throws "The 'self' column alias has no meaning here", as in Zapatos 6.6.1; `db.parent()`, alone or in a fragment, throws "The 'parent' table alias has no meaning here".

> **Security**
>
> Zapatos 6.6.1 wrote a plain string in `updateValues` as an identifier: `{ tagline: 'happy' }` put `"happy"` in the `ROW(…)`, and a string from a user could read other columns or tables.
>
> pgcast sends it as a parameter, `$3` in its place, as its type and `update()` always said. To read a column there, write a fragment, as in the table ([Security](https://pgcast.dev/security)).

With `noNullUpdateColumns`, a `NULL` leaves the existing value. Film 1002 comes back from a feed with no tagline, and keeps its own:

You write:

```ts
const film = await db.upsert('films', {
  id: 1002,
  studio_id: 1000,
  title: "The Cartographer's Silence",
  release_year: 2008,
  runtime_minutes: 131,
  tagline: null,
}, 'id', { noNullUpdateColumns: 'tagline' }).run(pool);
```

Postgres runs:

```sql
INSERT INTO "films" ("id", "release_year", "runtime_minutes", "studio_id", "tagline", "title")
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT ("id") DO UPDATE SET ("id", "studio_id", "title", "release_year", "runtime_minutes", "tagline") = ROW(EXCLUDED."id", EXCLUDED."studio_id", EXCLUDED."title", EXCLUDED."release_year", EXCLUDED."runtime_minutes", CASE WHEN EXCLUDED."tagline" IS NULL THEN "films"."tagline" ELSE EXCLUDED."tagline" END)
RETURNING to_jsonb("films".*) ||
  jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result
```

Values: `[1002, 2008, 131, 1000, null, "The Cartographer's Silence"]`

TypeScript knows:

```ts
const film: db.UpsertReturnableForTable<"films", db.ColumnsOption<"films">, db.ExtrasOption<"films">, "suppress" | undefined>
// that is
const film: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
  $action: "INSERT" | "UPDATE";
}
```

- The update still sets every column inserted, `"id"` included; only `"tagline"` keeps the existing value when the new one is `NULL`.
- `film` is the updated row: `$action` is `'UPDATE'`, and `tagline` is still `'Every map leaves something out.'`.

With `reportAction: 'suppress'`, the result has no `$action`:

You write:

```ts
const rating = await db.upsert('ratings', {
  film_id: 1002,
  stars: 4,
  votes: 2,
}, ['film_id', 'stars'], { reportAction: 'suppress' }).run(pool);
```

Postgres runs:

```sql
INSERT INTO "ratings" ("film_id", "stars", "votes")
VALUES ($1, $2, $3)
ON CONFLICT ("film_id", "stars") DO UPDATE SET ("film_id", "stars", "votes") = ROW(EXCLUDED."film_id", EXCLUDED."stars", EXCLUDED."votes")
RETURNING to_jsonb("ratings".*) AS result
```

Values: `[1002, 4, 2]`

TypeScript knows:

```ts
const rating: ratings.JSONSelectable
// that is
const rating: {
  film_id: number;
  stars: number;
  votes: number;
}
```

With `strictNullChecks` off, the type keeps `$action`, which is then `undefined`.

### Rows with different keys

In several rows without `updateColumns`, a key missing from one row is inserted as `DEFAULT`, then set from `EXCLUDED` on conflict: that row's existing value is lost.

- `[{ film_id: 1002, stars: 5, votes: 3 }, { film_id: 1002, stars: 4 }]` sends `($4, $5, DEFAULT)`, so an existing count of 4-star votes goes back to 1.
- It runs, as in Zapatos 6.6.1, and warns once per process: `PGCAST_DEP001`.
- A key of `updateValues`, or a column of an array or string `conflictTarget`, does not count: new rows without their serial `id`, upserted on `'id'`, neither warn nor throw. With `constraint()`, every key counts.
- Rows with the same keys, one row, and any `updateColumns` never warn.

> **Opt-in**
>
> With `upsertRequiresSameKeys: true` ([`Config`](https://pgcast.dev/runtime-config#Config.upsertRequiresSameKeys)), such an upsert throws before any query: "rows have different keys: pass updateColumns, or give every row the same keys". Without it, pgcast warns as `PGCAST_DEP001`; it becomes the default in 7.0 ([Versions](https://pgcast.dev/versions)).

## `constraint()`

```ts
db.constraint(name)
```

Wrap the name of a unique or primary-key constraint of the table, for the `conflictTarget` of `upsert()`: `ON CONFLICT ON CONSTRAINT "ratings_pkey"`, in place of `ON CONFLICT ("film_id", "stars")`. The names come from the schema, so one the table doesn't have fails to compile:

```ts
db.upsert('ratings', { film_id: 1002, stars: 4 }, db.constraint('ratings_pkey'));
db.upsert('ratings', { film_id: 1002, stars: 4 }, db.constraint('ratings_stars_key'));  // error TS2345
```

- The type takes every unique index of the table, and Postgres only a constraint. An index made with `CREATE UNIQUE INDEX`, which is no constraint, type-checks, and Postgres refuses it: 42704, `constraint "…" for table "…" does not exist`.
- For such an index, pass its columns as the `conflictTarget`: `'code'` for a unique index on `code`.

The type does not check that a constraint belongs to the upsert's table, and Postgres refuses another table's: 42704. Only inside the call does `constraint()` take the table's names, as above. Held in a variable, `const k = db.constraint('tags_pkey')` takes any table's name, so `db.upsert('ratings', …, k)` compiles. Give the table, `db.constraint<'ratings'>('ratings_pkey')`, and the type checks the name against it:

```ts
const k = db.constraint('tags_pkey');
db.upsert('ratings', { film_id: 1002, stars: 4 }, k);  // compiles, then 42704
db.constraint<'ratings'>('tags_pkey');
```

## `Constraint`

The class that `constraint()` returns, its constraint name in `value`: `new db.Constraint<'ratings'>('ratings_pkey')` is the same.

## `doNothing`

An empty array, for the `updateColumns` of `upsert()`: a row that conflicts is left as it is, and returns nothing.

You write:

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

Postgres runs:

```sql
INSERT INTO "tags" ("film_id", "tag")
VALUES ($1, $2)
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: `[1003, "coastal"]`

TypeScript knows:

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

- One row: `undefined` when it conflicts, and the type says so.
- An array: the rows inserted only; `[]` when every row conflicts.

## `UpsertAction`

`{ $action: 'INSERT' | 'UPDATE' }`, added to each result of `upsert()`: `'INSERT'` for a new row, `'UPDATE'` for an updated one, read from the row's `xmax`. `reportAction: 'suppress'` leaves it out of the SQL and of the type.

## `UpsertReturnableForTable`

`UpsertReturnableForTable<T, C, E, RA>`: one row of what `upsert()` returns, the [`ReturningTypeForTable`](https://pgcast.dev/insert#ReturningTypeForTable) with [`UpsertAction`](https://pgcast.dev/insert#UpsertAction), unless `RA`, the `reportAction`, is `'suppress'`: `db.UpsertReturnableForTable<'ratings', undefined, undefined, undefined>` is `ratings.JSONSelectable & UpsertAction`. It does not add the `undefined` of `updateColumns: []` ([`doNothing`](https://pgcast.dev/insert#doNothing)). It needs `strictNullChecks`: without it, it is the whole `JSONSelectable` with `UpsertAction`, whatever `C`, `E` and `RA`.

## `update()`

```ts
db.update(table, values, where, options?)
```

Update the rows that match, and return them. The arguments come in the order of the SQL: `SET`, then `WHERE`.

- `table`: a table of the schema.
- `values`: an `Updatable`, with at least one key. A `sql` fragment is written as SQL, where `db.self` is the column, and so is a condition such as [`conditions.add()`](https://pgcast.dev/conditions#conditions.add). `raw()`, `cols()`, `vals()` and `parent()` throw, as for [`insert()`](https://pgcast.dev/insert#insert). An `undefined` value is `NULL` ([An `undefined` value](https://pgcast.dev/insert#undefined-values)).
- `where`: a `Whereable`, or a `sql` fragment ([Conditions](https://pgcast.dev/conditions)).
- `options`: `returning` and `extras`, as for [`insert()`](https://pgcast.dev/insert#insert-options).

You write:

```ts
const screenings = await db.update('screenings', {
  starts_at: db.sql`${db.self} + interval '15 minutes'`,
  subtitled: true,
}, { id: 1002 }).run(pool);
```

Postgres runs:

```sql
UPDATE "screenings" SET ("starts_at", "subtitled") = ROW("starts_at" + interval '15 minutes', $1)
WHERE ("id" = $2)
RETURNING to_jsonb("screenings".*) AS result
```

Values: `[true, 1002]`

TypeScript knows:

```ts
const screenings: screenings.JSONSelectable[]
// that is
const screenings: {
  cinema_id: number;
  film_id: number;
  id: number;
  starts_at: db.TimestampTzString;
  subtitled: boolean | null;
}[]
```

- Always an array: every row updated, `[]` when none matches.
- `SET (…) = ROW(…)`: `ROW()` keeps a single column valid since Postgres 10.
- `values` with no key throws: "nothing to update: an Updatable needs at least one column". Zapatos 6.6.1 sent `SET () = ROW()`, which Postgres refused (42601).

> **Every row**
>
> An empty `Whereable`, `{}`, compiles to `WHERE TRUE`: `update()` updates every row of the table, and `deletes()` deletes them all. An empty list in [`and(...filters)` or `not(or(...ids))`](https://pgcast.dev/conditions#conditions.and) reaches every row too.

## `deletes()`

```ts
db.deletes(table, where, options?)
```

Delete the rows that match, and return them. `delete` is a reserved word in JavaScript, hence the name.

- `table`: a table of the schema.
- `where`: a `Whereable`, or a `sql` fragment ([Conditions](https://pgcast.dev/conditions)); `{}` is every row, as for [`update()`](https://pgcast.dev/insert#update).
- `options`: `returning` and `extras`, as for [`insert()`](https://pgcast.dev/insert#insert-options).

You write:

```ts
const removed = await db.deletes('tags', {
  film_id: 1003,
}, { returning: ['tag'] }).run(pool);
```

Postgres runs:

```sql
DELETE
FROM "tags"
WHERE ("film_id" = $1)
RETURNING jsonb_build_object($2::text, "tag") AS result
```

Values: `[1003, "tag"]`

TypeScript knows:

```ts
const removed: db.JSONOnlyColsForTable<"tags", "tag"[]>[]
// that is
const removed: {
  tag: string;
}[]
```

- Always an array: every row deleted, `[]` when none matches.

## `DeleteSignatures`

The interface of `deletes()`'s call signature, `(table, where, options?) => SQLFragment<…[]>`, for a function that wraps it and keeps its types. A wrapper typed with it gets the types of its parameters from it, with no cast, and its calls are checked and typed as those of `deletes()`:

```ts
const loggedDeletes: db.DeleteSignatures = (table, where, options) => {
  const query = db.deletes(table, where, options);
  console.log(query.compile().text);
  return query;
};

const removed = loggedDeletes('tags', { film_id: 1003 }, { returning: ['tag'] });
```

## `truncate()`

```ts
db.truncate(table, ...options)
```

Empty one table, or several in one statement.

- `table`: a table of the schema, or an array of them.
- `options`: `'CONTINUE IDENTITY'` or `'RESTART IDENTITY'`, then `'RESTRICT'` or `'CASCADE'`, each optional.

You write:

```ts
const emptied = await db.truncate(['seat_bookings', 'ratings', 'tags'], 'RESTART IDENTITY').run(pool);
```

Postgres runs:

```sql
TRUNCATE "seat_bookings", "ratings", "tags" RESTART IDENTITY
```

Values: `[]`

TypeScript knows:

```ts
const emptied: undefined
```

| Option | SQL it adds | Effect on the type |
|---|---|---|
| none | `TRUNCATE "tags"` | `undefined` |
| `'CONTINUE IDENTITY'` | `CONTINUE IDENTITY`: sequences keep their values, as by default | none |
| `'RESTART IDENTITY'` | `RESTART IDENTITY`: the tables' sequences restart | none |
| `'RESTRICT'` | `RESTRICT`: Postgres refuses if a table not in the list references one of them, as by default | none |
| `'CASCADE'` | `CASCADE`: also empties the tables that reference them | none |

With `'CASCADE'`, Postgres empties every table that references one of them, at any depth: `films` takes `roles`, `screenings`, `ratings` and `tags` with it, and `seat_bookings` through `screenings`, each named in a notice, `truncate cascades to table "roles"`:

You write:

```ts
const emptied = await db.truncate('films', 'CASCADE').run(pool);
```

Postgres runs:

```sql
TRUNCATE "films" CASCADE
```

Values: `[]`

TypeScript knows:

```ts
const emptied: undefined
```

- `.run()` resolves with `[]`, where the type says `undefined`.
- The type takes the options in that order, one of each kind. pgcast writes them as given: in JavaScript, or past the type with `as any`, `truncate('tags', 'CASCADE', 'RESTART IDENTITY')` sends `TRUNCATE "tags" CASCADE RESTART IDENTITY`, and Postgres refuses it (42601).
- Any other option throws: "Truncate option must be CONTINUE IDENTITY/RESTART IDENTITY/RESTRICT/CASCADE, not '…'". Zapatos 6.6.1 wrote it into the SQL as it was.

## An `undefined` value

In the values of `insert()`, `update()` and `upsert()`, Zapatos 6.6.1 sends `undefined` as `NULL`: a `NOT NULL DEFAULT` column fails, and `update()` erases the column instead of leaving it.

| In | By default, as in 6.6.1 | With `undefinedMeansMissing: true` |
|---|---|---|
| `insert()` | `NULL` | `DEFAULT`: `VALUES ($1, $2, $3, DEFAULT, $4)` |
| `update()` | sets the column to `NULL`, and counts as a key | left out: `SET ("title") = ROW($1)`; with no key left, it throws "nothing to update…" |
| `upsert()` | inserts `NULL`, then sets it from `EXCLUDED` | left out: not inserted, nor updated unless `updateColumns` names it (then from `EXCLUDED`, so an existing row takes the column's `DEFAULT`); in several rows, its cell is `DEFAULT` and counts as missing ([Rows with different keys](https://pgcast.dev/insert#upsert-different-keys)) |

- By default, each sends the SQL of 6.6.1 and warns once per process: `PGCAST_DEP002`.
- An `undefined` in a `where` is unchanged.
- An `undefined` in `updateValues` is unchanged too, in both modes: its key is still updated, from `EXCLUDED`. So an existing row takes the value inserted, or the column's `DEFAULT` when the row doesn't insert it, with no warning: `upsert('ratings', { film_id: 1002, stars: 4 }, ['film_id', 'stars'], { updateValues: { votes: undefined } })` sends `EXCLUDED."votes"`, and an existing count goes back to 1.

> **Opt-in**
>
> `undefinedMeansMissing: true` ([`Config`](https://pgcast.dev/runtime-config#Config.undefinedMeansMissing)) reads an `undefined` value as a missing key, as `JSON.stringify()` does. Without it, pgcast warns as `PGCAST_DEP002`; it becomes the default in 7.0 ([Versions](https://pgcast.dev/versions)).

## `ReturningOptionsForTable`

`{ returning?, extras? }`: the options of `insert()`, `upsert()`, `update()` and `deletes()` that shape each result ([Options](https://pgcast.dev/insert#insert-options)).

## `ReturningTypeForTable`

`ReturningTypeForTable<T, C, E>`: one row of what `insert()`, `update()` and `deletes()` return, for the table `T`, the `returning` columns `C` and the `extras` `E` (`undefined` for an option not given): the `JSONSelectable`, narrowed to `C`, with the keys of `E`.

`insert()` of one row returns it; `insert()` of an array, `update()` and `deletes()` return an array of it. It types a fake result, in a test, from the schema:

```ts
type NewFilm = db.ReturningTypeForTable<'films', ['id', 'title'], undefined>;

const inserted: NewFilm = { id: 1000, title: 'The Long Take' };
```

- Stable: type tests pin it to what `.run()` gives, here for `db.insert('films', film, { returning: ['id', 'title'] })`.
- `C` may be a `readonly` tuple, as `returning` may ([Options](https://pgcast.dev/insert#insert-options)).
- It needs `strictNullChecks`: without it, it is the whole `JSONSelectable`, whatever `C` and `E`.