# Conditions

Source: https://pgcast.dev/conditions

`db.conditions` holds the tests, comparisons and operators you give as the value of a Whereable's key, each a `sql` fragment with that column on its left. This page lists each one, the SQL it writes and the values it takes.

## `conditions`

```ts
db.conditions.eq(value)  // or: import { conditions as dc } from 'zapatos/db'
```

Each member is a [`sql`](https://pgcast.dev/sql-template#sql) fragment written around [`db.self`](https://pgcast.dev/sql-template#self), or a function that returns one. As the value of a key of a [Whereable](https://pgcast.dev/sql-template#whereable), `self` is that key's column, and the condition sits in its own parentheses inside the Whereable's: `{ release_year: db.conditions.gte(2000) }` gives `(("release_year" >= $1))`.

You write:

```ts
const films = await db.select('films', {
  release_year: db.conditions.between(1990, 2010),
  title: db.conditions.like('The %'),
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("films".*) AS result
  FROM "films"
  WHERE (("release_year" BETWEEN ($1) AND ($2)) AND ("title" LIKE $3))
) AS "sq_films"
```

Values: `[1990, 2010, "The %"]`

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

- **Where**: as a Whereable value, so in the `where` of [`select()`](https://pgcast.dev/select#select), [`count()`](https://pgcast.dev/select#count), [`update()`](https://pgcast.dev/insert#update) and [`deletes()`](https://pgcast.dev/insert#deletes), the `having` of `select()`, and a Whereable in a `sql` template. Elsewhere `self` has no column: `` db.sql`SELECT ${db.conditions.isNull}` `` throws "The 'self' column alias has no meaning here" as it compiles.
  - [`and()`](https://pgcast.dev/conditions#conditions.and), [`or()`](https://pgcast.dev/conditions#conditions.or) and [`not()`](https://pgcast.dev/conditions#conditions.not) also take Whereables, so they can be a whole `where`; [`now`](https://pgcast.dev/conditions#conditions.now) and [`fromNow()`](https://pgcast.dev/conditions#conditions.fromNow) hold no `self`, and go anywhere.
- **Values**: each value is a parameter, `$1`, except a [`Parameter`](https://pgcast.dev/sql-template#Parameter), kept as it is with its cast; a `sql` fragment, written as its SQL; and [`parent()`](https://pgcast.dev/sql-template#parent), the parent query's column in a `lateral` subquery. `Default`, `self`, `raw()`, `cols()` and `vals()` throw: [Values a condition refuses](https://pgcast.dev/conditions#refused-values).
- **Type**: a condition is a `SQLFragment` whose second type argument, `T`, is the type of the value it compares with (its `Constraint`, [`sql`](https://pgcast.dev/sql-template#sql)); the Whereable checks `T` against the column's type. The eight tests, `now` and `fromNow()` have no `T`, and pass on any column.

A value of another type does not compile:

```ts
db.select('films', { release_year: db.conditions.eq('1991') });  // error TS2322
```

With `strictNullChecks` (or `strict`) on, the type also refuses `null` as a value, in `eq()`, `isIn()` and the others; off, `eq(null)` compiles to `"col" = $1`, which matches no row. The test for `NULL` is [`isNull`](https://pgcast.dev/conditions#conditions.isNull).

## Tests: null, true, false, unknown

Constants, not functions: `{ subtitled: db.conditions.isNull }`, no `()`. Each writes the column, then its test.

| Test | Matches a `boolean` that is |
|---|---|
| `isNull`, `isUnknown` | `NULL` |
| `isNotNull`, `isNotUnknown` | `true` or `false` |
| `isTrue` | `true` |
| `isNotTrue` | `false` or `NULL` |
| `isFalse` | `false` |
| `isNotFalse` | `true` or `NULL` |

You write:

```ts
const screenings = await db.select('screenings', {
  subtitled: db.conditions.isNotTrue,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("screenings".*) AS result
  FROM "screenings"
  WHERE (("subtitled" IS NOT TRUE))
) AS "sq_screenings"
```

Values: `[]`

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

`isNull` and `isNotNull` work on any column. The six others are for a `boolean`: on another type Postgres refuses them (42804), though the type lets them through.

### `isNull`

`"col" IS NULL`.

### `isNotNull`

`"col" IS NOT NULL`.

### `isTrue`

`"col" IS TRUE`.

### `isNotTrue`

`"col" IS NOT TRUE`.

### `isFalse`

`"col" IS FALSE`.

### `isNotFalse`

`"col" IS NOT FALSE`.

### `isUnknown`

`"col" IS UNKNOWN`: as `isNull`, for a `boolean`.

### `isNotUnknown`

`"col" IS NOT UNKNOWN`: as `isNotNull`, for a `boolean`.

## Comparisons

Each takes one value of the column's type, `<T>(a: T)`, and writes the column, its operator and the value.

You write:

```ts
const films = await db.select('films', {
  release_year: db.conditions.gte(2000),
  studio_id: db.conditions.ne(1001),
}).run(pool);
```

Postgres runs:

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

Values: `[2000, 1001]`

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

`ne()` and the four orderings match no row whose column is `NULL`; `isDistinctFrom()` does: on `screenings`, `{ subtitled: ne(true) }` finds the `false` row, `{ subtitled: isDistinctFrom(true) }` the `false` row and the two `NULL`s.

### `eq()`

`"col" = $1`.

### `ne()`

`"col" <> $1`.

### `gt()`

`"col" > $1`. [`after()`](https://pgcast.dev/conditions#conditions.after) is the same function.

### `gte()`

`"col" >= $1`.

### `lt()`

`"col" < $1`. [`before()`](https://pgcast.dev/conditions#conditions.before) is the same function.

### `lte()`

`"col" <= $1`.

### `isDistinctFrom()`

`"col" IS DISTINCT FROM $1`: as `ne()`, with `NULL` a value like any other, so it is never unknown.

### `isNotDistinctFrom()`

`"col" IS NOT DISTINCT FROM $1`: as `eq()`, with `NULL` a value like any other. With `strictNullChecks` on, the type refuses `null` here too: [`isNull`](https://pgcast.dev/conditions#conditions.isNull) is the test for it.

## Ranges

Each takes two values of the column's type, `<T>(a: T, b: T)`, each a parameter in its own parentheses. Both bounds belong to the range: `between()` matches them, `notBetween()` does not.

You write:

```ts
const films = await db.select('films', {
  release_year: db.conditions.betweenSymmetric(2010, 1990),
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("films".*) AS result
  FROM "films"
  WHERE (("release_year" BETWEEN SYMMETRIC ($1) AND ($2)))
) AS "sq_films"
```

Values: `[2010, 1990]`

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

### `between()`

`"col" BETWEEN ($1) AND ($2)`. `a` is the low bound: `between(2010, 1990)` matches no row.

### `betweenSymmetric()`

`"col" BETWEEN SYMMETRIC ($1) AND ($2)`: the bounds in either order.

### `notBetween()`

`"col" NOT BETWEEN ($1) AND ($2)`. With the bounds reversed, every row whose column is not `NULL`.

### `notBetweenSymmetric()`

`"col" NOT BETWEEN SYMMETRIC ($1) AND ($2)`: the bounds in either order.

## Patterns

Each takes one pattern, `<T extends string>(a: T)`, so only on a column whose type is a string: on `release_year`, the type refuses them (TS2322).

| Family | Syntax | Matches |
|---|---|---|
| `like()`, `ilike()` | `%` any text, `_` one character | the whole value |
| `similarTo()` | Postgres's SQL regular expressions | the whole value |
| `reMatch()`, `reImatch()` | POSIX regular expressions | any part of the value |

So `like('Lantern')` matches no title, and `reMatch('Lantern')` matches `A Lantern for Edith`. The `i` forms ignore case.

You write:

```ts
const films = await db.select('films', {
  title: db.conditions.reImatch('^(the|a) '),
}).run(pool);
```

Postgres runs:

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

Values: `["^(the|a) "]`

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

### `like()`

`"col" LIKE $1`.

### `notLike()`

`"col" NOT LIKE $1`.

### `ilike()`

`"col" ILIKE $1`.

### `notIlike()`

`"col" NOT ILIKE $1`.

### `similarTo()`

`"col" SIMILAR TO $1`.

### `notSimilarTo()`

`"col" NOT SIMILAR TO $1`.

### `reMatch()`

`"col" ~ $1`.

### `reImatch()`

`"col" ~* $1`.

### `notReMatch()`

`"col" !~ $1`.

### `notReImatch()`

`"col" !~* $1`.

## Lists

Each takes an array of values of the column's type, `<T>(a: readonly T[])`, compiled as [`vals()`](https://pgcast.dev/sql-template#vals) of it: one parameter per item.

You write:

```ts
const films = await db.select('films', {
  id: db.conditions.isIn([1000, 1002]),
}).run(pool);
```

Postgres runs:

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

Values: `[1000, 1002]`

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

An item compiles as in `vals([…])` within a filter:

| Item | Compiles to |
|---|---|
| a plain value | a parameter, `$1` |
| a `Parameter` | as it is, with its cast: `isIn([db.param('x', 'text'), 'y'])` gives `IN (CAST($1 AS "text"), $2)` |
| a `sql` fragment | its SQL |
| `parent(c?)` | the parent column, in a `lateral` subquery: `IN ("films"."id")` |
| `Default`, `self`, `raw()`, `cols()`, `vals()` | throws ([Values a condition refuses](https://pgcast.dev/conditions#refused-values)) |

Zapatos 6.6.1 sent a `Parameter` and `parent()` there as one more parameter, as JSON (`'{"value":"x","cast":"text"}'`, `'{"value":"id"}'`), so `isIn()` found nothing and `isNotIn()` matched every row on a text column.

### `isIn()`

`"col" IN ($1, $2)`. An empty array gives `false`: no row.

### `isNotIn()`

`"col" NOT IN ($1, $2)`. An empty array gives `true`: every row. A `NULL` in the list, as Postgres reads `NOT IN`, makes it match no row; the type refuses `null` there with `strictNullChecks` on.

You write:

```ts
const films = await db.select('films', {
  studio_id: db.conditions.isNotIn([1001]),
}).run(pool);
```

Postgres runs:

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

Values: `[1001]`

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

## Arrays

Each takes an array, sent as one parameter, or [`parent()`](https://pgcast.dev/sql-template#parent): `<T>(a: T[] | ParentColumn)`. They compare the elements, whatever the dimensions: `{drama,romance}` overlaps `{{romance,thriller}}`.

`T` is the element, as in Zapatos 6.6.1, where the Whereable expects the column's array type: on `genres`, a `text[]`, the type refuses `arrayOverlaps(['romance'])`.

```ts
db.select('films', { genres: db.conditions.arrayOverlaps(['romance']) });
```

It stays that way: every type I tried on the column broke some code that compiles under 6.6.1, either a mock written to its signature or the typed result of `.map(db.conditions.arrayContains)` ([#40](https://github.com/AlexandreDemartin/pgcast/issues/40)). Two ways compile. Given the column's type as `T`, the value is an array of arrays:

You write:

```ts
const films = await db.select('films', {
  genres: db.conditions.arrayOverlaps<string[]>([['romance', 'thriller']]),
}).run(pool);
```

Postgres runs:

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

Values: `[[["romance","thriller"]]]`

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

Or write a `sql` fragment value, where `db.self` is the column, and cast its parameter: `` db.sql`${db.self} @> ${db.param(['drama'], 'text[]')}` `` gives `"col" @> CAST($1 AS "text"[])`.

You write:

```ts
const dramas = await db.select('films', {
  genres: db.sql`${db.self} @> ${db.param(['drama'], 'text[]')}`,
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("films".*) AS result
  FROM "films"
  WHERE (("genres" @> CAST($1 AS "text"[])))
) AS "sq_films"
```

Values: `[["drama"]]`

TypeScript knows:

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

With [`castArrayParamsToJson`](https://pgcast.dev/runtime-config#Config.castArrayParamsToJson) on, a condition sends its array as `CAST($1 AS "json")`, an array of arrays too, which Postgres refuses (42883: `text[] @> json`). The condition's type refuses a `Parameter` (TS2345), but in the fragment, the cast given to `param()` wins over the option ([#36](https://github.com/AlexandreDemartin/pgcast/issues/36)): it runs either way.

### `arrayContains()`

`"col" @> $1`: the column holds every element of the value.

### `arrayContainedIn()`

`"col" <@ $1`: every element of the column is in the value.

### `arrayOverlaps()`

`"col" && $1`: they share at least one element.

## Combining: `and()`, `or()`, `not()`

Each takes conditions or Whereables, a Whereable compiled with its own keys. As a Whereable value, `self` reaches the conditions inside: `{ release_year: or(lt(1980), gt(2010)) }` gives `((("release_year" < $1 OR "release_year" > $2)))`. As a whole `where`, they take Whereables:

You write:

```ts
const films = await db.select('films', db.conditions.or(
  { studio_id: 1002 },
  { release_year: db.conditions.lt(1980) },
)).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) OR (("release_year" < $2)))
) AS "sq_films"
```

Values: `[1002, 1980]`

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

### `and()`

```ts
db.conditions.and<T>(...conditions: (SQLFragment<any, T> | Whereable)[])
```

`((a) AND (b))`: each operand in its own parentheses, joined by `AND`.

- A `sql` fragment with a bare `OR` at its top level stays whole: `` and(db.sql`${'id'} = 1000 OR ${'id'} = 1001`, { studio_id: 1001 }) `` gives `(("id" = 1000 OR "id" = 1001) AND (("studio_id" = $1)))`, film 1001 alone.
- With no argument, `TRUE`: every row, as a Whereable `{}`. Zapatos 6.6.1 wrote `()`, which Postgres refuses (42601).

> **Every row**
>
> `update()` and `deletes()` given `and(...filters)` or `not(or(...ids))` with an empty list now reach every row: `deletes('films', and())` sends `WHERE TRUE` and deletes them all, `not(or())` gives `(NOT (FALSE))`. Zapatos 6.6.1 threw (42601) before any row changed. Keep the length check where an empty list must not reach every row.

Zapatos 6.6.1 wrote the operands bare, `(a OR b AND c)`, which Postgres reads `a OR (b AND c)`: the example above found film 1000 too, whatever its studio, and `deletes()` or `update()` given it deleted or updated that row.

Only an `and()` of two operands or more, one with a bare `OR` at its top level, now gives other rows. One whose SQL ends in the next operand:

- now fails (42601), `` and(db.sql`${'release_year'} BETWEEN 1970`, db.sql`2000`) ``;
- or, when the split falls inside a quoted literal, compares with another value, with no error: `` and(db.sql`${'title'} = 'x`, db.sql`y'`) `` compares `"title"` with `'x) AND (y'`, where it was `'x AND y'`.

Any other gains parentheses and gives the same rows.

### `or()`

```ts
db.conditions.or<T>(...conditions: (SQLFragment<any, T> | Whereable)[])
```

`(a OR b)`: its operands joined by `OR`, as they are: `AND` binds tighter than `OR`, so none reaches across it. With no argument, `FALSE`: no row. Zapatos 6.6.1 wrote `()` here too.

### `not()`

```ts
db.conditions.not<T>(condition: SQLFragment<any, T> | Whereable)
```

`(NOT (…))`: the whole condition negated.

You write:

```ts
const films = await db.select('films', {
  studio_id: db.conditions.not(db.conditions.isIn([1000, 1001])),
}).run(pool);
```

Postgres runs:

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

Values: `[1000, 1001]`

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

On `release_year`, `` not(db.sql`${db.self} = 1974 OR ${db.self} = 1991`) `` writes `(NOT ("release_year" = 1974 OR "release_year" = 1991))`.

Zapatos 6.6.1 wrote `(NOT "release_year" = 1974 OR "release_year" = 1991)`, which Postgres reads `(NOT "release_year" = 1974) OR "release_year" = 1991`: the 1991 film too. Only a condition with a bare `AND` or `OR` at its top level changes rows; any other gains one pair of parentheses.

## Dates and times

[`after()`](https://pgcast.dev/conditions#conditions.after) and [`before()`](https://pgcast.dev/conditions#conditions.before) compare with a value: a `Date`, an ISO 8601 string with its offset (`'2030-03-01T20:00:00Z'`, a `TimestampTzString`; the type refuses another form such as `'2030-03-02'`, TS2322), or one of the two fragments below. `pg` sends a `Date` as a string in the local time zone, with its offset: on a `timestamptz` column, the same instant.

You write:

```ts
const screenings = await db.select('screenings', {
  starts_at: db.conditions.after(new Date('2030-03-02T00:00:00Z')),
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("screenings".*) AS result
  FROM "screenings"
  WHERE (("starts_at" > $1))
) AS "sq_screenings"
```

Values: `["2030-03-02T00:00:00.000Z"]`

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

### `after()`

`gt()` under another name: `"col" > $1`.

### `before()`

`lt()` under another name: `"col" < $1`.

### `now`

`now()`: a constant, the start of the current transaction. A value, not a condition: `{ starts_at: after(now) }` gives `(("starts_at" > now()))`, and in `update()` it sets the column. Alone as a Whereable value, `{ starts_at: now }`, Postgres refuses it (42804), though the type lets it through.

### `fromNow()`

```ts
db.conditions.fromNow(n: number, unit: IntervalUnit = 'millisecond')
```

`now() + $1`, the value `` `${n} ${unit}` ``, which Postgres reads as an interval: `fromNow(-7, 'days')` sends `'-7 days'`, `fromNow(1.5, 'hours')` `'1.5 hours'`. A value, as [`now`](https://pgcast.dev/conditions#conditions.now).

`unit` is one of `microsecond`, `millisecond`, `second`, `minute`, `hour`, `day`, `week`, `month`, `year`, `decade`, each with or without an `s`, then `century`, `centuries`, `millennium`, `millennia`. An `n` whose string Postgres does not read (`NaN`, `1e21`, written `1e+21`) fails with 22007.

The screenings of the next seven days, from `now()`, here the time of the query:

You write:

```ts
const thisWeek = await db.select('screenings', {
  starts_at: db.conditions.and(
    db.conditions.after(db.conditions.now),
    db.conditions.before(db.conditions.fromNow(7, 'days')),
  ),
}).run(pool);
```

Postgres runs:

```sql
SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT to_jsonb("screenings".*) AS result
  FROM "screenings"
  WHERE (((("starts_at" > now()) AND ("starts_at" < now() + $1))))
) AS "sq_screenings"
```

Values: `["7 days"]`

TypeScript knows:

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

## Arithmetic: `add()`, `subtract()`

```ts
db.conditions.add<T extends number | Date>(a: T)
db.conditions.subtract<T extends number | Date>(a: T)
```

Operations, not tests: for an [`update()`](https://pgcast.dev/insert#update) value, where `self` is the column it sets.

You write:

```ts
const ratings = await db.update('ratings', {
  votes: db.conditions.add(1),
}, { film_id: 1000, stars: 5 }).run(pool);
```

Postgres runs:

```sql
UPDATE "ratings" SET ("votes") = ROW("votes" + $1)
WHERE ("film_id" = $2 AND "stars" = $3)
RETURNING to_jsonb("ratings".*) AS result
```

Values: `[1, 1000, 5]`

TypeScript knows:

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

- In a `where`, the type lets them through, and Postgres refuses them (42804: "argument of WHERE must be type boolean").
- In `upsert()`'s `updateValues`, there is no current column: they throw "The 'self' column alias has no meaning here" ([`upsert()`](https://pgcast.dev/insert#upsert)). In `insert()`, the column is written, and Postgres refuses it (42703).
- They take a number or a `Date`, not a `Parameter` (TS2345): for a cast, write a `sql` fragment value (below).
- On a `date` column, the type refuses a number (TS2322), and Postgres could not run it either: `date + unknown` is ambiguous (42725).
- On a `date`, `timestamp` or `timestamptz` column, a `Date` type-checks, and Postgres refuses it ([#180](https://github.com/AlexandreDemartin/pgcast/issues/180)). On `date`, `add()` is ambiguous (42725) and `subtract()` gives an integer, not a date (42804); on the other two, `add()` reads it as an interval (22007), `subtract()` gives an interval, not a time (42804).

For dates, write a `sql` fragment value, where `db.self` is the column, and cast its parameter. On a `date` column, `` db.sql`${db.self} + ${db.param(3, 'int4')}` `` adds three days: `"col" + CAST($1 AS "int4")`. On a `timestamptz` column, add an interval: a screening moved to the next day.

You write:

```ts
const postponed = await db.update('screenings', {
  starts_at: db.sql`${db.self} + ${db.param('1 day', 'interval')}`,
}, { id: 1000 }).run(pool);
```

Postgres runs:

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

Values: `["1 day", 1000]`

TypeScript knows:

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

The type does not check a fragment: an `int4` on a `timestamptz` column type-checks, and Postgres refuses it (42883).

### `add()`

`"col" + $1`.

### `subtract()`

`"col" - $1`.

## Values a condition refuses

`Default`, `self`, `raw()`, `cols()` and `vals()` have no meaning as the value of a condition. The type refuses them (TS2322, or TS2345 where the condition bounds its argument: a pattern, a range's second bound, an array condition, `add()`, `subtract()`).

At run time, calling the condition does not throw, compiling it does, so `run()` rejects with a plain `Error` before any query.

Zapatos 6.6.1 sent `Default` and `self` as the strings `'Symbol(DEFAULT)'` and `'Symbol(self)'`, and `raw()`, `cols()` and `vals()` as JSON, `'{"value":…}'`: no row on a text column, every row with `ne()` or `isNotIn()`, 22P02 or 22007 on the others. Why a `raw()` value is not SQL: [Security](https://pgcast.dev/security).

| Message | When |
|---|---|
| `` `db.Default` has no meaning as the value of `eq()`, `add()` and the other conditions: compare with a value. To set a column to its default in an UPDATE, give `db.Default` as the value itself: `{ n: db.Default }`. `` | `Default` as the value of a comparison, a range, a pattern, an array condition, `add()` or `subtract()` |
| `` `db.self` has no meaning as the value of `eq()`, `add()` and the other conditions: it is already the column on the left. To compare the column with itself, write a `sql` fragment with `db.self` on both sides. `` | `self` there |
| `` `db.raw()`, `db.cols()` and `db.vals()` have no meaning as the value of `eq()`, `add()` and the other conditions: for SQL, write a `sql` fragment with `db.self` in place of the condition; for a value, give the value itself, or `db.param()`. `` | `raw()`, `cols()` or `vals()` there |
| `` `db.Default` has no meaning in a Whereable (a filter): leave the key out, or compare with a value. `DEFAULT` is for INSERT and UPDATE values. `` | `Default` in `isIn()` or `isNotIn()` |
| `` `db.self` has no meaning as a value in a Whereable (a filter), nor in `isIn()`: compare with a value, or use it in a `sql` fragment value, where it stands for the column. `` | `self` in `isIn()` or `isNotIn()` |
| `` `db.raw()`, `db.cols()` and `db.vals()` have no meaning as a value in a Whereable (a filter), nor in `isIn()`: to compare with SQL, use a `sql` fragment value, where `db.self` stands for the column; to compare with a value, give the value itself, or `db.param()`. `` | `raw()`, `cols()` or `vals()` in `isIn()` or `isNotIn()` |
| `The 'self' column alias has no meaning here` | a condition where `self` has no column: in a `sql` template outside a Whereable, in `upsert()`'s `updateValues` |
| `The 'parent' table alias has no meaning here` | `parent()` in a condition outside a `lateral` subquery |

A comparison with SQL is a `sql` fragment value, `` { title: db.sql`${db.self} = ${db.raw("'x'")}` } ``: [Raw SQL](https://pgcast.dev/raw-sql).