# Columns

<Callout type="info" title="Where this fits">
The [key](/docs/define/table/keys) is one column. Each other field is a builder on this page. Use the table first, then the section for that storage. [Defaults](/docs/define/table/defaults) come after the type.
</Callout>

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](/docs/define/schema/extensions).

An array is [`.array()`](/docs/define/table/shape#array) on a scalar. A fixed set of strings on a text column is a [picklist](/docs/define/table/shape#picklist). A named Postgres enum is its own type, under [enum](/docs/define/table/shape#enum).

| You store | Builder |
| --- | --- |
| a string with no length cap | `t.text()` |
| a string with a maximum length | `t.varchar(n)` |
| a string compared without case | `t.citext()` |
| a tight integer | `t.smallint()` |
| a typical count | `t.integer()` |
| a 64-bit integer | `t.bigint()` |
| a generated integer key | `t.identity()` |
| money, a rate, or any exact quantity | `t.numeric(precision, scale)` |
| a measurement that may round | `t.double()` |
| an instant | `t.timestamptz()` |
| a calendar day | `t.date()` |
| a span | `t.interval()` |
| true or false | `t.boolean()` |
| bytes | `t.bytea()` |
| a document you query | `t.jsonb()` |
| a uuid that is not the primary key | `t.uuid()` |
| a primary key the application mints | `t.id({ default: okid() })` |
| a closed set of labels | `t.enum(name, values)` |

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

```ts title="unbounded.ts"
import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  name: t.text(), // [!code focus]
});
```

#### Varchar

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

```ts title="varchar.ts"
import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  code: t.varchar(16), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
A length outside 1 to 10485760 is refused.
</Callout>

<Callout type="error" title="OKM1210">
A longer value is refused when it is encoded.
</Callout>

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

```ts title="char.ts"
import { table, t } from "okmodel/pg";

export const people = table("people", {
  id: t.identity(),
  region: t.char(2), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
A length outside 1 to 10485760 is refused.
</Callout>

<Callout type="error" title="OKM1210">
A longer value is refused when it is encoded.
</Callout>

#### Citext

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

```ts title="citext.ts"
import { citext } from "okmodel/pg/citext";
import { schema, table, t } from "okmodel/pg";

const people = table("people", {
  id: t.identity(),
  email: t.citext(), // [!code focus]
});

export default schema({
  tables: [people],
  extensions: [citext()], // [!code focus]
});
```

<Callout type="error" title="OKM1020">
`t.citext()` is refused when `extensions` is omitted or empty.
</Callout>

<Callout type="error" title="OKM1810">
`t.citext()` is refused when `extensions` is a list that does not contain `citext`.
</Callout>

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

```ts title="smallint.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  flag: t.smallint(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
A value that is not an integer in that range is refused when it is encoded.
</Callout>

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

```ts title="integer.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  total: t.integer(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
A value that is not an integer in that range is refused when it is encoded.
</Callout>

#### 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](/docs/define/schema/codecs) other than `"string"` needs the same `as` on this column. A [safe integer](#safe-integer) or a [JavaScript bigint](#javascript-bigint) selects the other modes.

```ts title="bigint.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
A string that is not a canonical integer is refused when it is encoded. The spelling has no leading zeros, for example `0` or `-12`.
</Callout>

#### Safe integer

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

```ts title="safe-integer.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint({ as: "number" }), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
A value outside the safe integer range is refused when it is encoded.
</Callout>

#### JavaScript bigint

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

```ts title="javascript-bigint.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(),
  sequence: t.bigint({ as: "bigint" }), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
Any other `as` on `t.bigint()` or `t.identity()` is refused.
</Callout>

#### 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](#bigint). A generated integer key uses this builder.

```ts title="identity.ts"
import { table, t } from "okmodel/pg";

export const counts = table("counts", {
  id: t.identity(), // [!code focus]
});
```

### 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](/docs/define/schema/codecs) 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.

```ts title="unconstrained.ts"
import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(), // [!code focus]
});
```

#### Precision

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

```ts title="precision.ts"
import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
A precision outside 1 to 1000 is refused.
</Callout>

#### Scale

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

```ts title="scale.ts"
import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12, 2), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
A scale outside 0 to the precision is refused.
</Callout>

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

```ts title="number.ts"
import { table, t } from "okmodel/pg";

export const payments = table("payments", {
  id: t.identity(),
  amount: t.numeric(12, 2, { as: "number" }), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
`as` must be `"string"` or `"number"`.
</Callout>

### Floats

`t.real()` and `t.double()` are binary floating point. A sum of them is not exact. An exact quantity uses [numeric](#numbers). 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.

```ts title="real.ts"
import { table, t } from "okmodel/pg";

export const samples = table("samples", {
  id: t.identity(),
  reading: t.real(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
Infinity and NaN are refused when the value is encoded.
</Callout>

#### Double

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

```ts title="double.ts"
import { table, t } from "okmodel/pg";

export const samples = table("samples", {
  id: t.identity(),
  reading: t.double(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
Infinity and NaN are refused when the value is encoded.
</Callout>

### Time

The value is a `Temporal` object. The schema [timestamps codec](/docs/define/schema/codecs) is `"temporal"`. [Fractional seconds](#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.

```ts title="polyfill.ts"
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.

```ts title="instant.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  publishedAt: t.timestamptz(), // [!code focus]
});
```

#### Timestamp

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

```ts title="timestamp.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  editedAt: t.timestamp(), // [!code focus]
});
```

#### Date

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

```ts title="date.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  day: t.date(), // [!code focus]
});
```

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

```ts title="time-of-day.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  opens: t.time(), // [!code focus]
});
```

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

```ts title="offset.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  shift: t.timetz(), // [!code focus]
});
```

<Callout type="error" title="OKM1210">
An offset other than `±HH:MM` is refused when the value is encoded.
</Callout>

#### 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`.

