ColumnsCopy page

Each field is a data type. The builder name is that type. Postgres is the only database today. A cell is yes when that database has the type built in, and extension when the schema must list that extension. Another database is another column on the same row. A missing extension declaration is OKM1020. See extensions.

An array is .array() on a scalar. A fixed set of strings on a text column is a picklist. A named Postgres enum is its own type, under enum.

Text

Postgres stores text, varchar, and char with the same representation. The length is a check, counted in characters. A value up to 126 bytes is 1 byte plus the string. A longer value is 4 bytes plus the string. char includes its padding in that string. citext uses the same size.

Unbounded

t.text() is text. The TypeScript value is a string. A title, a body, or a name uses it.

import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  name: t.text(), 
});

Varchar

t.varchar(n) is varchar(n). The TypeScript value is a string. n is an integer from 1 to 10485760.

import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  code: t.varchar(16), 
});

Char

t.char(n) is char(n). The TypeScript value is a string. n is an integer from 1 to 10485760. Encode stores the characters you pass.

import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  region: t.char(2), 
});

Citext

t.citext() is citext. The TypeScript value is a string. Comparison ignores case. The schema lists citext() from okmodel/pg/citext. See extensions.

import { citext } from "okmodel/pg/citext";
import { schema, table, t } from "okmodel/pg";

const people = table("people", {
  id: t.identity(),
  email: t.citext(), 
});

export default schema({
  tables: [people],
  extensions: [citext()], 
});

Integers

The value is a whole number inside that range.

Smallint

t.smallint() is smallint. The range is -32768 to 32767. The TypeScript value is a number. Storage is 2 bytes.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  flag: t.smallint(), 
});

Integer

t.integer() is integer. The range is -2147483648 to 2147483647. The TypeScript value is a number. Storage is 4 bytes. This is the usual count.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  total: t.integer(), 
});

Bigint

t.bigint() is bigint. The range is -9223372036854775808 to 9223372036854775807. The TypeScript value is a decimal string, so a value past a safe integer stays exact. Storage is 8 bytes. A bare call stays a string. A schema bigint codec other than "string" needs the same as on this column. A safe integer or a JavaScript bigint selects the other modes.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint(), 
});

Safe integer

{ as: "number" } on t.bigint() or t.identity() makes the TypeScript value a number. It accepts a safe integer only.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint({ as: "number" }), 
});

JavaScript bigint

{ as: "bigint" } on t.bigint() or t.identity() makes the TypeScript value a JavaScript bigint.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint({ as: "bigint" }), 
});

Identity

t.identity() is bigint GENERATED ALWAYS AS IDENTITY and a primary key. Insert and update omit it. The range and the as modes match bigint. A generated integer key uses this builder.

import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(), 
});

Numbers

t.numeric() is exact. Money, rates, and quantities use it. Postgres decimal is this same type. The size is two bytes for each group of four decimal digits, plus 3 to 8 bytes. Leading and trailing zeros are not stored. A bare call stays a string. A schema numeric codec of "number" needs { as: "number" } on this column.

Unconstrained

t.numeric() is numeric. The TypeScript value is a string. Omit precision and scale for an unconstrained numeric. The string keeps every digit.

import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(), 
});

Precision

t.numeric(precision) is numeric(precision). Precision is an integer from 1 to 1000.

import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12), 
});

Scale

t.numeric(precision, scale) is numeric(precision, scale). Scale is an integer from 0 to that precision.

import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12, 2), 
});

Number

{ as: "number" } makes the TypeScript value a number. The SQL type stays the same. It can lose digits past a JavaScript number. Any other as is refused.

import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12, 2, { as: "number" }), 
});

Floats

t.real() and t.double() are binary floating point. A sum of them is not exact. An exact quantity uses numeric. Postgres float is this family.

Real

t.real() is real, single precision, about 6 decimal digits. The TypeScript value is a number. Storage is 4 bytes.

import { table, t } from "okmodel/pg";

export const samples = table("samples", {
  id: t.identity(),
  reading: t.real(), 
});

Double

t.double() is double precision, about 15 decimal digits. The TypeScript value is a number. Storage is 8 bytes.

import { table, t } from "okmodel/pg";

export const samples = table("samples", {
  id: t.identity(),
  reading: t.double(), 
});

Time

The value is a Temporal object. The schema timestamps codec is "temporal". Fractional seconds apply to timestamptz, timestamp, time, and timetz.

Bun and Node 26 provide a Temporal global. Node 22 and 24 need a polyfill assigned to globalThis.Temporal before these columns run. okmodel does not ship one. A missing global is OKM1210.

import { Temporal } from "temporal-polyfill";

globalThis.Temporal = Temporal;

Instant

t.timestamptz() is timestamptz. The TypeScript value is Temporal.Instant. Postgres stores it in UTC. Storage is 8 bytes. A created time or an event uses it.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  publishedAt: t.timestamptz(), 
});

Timestamp

t.timestamp() is timestamp. The TypeScript value is Temporal.PlainDateTime. It stores a civil date and time. Storage is 8 bytes.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  editedAt: t.timestamp(), 
});

Date

t.date() is date. The TypeScript value is Temporal.PlainDate. It stores a calendar day. Storage is 4 bytes.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  day: t.date(), 
});

Time of day

