Postgres typesCopy page

These types are specific to Postgres.

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. An enum or domain name with a capital letter, a space, or a quote is written as one quoted identifier wherever the type is used, so it never runs as SQL.

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.

Next: Columns.