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`

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

Each member is a sql fragment written around db.self, or a function that returns one. As the value of a key of a 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)).

TSYou write Open in playground

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

SQLPostgres 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

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(), count(), update() and 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(), or() and not() also take Whereables, so they can be a whole where; now and fromNow() hold no self, and go anywhere.
Values: each value is a parameter, $1, except a Parameter, kept as it is with its cast; a sql fragment, written as its SQL; and parent(), the parent query's column in a lateral subquery. Default, self, raw(), cols() and vals() throw: Values a condition refuses.
Type: a condition is a SQLFragment whose second type argument, T, is the type of the value it compares with (its Constraint, 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:

db.select
('films', { release_year: db.conditions.eq
('1991') });Type 'SQLFragment<boolean | null, string>' is not assignable to type 'number | Parameter<number> | SQLFragment<any[], never> | ParentColumn<Column | undefined> | SQLFragment<any, number | Parameter<number> | SQLFragment<...> | ParentColumn<...>> | undefined'.
  Type 'SQLFragment<boolean | null, string>' is not assignable to type 'Parameter<number> | SQLFragment<any[], never> | ParentColumn<Column | undefined> | SQLFragment<any, number | Parameter<number> | SQLFragment<...> | ParentColumn<...>>'.
    Type 'SQLFragment<boolean | null, string>' is not assignable to type 'SQLFragment<any[], never>'.
      Type 'boolean | null' is not assignable to type 'any[]'.
        Type 'null' is not assignable to type 'any[]'.

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.

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`

TSYou write Open in playground

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

SQLPostgres 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

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.

TSYou write Open in playground

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

SQLPostgres 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

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 NULLs.

`eq()`

"col" = $1.

`ne()`

"col" <> $1.

`gt()`

"col" > $1. after() is the same function.

`gte()`

"col" >= $1.

`lt()`

"col" < $1. 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 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.

TSYou write Open in playground

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

SQLPostgres 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

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.

TSYou write Open in playground

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

SQLPostgres 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

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() of it: one parameter per item.

TSYou write Open in playground

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

SQLPostgres 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

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)

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.

TSYou write Open in playground

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

SQLPostgres 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

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(): <T>(a: T[] | ParentColumn). They compare the elements, whatever the dimensions: {drama,romance} overlaps .

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-expect-error genres takes a string[], and arrayOverlaps(['romance']) gives a string (TS2322)
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). Two ways compile. Given the column's type as T, the value is an array of arrays:

TSYou write Open in playground

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

SQLPostgres 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

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"[]).

TSYou write Open in playground

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

SQLPostgres 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

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

TSYou write Open in playground

const films
 = await db.select
('films', db.conditions.or
(
  { studio_id
: 1002 },
  { release_year
: db.conditions.lt
(1980) },
)).run
(pool
);

SQLPostgres 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

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()`

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()`

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()`

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

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

TSYou write Open in playground

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

SQLPostgres 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

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() and 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.

TSYou write Open in playground

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

SQLPostgres 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

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()`

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.

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:

TSYou write Open in playground

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

SQLPostgres 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

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()`

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

Operations, not tests: for an update() value, where self is the column it sets.

TSYou write Open in playground

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

SQLPostgres 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

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()). 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). 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.

TSYou write Open in playground

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

SQLPostgres 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

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.

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.

Conditions ​

conditions ​

Tests: null, true, false, unknown ​

isNull ​

isNotNull ​

isTrue ​

isNotTrue ​

isFalse ​

isNotFalse ​

isUnknown ​

isNotUnknown ​

Comparisons ​

eq() ​

ne() ​

gt() ​

gte() ​

lt() ​

lte() ​

isDistinctFrom() ​

isNotDistinctFrom() ​

Ranges ​

between() ​

betweenSymmetric() ​

notBetween() ​

notBetweenSymmetric() ​

Patterns ​

like() ​

notLike() ​

ilike() ​

notIlike() ​

similarTo() ​

notSimilarTo() ​

reMatch() ​

reImatch() ​

notReMatch() ​

notReImatch() ​

Lists ​

isIn() ​

isNotIn() ​

Arrays ​

arrayContains() ​

arrayContainedIn() ​

arrayOverlaps() ​

Combining: and(), or(), not() ​

and() ​

or() ​

not() ​

Dates and times ​

after() ​

before() ​

now ​

fromNow() ​

Arithmetic: add(), subtract() ​

add() ​

subtract() ​

Values a condition refuses ​

Conditions

`conditions`

Tests: null, true, false, unknown

`isNull`

`isNotNull`

`isTrue`

`isNotTrue`

`isFalse`

`isNotFalse`

`isUnknown`

`isNotUnknown`

Comparisons

`eq()`

`ne()`

`gt()`

`gte()`

`lt()`

`lte()`

`isDistinctFrom()`

`isNotDistinctFrom()`

Ranges

`between()`

`betweenSymmetric()`

`notBetween()`

`notBetweenSymmetric()`

Patterns

`like()`

`notLike()`

`ilike()`

`notIlike()`

`similarTo()`

`notSimilarTo()`

`reMatch()`

`reImatch()`

`notReMatch()`

`notReImatch()`

Lists

`isIn()`

`isNotIn()`

Arrays

`arrayContains()`

`arrayContainedIn()`

`arrayOverlaps()`

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

`and()`

`or()`

`not()`

Dates and times

`after()`

`before()`

`now`

`fromNow()`

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

`add()`

`subtract()`

Values a condition refuses