Postgres

Every type, null, row count and parameter rule on Postgres

This page is the reference behind the main docs for Postgres: the place to look when a type, a | null, a row count or a parameter surprises you. MySQL and SQLite have the same page, with every answer in the same place.

create extension if not exists pg_jsonschema;

create type user_role as enum ('admin', 'member');
create domain amount as numeric(12, 2) check (value >= 0);

create table users (
  id serial primary key,
  name text not null,
  email text unique,
  role user_role not null default 'member',
  tags text[] not null default '{}',
  created_at timestamptz not null default now()
);
create unique index users_email_lower on users (lower(email));

create table orders (
  id bigserial primary key,
  user_id integer not null references users (id),
  total amount not null,
  paid boolean not null default false,
  placed_at timestamptz not null default now(),
  ships_on date,
  receipt bytea
);

create table events (
  id bigserial primary key,
  kind text not null check (kind in ('click', 'key')),
  payload jsonb not null check (jsonb_matches_schema('{
    "type": "object",
    "properties": {
      "x": { "type": "integer" },
      "y": { "type": "integer" },
      "code": { "type": "string" }
    }
  }', payload)),
  at timestamp not null default localtimestamp
);

Data types

Reylo decodes every value of its own queries itself, on every driver, so each SQL type has one TypeScript type and one form at run time. In each pair below the table, the SQL is a value and the JavaScript is what Reylo returns: select <SQL> as v gives the row { v: <JavaScript> }.

Integers

A bigint is a JavaScript bigint, and so is count(*), which Postgres types bigint. Cast in the statement where you want a number:

import countOrders from "./pg_count_orders.sql";

const counts = await countOrders.run(db);
       
const counts: {
    total: bigint;
    total_number: number;
}

select count(*) as total, count(*)::int as total_number
from orders

Decimals

A numeric is its exact text, "12.50", with the column's scale, and so is an avg(). A money is its text in the server's currency format, "$1.50".

Dates and times

No value is a Date. A timestamptz is the instant in UTC, in ISO 8601 with a Z, and every type without a zone is the text Postgres writes, with a T between the date and the time of a timestamp:

import orderDates from "./pg_order_dates.sql";

const order = await orderDates.run(db, { id: 1 });
      
const order: {
    placed_at: string;
    ships_on: string | null;
} | null

select placed_at, ships_on from orders where id = :id

Where your runtime has Temporal, Temporal.Instant.from() reads the instant, and Temporal.PlainDate.from() the date. Each text goes back in as a parameter as it is (What a parameter takes).

Truth values

A boolean is a JavaScript boolean, and so is every comparison, IS NULL, EXISTS, IN, LIKE, NOT, AND and OR the statement computes. Postgres stores a real boolean, so no truth value is refused here, as it can be on MySQL and SQLite.

JSON

A json or jsonb value is parsed, and typed unknown, unless a JSON Schema CHECK holds the column or the statement builds the JSON. With the schema's CHECK, events.payload is what the schema admits, and kind, which a CHECK keeps to two strings, is their union:

import events from "./pg_events.sql";

const rows = await events.run(db);
      
const rows: {
    kind: "click" | "key";
    payload: {
        x?: number;
        y?: number;
        code?: string;
        [key: string]: unknown;
    };
}[]

select kind, payload from events

JSON the statement builds with jsonb_build_object, json_build_object, jsonb_agg, json_agg or to_jsonb of a whole row is typed from its arguments, each value as JSON writes it:

import orderSummaries from "./pg_order_summaries.sql";

const rows = await orderSummaries.run(db);
      
const rows: {
    name: string;
    summary: {
        total: number;
        placed_at: string;
        paid: boolean;
    };
}[]

select u.name,
  jsonb_build_object(
    'total', o.total,
    'placed_at', o.placed_at,
    'paid', o.paid
  ) as summary
from orders o
join users u on u.id = o.user_id

Through an aggregate the schema's type holds, and a whole row as JSON is typed column by column, an enum's labels included:

import eventsByKind from "./pg_events_by_kind.sql";

const groups = await eventsByKind.run(db);
       
const groups: {
    kind: "click" | "key";
    payloads: {
        x?: number;
        y?: number;
        code?: string;
        [key: string]: unknown;
    }[];
}[]

import userRow from "./pg_user_row.sql";

const row = await userRow.run(db, { id: 1 });
     
const row: {
    user: {
        id: number;
        name: string;
        email: string | null;
        role: "admin" | "member";
        tags: string[];
        created_at: string;
    };
} | null

select kind, jsonb_agg(payload order by id) as payloads
from events
group by kind

select to_jsonb(u) as "user" from users u where u.id = :id::int

Binary

A bytea is a Uint8Array. Most drivers give Reylo a Buffer, which is one, so .run() returns it as it is.

Arrays

An array is a JavaScript array of its element's type, on every driver, an enum's and a domain's included:

import orderIds from "./pg_order_ids.sql";

const rows = await orderIds.run(db);
      
const rows: {
    name: string;
    tags: string[];
    order_ids: (bigint | null)[];
}[]

select u.name, u.tags, array_agg(o.id) as order_ids
from users u
left join orders o on o.user_id = u.id
group by u.id

Enums and domains

An enum is the union of its labels, and so is a text or varchar column whose CHECK keeps it to a list of strings (kind in ('click', 'key')). A domain is its base type: orders.total, an amount, is a numeric, so a string.

Other types

Every other type is the text Postgres writes for it, a string: uuid, inet, cidr, macaddr, ranges and multiranges, geometric types, tsvector, xml, money, and a type an extension creates.

Values Reylo refuses

Every Postgres driver but Bun.sql hands Reylo each value's text, which Reylo decodes itself, so one value is refused, with an error that names its column:

Nullability

A column is | null unless Reylo infers it never is. On Postgres, Reylo reads the catalog and the statement:

• A table's column is non-null where its NOT NULL is valid and holds in every table that inherits from it. A NOT NULL added NOT VALID tells Reylo nothing, since rows written before it may hold NULL.
• An outer join makes the joined table's columns nullable: the right side of a left join, the left side of a right join, both sides of a full join.
• A test in the where that fails on NULL makes the column non-null, and a later inner join's on shows the joined row exists: where o.total > 0, where email is not null, where lower(email) = :email.
• An expression is non-null when Reylo's rules infer it:
  • a literal, a cast, count, exists and is null;
  • concatenation with ||, a comparison and arithmetic over non-null operands;
  • a built-in function that returns NULL only for a NULL argument (length, lower, round, …);
  • a coalesce with a non-null argument;
  • a case whose branches and else are all non-null.
• An aggregate other than count is NULL over no rows, so it is nullable without a group by. With one, every group has a row:
  • the aggregates that write a NULL into what they build, array_agg, json_agg, jsonb_agg and jsonb_object_agg, are non-null;
  • those that skip a NULL, sum, max, string_agg and the others, are non-null where their argument is;
  • an aggregate with a filter stays nullable, since the filter can leave a group no rows.
• A table's whole row (select e, jsonb_agg(e)) is never NULL unless an outer join nulls it, and keeps its columns' NOT NULL and their CHECK lists.
• A scalar subquery is non-null only when it is an aggregate without a group by over a non-null value, as (select count(*) …) is.
• A column of a union is non-null when it is in every arm.
• An operator, function or cast your database defines, or an extension does, tells Reylo nothing: the rules stand down wherever Postgres could pick one of those over the built-in.

import userTotals from "./pg_user_totals.sql";

const rows = await userTotals.run(db);
      
const rows: {
    name: string;
    total: string | null;
    paid: boolean;
}[]

select u.name, o.total, coalesce(o.paid, false) as paid
from users u
left join orders o on o.user_id = u.id

A test in the where shows the joined row exists, so its columns are non-null; a window function is typed like any other, lag nullable on the first row:

import paidOrders from "./pg_paid_orders.sql";

const rows = await paidOrders.run(db);
      
const rows: {
    name: string;
    total: string;
    placed_at: string;
}[]

import orderGaps from "./pg_order_gaps.sql";

const rows = await orderGaps.run(db);
      
const rows: {
    id: bigint;
    placed_at: string;
    since_previous: string | null;
    n: bigint;
}[]

select u.name, o.total, o.placed_at
from users u
left join orders o on o.user_id = u.id
where o.paid

select id, placed_at,
  placed_at - lag(placed_at) over (order by placed_at) as since_previous,
  row_number() over (order by placed_at) as n
from orders

Where you know more than Reylo can infer, say it in the statement: coalesce(x, …) gives a default, and a where x is not null test both filters and makes the column non-null.

Row counts

How many rows .run() resolves to comes from what Reylo infers the statement can return:

A unique index on an expression counts when the statement compares the same expression, and a partial unique index when the where holds its predicate:

import userByEmail from "./pg_user_by_email.sql";

const user = await userByEmail.run(db, { email: "Ada@example.com" });
      
const user: {
    id: number;
    name: string;
} | null

select id, name from users where lower(email) = lower(:email)

• The expression is matched as Postgres stores it in the index. An index over a domain column stores a cast, lower((email)::text), and the statement must write it the same way to match.
• A partial unique index without its predicate in the where counts for nothing: where slug = :slug over an index on slug where not deleted is Row[].
• Part of a composite key counts for nothing, and neither does a having, or an insert … select … returning.
• At run time a Row statement throws unless it gets exactly one row, and a Row | null statement throws when it gets more than one.

Parameters

Placeholders

Write each parameter as :name. Reylo sends $1, $2, … and .run() takes an object keyed by name. A statement may use Postgres's own $1 instead, never beside :name, and the object's keys are then the numbers, { 1: 42 }.

• Each parameter appears once in a statement, so that one place types it. To use a value twice, select it once in a with and read it from there.
• A $name is no placeholder on Postgres: Reylo's error says to write :name.

What a parameter takes

Every value .run() returns goes back in as a parameter of its type, on every driver, so a row can be passed straight to the next statement; a null goes back in where the parameter takes one (Parameters that may be null):

• The parameters of limit :n and offset :n are bigint, so they take a number too.
• A timestamptz parameter takes the text of an instant that names its zone, as .run() returns it: "2026-10-12T19:06:48Z", "2026-10-12 19:06:48+09", "2026-10-12 19:06:48 Asia/Tokyo", or infinity, -infinity, epoch and now. Pass a Date as date.toISOString().
• Text without a zone is refused by .run() before the statement runs, since the server would read it in the session's time zone.
• A timestamp or a date parameter refuses a Date: drivers write a Date's time in different zones, the app's, the server's or UTC.
• Postgres refuses the extended years a timestamptz comes back with, +010000-… and 0000, so Reylo writes them as 10000-… and … BC, and the value goes back in.
• A JSON parameter reaches the database as its JSON text on every driver, so a string is a JSON string: '{"a":1}' is stored as the string "{\"a\":1}". To pass JSON text you already hold, cast it from text: :data::text::jsonb.
• A typed JSON parameter, :events::jsonb('…'), reaches Postgres as $1::jsonb (or $1::json) holding its JSON text, after .run() has filled in its schema's defaults and checked the value against the schema.
• An array's elements never take null, and .run() refuses an array that holds one: id = any(:ids) matches nothing for a NULL element.

Parameters without a type

Postgres types every parameter from where the statement uses it:

• the column it is compared with or written to;
• the function it is passed to;
• the cast around it.

A bare select :x is text, so a string. Where nothing types it, as in select :x is null or concat(:x, '!'), Postgres refuses the statement, "could not determine data type of parameter" (42P18), until a cast types it: :x::text.

Parameters that may be null

A parameter takes null in two places:

• as the first argument of coalesce, with a cast or without;
• written whole, with no cast, to a column that may hold NULL.

A cast is the parameter's whole type, so :email::text never takes null, and a test for null (:role is null, is not distinct from) makes nothing nullable. A column holds NULL unless it is NOT NULL, its domain is declared not null, or a check (… is not null) holds it:

import usersByRole from "./pg_users_by_role.sql";

const users = await usersByRole.run(db, { role: null });
                                          
role: "admin" | "member" | null

select id, name from users
where role = coalesce(:role::user_role, role)

Casts

A :: cast is Postgres's own, and Reylo sends it as written: :n::int8, :tags::text[], :at::timestamptz. So is cast(:n as int8). A cast types a parameter wherever its context does not, and the parameter takes what its cast's type takes.

How the text is read

Reylo reads the statement as Postgres does: a :name is a placeholder only outside single-quoted strings (with ''), E'' strings (with backslashes), dollar-quoted strings ($$…$$, $tag$…$tag$), double-quoted names, and line and nested block comments, and a :: is a cast.

Drivers

Each driver reads Postgres's protocol as it always does, and Reylo makes only the choice per type that follows, for its own query, on the client you pass. A parser or option you set for your own queries does not reach Reylo's, and none of Reylo's reaches yours.

• A postgres.js transform that renames your columns does not rename Reylo's.
• On Bun.sql, a statement with a parameter is read over the binary protocol and one without as text. So a real is its exact float, 1.100000023841858, only with a parameter, and a timestamp or a timestamptz before 1 AD is refused only without one; a date before 1 AD is refused either way.
• Bun fails with its own error, "Failed to read data", before Reylo sees the value, on a two-dimensional int[] in a statement with a parameter and on an array with explicit bounds in one without.
• The @vercel/postgres package is deprecated on npm: Reylo's types accept its client, and its tests do not run it.
• Each driver takes its transactions and connections too: pg's Pool, Client and PoolClient; postgres.js's and Bun.sql's .begin(), .savepoint() and .reserve(); PGlite's .transaction(); Neon's Pool and Client; and pg-promise's .task() and .tx().

The type check reads the schema through postgres.js or pg, so an app on PGlite, Neon, Bun.sql, pg-promise or @vercel/postgres adds one of them as a dev dependency.

Limits

The edges of what Reylo types on Postgres, each with what to write instead:

• A function's body. A SQL-language function is typed from its declaration (returns), never from its body, and a PL/pgSQL body is not read at all. Declare the result you return.
• An array's dimensions and elements. An int[] is number[] however many dimensions it holds, and its elements are typed without null unless the statement builds it. Keep an array one-dimensional, or build it with array_agg.
• A row value outside JSON. It is its text. Select its fields as columns, or build an object with to_jsonb or jsonb_build_object.
• A parameter in place of a name. A parameter is a value: select * from :table is Postgres's syntax error, and order by :column sorts by a constant. Choose a column with case :sort when 'name' then name end.
• A statement nested past 1,000 levels of the parse tree (about 496 terms of a + b + …, or 164 nested subqueries), which Reylo types from the server's description alone: any number of rows, every column nullable.
• One statement per file. A file holds one select, insert, update, delete, merge, values or table. A file of several statements or of DDL, such as a migration, is an error in the type check.