```ts title="interval.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  length: t.interval("day to second"), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
Any other `fields` value is refused.
</Callout>

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

```ts title="fractional-seconds.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.identity(),
  editedAt: t.timestamp(0), // [!code focus]
});
```

<Callout type="error" title="OKM1060">
A precision outside 0 to 6 is refused.
</Callout>

### Boolean and binary

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.boolean()` | `boolean` | `boolean` | 1 | yes |
| `t.bytea()` | `bytea` | `Uint8Array` | 1 or 4 + bytes | yes |

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

```ts title="files.ts"
import { table, t } from "okmodel/pg";

export const files = table("files", {
  id: t.identity(),
  published: t.boolean().default(false), // [!code focus]
  body: t.bytea(), // [!code focus]
});
```

### JSON

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.json()` | `json` | the `T` you name, or `unknown` | 1 or 4 + text | yes |
| `t.jsonb()` | `jsonb` | the `T` you name, or `unknown` | variable | yes |

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

```ts title="notes.ts"
import { table, t } from "okmodel/pg";

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

### UUID

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.uuid()` | `uuid` | `string` | 16 | yes |
| `t.id()` | `uuid` | `string` | 16 | yes |

`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](/docs/define/table/keys). An [OKID](#okid) default is `text`, not this row.

```ts title="notes.ts"
import { table, t } from "okmodel/pg";

export const notes = table("notes", {
  id: t.id(), // [!code focus]
  authorId: t.uuid(), // [!code focus]
});
```

### 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](#text): 1 byte plus the string up to 126 bytes, then 4 bytes plus the string.

| Call | Body | Bytes |
| --- | --- | --- |
| `okid()` | 21 characters | 22 |
| `okid(length)` | `length` characters, from 8 to 128 | 1 + the string |
| `okid({ sortable: true })` | 8 time characters, then random. Length is at least 16. | 1 + the string |
| `okid({ prefix })` | The prefix, then the body. The prefix is at most 32 characters. | 1 + 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](/docs/define/schema/defaults).

```ts title="users.ts"
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 }) }), // [!code focus]
  email: t.text(),
});
```

### Ranges

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

| Builder | SQL | Bound | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.int4range()` | `int4range` | `number`, the integer range | variable | yes |
| `t.int8range()` | `int8range` | `string`, a bigint | variable | yes |
| `t.numrange()` | `numrange` | `string`, a numeric | variable | yes |
| `t.daterange()` | `daterange` | `Temporal.PlainDate` | variable | yes |
| `t.tstzrange()` | `tstzrange` | `Temporal.Instant` | variable | yes |

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

```ts title="plans.ts"
import { table, t } from "okmodel/pg";

