PostgreSQL column types

We have native support for all of them, yet if that’s not enough for you, feel free to create custom types.

integer

integer int int4
Signed 4-byte integer

If you need integer autoincrement please refer to serial.

import { integer, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  int: integer('int')
});

CREATE TABLE IF NOT EXISTS "table" (
  "int" integer
);

import { sql } from "drizzle-orm";
import { integer, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  int1: integer('int1').default(10)
  int2: integer('int2').default(sql`'10'::int`)
});

CREATE TABLE IF NOT EXISTS "table" (
  "int1" integer DEFAULT 10
  "int2" integer DEFAULT '10'::int
);

smallint

smallint int2
Small-range signed 2-byte integer

If you need smallint autoincrement please refer to smallserial.

import { smallint, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  smallint: smallint('smallint')
});

CREATE TABLE IF NOT EXISTS "table" (
  "smallint" smallint
);

import { sql } from "drizzle-orm";
import { smallint, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  smallint1: smallint('smallint1').default(10)
  smallint2: smallint('smallint2').default(sql`'10'::smallint`)
});

CREATE TABLE IF NOT EXISTS "table" (
  "smallint1" smallint DEFAULT 10
  "smallint2" smallint DEFAULT '10'::smallint
);

bigint

bigint int8
Signed 8-byte integer

If you need bigint autoincrement please refer to bigserial.

If you’re expecting values above 2^31 but below 2^53, you can utilise mode: 'number' and deal with javascript number as opposed to bigint.

import { bigint, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  bigint: bigint('bigint', { mode: 'number' })
});

// will be inferred as `number`
bigint: bigint('bigint', { mode: 'number' })

// will be inferred as `bigint`
bigint: bigint('bigint', { mode: 'bigint' })

CREATE TABLE IF NOT EXISTS "table" (
  "bigint" bigint
);

import { sql } from "drizzle-orm";
import { bigint, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  bigint1: bigint('bigint1').default(10)
  bigint2: bigint('bigint2').default(sql`'10'::bigint`)
});

CREATE TABLE IF NOT EXISTS "table" (
  "bigint1" bigint DEFAULT 10
  "bigint2" bigint DEFAULT '10'::bigint
);

serial

serial serial4
Auto incrementing 4-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

For more info please refer to the official PostgreSQL docs.

import { serial, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  serial: serial('serial'),
});

CREATE TABLE IF NOT EXISTS "table" (
  "serial" serial NOT NULL,
);

smallserial

smallserial serial2
Auto incrementing 2-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

For more info please refer to the official PostgreSQL docs.

import { smallserial, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  smallserial: smallserial('smallserial'),
});

CREATE TABLE IF NOT EXISTS "table" (
  "smallserial" smallserial NOT NULL,
);

bigserial

bigserial serial8
Auto incrementing 8-bytes integer, notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

For more info please refer to the official PostgreSQL docs.

If you’re expecting values above 2^31 but below 2^53, you can utilise mode: 'number' and deal with javascript number as opposed to bigint.

import { bigserial, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  bigserial: bigserial('bigserial', { mode: 'number' }),
});

CREATE TABLE IF NOT EXISTS "table" (
  "bigserial" bigserial NOT NULL,
);

boolean

PostgreSQL provides the standard SQL type boolean.

For more info please refer to the official PostgreSQL docs.

import { boolean, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  boolean: boolean('boolean')
});

CREATE TABLE IF NOT EXISTS "table" (
  "boolean" boolean,
);

text

text
Variable-length(unlimited) character string.

For more info please refer to the official PostgreSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

import { text, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  text: text('text')
});

// will be inferred as text: "value1" | "value2" | null
text: text('text', { enum: ["value1", "value2"] })

CREATE TABLE IF NOT EXISTS "table" (
  "text" text,
);

varchar

character varying(n) varchar(n)
Variable-length character string, can store strings up to n characters (not bytes).

For more info please refer to the official PostgreSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to PostgreSQL docs.

import { varchar, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  varchar1: varchar('varchar1'),
  varchar1: varchar('varchar2', { length: 256 }),
});

// will be inferred as text: "value1" | "value2" | null
varchar: varchar('varchar', { enum: ["value1", "value2"] }),

