---
title: "Postgres"
description: "The Postgres dialect, its column types and functions."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Postgres

The same builder writes Postgres SQL. Queries, params, tenant scoping and row decoding work
as they do for ClickHouse. `@maple-dev/effect-orm/postgres` is the one import for it: the
builder, column types whose codecs read what Postgres drivers send, functions spelled the
Postgres way, `table` with its keys, indexes, unique constraints and foreign keys, `enumType`,
and a `compile` that defaults to
the Postgres dialect. Nothing from `/clickhouse` is needed.

```ts title="postgres-quickstart.ts"
import { PGlite } from "@electric-sql/pglite"
import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

const Requests = PG.table("requests", {
	columns: {
		Id: PG.column(PG.int8, { identity: "always" }),
		OrgId: PG.text,
		Route: PG.text,
		DurationMs: PG.int8,
		At: PG.timestamptz,
	},
	primaryKey: ["Id"],
	tenantColumn: "OrgId",
})

const query = PG.from(Requests)
	.select(($) => ({
		route: $.Route,
		count: PG.count(),
		slow: PG.countIf($.DurationMs.gte(500)),
		p50: PG.percentileCont(0.5, $.DurationMs),
	}))
	.where(($) => [$.OrgId.eq(PG.param.string("orgId")), $.At.gte(PG.param.dateTime("since"))])
	.groupBy("route")
	.orderBy(["count", "desc"])

export const compiled = PG.compileUnsafe(query, { orgId: "org_1", since: new Date("2026-01-01T00:00:00Z") })
// compiled.sql:        ... FILTER (WHERE "requests"."DurationMs" >= $1) ...
//                      ... WHERE "requests"."OrgId" = $2 AND "requests"."At" >= $3 ...
// compiled.parameters: [500, "org_1", "2026-01-01T00:00:00.000Z"]

const db = new PGlite()
// The CREATE TABLE comes from the definition itself; migrations.md shows the managed way.
for (const statement of S.renderPgSchema(Effect.runSync(S.pgEntitiesOf([Requests])))) await db.exec(statement)
await db.exec(`
	INSERT INTO requests ("OrgId", "Route", "DurationMs", "At") VALUES
		('org_1', '/checkout', 120, '2026-01-01T10:00:00Z'),
		('org_1', '/checkout', 900, '2026-01-01T10:01:00Z'),
		('org_1', '/search', 40, '2026-01-01T10:02:00Z');
`)
const result = await db.query<Record<string, unknown>>(compiled.sql, [...compiled.parameters])
export const rows = await Effect.runPromise(compiled.decodeRows(result.rows))
// [{ route: "/checkout", count: 2, slow: 1, p50: 510 }, { route: "/search", count: 1, slow: 0, p50: 40 }]
await db.close()
```