export const plans = table("plans", {
  id: t.identity(),
  scores: t.int4range(), // [!code focus]
  active: t.tstzrange(), // [!code focus]
});
```

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

### Network

Values are canonical text.

| Builder | SQL | Value | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.inet()` | `inet` | An IPv4 or IPv6 host. A prefix is optional. | 7 or 19 | yes |
| `t.cidr()` | `cidr` | An IPv4 or IPv6 network. A prefix is required. | 7 or 19 | yes |
| `t.macaddr()` | `macaddr` | Six lowercase groups: `aa:bb:cc:dd:ee:ff`. | 6 | yes |
| `t.macaddr8()` | `macaddr8` | Eight lowercase groups. | 8 | yes |

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.

```ts title="hosts.ts"
import { table, t } from "okmodel/pg";

export const hosts = table("hosts", {
  id: t.identity(),
  address: t.inet(), // [!code focus]
  block: t.cidr(), // [!code focus]
  mac: t.macaddr(), // [!code focus]
});
```

### Geometry

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

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.point()` | `point` | `{ x: number, y: number }` | 16 | yes |
| `t.line()` | `line` | `{ a: number, b: number, c: number }` | 24 | yes |

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

```ts title="places.ts"
import { table, t } from "okmodel/pg";

export const places = table("places", {
  id: t.identity(),
  location: t.point(), // [!code focus]
});
```

### Search

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.tsvector()` | `tsvector` | `string` | variable | yes |
| `t.ltree()` | `ltree` | `string` | variable | extension |

`t.tsvector()` stores the vector text. A generated vector is [`.generated()`](/docs/define/table/defaults). `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](/docs/define/schema/extensions). There is no `tsquery` builder.

```ts title="notes.ts"
import { extension } from "okmodel/ext";
import { schema, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  search: t.tsvector(), // [!code focus]
  path: t.ltree(), // [!code focus]
});

export default schema({
  tables: [notes],
  extensions: [extension("ltree")], // [!code focus]
});
```

<Callout type="error" title="OKM1020">
`t.ltree()` with no `ltree` extension on the schema is refused when the catalog is built.
</Callout>

### Named types

| Builder | SQL | TypeScript | Bytes | Postgres |
| --- | --- | --- | --- | --- |
| `t.enum(name, values)` | the enum name | the union of the labels | 4 | yes |
| `t.domain(name, base, check)` | the domain name | the base column's type | the base type | yes |
| `t.custom({ sqlType, encode, decode })` | `sqlType` | the type `encode` accepts | the SQL type | yes |

An [enum](/docs/define/table/shape#enum) is its own type. A [picklist](/docs/define/table/shape#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"`.

```ts title="notes.ts"
import { table, t } from "okmodel/pg";

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

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

| Postgres type | Bytes | Call | Postgres |
| --- | --- | --- | --- |
| `smallserial`, `serial`, `bigserial` | 2, 4, 8 | `t.identity()` | yes |
| `money` | 8 | `t.numeric(precision, scale)` | yes |
| `decimal` | variable | `t.numeric()` | yes |
| `tsrange` | variable | `t.tstzrange()` | yes |
| `tsquery` | variable | `t.custom()` with `sqlType: "tsquery"` | yes |
| `bit`, `bit varying` | 1 byte per 8 bits, plus 5 or 8 | `t.custom()` with that SQL type | yes |
| `xml`, `hstore` | variable | `t.custom()` with that SQL type | yes |
| `box`, `lseg` | 32 | `t.custom()` with that SQL type | yes |
| `circle` | 24 | `t.custom()` with that SQL type | yes |
| `path` | 16 + 16 per point | `t.custom()` with that SQL type | yes |
| `polygon` | 40 + 16 per point | `t.custom()` with that SQL type | yes |
| `oid` | 4 | `t.custom()` with that SQL type | yes |
| `pg_lsn` | 8 | `t.custom()` with that SQL type | yes |
| multirange | variable | `t.custom()` with that SQL type | yes |