CREATE TABLE IF NOT EXISTS "table" (
  "varchar1" varchar,
  "varchar2" varchar(256),
);

char

character(n) char(n)
Fixed-length, blank padded character string, can store strings up to n characters(not bytes).

For more info please refer to the official PostgreSQL docs.

You can define { enum: ["value1", "value2"] } config to infer insert and select types, it won’t check runtime values.

The length parameter is optional according to PostgreSQL docs.

import { char, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  char1: char('char1'),
  char2: char('char2', { length: 256 }),
});

// will be inferred as text: "value1" | "value2" | null
char: char('char', { enum: ["value1", "value2"] }),

CREATE TABLE IF NOT EXISTS "table" (
  "char1" char,
  "char2" char(256),
);

numeric

numeric decimal
Exact numeric of selectable precision. Can store numbers with a very large number of digits, up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

For more info please refer to the official PostgreSQL docs.

import { numeric, pgTable } from "drizzle-orm/pg-core";

export const table = pgTable('table', {
  numeric1: numeric('numeric1'),
  numeric2: numeric('numeric2', { precision: 100 }),
  numeric3: numeric('numeric3', { precision: 100, scale: 20 }),
});

CREATE TABLE IF NOT EXISTS "table" (
  "numeric1" numeric,
  "numeric2" numeric(100),
  "numeric3" numeric(100, 20),
);

decimal

An alias of numeric.

real

real float4
Single precision floating-point number (4 bytes)

For more info please refer to the official PostgreSQL docs.

import { sql } from "drizzle-orm";
import { real, pgTable } from "drizzle-orm/pg-core";  