`PGlite` stands in for any driver that takes `(sql, values)`: node-postgres, postgres.js, or
`@effect/sql-pg`'s `unsafe`. The builder never runs the query. `Id` is an identity column, so an
insert may leave it out: `PG.InsertRowOf<typeof Requests>` makes it optional. See
[Tables and column types](https://effect-orm.maple.dev/postgres/queries/tables-and-types/) for column options and external tables, and
[Schema and migrations](https://effect-orm.maple.dev/postgres/database/migrations/#postgres) for indexes, unique constraints, foreign keys,
and `generate`; [Enums](#enums) for enum types.

## What the dialect changes

| | ClickHouse (`clickhouseDialect`) | Postgres (`postgresDialect`) |
| --- | --- | --- |
| Identifiers | Bare: `events.OrgId` | Quoted: `"events"."OrgId"` |
| String literals | Backslash escapes: `'it\'s'` | Doubled quotes: `'it''s'` |
| Params | Written in as literals; `parameters` empty | Bound as `$1`, `$2`, … in `parameters` |
| Values compared with a column, `LIKE` patterns | Written in as literals | Bound too, one param per distinct value |
| `param.bool` | `1` / `0` | `true` / `false` |
| `param.dateTime` | `'2026-01-01 00:00:00'` (UTC, zoneless) | `'2026-01-01T00:00:00.000Z'` |
| `GROUP BY` keys | Select aliases | Select-list positions (`GROUP BY 1`) |
| Wrapped union | `SELECT * FROM (…)` | `SELECT * FROM (…) AS "__union"` |
| `.format()` | `FORMAT JSON` | Refused at compile time |

Postgres reads a bare name in `GROUP BY` as an input column before a select alias, so
`select({ Service: lower($.Service) }).groupBy("Service")` would group by the raw column. Writing
the position instead keeps ClickHouse's meaning.

A value compared with a column (`$.email.eq(email)`, `in_`, `between`, a `LIKE` pattern) is
bound like a param, so the statement text carries no values: it stays out of logs and traces, and
one query shape is one statement. Two exceptions stay literals: an `onConflict*` `targetWhere`,
which Postgres matches against a partial index's predicate as written, and DDL.

Every string that reaches the SQL as a literal is escaped for Postgres. A value that spells the
param marker `__PARAM_` is written as an `E'…'` string with the marker hex-escaped, and a
literal that still contained it would fail the compile with `InvalidLiteral`.

## Column types

| Constructor | Postgres type | Decodes to | Accepts on the wire |
| --- | --- | --- | --- |
| `text`, `uuid` | `text`, `uuid` | `string` | string |
| `bool` | `boolean` | `boolean` | boolean |
| `int2`, `int4`, `int8`, `float4`, `float8`, `numeric` | same | `number` | number, numeric string, `bigint` |
| `timestamptz` | `timestamptz` | `DateTime.Utc` | `Date`, or text such as `2026-01-01 00:00:00+00` |
| `timestamptzMillis` | `timestamptz` | epoch milliseconds (`number`) | the same |
| `jsonb(schema?)` | `jsonb` | the schema's type (`unknown` by default) | a parsed value |
| `enumType(name, labels, { schema? })` | the enum type | one of its labels | string |
| `array(type)` | `type[]` | `ReadonlyArray` | array |
| `nullable(type)` | the same type | `T \| null` | the same, or `null` |
| `custom(sql, schema, literalSchema?)` | anything | the schema's type | whatever the schema reads |
| `brand(type, schema)` | the base type | the schema's type | what the base type reads |

`int8` and `numeric` decode to `number`, so values beyond 2^53 or a double's precision lose
digits. Where exact digits matter, declare
`PG.custom("int8", Schema.Union([Schema.BigInt, Schema.BigIntFromString]))`: drivers send int8 as a
`bigint` (PGlite, postgres.js with `types.bigint`) or as a string (node-postgres), and this reads
both as a `bigint`. A `timestamptz`
compared against a `Date`, a `DateTime.Utc` or a string is written as an ISO-8601 instant,
which no session time zone can reinterpret; a zoneless string is read as UTC.

Comparisons with a literal-union column take only its members: with `status` typed
`"open" | "closed"`, `$.status.eq("opne")` is a type error. A param of the primitive
(`param.string`) still compares, for a value known only at run time.

## Enums

`PG.enumType(name, labels)` declares a Postgres enum type. The value is a column type, so rows
decode to the union of its labels and inserts and comparisons take only those, and it is a
schema object, so `generate` writes `CREATE TYPE ... AS ENUM` before any table that uses it and
alters the type when its labels change. Use it bare, in `PG.column` with a `default`, or inside
`PG.nullable` and `PG.array`:

```ts title="postgres-enums.ts"
import { Effect } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"
import * as S from "@maple-dev/effect-orm/schema"

export const ChannelType = PG.enumType("channel_type", ["public", "private", "direct"])

export const Channels = PG.table("channels", {
	columns: {
		id: PG.text,
		org_id: PG.text,
		slug: PG.column(PG.text, { unique: true }),
		type: PG.column(ChannelType, { default: "public" }),
		allowed: PG.nullable(PG.array(ChannelType)),
	},
	primaryKey: ["id"],
	uniqueConstraints: [PG.unique("channels_org_slug_unique", ["org_id", "slug"])],
})

export type Channel = PG.SelectRowOf<typeof Channels>
// { ...; readonly type: "public" | "private" | "direct"; readonly allowed: ReadonlyArray<...> | null }

export const shared = PG.compileUnsafe(
	PG.from(Channels)
		.select("id", "type")
		.where(($) => [$.type.in_("public", "private")]),
	{},
)

export const upsert = PG.compileUnsafe(
	PG.insertInto(Channels)
		.values({ id: "c1", org_id: "o", slug: "general", type: "private" })
		.onConflictDoUpdate({ target: ["slug"], set: { type: "private" } }),
	{},
)

export const ddl = Effect.map(S.pgEntitiesOf([ChannelType, Channels]), (entities) => S.renderPgSchema(entities))
// CREATE TYPE "channel_type" AS ENUM ('public', 'private', 'direct')   (skipped if it exists)
// CREATE TABLE IF NOT EXISTS "channels" (... "type" channel_type NOT NULL DEFAULT 'public', ...)
// ALTER TABLE "channels" ADD CONSTRAINT "channels_org_slug_unique" UNIQUE ("org_id", "slug")
// ALTER TABLE "channels" ADD CONSTRAINT "channels_slug_unique" UNIQUE ("slug")
```

`ChannelType.values` lists the labels for application code. Exporting the enum from a schema
module is optional: the tables that use it bring it along. Pass `{ schema: "auth" }` for a type
in another schema; leave it out for the schema migrations run in (usually `public`), which is
how columns of the type are spelled there.

Drivers parse arrays of built-in types but not of an enum, whose array type they do not know:
node-postgres and PGlite send `{public,private}` as text. An `array(enumType)` column reads
either form, and writes its value as array text, which Postgres casts to the column's type.

`PG.brand(PG.text, Schema.Literals([...]))` and `PG.custom("public.channel_type", ...)` still
work for an enum `generate` does not manage; see [migrations](https://effect-orm.maple.dev/postgres/database/migrations/#postgres) for
moving one to `enumType`.

## JSON paths

A `jsonb(schema)` column knows the shape of its documents, so a path into one is typed and
decoded by the same schema. `jsonPath(column, ...keys)` reads the value at a path with `->`;
a string key steps into an object, a number into an array. Only paths the schema has
type-check, and the result decodes with the field's own schema (a `NumberFromString` field
reads as a number). A key that is absent reads as `null`.

```ts title="postgres-json-paths.ts"
import { PGlite } from "@electric-sql/pglite"
import { Effect, Schema } from "effect"
import * as PG from "@maple-dev/effect-orm/postgres"

const Settings = Schema.Struct({
	notifications: Schema.Struct({ sms: Schema.Boolean, email: Schema.Boolean }),
	tags: Schema.Array(Schema.String),
})

const Users = PG.table("users", {
	columns: { id: PG.int4, settings: PG.jsonb(Settings) },
	primaryKey: ["id"],
})

// Read and filter: `sms` is an Expr<boolean | null>.
export const read = PG.compileUnsafe(
	PG.from(Users)
		.select(($) => ({ id: $.id, sms: PG.jsonPath($.settings, "notifications", "sms") }))
		.where(($) => [PG.jsonPath($.settings, "notifications", "email").eq(true), PG.jsonContains($.settings, { tags: ["beta"] })]),
	{},
)
// read.sql:        SELECT "users"."id" AS "id", ("users"."settings" -> 'notifications' -> 'sms') AS "sms" FROM "users"
//                  WHERE ("users"."settings" -> 'notifications' -> 'email') = $1 AND ("users"."settings" @> $2::jsonb)
// read.parameters: ["true", '{"tags":["beta"]}']

// Write one path, without reading the row first.
export const write = PG.compileUnsafe(
	PG.update(Users)
		.set(($) => ({ settings: PG.jsonSet($.settings, ["notifications", "sms"], false) }))
		.where(($) => [$.id.eq(1)]),
	{},
)
// write.sql: UPDATE "users" SET "settings" = jsonb_set("settings", ARRAY['notifications', 'sms'], $1::jsonb) WHERE "id" = $2

const db = new PGlite()
await db.exec(`
	CREATE TABLE users (id int4 PRIMARY KEY, settings jsonb NOT NULL);
	INSERT INTO users VALUES (1, '{"notifications": {"sms": true, "email": true}, "tags": ["beta"]}');
`)
await db.query(write.sql, [...write.parameters])
const result = await db.query<Record<string, unknown>>(read.sql, [...read.parameters])
export const rows = await Effect.runPromise(read.decodeRows(result.rows))
// [{ id: 1, sms: false }]
await db.close()
```

| Function | SQL | Result |
| --- | --- | --- |
| `jsonPath(x, ...keys)` | `(x -> 'k1' -> 'k2')` | the field's type, or `null` |
| `jsonPathText(x, ...keys)` | `(x -> 'k1' ->> 'k2')` | `string \| null`: a JSON string without its quotes |
| `jsonContains(x, part)` | `(x @> $n::jsonb)` | a condition |
| `jsonSet(x, [keys], value)` | `jsonb_set(x, ARRAY['k1', 'k2'], $n::jsonb)` | the document's type |

- A value compared with a `jsonPath`, and the value of a `jsonSet`, is encoded by the field's
  schema and bound as JSON text: `.eq("Kyiv")` matches the JSON string `"Kyiv"`. For text
  comparisons and `like`, use `jsonPathText`.
- `jsonContains` takes part of the document, at any depth: objects match on the keys given,
  arrays on the elements given, in any order. It accepts a `jsonPath` too. Its value is written
  as given, so a field whose schema transforms (a date stored as text) is written in its stored
  form. A GIN index on the column serves `@>`.
- `jsonSet` creates the last key if it is absent. A missing parent leaves the document as it is,
  and a `NULL` document stays `NULL`. Nest calls to set more than one path.
- Keys are written into the SQL as literals, not bound, so an expression index on
  `(settings -> 'notifications')` matches.
- The schema walk follows structs, records, arrays and tuples, through unions, `NullOr`,
  optional keys and `Schema.suspend`. A path through a field whose stored form is not its
  type (`Schema.encodeKeys`, a class) is refused when the query compiles. A `jsonb()` column
  with no schema takes any keys and reads `unknown`.
- These are Postgres functions. Compiled for ClickHouse, SQLite or D1 they die with a
  `QueryBuilderDefect` ("is a Postgres function") instead of sending SQL the server would
  reject. SQLite and D1 have no typed JSON paths yet: a `SQLite.json(schema)` column is read and
  written whole.

## Functions

| Function | SQL | Notes |
| --- | --- | --- |
| `count()`, `countDistinct(x)` | `count(*)`, `count(DISTINCT x)` | |
| `countIf(c)`, `sumIf(x, c)` | `count(*) FILTER (WHERE c)`, `sum(x) FILTER (WHERE c)` | ClickHouse's `-If` combinators |
| `sum`, `avg`, `min`, `max` | same | `null` over no rows, where ClickHouse returns `0` for `sum` |
| `percentileCont(f, x)` | `percentile_cont(f) WITHIN GROUP (ORDER BY x)` | Interpolated |
| `arrayAgg(x)` | `array_agg(x)` | |
| `dateTrunc(unit, ts)` | `date_trunc(unit, ts, 'UTC')` | UTC buckets; Postgres 12+ |
| `dateBin(seconds, ts)` | `date_bin(…, ts, epoch)` | Epoch-aligned buckets; Postgres 14+ |
| `now()` | `now()` | |
| `lower`, `upper`, `length` | same | |
| `coalesce(x, fallback)` | `coalesce(x, fallback)` | No longer nullable |
| `jsonText(x, key)` | `(x ->> key)` | `null` when absent |
| `jsonPath`, `jsonPathText`, `jsonContains`, `jsonSet` | `->`, `->>`, `@>`, `jsonb_set` | Typed by the column schema; see [JSON paths](#json-paths) |
| `greatest(a, ...)`, `least(a, ...)` | same | NULL arguments are skipped |
| `caseWhen([[c, v], ...], otherwise)` | `CASE WHEN c THEN v ... ELSE otherwise END` | |
| `asBoolean(c)` | `(c)` | A condition as a value, to select or `set` |
| `typedValue(type, value)` | a bound param | Encoded as `type` writes it: `typedValue(T.columns.at, ms)` |

`undecoded($.column)` reads a column without its codec: a jsonb document as `unknown`, a branded
id as its string. Use it where the reader decodes stored values itself (to tolerate an older
document shape, or to name the bad row in its own error); every other read stays typed.

The shared operators (`eq`, `in_`, `like`, `ilike`, `and`, `or`, `not`, arithmetic, `lit`) work
unchanged. `/postgres` exports only functions Postgres has, plus `nullIf`, which renders the
same on both. The ClickHouse catalog on `/clickhouse` (`quantile`, `toStartOfInterval`, its
`count()`, …) writes ClickHouse SQL, so a query that uses one is a type error where it is
compiled or run for Postgres, and the Postgres functions above are one on ClickHouse (see
[Checked in the type](https://effect-orm.maple.dev/postgres/queries/params-and-compilation/#checked-in-the-type)). Past the types, the
compile still fails with a `QueryBuilderDefect` naming the function. A custom `Dialect` opts in
with `functions: "clickhouse"` or `"postgres"`; without it, nothing is checked.

## Known differences

- Integer division truncates in Postgres (`7 / 2` is `3`) and promotes to a float in ClickHouse.
- `rawExpr`, `untypedExpr`, `dynamicColumn` and `outerRef` take SQL as written; quote
  identifiers yourself (`"OrgId"`) when targeting Postgres.
- An outer join fills missing columns with `NULL` in Postgres and with type defaults in
  ClickHouse (unless `join_use_nulls` is set). Joined columns are typed nullable either way.

See [`design/dialects.md`](https://github.com/MapleTechLabs/effect-orm/blob/main/design/dialects.md) for how dialects are built and what a new one
needs to provide.

Source: https://effect-orm.maple.dev/postgres/specifics/overview/index.mdx