t.time() is time. The TypeScript value is Temporal.PlainTime. It stores a clock time. The zone is its own column. Storage is 8 bytes.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  opens: t.time(), 
});

Offset

t.timetz() is timetz. The TypeScript value is { time, offset }. time is a Temporal.PlainTime. offset is +HH:MM or -HH:MM. A daylight-saving change needs the zone in its own column. Storage is 12 bytes.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  shift: t.timetz(), 
});

Interval

t.interval() is interval. The TypeScript value is Temporal.Duration. It stores a span. Storage is 16 bytes. fields is one of year, month, day, hour, minute, second, year to month, day to hour, day to minute, day to second, hour to minute, hour to second, minute to second.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  length: t.interval("day to second"), 
});

Fractional seconds

precision is fractional seconds, an integer from 0 to 6. Omit it for full precision. It applies to t.timestamptz(), t.timestamp(), t.time(), and t.timetz().

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  editedAt: t.timestamp(0), 
});

Boolean and binary

t.boolean() is true or false. The wire text may be true, false, t, f, 1, or 0. t.bytea() is raw bytes. Up to 126 bytes, the header is 1 byte. A longer value uses a 4-byte header. The wire text is \x plus lowercase hex.

import { table, t } from "okmodel/pg";

export const files = table("files", {
  id: t.identity(),
  published: t.boolean().default(false), 
  body: t.bytea(), 
});

JSON

T is the shape you pass. The sample names it. The codec does not check it. t.jsonb() is the document you query: Postgres stores it in a binary form and can index it. t.json() stores the text, including key order and insignificant space. A bigint inside the value is written as a decimal string. undefined and a function are refused.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  meta: t.json<{ source: string }>(), 
  doc: t.jsonb<{ tags: string[] }>(), 
});

UUID

t.uuid() has no default. The text is 8-4-4-4-12 hexadecimal digits, 128 bits. t.id() is a primary key. A bare t.id() is uuid, 16 bytes. The default is covered in keys. An OKID default is text, not this row.

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.id(), 
  authorId: t.uuid(), 
});

OKID

okid comes from okmodel/ids. It is not a Postgres type. t.id({ default: okid() }) is a primary key of text with collation C. The client mints one id on insert. The catalog stores no default. Calling okid() returns the generator. It does not mint the id at definition time.

A default body is 21 characters on a URL-safe alphabet, so the stored value is 22 bytes. A longer body, or a prefix, follows text: 1 byte plus the string up to 126 bytes, then 4 bytes plus the string.

length is the body. The prefix is extra. sortable keeps time order under collation C. lowercase, uppercase, numbers, and symbols default to included. lookAlikes: false drops characters that are easy to confuse.

schema({ defaults: { id: okid() } }) applies to every bare t.id(). A column option wins. See id default.

import { okid } from "okmodel/ids";
import { table, t } from "okmodel/pg";

export const users = table("users", {
  id: t.id({ default: okid({ prefix: "usr_", sortable: true }) }), 
  email: t.text(),
});

Ranges

A range is empty, or a pair of bounds. A null bound is unbounded. lowerInclusive and upperInclusive choose [ or (.

There is no tsrange builder. An instant range is t.tstzrange().

import { table, t } from "okmodel/pg";

export const plans = table("plans", {
  id: t.identity(),
  scores: t.int4range(), 
  active: t.tstzrange(), 
});

A bound is { empty: false, lower, upper, lowerInclusive, upperInclusive }. { empty: true } is the empty range.

Network

Values are canonical text.

7 bytes is IPv4. 19 bytes is IPv6.

An IPv4 prefix above 32, or any prefix above 128, is refused. t.cidr() with no prefix is refused.

import { table, t } from "okmodel/pg";

export const hosts = table("hosts", {
  id: t.identity(),
  address: t.inet(), 
  block: t.cidr(), 
  mac: t.macaddr(), 
});

Geometry

Built-in plane geometry. A point is { x, y }. A line is { a, b, c } for ax + by + c = 0.

box, circle, lseg, path, and polygon have no builder. PostGIS is not these types.

import { table, t } from "okmodel/pg";

export const places = table("places", {
  id: t.identity(),
  location: t.point(), 
});

Search

t.tsvector() stores the vector text. A generated vector is .generated(). t.ltree() is labels separated by dots, such as top.science. Each label is letters, digits, or underscores. The schema lists extension("ltree") from okmodel/ext. See extensions. There is no tsquery builder.

import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  search: t.tsvector(), 
  path: t.ltree(), 
});

export default schema({
  tables: [notes],
  extensions: [extension("ltree")], 
});

Named types

An enum is its own type. A picklist stays a string column and adds a CHECK.

t.domain("email", t.text(), "VALUE LIKE '%@%'") stores the check as written. The name and the check must be non-empty. An enum cannot be the base. Changing the base type of an existing domain is OKM1020.

t.custom() stores a SQL type that has no builder. sqlType must be non-empty. accepts names the object kinds the codec takes, such as "Object". Omit accepts for a scalar. extension records an extension dependency. sqlForm defaults to "quote".

import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  email: t.domain("email", t.text(), "VALUE LIKE '%@%'"), 
  label: t.custom({
    sqlType: "text",
    encode: (value: string) => value,
    decode: (wire: string) => wire,
  }), 
});

Other Postgres types

Postgres has these types. This dialect has no builder under that name. The Postgres cell stays yes, because the database has the type. The call is what you write instead.