const table = pgTable('table', {
  real1: real('real1'),
  real2: real('real2').default(10.10),
  real2: real('real2').default(sql`'10.10'::real`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "real1" real,
  "real2" real default 10.10,
  "real2" real default '10.10'::real
);

double precision

double precision float8
Double precision floating-point number (8 bytes)

For more info please refer to the official PostgreSQL docs.

import { sql } from "drizzle-orm";
import { doublePrecision, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  double1: doublePrecision('double1'),
  double2: doublePrecision('double2').default(10.10),
  double3: doublePrecision('double3').default(sql`'10.10'::double precision`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "double1" double precision,
  "double2" double precision default 10.10,
  "double3" double precision default '10.10'::double precision,
);

json

json
Textual JSON data, as specified in RFC 7159.

For more info please refer to the official PostgreSQL docs.

import { sql } from "drizzle-orm";
import { json, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  json1: json('json1'),
  json2: json('json2').default({ foo: "bar" }),
  json3: json('json3').default(sql`'{foo: "bar"}'::json`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "json1" json,
  "json2" json default '{"foo": "bar"}'::json,
  "json3" json default '{"foo": "bar"}'::json,
);

You can specify .$type<..>() for json object inference, it won’t check runtime values. It provides compile time protection for default values, insert and select schemas.

// will be infered as { foo: string }
json: json('json').$type<{ foo: string }>();

// will be infered as string[]
json: json('json').$type<string[]>();

// won't compile
json: json('json').$type<string[]>().default({});

jsonb

jsonb
Binary JSON data, decomposed.

For more info please refer to the official PostgreSQL docs.

import { jsonb, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  jsonb1: jsonb('jsonb1'),
  jsonb2: jsonb('jsonb2').default({ foo: "bar" }),
  jsonb3: jsonb('jsonb3').default(sql`'{foo: "bar"}'::jsonb`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "jsonb1" jsonb,
  "jsonb2" jsonb default '{"foo": "bar"}'::jsonb,
  "jsonb3" jsonb default '{"foo": "bar"}'::jsonb,
);

You can specify .$type<..>() for json object inference, it won’t check runtime values. It provides compile time protection for default values, insert and select schemas.

// will be infered as { foo: string }
jsonb: jsonb('jsonb').$type<{ foo: string }>();

// will be infered as string[]
jsonb: jsonb('jsonb').$type<string[]>();

// won't compile
jsonb: jsonb('jsonb').$type<string[]>().default({});

time

time timetz time with timezone time without timezone
Time of day with or without time zone.

For more info please refer to the official PostgreSQL docs.

import { time, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  time1: time('time1'),
  time2: time('time2', { withTimezone: true }),
  time3: time('time3', { precision: 6 }),
  time4: time('time4', { precision: 6, withTimezone: true })
});

CREATE TABLE IF NOT EXISTS "table" (
  "time1" time,
  "time2" time with timezone,
  "time3" time(6),
  "time4" time(6) with timezone,
);

timestamp

timestamp timestamptz timestamp with time zone timestamp without time zone
Date and time with or without time zone.

For more info please refer to the official PostgreSQL docs.

import { sql } from "drizzle-orm";
import { timestamp, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  timestamp1: timestamp('timestamp1'),
  timestamp2: timestamp('timestamp2', { precision: 6, withTimezone: true }),
  timestamp3: timestamp('timestamp3').defaultNow(),
  timestamp4: timestamp('timestamp4').default(sql`now()`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "timestamp1" timestamp,
  "timestamp2" timestamp (6) with time zone,
  "timestamp3" timestamp default now(),
  "timestamp4" timestamp default now(),
);

You can specify either date or string infer modes:

// will infer as date
timestamp: timestamp('timestamp', { mode: "date" }),

// will infer as string
timestamp: timestamp('timestamp', { mode: "string" }),

The string mode does not perform any mappings for you. This mode was added to Drizzle ORM to provide developers with the possibility to handle dates and date mappings themselves, depending on their needs. Drizzle will pass raw dates as strings to and from the database, so the behavior should be as predictable as possible and aligned 100% with the database behavior

The date mode is the regular way to work with dates. Drizzle will take care of all mappings between the database and the JS Date object

show timezone;

date

date
Calendar date (year, month, day)

For more info please refer to the official PostgreSQL docs.

import { date, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  date: date('date'),
});

CREATE TABLE IF NOT EXISTS "table" (
  "date" date,
);

You can specify either date or string infer modes:

// will infer as date
date: date('date', { mode: "date" }),

// will infer as string
date: date('date', { mode: "string" }),

interval

interval
Time span

For more info please refer to the official PostgreSQL docs.

import { interval, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  interval1: interval('interval1'),
  interval2: interval('interval2', { fields: 'day' }),
  interval3: interval('interval3', { fields: 'month' , precision: 6 }),
});

CREATE TABLE IF NOT EXISTS "table" (
  "interval1" interval,
  "interval2" interval day,
  "interval3" interval(6) month,
);

point

point
Geometric point type

For more info please refer to the official PostgreSQL docs.

Type point has 2 modes for mappings from the database: tuple and xy.

• tuple will be accepted for insert and mapped on select to a tuple. So, the database Point(1,2) will be typed as [1,2] with drizzle.

• xy will be accepted for insert and mapped on select to an object with x, y coordinates. So, the database Point(1,2) will be typed as { x: 1, y: 2 } with drizzle

const items = pgTable('items', {
 point: point('point'),
 pointObj: point('point_xy', { mode: 'xy' }),
});

CREATE TABLE IF NOT EXISTS "items" (
  "point" point,
  "point_xy" point,
);

line

line
Geometric line type

For more info please refer to the official PostgreSQL docs.

Type line has 2 modes for mappings from the database: tuple and abc.

• tuple will be accepted for insert and mapped on select to a tuple. So, the database Line3 will be typed as [1,2,3] with drizzle.

• abc will be accepted for insert and mapped on select to an object with a, b, and c constants from the equation Ax + By + C = 0. So, the database Line3 will be typed as { a: 1, b: 2, c: 3 } with drizzle.

const items = pgTable('items', {
 line: line('line'),
 lineObj: point('line_abc', { mode: 'abc' }),
});

CREATE TABLE IF NOT EXISTS "items" (
  "line" line,
  "line_abc" line,
);

enum

enum enumerated types
Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.

For more info please refer to the official PostgreSQL docs.

import { pgEnum, pgTable } from "drizzle-orm/pg-core";

export const moodEnum = pgEnum('mood', ['sad', 'ok', 'happy']);

export const table = pgTable('table', {
  mood: moodEnum('mood'),
});

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

CREATE TABLE IF NOT EXISTS "table" (
  "mood" mood,
);

Customizing column data type

Every column builder has a .$type() method, which allows you to customize the data type of the column.

This is useful, for example, with unknown or branded types:

type UserId = number & { __brand: 'user_id' };
type Data = {
  foo: string;
  bar: number;
};

const users = pgTable('users', {
  id: serial('id').$type<UserId>().primaryKey(),
  jsonField: json('json_field').$type<Data>(),
});

Constraints & defaults

Identity Columns

PostgreSQL supports identity columns as a way to automatically generate unique integer values for a column. These values are generated using sequences and can be defined using the GENERATED AS IDENTITY clause.

Types of Identity Columns

• GENERATED ALWAYS AS IDENTITY: The database always generates a value for the column. Manual insertion or updates to this column are not allowed unless the OVERRIDING SYSTEM VALUE clause is used.
• GENERATED BY DEFAULT AS IDENTITY: The database generates a value by default, but manual values can also be inserted or updated. If a manual value is provided, it will be used instead of the system-generated value.

Key Features

• Automatic Value Generation: Utilizes sequences to generate unique values for each new row.
• Customizable Sequence Options: You can define starting values, increments, and other sequence options.
• Support for Multiple Identity Columns: PostgreSQL allows more than one identity column per table.

Limitations

• Manual Insertion Restrictions: For columns defined with GENERATED ALWAYS AS IDENTITY, manual insertion or updates require the OVERRIDING SYSTEM VALUE clause.
• Sequence Constraints: Identity columns depend on sequences, which must be managed correctly to avoid conflicts or gaps.

Usage example

import { pgTable, integer, text } from 'drizzle-orm/pg-core' 

export const ingredients = pgTable("ingredients", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity({ startWith: 1000 }),
  name: text("name").notNull(),
  description: text("description"),
});

You can specify all properties available for sequences in the .generatedAlwaysAsIdentity() function. Additionally, you can specify custom names for these sequences

PostgreSQL docs reference.

Default value

The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL.

An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses.

import { sql } from "drizzle-orm";
import { integer, pgTable, uuid } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  integer1: integer('integer1').default(42),
  integer2: integer('integer2').default(sql`'42'::integer`),
  uuid1: uuid('uuid1').defaultRandom(),
  uuid2: uuid('uuid2').default(sql`gen_random_uuid()`),
});

