# Select

Source: https://pgcast.dev/select

The read shortcuts: `select()`, `selectOne()` and `selectExactlyOne()` for rows, `count()`, `sum()`, `avg()`, `min()` and `max()` for one number. Each option, the SQL it adds and what it does to the type, then the types they share.

## `select()`

```ts
db.select(table, where, options?, mode?, aggregate?)
```

Builds one `SELECT` of the rows of `table` that match `where`. It returns a [`SQLFragment`](https://pgcast.dev/sql-template#SQLFragment): `.run(pool)` gives an array of the table's `JSONSelectable` rows, `[]` when none match.

- `table`: a table of the generated schema.
- `where`: a `Whereable`, an object of columns and their values or [conditions](https://pgcast.dev/conditions), its keys in alphabetical order, joined by `AND` in one pair of parentheses, a condition or `sql` value in its own (`("id" = $1 AND ("studio_id" > $2) AND "title" = $3)`); a [`sql`](https://pgcast.dev/sql-template#sql) fragment, written as it is; or [`db.all`](https://pgcast.dev/sql-template#all), for no `WHERE` at all.
- `options`: the [options](https://pgcast.dev/select#options) below, all optional.
- `mode` and `aggregate`: what `selectOne()`, `count()` and the others pass; see [`SelectResultMode`](https://pgcast.dev/select#SelectResultMode).

You write:

```ts
const films = await db.select('films', { studio_id: 1000 }).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("films".*) AS result
  FROM "films"
  WHERE ("studio_id" = $1)
) AS "sq_films"
```

Values: `[1000]`

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;
}[]
```

### The SQL it writes

- Each row is one `jsonb` value, `to_jsonb("films".*)`, so dates and times come back as strings and `bigint` as a number: the `JSONSelectable` types say so ([The catalog](https://pgcast.dev/catalog-types)).
- The rows are gathered into one array, `coalesce(jsonb_agg(result), '[]')`, around a subquery aliased `"sq_<alias or table>"`, so `ORDER BY` and `LIMIT` apply to the rows. One query, one value back.
- Inside, the clauses come in this order: `SELECT [DISTINCT …]` · the columns `AS result` · `FROM` the table `[AS alias]` · each `LEFT JOIN LATERAL` · `WHERE` · `GROUP BY` · `HAVING` · `ORDER BY` · `LIMIT` or `FETCH FIRST … WITH TIES` · `OFFSET` · each `FOR …` lock.
- Every value is a parameter (`$1`, `$2`); every name is quoted as Postgres quotes it, any `"` in it doubled. Zapatos 6.6.1 let a name that held a `"` close its quotes; pgcast fixed this ([changelog](https://pgcast.dev/changelog#6.6.2-security)).

### Options

The SQL of each row is what the built library compiles, on the docs schema; `…` stands for a `sql` fragment.

| Option | SQL it adds | Effect on the type |
|---|---|---|
| `columns: ['title']` | `jsonb_build_object($1::text, "title")` in place of `to_jsonb("films".*)` | only those keys (`JSONOnlyColsForTable`) |
| `order: { by: 'title', direction: 'ASC' }` | `ORDER BY "title" ASC` | none |
| `limit: 2` | `LIMIT $1` | none |
| `offset: 2` | `OFFSET $1` | none |
| `withTies: true` | `FETCH FIRST $1 ROWS WITH TIES` in place of `LIMIT $1` | none |
| `extras: { cinema: 'name' }` | `\|\| jsonb_build_object($1::text, "name")` | a key per extra (`ExtrasResult`) |
| `lateral: { cast: … }` | `LEFT JOIN LATERAL (…) AS "lateral_cast" ON true`, read by `\|\| jsonb_build_object($1::text, "lateral_cast".result)` | a key per subquery (`LateralResult`) |
| `lateral: …`, one subquery | `"lateral_passthru".result` in place of the columns, and its `LEFT JOIN LATERAL` | the subquery's result, in place of the row |
| `alias: 'mentor'` | `FROM "crew" AS "mentor"`, then `"mentor"` for the table | none |
| `distinct: true` | `SELECT DISTINCT` | none |
| `distinct: 'cinema_id'` | `SELECT DISTINCT ON ("cinema_id")` | none |
| `groupBy: 'film_id'` | `GROUP BY "film_id"` | none |
| `having: …` | `HAVING sum("votes") >= $1` | none |
| `lock: { for: 'UPDATE' }` | `FOR UPDATE` | none |

> **Strict**
>
> `columns`, `extras` and `lateral` change the type only with `strictNullChecks` (or `strict`) on. Without it, the result stays the full `JSONSelectable` and an extra or a lateral key is not in the type; the type refuses nothing, not even `columns` beside one `lateral` subquery, and one subquery is typed as the table's row while its own result comes back.

### `columns`

`columns?: readonly ColumnForTable<T>[]`, a [`ColumnsOption`](https://pgcast.dev/select#ColumnsOption): only these columns, each a key of the result object, in place of the whole row. It does not rename a column: [`extras`](https://pgcast.dev/select#extras) does.

A `readonly` array, `['id', 'title'] as const` or a `readonly ColumnForTable<T>[]`, gives the same SQL and the same type as a mutable one. With `strictNullChecks`, Zapatos 6.6.1 typed its rows as `never`, which any type accepted; a type that the query does not return is now refused:

```ts
const columns = ['id', 'title'] as const;

async function filmCards(queryable: db.Queryable) {
  const cards: { id: number; title: string }[] = await db.select('films', db.all, { columns }).run(queryable);
  const withTaglines: { title: string; tagline: string | null }[] = await db.select('films', db.all, { columns }).run(queryable);
  return [cards, withTaglines];
}
```

### `order`, `limit`, `offset` and `withTies`

- `order`: one `{ by, direction, nulls? }` or an array of them, in that order. `by` is a column or any SQL of the table, its `SQL` type: a `sql` fragment (`` db.sql`array_length(${'genres'}, 1)` ``), [`db.parent()`](https://pgcast.dev/sql-template#parent); `direction` is `'ASC'` or `'DESC'`; `nulls`, `'FIRST'` or `'LAST'`.
- `limit` and `offset`: numbers, sent as parameters.
- `withTies: true`, with `limit` and `order`: also the rows that tie with the last one on the `order`. Without `limit` it adds nothing; without `order`, Postgres refuses the query (42601).

You write:

```ts
const page = await db.select('films', db.all, {
  columns: ['title', 'release_year'],
  order: [
    { by: 'release_year', direction: 'DESC' },
    { by: 'title', direction: 'ASC' },
  ],
  limit: 2,
  offset: 2,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "title", $2::text, "release_year") AS result
  FROM "films"
  ORDER BY "release_year" DESC, "title" ASC
  LIMIT $3
  OFFSET $4
) AS "sq_films"
```

Values: `["title", "release_year", 2, 2]`

TypeScript knows:

```ts
const page: db.JSONOnlyColsForTable<"films", ("release_year" | "title")[]>[]
// that is
const page: {
  release_year: number;
  title: string;
}[]
```

You write:

```ts
const leads = await db.select('roles', db.all, {
  columns: ['film_id', 'character'],
  order: { by: 'billing', direction: 'ASC' },
  limit: 1,
  withTies: true,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "film_id", $2::text, "character") AS result
  FROM "roles"
  ORDER BY "billing" ASC FETCH FIRST $3 ROWS WITH TIES
) AS "sq_roles"
```

Values: `["film_id", "character", 1]`

TypeScript knows:

```ts
const leads: db.JSONOnlyColsForTable<"roles", ("film_id" | "character")[]>[]
// that is
const leads: {
  film_id: number;
  character: string;
}[]
```

Paging through a long list: [Pagination](https://pgcast.dev/paginate).

### `extras`

`extras?: { [key: string]: column | SQLFragment }`, an [`ExtrasOption`](https://pgcast.dev/select#ExtrasOption): more keys on each result object. A column gives its value under another key; a `sql` fragment gives the type of its second type parameter, `` db.sql<s.cinemas.SQL, number>`…` ``, so `number` here. The keys are added to the row with `||`, after `columns`.

You write:

```ts
const cinemas = await db.select('cinemas', db.all, {
  columns: ['id'],
  extras: {
    cinema: 'name',
    km: db.sql<s.cinemas.SQL, number>`${'location'} <-> point(${db.param(0)}, ${db.param(0)})`,
  },
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "id") ||
    jsonb_build_object($2::text, "name", $3::text, "location" <-> point($4, $5)) AS result
  FROM "cinemas"
) AS "sq_cinemas"
```

Values: `["id", "cinema", "km", 0, 0]`

TypeScript knows:

```ts
const cinemas: db.ReturningTypeForTable<"cinemas", "id"[], {
  cinema: "name";
  km: db.SQLFragment<number, never>;
}>[]
// that is
const cinemas: {
  id: number;
  cinema: string;
  km: number;
}[]
```

### `lateral`

`lateral?: { [key: string]: SQLFragment } | SQLFragment`, a [`LateralOption`](https://pgcast.dev/select#LateralOption): other shortcuts, nested, each joined with `LEFT JOIN LATERAL … ON true`. In them, [`db.parent()`](https://pgcast.dev/sql-template#parent) names a column of this query's table.

- **An object**: each key becomes a key of the result, holding its subquery's result: an array for `select()`, an object or `null` for `selectOne()`, a number for `count()`. The keys are joined in alphabetical order; each is one identifier, `"lateral_<key>"`.
- **One subquery**: its result is passed through in place of each row. Then `columns` and `extras` have nothing to add to, and the type refuses them, with `strictNullChecks` on.

Steps, nesting and self-joins: [Joins](https://pgcast.dev/lateral). Since pgcast 6.6.2, a key or an `alias` with a `"` in it stays one identifier; in Zapatos 6.6.1 it could close its quotes ([changelog](https://pgcast.dev/changelog#6.6.2-security)).

### `alias`

`alias?: string`: a name for the table in this query, needed to join a table to itself (`crew` and its `mentor_id`). It replaces the table name in the columns, in `"sq_<alias>"` and for the `parent()` of each `lateral` subquery, and `lock.of` takes it. Its steps: [Join a table to itself](https://pgcast.dev/lateral#join-a-table-to-itself).

### `distinct`

With `true`: `SELECT DISTINCT`, on the whole result object. The select list is that one `result`, so Postgres refuses an `order` on a column with it (42P10).

You write:

```ts
const cinemaIds = await db.select('screenings', db.all, {
  columns: ['cinema_id'],
  distinct: true,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT DISTINCT jsonb_build_object($1::text, "cinema_id") AS result
  FROM "screenings"
) AS "sq_screenings"
```

Values: `["cinema_id"]`

TypeScript knows:

```ts
const cinemaIds: db.JSONOnlyColsForTable<"screenings", "cinema_id"[]>[]
// that is
const cinemaIds: {
  cinema_id: number;
}[]
```

With a column, an array of columns or a `sql` fragment: `SELECT DISTINCT ON (…)`, one row for each value. Give an `order` that starts with the same columns, or which row you get is up to Postgres.

You write:

```ts
const firstShows = await db.select('screenings', db.all, {
  columns: ['cinema_id', 'film_id', 'starts_at'],
  distinct: 'cinema_id',
  order: [
    { by: 'cinema_id', direction: 'ASC' },
    { by: 'starts_at', direction: 'ASC' },
  ],
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT DISTINCT ON ("cinema_id") jsonb_build_object($1::text, "cinema_id", $2::text, "film_id", $3::text, "starts_at") AS result
  FROM "screenings"
  ORDER BY "cinema_id" ASC, "starts_at" ASC
) AS "sq_screenings"
```

Values: `["cinema_id", "film_id", "starts_at"]`

TypeScript knows:

```ts
const firstShows: db.JSONOnlyColsForTable<"screenings", ("film_id" | "cinema_id" | "starts_at")[]>[]
// that is
const firstShows: {
  film_id: number;
  cinema_id: number;
  starts_at: db.TimestampTzString;
}[]
```

### `groupBy` and `having`

- `groupBy`: a column, an array of columns (`GROUP BY "film_id", "stars"`) or a `sql` fragment.
- `having`: a `Whereable`, in parentheses as in `where`, or a `sql` fragment.

Postgres returns only the grouped columns, those a grouped primary key determines, and aggregates, and refuses any other (42803): the whole row, without `columns`, runs when `groupBy` holds the primary key.

You write:

```ts
const popular = await db.select('ratings', db.all, {
  columns: ['film_id'],
  extras: { votes: db.sql<s.ratings.SQL, number>`sum(${'votes'})` },
  groupBy: 'film_id',
  having: db.sql<s.ratings.SQL>`sum(${'votes'}) >= ${db.param(10)}`,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "film_id") ||
    jsonb_build_object($2::text, sum("votes")) AS result
  FROM "ratings"
  GROUP BY "film_id"
  HAVING sum("votes") >= $3
) AS "sq_ratings"
```

Values: `["film_id", "votes", 10]`

TypeScript knows:

```ts
const popular: db.ReturningTypeForTable<"ratings", "film_id"[], {
  votes: db.SQLFragment<number, never>;
}>[]
// that is
const popular: {
  film_id: number;
  votes: number;
}[]
```

### `lock`

`lock?: SelectLockingOptions | SelectLockingOptions[]`: a locking clause each, in order, written after everything else.

| Key | Values | SQL |
|---|---|---|
| `for` | `'UPDATE'`, `'NO KEY UPDATE'`, `'SHARE'`, `'KEY SHARE'` | `FOR UPDATE` |
| `of`, optional | a table name, or the `alias`; or an array of them | `OF "screenings"` |
| `wait`, optional | `'NOWAIT'`, `'SKIP LOCKED'` | `NOWAIT` |

Postgres refuses a lock with `DISTINCT`, `GROUP BY` or an aggregate (0A000).

You write:

```ts
const screening = await db.selectOne('screenings', { id: 1000 }, {
  lock: { for: 'UPDATE', wait: 'NOWAIT' },
}).run(pool);
```

Postgres runs:

```sql
SELECT to_jsonb("screenings".*) AS result
FROM "screenings"
WHERE ("id" = $1)
LIMIT $2
FOR UPDATE NOWAIT
```

Values: `[1000, 1]`

TypeScript knows:

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

### Errors

`select()` throws these as it builds the query, before any is sent:

| Message | When | Since |
|---|---|---|
| `Direction must be ASC/DESC, not '…'` | an `order` with another `direction` | Zapatos |
| `Nulls must be FIRST/LAST/undefined, not '…'` | an `order` with another `nulls` | Zapatos |
| `Lock strength must be UPDATE/NO KEY UPDATE/SHARE/KEY SHARE, not '…'` | a `lock` with another `for` | pgcast 6.6.2 |
| `Lock wait must be NOWAIT/SKIP LOCKED/undefined, not '…'` | a `lock` with another `wait` | pgcast 6.6.2 |
| `Aggregate must be a function name (e.g. sum, or my_schema.my_agg), not '…'` | an `aggregate` that is not one, in `SelectResultMode.Numeric` | pgcast 6.6.2 |

The type refuses a bad `direction`, `nulls` or `lock`, not a bad `aggregate`, a `string`. Zapatos 6.6.1 wrote a bad `lock` or `aggregate` into the SQL as it was, where it could run a second statement. A value that has no meaning in `where` or `having` (`db.Default`, `db.self`, `db.raw()`) throws as the query compiles: [Conditions](https://pgcast.dev/conditions).

## `selectOne()`

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

As [`select()`](https://pgcast.dev/select#select), with `LIMIT $1`, its value `1`, and no `jsonb_agg`: `.run(pool)` gives the first row, or `undefined` when none matches. Any `limit` you pass is replaced by `1`; `offset` and the other options are kept. In a `lateral`, its key holds the row or `null`.

With `withTies` and `order`, it writes `FETCH FIRST $1 ROWS WITH TIES`, here and in `selectExactlyOne()`. `.run()` then gives one of the tied rows; in a `lateral`, the parent row comes back once per tie.

You write:

```ts
const latest = await db.selectOne('films', { studio_id: 1000 }, {
  order: { by: 'release_year', direction: 'DESC' },
}).run(pool);
```

Postgres runs:

```sql
SELECT to_jsonb("films".*) AS result
FROM "films"
WHERE ("studio_id" = $1)
ORDER BY "release_year" DESC
LIMIT $2
```

Values: `[1000, 1]`

TypeScript knows:

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

## `selectExactlyOne()`

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

As [`selectOne()`](https://pgcast.dev/select#selectOne), but its type has no `undefined`: when no row matches, `.run()` rejects with a [`NotExactlyOneError`](https://pgcast.dev/select#NotExactlyOneError). It checks for at least one row, not exactly one: with `LIMIT $2`, its value `1`, it takes the first. In a `lateral`, a missing row gives `null` and no error, though the type says otherwise.

You write:

```ts
const studio = await db.selectExactlyOne('studios', { id: 1000 }).run(pool);
```

Postgres runs:

```sql
SELECT to_jsonb("studios".*) AS result
FROM "studios"
WHERE ("id" = $1)
LIMIT $2
```

Values: `[1000, 1]`

TypeScript knows:

```ts
const studio: studios.JSONSelectable
// that is
const studio: {
  id: number;
  name: string;
}
```

With [`selectExactlyOneChecksForSecondRow`](https://pgcast.dev/runtime-config#Config.selectExactlyOneChecksForSecondRow), the query run on its own asks for two rows, `LIMIT $2` with `2`, and `.run()` rejects on a second row too. Nested in another query, in a `lateral` or a `sql` template, it keeps `LIMIT 1`, option or not: a second row there would join the parent row twice.

### `NotExactlyOneError`

```ts
class NotExactlyOneError extends Error {
  query: SQLFragment;
}
```

What `selectExactlyOne()`, the mode `ExactlyOne` of `select()`, rejects with when no row matches. Its `name` is `'NotExactlyOneError'`, its `message` "One result expected but none returned (hint: check `.query.compile()` on this Error)"; with [`selectExactlyOneChecksForSecondRow`](https://pgcast.dev/runtime-config#Config.selectExactlyOneChecksForSecondRow), a second row gives "One result expected but more returned (hint: check `.query.compile()` on this Error)".

Its `query` is the [`SQLFragment`](https://pgcast.dev/sql-template#SQLFragment) that ran: `err.query.compile()` gives its SQL and values. `err instanceof db.NotExactlyOneError` recognises it; catching it: [Tell a missing row from the others](https://pgcast.dev/handle-errors#tell-a-missing-row-from-the-others).

## `count()`

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

`SELECT count(*) AS result`, with no `jsonb_agg`: `.run(pool)` gives a `number`, `0` when no row matches the `where`. The `bigint` that Postgres returns goes through `Number()`.

You write:

```ts
const roleCount = await db.count('roles', { actor_id: 1000 }).run(pool);
```

Postgres runs:

```sql
SELECT count(*) AS result
FROM "roles"
WHERE ("actor_id" = $1)
```

Values: `[1000]`

TypeScript knows:

```ts
const roleCount: number
```

`count()` and the four below take the options of [`select()`](https://pgcast.dev/select#options), with these differences:

| Option | SQL it adds | Effect on the type |
|---|---|---|
| `columns: ['subtitled']` | `count("subtitled")` in place of `count(*)`: the rows where it is not `NULL`; one column, as several (or none, `[]`) throw | none |
| `distinct: true` | `SELECT DISTINCT count(*)`, on the one result: not `count(DISTINCT …)`, which needs a `sql` template | none |
| `groupBy: 'film_id'` | `GROUP BY "film_id"`; `.run()` gives the first group's value only, and with no group rejects with a `TypeError` | none |
| `extras` | nothing | none |
| `lateral: { … }` | its `LEFT JOIN LATERAL`s, whose results are not read; a single subquery throws, as there is no row to pass it through to | none |
| `having`, `limit`, `offset` | as in `select()`; when they leave no row (a `having` that keeps none, `limit: 0`, `offset: 1`), `.run()` rejects with a `TypeError` | none |

`count()` and the four below throw these while compiling, so `.run()` rejects with a plain `Error`, not a `DatabaseError`, before any query, and `.compile()` throws; so does a query that nests one:

| Message | When | Since |
|---|---|---|
| `…() needs one column to aggregate: pass columns: ['column']` | `sum()`, `avg()`, `min()` or `max()` without `columns` | pgcast 6.7.0 |
| `…() takes one column in columns, not …` | `columns` with several columns, or none (`[]`) | pgcast 6.7.0 |
| `…() cannot pass a lateral query through: nest it in the lateral of a select() instead` | a single subquery as `lateral` | pgcast 6.7.0 |

Zapatos 6.6.1 sent the SQL, which Postgres refused: `sum(*)` and `count("id", "mood")` (42883), `count()` (42809), `SELECT  AS result` (42601). The type is that of 6.6.1, which lets these calls through, a single subquery as `lateral` when there is no `columns` nor `extras`.

Two calls that ran under 6.6.1 now reject too:

- An aggregate of your own that takes another number of arguments under a built-in name. After `CREATE AGGREGATE max(int4, int4) (…)`, `db.max('ratings', db.all, { columns: ['stars', 'votes'] })` sent `max("stars", "votes")`. `db.select('ratings', db.all, { columns: ['stars', 'votes'] }, db.SelectResultMode.Numeric, 'max')` sends the same SQL.
- A refused aggregate marked `noop`, which resolved to its `noopResult`: `run()` compiles the query before it reads `noop`.

With `columns`, it counts two of the four screenings: the other two have `subtitled` `NULL`.

You write:

```ts
const subtitledKnown = await db.count('screenings', db.all, {
  columns: ['subtitled'],
}).run(pool);
```

Postgres runs:

```sql
SELECT count("subtitled") AS result
FROM "screenings"
```

Values: `[]`

TypeScript knows:

```ts
const subtitledKnown: number
```

`count(DISTINCT …)` is a `sql` template. Postgres's `count()` returns a `bigint`, which `pg` gives as a string (`'3'`): `::int` makes it a number, here `3` cinemas.

You write:

```ts
const distinctCinemas = await db.sql<s.screenings.SQL, { cinemas: number }[]>`SELECT count(DISTINCT ${'cinema_id'})::int AS cinemas
  FROM ${'screenings'}`.run(pool);
```

Postgres runs:

```sql
SELECT count(DISTINCT "cinema_id")::int AS cinemas
FROM "screenings"
```

Values: `[]`

TypeScript knows:

```ts
const distinctCinemas: {
  cinemas: number;
}[]
```

`where`, `alias`, `withTies` and `lock` add what they add to `select()`. So does `order`, by a `groupBy` column or a `sql` fragment (`` db.sql`count(*)` ``): by another column, Postgres refuses the query (42803).

`.run()` reads the first row of the result. When the query returns none, it rejects with `TypeError: Cannot read properties of undefined (reading 'result')`; so do `sum()`, `avg()`, `min()` and `max()`.

## `sum()`

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

`SELECT sum("column") AS result`, a `number`; otherwise as [`count()`](https://pgcast.dev/select#count). Give one column in `columns`: without it, `.run()` rejects before any query, where Zapatos 6.6.1 sent `sum(*)`, which Postgres refuses (42883). The type lets that call through, as in 6.6.1. When no row matches the `where`, Postgres returns `NULL`, and `Number(null)` makes it `0`.

You write:

```ts
const minutes = await db.sum('films', { studio_id: 1000 }, {
  columns: ['runtime_minutes'],
}).run(pool);
```

Postgres runs:

```sql
SELECT sum("runtime_minutes") AS result
FROM "films"
WHERE ("studio_id" = $1)
```

Values: `[1000]`

TypeScript knows:

```ts
const minutes: number
```

## `avg()`

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

`SELECT avg("column") AS result`: the mean, through `Number()`, so `0` when no row matches. Otherwise as [`sum()`](https://pgcast.dev/select#sum).

You write:

```ts
const meanRuntime = await db.avg('films', db.all, {
  columns: ['runtime_minutes'],
}).run(pool);
```

Postgres runs:

```sql
SELECT avg("runtime_minutes") AS result
FROM "films"
```

Values: `[]`

TypeScript knows:

```ts
const meanRuntime: number
```

## `min()`

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

`SELECT min("column") AS result`, through `Number()`, so `0` when no row matches. The type says `number` whatever the column:

- a date or time column (`date`, `timestamp`, `timestamptz`) gives its milliseconds since 1970, as node-postgres reads it as a `Date`;
- text or an enum gives `NaN`, unless the text reads as a number: `'9'` gives `9`.

Otherwise as [`sum()`](https://pgcast.dev/select#sum).

You write:

```ts
const earliest = await db.min('films', db.all, {
  columns: ['release_year'],
}).run(pool);
```

Postgres runs:

```sql
SELECT min("release_year") AS result
FROM "films"
```

Values: `[]`

TypeScript knows:

```ts
const earliest: number
```

## `max()`

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

`SELECT max("column") AS result`; as [`min()`](https://pgcast.dev/select#min): `0` when no row matches, a date's milliseconds, and `NaN` on text or an enum unless the text reads as a number.

You write:

```ts
const longest = await db.max('films', { studio_id: 1000 }, {
  columns: ['runtime_minutes'],
}).run(pool);
```

Postgres runs:

```sql
SELECT max("runtime_minutes") AS result
FROM "films"
WHERE ("studio_id" = $1)
```

Values: `[1000]`

TypeScript knows:

```ts
const longest: number
```

## Types

What the signatures above are made of, from `zapatos/db`. `T` is a table, `C` the `columns`, `E` the `extras`, `L` the `lateral` and `A` the `alias`.

### `SelectResultMode`

```ts
enum SelectResultMode { Many, One, ExactlyOne, Numeric }
```

The fourth argument of `select()`: an array of rows (`select()`), a row or `undefined` (`selectOne()`), a row or an error (`selectExactlyOne()`), a number (`count()` and the others).

With `Numeric`, the fifth argument names the aggregate, `'count'` by default; since pgcast 6.6.2 it must be a function name, plain, quoted or `U&` quoted, in up to three dotted parts (`stddev_pop`, `my_schema."MyAgg"`). Any aggregate that returns a number works:

You write:

```ts
const spread = await db.select('films', db.all, {
  columns: ['runtime_minutes'],
}, db.SelectResultMode.Numeric, 'stddev_pop').run(pool);
```

Postgres runs:

```sql
SELECT stddev_pop("runtime_minutes") AS result
FROM "films"
```

Values: `[]`

TypeScript knows:

```ts
const spread: number
```

### `SelectOptionsForTable`

`SelectOptionsForTable<T, C, L, E, A>`: the `options` object of `select()`, `selectOne()`, `selectExactlyOne()` and the aggregates, every key optional, as in [Options](https://pgcast.dev/select#options).

### `SelectLockingOptions`

```ts
interface SelectLockingOptions<A extends string> {
  for: 'UPDATE' | 'NO KEY UPDATE' | 'SHARE' | 'KEY SHARE';
  of?: Unprefixed<Table> | A | (Unprefixed<Table> | A)[];
  wait?: 'NOWAIT' | 'SKIP LOCKED';
}
```

One locking clause of [`lock`](https://pgcast.dev/select#lock). `of` takes a table name without its schema, or the query's `alias` (`A`).

### `ColumnsOption`

`ColumnsOption<T> = readonly ColumnForTable<T>[] | undefined`: the type of `columns`, and of `returning` in [`insert()`](https://pgcast.dev/insert#insert) and the other writes. A `readonly` array gives the same type as a mutable one ([`columns`](https://pgcast.dev/select#columns)).

### `ExtrasOption`

`ExtrasOption<T> = SQLFragmentOrColumnMap<T> | undefined`: the type of `extras`, here and in the writes.

### `LateralOption`

`LateralOption<C, E>`: the type of `lateral`. An object of subqueries ([`SQLFragmentMap`](https://pgcast.dev/select#SQLFragmentMap)); or one subquery, only when neither `columns` nor `extras` is given.

### `SQLFragmentMap`

`interface SQLFragmentMap { [k: string]: SQLFragment<any> }`: the object form of `lateral`, a key per subquery.

### `SQLFragmentOrColumnMap`

`interface SQLFragmentOrColumnMap<T> { [k: string]: SQLFragment<any> | ColumnForTable<T> }`: the form of `extras`, a key per column or fragment.

### `JSONOnlyColsForTable`

`JSONOnlyColsForTable<T, C> = Pick<JSONSelectableForTable<T>, C[number]>`: a row with only the `columns` (or `returning`) given. It names that row in a signature:

```ts
type FilmCard = db.JSONOnlyColsForTable<'films', ['id', 'title']>;

function filmCardsOf(queryable: db.Queryable, studio_id: number): Promise<FilmCard[]> {
  return db.select('films', { studio_id }, { columns: ['id', 'title'] }).run(queryable);
}
```

- `C` may be a `readonly` tuple, as `columns` may ([`columns`](https://pgcast.dev/select#columns)).

### `ExtrasResult`

`ExtrasResult<T, E>`: the keys that `extras` adds, each typed as its column in `JSONSelectable`, or as its fragment's result.

### `LateralResult`

`LateralResult<L> = { [K in keyof L]: RunResultForSQLFragment<L[K]> }`: the keys that a `lateral` object adds, each typed as its subquery's result, with `undefined` turned into `null` ([`RunResultForSQLFragment`](https://pgcast.dev/sql-template#RunResultForSQLFragment)).

### `FullSelectReturnTypeForTable`

`FullSelectReturnTypeForTable<T, C, L, E, M>`: what `.run()` gives for a mode `M`: the row (the `JSONSelectable`, narrowed by `columns`, with `extras` and `lateral`; or the passed-through subquery's result), as an array, as is, or with `undefined`; or a `number`. The row of the writes is [`ReturningTypeForTable`](https://pgcast.dev/insert#ReturningTypeForTable).

### `SelectSignatures`

The call signature of `select()`: `table`, `where`, `options?`, `mode?` (by default `Many`) and `aggregate?`, returning `SQLFragment<FullSelectReturnTypeForTable<…>>`.

### `SelectOneSignatures`

The call signature of `selectOne()`: `table`, `where`, `options?`, with the mode `One`.

### `SelectExactlyOneSignatures`

The call signature of `selectExactlyOne()`: `table`, `where`, `options?`, with the mode `ExactlyOne`.

### `NumericAggregateSignatures`

The call signature of `count()`, `sum()`, `avg()`, `min()` and `max()`: `table`, `where`, `options?`, returning `SQLFragment<number>`.