CREATE TABLE IF NOT EXISTS "table" (
  "integer1" integer DEFAULT 42,
  "integer2" integer DEFAULT '42'::integer,
  "uuid1" uuid DEFAULT gen_random_uuid(),
  "uuid2" uuid DEFAULT gen_random_uuid()
);

When using $default() or $defaultFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all insert queries.

These functions can assist you in utilizing various implementations such as uuid, cuid, cuid2, and many more.

import { text, pgTable } from "drizzle-orm/pg-core";
import { createId } from '@paralleldrive/cuid2';

const table = pgTable('table', {
  id: text('id').$defaultFn(() => createId()),
});

When using $onUpdate() or $onUpdateFn(), which are simply different aliases for the same function, you can generate defaults at runtime and use these values in all update queries.

Adds a dynamic update value to the column. The function will be called when the row is updated, and the returned value will be used as the column value if none is provided. If no default (or $defaultFn) value is provided, the function will be called when the row is inserted as well, and the returned value will be used as the column value.

import { integer, timestamp, text, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  updateCounter: integer('update_counter').default(sql`1`).$onUpdateFn((): SQL => sql`${table.update_counter} + 1`),
    updatedAt: timestamp('updated_at', { mode: 'date', precision: 3 }).$onUpdate(() => new Date()),
    alwaysNull: text('always_null').$type<string | null>().$onUpdate(() => null),
});

Not null

NOT NULL constraint dictates that the associated column may not contain a NULL value.

import { integer, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  integer: integer('integer').notNull(),
});

CREATE TABLE IF NOT EXISTS "table" (
  "integer" integer NOT NULL,
);

Primary key

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null.

import { integer, pgTable } from "drizzle-orm/pg-core";

const table = pgTable('table', {
  id: serial('id').primaryKey(),
});

CREATE TABLE IF NOT EXISTS "table" (
  "integer" serial PRIMARY KEY NOT NULL,
);