PostgreSQL

PostgreSQL is the most complete Atscript DB adapter. It supports transactional DDL, native foreign key enforcement, pgvector for vector similarity search, CITEXT for case-insensitive text, GIN-based fulltext search, and JSONB for structured data. If you need a production-grade relational database with advanced capabilities, PostgreSQL is the recommended adapter.

Installation

bash

pnpm add @atscript/db-postgres pg

The adapter uses node-postgres (pg) as its driver. Install @types/pg as a dev dependency if you need TypeScript types for the underlying pg library.

Setup

Driver and Adapter

Create a PgDriver with your connection details, then wrap it in a PostgresAdapter factory via DbSpace:

typescript

import { DbSpace } from "@atscript/db";
import { PgDriver, PostgresAdapter } from "@atscript/db-postgres";

const driver = new PgDriver("postgresql://user:pass@localhost:5432/mydb");
const db = new DbSpace(() => new PostgresAdapter(driver));

PgDriver accepts three input forms:

typescript

// Connection URI string
const driver = new PgDriver("postgresql://user:pass@localhost:5432/mydb");

// Pool configuration object
const driver = new PgDriver({
  host: "localhost",
  user: "postgres",
  database: "mydb",
  max: 10,
});

// Pre-created pg.Pool instance
import pg from "pg";
const pool = new pg.Pool({ connectionString: "..." });
const driver = new PgDriver(pool);

Convenience Helper

For quick setup, use the createAdapter shortcut that creates both the driver and DbSpace in one call:

typescript

import { createAdapter } from "@atscript/db-postgres";

const db = createAdapter("postgresql://user:pass@localhost:5432/mydb");

You can pass additional pool options as the second argument:

typescript

const db = createAdapter("postgresql://localhost:5432/mydb", { max: 20 });

The same options object takes textSearchConfigs (since 0.1.175), an adapter option rather than a pool one — see Text search configuration per language. With your own driver, pass it to the adapter: new PostgresAdapter(driver, { textSearchConfigs }).

Plugin Registration

To use PostgreSQL-specific annotations (@db.pg.*), register the plugin in your Atscript configuration:

typescript

import ts from "@atscript/typescript";
import { dbPlugin } from "@atscript/db/plugin";
import pg from "@atscript/db-postgres/plugin";

export default {
  plugins: [ts(), dbPlugin(), pg()],
};

dbPlugin() is required — it registers all portable @db.* annotations. The PostgreSQL plugin (pg()) is optional and only needed if you use @db.pg.type, @db.pg.schema, or @db.pg.collate. See Setup for full configuration details.

PostgreSQL-Specific Annotations

These annotations opt into PostgreSQL-specific behavior. Files using only portable @db.* annotations remain adapter-agnostic.

Annotation	Level	Purpose
`@db.pg.type "TYPE"`	Field	Override the native column type (e.g., `"CITEXT"`, `"INET"`, `"MACADDR"`)
`@db.pg.schema "name"`	Interface	Set the database schema (default: the connection's current schema — `public` unless `search_path` says otherwise)
`@db.pg.collate "collation"`	Interface / Field	Native PostgreSQL collation override (e.g., `"tr-x-icu"`, `"C"`)

Example:

atscript

@db.pg.schema "analytics"
@db.table "events"
export interface Event {
  @meta.id
  id: string

  @db.pg.type "INET"
  ip: string

  @db.pg.collate "tr-x-icu"
  name: string
}

Type Mapping

Atscript Type	PostgreSQL Type	Notes
`string`	`TEXT`	`VARCHAR(N)` when `@expect.maxLength` is set
`string` (PK or has default)	`VARCHAR(255)`
`string` with `@db.column.collate 'nocase'`	`CITEXT`	Case-insensitive text extension
`string` with `char` tag	`CHAR(1)`
`number`	`DOUBLE PRECISION`
`number` (integer tags)	`SMALLINT` / `INTEGER` / `BIGINT`	Based on int8/int16/int32/int64 tags
`number` with `@db.column.precision`	`NUMERIC(p,s)`
`number` with `@db.default.increment`	`BIGINT`	`GENERATED BY DEFAULT AS IDENTITY`
`number` with `@db.default.now`	`BIGINT`	Epoch milliseconds
`boolean`	`BOOLEAN`	Native boolean (not INTEGER)
`decimal`	`NUMERIC(p,s)`	Defaults to `NUMERIC(10,2)`
Nested objects	Flattened `__` columns	`address.city` becomes `address__city`
`T \| null`	The column of `T`, nullable	Unions of objects are flattened; see Nullable and Union Fields
`@db.json`	`JSONB`	Stored as a single JSONB column; descendant paths are not queryable (400 since 0.1.128)
Arrays	`JSONB`	Same — select the column as a whole; filters accept only `$exists` (since 0.1.132)
`@db.default.uuid`	`VARCHAR(255)`	`DEFAULT gen_random_uuid()`
`@db.search.vector`	`vector(N)`	pgvector extension; falls back to `JSONB`
`db.geoPoint`	`geography(Point,4326)`	PostGIS extension; falls back to `JSONB`. See Geo Search

To filter, sort or group by a leaf inside a @db.json column, expose it as a typed view field.

Unsigned Integer Promotion

PostgreSQL has no unsigned integer types. Unsigned types are promoted to the next-larger signed type to preserve the full value range:

Atscript Tag	PostgreSQL Type	Reason
`uint8`	`SMALLINT`	0--255 fits in SMALLINT
`uint16`	`INTEGER`	0--65535 exceeds SMALLINT max (32767)
`uint32`	`BIGINT`	0--4.3B exceeds INTEGER max (~2.1B)
`uint64`	`BIGINT`	Best available; large values may lose precision

CITEXT — Case-Insensitive Text

The portable @db.column.collate 'nocase' annotation maps to PostgreSQL's CITEXT column type, which handles case-insensitive equality, sorting, UNIQUE constraints, LIKE, range queries, and aggregation at the storage layer.

atscript

@db.table "users"
export interface User {
  @meta.id
  id: string

  @db.column.collate 'nocase'
  @db.index.unique
  email: string
}

The adapter auto-provisions the extension when CITEXT fields are detected:

sql

CREATE EXTENSION IF NOT EXISTS citext

No query-side wrapping (e.g., LOWER()) is needed — case insensitivity is handled entirely by the column type.

Managed PostgreSQL services

Some managed PostgreSQL services (e.g., certain AWS RDS configurations) require manual extension provisioning by an admin. If auto-provisioning fails, create the extension manually:

sql

CREATE EXTENSION IF NOT EXISTS citext;

Then use @db.pg.type "CITEXT" as a direct type override, which skips the auto-provisioning step.

pgvector — Vector Search

The adapter supports vector similarity search via the pgvector extension. Annotate a field with @db.search.vector to define a vector embedding column:

atscript

@db.table "documents"
export interface Document {
  @meta.id
  id: string

  title: string
  content: string

  @db.search.vector 1536, "cosine"
  embedding: number[]
}

How It Works

The field is stored as vector(N) where N is the dimension count
An HNSW index is created automatically for efficient approximate nearest-neighbor search
The adapter auto-provisions the pgvector extension: CREATE EXTENSION IF NOT EXISTS vector

Distance Metrics

Similarity	PG Operator	Index Ops Class	Description
`cosine` (default)	`<=>`	`vector_cosine_ops`	Cosine distance
`euclidean`	`<->`	`vector_l2_ops`	L2 / Euclidean distance
`dotProduct`	`<#>`	`vector_ip_ops`	Negative inner product

Runtime Search

typescript

const results = await table.vectorSearch(queryEmbedding, {
  filter: { status: "published" },
  controls: { $limit: 10, $threshold: 0.8 },
});

The $threshold parameter is a normalized similarity score (0--1) matching MongoDB Atlas semantics. The adapter converts it to the appropriate pgvector distance value internally:

Cosine: distance = 2 * (1 - score) (score range [0, 1] maps to distance range [2, 0])
Euclidean: threshold is used as max distance directly
Dot product: threshold is negated

Graceful Fallback

When the pgvector extension is not available (not installed on the server), vector fields are stored as JSONB instead. The data is preserved, but indexed similarity search is not available — vectorSearch() will throw an error.

TIP

Install pgvector on your PostgreSQL server before using vector search features. On most systems: sudo apt install postgresql-16-pgvector or build from source. See the pgvector installation guide.

GIN Fulltext Search

The @db.index.fulltext annotation creates a GIN index on the to_tsvector() expression for efficient text search:

atscript

@db.table "articles"
export interface Article {
  @meta.id
  id: string

  @db.index.fulltext "search_idx"
  title: string

  @db.index.fulltext "search_idx"
  body: string
}

This generates a GIN index on the concatenated tsvector:

sql

CREATE INDEX "search_idx" ON "articles"
  USING gin(to_tsvector('english', coalesce("title", '') || ' ' || coalesce("body", '')))

Search queries use plainto_tsquery():

typescript

const results = await table.search("database optimization", {
  filter: { published: true },
  controls: { $limit: 20 },
});

Multiple fields in the same fulltext index group are concatenated for combined search.

The configuration is english unless @db.index.fulltext.language names another (russian, german, …; none → simple) or textSearchConfigs maps that language to your own, used for the index and every query; changing it rebuilds the index on the next schema sync. @db.index.fulltext.match switches the query to websearch_to_tsquery — "all" passes the text as typed, "any" joins the terms with or, without - exclusions (since 0.1.168). See Language and term matching.

Without a $sort, results are ordered by ts_rank descending, then the primary key (since 0.1.165). Per-field weights (@db.index.fulltext 'idx', 5) apply to that ranking: the distinct weights map to setweight buckets A–D (highest first) with their relative values. The match itself keeps using the unweighted GIN expression, so the index needs no rebuild — but the rank recomputes to_tsvector for every matched row before LIMIT; on a broad term over a large table, narrow with a filter, pass a $sort, or turn ranking off with $rank: false (since 0.1.175). getSearchIndexes() lists the index under its declared name (the atscript__fulltext__<name> key is an alias).

Integer members are left out of the tsvector — coalesce(int, '') would not even be valid SQL — and matched by exact number instead. When the whole search term is a whole number, the predicate becomes (<tsvector match> OR "ref_no" = CAST(? AS BIGINT)); the BIGINT cast keeps a 10-digit term from overflowing an INTEGER column, and the member's btree index serves the equality next to the GIN index (a bitmap OR). An index of integer members only creates no DDL. See Text Search — Integer fields.

Text search configuration per language

Since 0.1.175. The textSearchConfigs adapter option replaces the built-in configuration of a @db.index.fulltext.language with one your app creates, for example a copy of russian that removes accents before stemming:

sql

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE TEXT SEARCH CONFIGURATION ru_unaccent (COPY = russian);
ALTER TEXT SEARCH CONFIGURATION ru_unaccent
  ALTER MAPPING FOR word, hword, hword_part WITH unaccent, russian_stem;

typescript

const db = createAdapter("postgresql://localhost:5432/mydb", {
  textSearchConfigs: { russian: "ru_unaccent" },
});
// or: new PostgresAdapter(driver, { textSearchConfigs: { russian: "ru_unaccent" } })

The built-in russian already stems words of plain ASCII letters with english_stem, so mixed Russian and English text needs no custom configuration for that.

Every index of that language and every query against it use the configuration. An index without @db.index.fulltext.language is english on PostgreSQL, so the english entry maps it too.
The name must be an unquoted lowercase identifier (letters, digits, _) that the connection finds through its search_path — no schema prefix. Other names, and keys that are not a @db.index.fulltext.language value, are rejected when the adapter is created.
Create the configuration before schema sync builds the index; atscript-db does not create it.
The configuration is part of the schema: changing an entry rebuilds the indexes of that language on the next schema sync.

Transactional DDL

PostgreSQL DDL is fully transactional, unlike MySQL and SQLite. Schema sync changes are wrapped in BEGIN/COMMIT blocks — if any DDL statement fails, the entire sync rolls back atomically. This means:

Adding columns, changing types, and renaming tables are all-or-nothing operations
Table recreation (for changes that require rebuilding the table) uses a transaction for safe data migration: create temp table, copy data, drop old, rename new
No risk of half-applied schema changes after a failure

TIP

This is a significant advantage over SQLite (no transactional DDL) and MySQL (implicit COMMIT before each DDL statement). If schema reliability matters, PostgreSQL is the safest choice.

Column Type Changes

A changed column type is applied in place (ALTER COLUMN … TYPE … USING), or by the copy step of a @db.sync.method 'recreate' table. Both convert the stored value through text to the new type:

A value that does not parse as the new type ('n/a' into a number) fails the sync. The transaction rolls back, the table keeps its rows and old type, and its entry is an error (hash withheld). Fix the data and re-run.
Since 0.1.137 a value longer than a new VARCHAR(n) / CHAR(n) (@expect.maxLength) fails the same way. Before, an in-place change truncated it silently.
Since 0.1.155 a DOUBLE PRECISION / REAL / NUMERIC column becoming the BIGINT of an epoch-ms field with @db.default.now (such as number.timestamp → number.timestamp.created) is rounded (round(col)::bigint) instead of failing on a fractional value. A fractional value changed to any other integer type (number.int, number.int.int64) still fails the sync.
In a recreate, a required column's NULLs take its @db.default, else the type's empty value ('', 0, false). Before 0.1.137, a recreate that changed a required column's type always failed the copy (invalid input syntax for type double precision: ""), and one that changed an optional column from string to number failed too.

Drops Never Cascade

Since 0.1.128 every drop schema sync owns — removed tables, @db.sync.method 'drop', destructive table-option changes — is a plain DROP TABLE IF EXISTS without CASCADE. When something the run does not drop first still depends on the table — a user-created view, a foreign key from an unmanaged table, or a foreign key from a table that stays in the inventory — PostgreSQL refuses and sync records an error entry for that table (the table stays tracked, the hash is withheld, the next run retries) instead of silently dropping the dependents. Put plainly: a surviving table that references a @db.sync.method 'drop' table makes that table's recreate an error entry on PostgreSQL — drop the constraint or remove the reference first. A removed table that references it is not a blocker: sync drops it right before the recreate (and, likewise, before a primary-key rebuild). The remaining removed tables are dropped children-first; a foreign-key cycle goes in one DROP TABLE IF EXISTS a, b statement, which PostgreSQL resolves without CASCADE.

Two related behaviours to know:

View recreation drops GRANTs. A managed view whose definition changed is DROP VIEW + CREATE VIEW; roles granted on the old view must be re-granted. The 0.1.128 upgrade recreates every managed view that has joins, a filter or a having clause once. A user-created view depending on a managed view makes that recreate fail with an error entry until it is dropped or the managed view is excluded.
Identity columns. @db.default.increment is an identity column, not a default: gaining it emits ALTER COLUMN … SET NOT NULL, ADD GENERATED BY DEFAULT AS IDENTITY, losing it emits ALTER COLUMN … DROP IDENTITY IF EXISTS (earlier releases emitted an invalid SET DEFAULT increment() / a rejected DROP DEFAULT). A primary-key change on an empty table is one ALTER TABLE … DROP CONSTRAINT <pk>, ADD PRIMARY KEY (…).

recreateTable (@db.sync.method 'recreate') never uses CASCADE either: it runs in one transaction, and when an object outside the sync inventory (a user view, an unmanaged foreign key) still depends on the table, PostgreSQL refuses the drop, the transaction rolls back and the error entry names the dependent object.

Batched Inserts

The insertMany method uses multi-row INSERT INTO ... VALUES (...), (...), ... RETURNING * for optimal performance. PostgreSQL has a protocol-level limit of approximately 65,535 parameters per query. The adapter automatically chunks large batches to stay within this limit (~60,000 parameters per chunk):

typescript

// 10,000 rows with 8 columns = 80,000 params
// Adapter splits into 2 chunks: 7500 rows + 2500 rows
const result = await table.insertMany(largeDataset);

The RETURNING clause returns inserted rows including any generated defaults (auto-increment IDs, UUIDs, timestamps), so insertedIds are always populated correctly.

The column list is the union of every row's fields, and a row that omits a column gets its DEFAULT — up to 0.1.131 columns absent from the first row were dropped from the whole batch (see Insert Many).

Calendar Buckets

Calendar buckets (since 0.1.132) use AT TIME ZONE with the zone written into the query, so labels do not depend on the session's TimeZone setting. Zones come from the server's tz database. A zone the server does not know — typically one newer than its tzdata — fails with BUCKET_TZ_UNAVAILABLE (HTTP 501): PostgreSQL does not recognize time zone "…" — update the server's time zone data.

Aggregate expressions

Arithmetic and first / last run in SQL. A double overflow (SQLSTATE 22003) is INVALID_QUERY (Arithmetic overflow), not a 500. min / max / first / last of a boolean field render as BOOL_AND / BOOL_OR (PostgreSQL has no MIN(boolean)). A first / last of a number, text, decimal or timestamp column is picked with a streaming MIN (one accumulator per group); an array column (@db.pg.type 'x[]') is picked with MIN over the array (its element type must be orderable); only a column whose physical type has no MIN (uuid, bytea, point, json / jsonb, citext, a @db.pg.type override) uses (ARRAY_AGG(x))[1], which holds one element per row of the group in memory — prefer an ordered type for first / last over huge groups.

Schema Support

Tables can be placed in PostgreSQL schemas (namespaces) using @db.pg.schema:

atscript

@db.pg.schema "analytics"
@db.table "events"
export interface Event {
  @meta.id
  id: string

  type: string
  payload: object
}

The table is created as "analytics"."events" and all queries are schema-qualified. The schema is created automatically during schema sync if it does not exist.

You can also use the portable @db.schema annotation, which has the same effect.

Auto-Increment

The @db.default.increment annotation uses PostgreSQL 10+ identity column syntax:

sql

"id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

This is the modern PostgreSQL approach (not the legacy SERIAL pseudo-type). After table recreation during schema sync, the adapter automatically resets the identity sequence to MAX(id) + 1 so new inserts don't conflict with existing data.

INFO

GENERATED BY DEFAULT (not GENERATED ALWAYS) allows explicit ID values during inserts, which is needed for data migration and seeding.

Connection Pooling

PgDriver uses a pg.Pool internally. Each CRUD operation acquires a connection from the pool, executes the query, and releases the connection automatically. For transactions, a dedicated connection is acquired via getConnection() and held for the duration of the transaction.

typescript

const driver = new PgDriver({
  connectionString: "postgresql://localhost:5432/mydb",
  max: 20, // maximum pool size
  idleTimeoutMillis: 30000,
});

The pool is initialized lazily — the pg module is dynamically imported on first use, so the package works in both ESM and CJS environments without top-level import issues.

To shut down cleanly, close the space — see Closing the space. driver.close() is idempotent.

Broken connections

A pool connection can break while nobody is using it: a server restart, pg_terminate_backend, a failover or a network reset. pg.Pool reports it as an 'error' event, which crashes the process when nothing listens. Since 0.1.154, a pool that PgDriver creates (from a URI or a config object) listens for it: the error is logged as a warning and the pool drops the connection — the next query opens a fresh one. A connection checked out for a transaction is covered the same way: its remaining statements reject, and it is discarded when released.

A COMMIT that fails on the client (a query timeout, a lost socket) leaves the transaction in an unknown state, so since 0.1.175 the adapter closes that connection instead of returning it to the pool. PgDriver does it through the optional destroy() of TPgConnection; a custom driver without destroy() gets a best-effort ROLLBACK, then release().

Warnings go to console by default. Pass a logger (anything with a warn method) as the second argument to route them elsewhere:

typescript

const driver = new PgDriver("postgresql://user:pass@localhost:5432/mydb", { logger: myLogger });

Pre-created pools

PgDriver does not attach a listener to a pg.Pool you pass in — the pool is yours. Add one yourself, or an idle connection's error crashes the process:

typescript

const pool = new pg.Pool({ connectionString: "..." });
pool.on("error", (err) => console.warn("idle postgres connection lost:", err.message));
const driver = new PgDriver(pool);

SSL for managed Postgres

Most managed providers (Neon, Supabase, RDS, Heroku, etc.) require TLS. SSL options are passed straight through to pg.Pool — see the node-postgres SSL docs for the full option set:

typescript

const driver = new PgDriver({
  connectionString: process.env.DATABASE_URL,
  ssl: { rejectUnauthorized: true },
});

For providers that require self-signed certs (e.g., RDS with the bundled CA), pass the certificate via ssl: { ca: fs.readFileSync('./rds-ca.pem') }. When using a pre-created pg.Pool, configure SSL on the pool directly.

Error Handling

The adapter maps PostgreSQL constraint errors to structured DbError instances:

PG SQLSTATE	Error Type	Description
`23505`	`CONFLICT`	Unique constraint violation — includes the field path and constraint detail
`23503`	`FK_VIOLATION`	Foreign key violation — extracts the offending column from PG's error detail

These structured errors are the same across all adapters, so error handling code is portable:

typescript

import { DbError } from "@atscript/db";

try {
  await table.insertOne({ email: "duplicate@example.com" });
} catch (err) {
  if (err instanceof DbError && err.code === "CONFLICT") {
    console.log("Duplicate:", err.errors[0].path); // "email"
  }
}

Custom Type Parsers

The PgDriver configures custom type parsers for consistent JavaScript value handling:

PostgreSQL Type	Parsed As	Why
`TIMESTAMP` / `TIMESTAMPTZ`	`number` (epoch ms)	Consistent with SQLite and MongoDB adapters. A `TIMESTAMP` (without time zone) holds a UTC wall time: it is read as UTC whatever the process time zone (since 0.1.151)
`NUMERIC`	`number`	Default pg driver returns `string`
`INT8` / `BIGINT`	`number`	Returns `string` only if value exceeds `Number.MAX_SAFE_INTEGER`
`BOOLEAN`	`boolean`	Native (no conversion needed)

These parsers are applied per-pool (not globally), so they don't affect other pg usage in the same process. When using a pre-created pg.Pool, type parsing is the caller's responsibility.

Views

Managed views are created with CREATE OR REPLACE VIEW.

Views are always read by name: PostgreSQL itself removes a left join whose target is unique on the join condition and unused by the query — through the view, in COUNT(*), for first-row joins too — so the adapter needs no join skipping of its own.

Since 0.1.147 a first-row join renders as a correlated scalar subquery in the join's ON (no window functions):

sql

LEFT JOIN "issues" AS "OldestOpenIssue"
  ON "OldestOpenIssue"."id" = (SELECT "OldestOpenIssue"."id" FROM "issues" AS "OldestOpenIssue"
      WHERE <condition> ORDER BY "OldestOpenIssue"."raised_at" ASC NULLS FIRST, "OldestOpenIssue"."id" ASC NULLS FIRST LIMIT 1)

It runs once per entry row; an index on the condition's join key plus the order keys ((ticket_id, raised_at, id)) keeps each lookup an index scan. PostgreSQL sorts NULL last by default, so order keys render ASC NULLS FIRST / DESC NULLS LAST — NULL is the smallest value on every adapter. A default B-tree index serves NULLS FIRST only when declared that way: create (ticket_id, raised_at NULLS FIRST, id), or keep order keys required.

Computed columns cast every field and literal operand with CAST(x AS DOUBLE PRECISION) and divide with NULLIF(divisor, 0), so 7 / 2 = 3.5 and division by zero is NULL. Without the cast, PostgreSQL would divide integers (7 / 2 = 3) and raise on division by zero. A result outside the double precision range fails the read with value out of range: overflow.

Derived Columns

A @db.column.derived field (since 0.1.141) is a GENERATED ALWAYS AS (…) STORED column — PostgreSQL has no virtual generated columns, so the value is computed on every write of the row and occupies storage. The expression is jsonb_typeof()-guarded #>> extraction with a cast to boolean / double precision for those leaves; a @db.column.collate 'nocase' leaf is CITEXT like a stored one, other collations render COLLATE "…" before the generated clause. ALTER COLUMN TYPE is never used on a generated column: a changed extraction or type is DROP COLUMN + ADD COLUMN (managed indexes dropped first). Introspection reads information_schema.columns.is_generated; a recreateTable copies every column but the generated ones, which the new table recomputes from the copied JSON.

Relational Predicates

$some / $none filters (since 0.1.147) render as correlated EXISTS (SELECT 1 FROM … WHERE …) / NOT EXISTS subqueries, in reads and in mutation filters; a @db.schema-qualified table is addressed with its schema.

Indexes. PostgreSQL does not index foreign-key columns by itself — a REFERENCES constraint indexes only the referenced key. Declare an index on the foreign-key column of the @db.rel.from side and on both foreign-key columns of a @db.rel.via junction, or each predicate scans the related table per candidate row:

atscript

@db.table 'issues'
export interface Issue {
    @meta.id
    id: number

    @db.rel.FK
    @db.index.plain 'issues_ticket_idx'
    ticketKey?: Ticket.key
}

Conflict-ignoring inserts

insertMany(rows, { onConflict: "ignore" }) (CRUD) runs one batched statement per chunk: INSERT … VALUES (…), (…) ON CONFLICT DO NOTHING RETURNING <primary key and unique-index columns>. A conflict never raises, so an outer transaction stays usable. The rows missing from RETURNING are the skipped ones; the adapter maps the returned rows back to input positions by key values: a row counts as inserted only when the next returned row carries every key (primary key and unique index) that the input row fully defines (duplicates inside the batch are removed beforehand, so keys are unique within a batch). The result is exact, falling back to per-row when the mapping is ambiguous: the chunk runs inside a SAVEPOINT and, when a row cannot be matched unambiguously, it is rolled back to it and redone row by row with the same ON CONFLICT DO NOTHING. Ambiguous means a row with no key value of its own (a generated key can collide with a stored explicit value, so a generated value proves nothing), a skipped-looking row whose key the server may store in another form than it was sent (such as a value rounded by NUMERIC(10,2)), or a key that equals the returned one only when written differently (5 and '5.00', a blank-padded CHAR(n)). The batched mapping also relies on RETURNING coming back in VALUES order, as an in-order subsequence of the input; when the counts or keys do not line up it falls back instead of guessing. An all-new chunk costs three round trips, one of them the INSERT: SAVEPOINT, the INSERT … ON CONFLICT DO NOTHING RETURNING, RELEASE SAVEPOINT (the savepoint is what makes the row-by-row redo possible, and whether a chunk is mappable is only known from RETURNING, so it cannot be skipped up front; the two savepoint commands carry no data). Keys that differ only in letter case (a uuid comes back lower-cased, a citext key is case-insensitive) count as ambiguous too and take the same row-by-row path. Chunks follow the same parameter limit as a plain insertMany. A table with no primary key and no unique index cannot collide, so every row is reported inserted. Sequences advance for skipped rows (gaps).

ON CONFLICT DO NOTHING takes no lock on a stored conflicting row, so ignore-inserting rows and then updating them in two concurrent transactions does not deadlock. With lockConflicts: true (CRUD, since 0.1.153) the call first locks the stored rows sharing a key with the batch — one SELECT … FOR UPDATE … ORDER BY <primary key> — then inserts; a row a concurrent writer committed in between is skipped by DO NOTHING and locked by one more SELECT … FOR UPDATE afterwards (under READ COMMITTED, the default). Deadlocks (40P01), lock timeouts (55P03) and serialization failures (40001) throw DbError("DEADLOCK" | "LOCK_TIMEOUT" | "SERIALIZATION_FAILURE") with retryable: true since 0.1.153 — on every statement and at COMMIT since 0.1.175, with the pg error as the DbError's cause (Transactions).

Locking reads (since 0.1.162) render SELECT … FOR UPDATE / FOR SHARE [NOWAIT / SKIP LOCKED]. withTransaction(fn, { isolation }) opens the transaction with BEGIN ISOLATION LEVEL …. See Locking reads.

Limitations

No unsigned integer types — unsigned types are promoted to the next-larger signed type (see Unsigned Integer Promotion)
Extension privileges required — CREATE EXTENSION for citext and pgvector requires database-level CREATE privilege. Managed services may need manual provisioning by an admin
UPDATE/DELETE with LIMIT — PostgreSQL does not support LIMIT on UPDATE or DELETE statements. The adapter uses a ctid subquery (WHERE ctid = (SELECT ctid FROM t WHERE ... LIMIT 1)) to achieve single-row update/delete
pgvector installation — the pgvector extension must be installed separately on the PostgreSQL server; it is not bundled with PostgreSQL
Parameter limit — PostgreSQL supports ~65,535 bind parameters per query. Large insertMany calls are auto-chunked, but custom raw queries should be aware of this limit

PostgreSQL ​

Installation ​

Setup ​

Driver and Adapter ​

Convenience Helper ​

Plugin Registration ​

PostgreSQL-Specific Annotations ​

Type Mapping ​

Unsigned Integer Promotion ​

CITEXT — Case-Insensitive Text ​

pgvector — Vector Search ​

How It Works ​

Distance Metrics ​

Runtime Search ​

Graceful Fallback ​

GIN Fulltext Search ​

Text search configuration per language ​

Transactional DDL ​

Column Type Changes ​

Drops Never Cascade ​

Batched Inserts ​

Calendar Buckets ​

Aggregate expressions ​

Schema Support ​

Auto-Increment ​

Connection Pooling ​

Broken connections ​

SSL for managed Postgres ​

Error Handling ​

Custom Type Parsers ​

Views ​

Derived Columns ​

Relational Predicates ​

Conflict-ignoring inserts ​

Limitations ​

See Also ​

PostgreSQL

Installation

Setup

Driver and Adapter

Convenience Helper

Plugin Registration

PostgreSQL-Specific Annotations

Type Mapping

Unsigned Integer Promotion

CITEXT — Case-Insensitive Text

pgvector — Vector Search

How It Works

Distance Metrics

Runtime Search

Graceful Fallback

GIN Fulltext Search

Text search configuration per language

Transactional DDL

Column Type Changes

Drops Never Cascade

Batched Inserts

Calendar Buckets

Aggregate expressions

Schema Support

Auto-Increment

Connection Pooling

Broken connections

SSL for managed Postgres

Error Handling

Custom Type Parsers

Views

Derived Columns

Relational Predicates

Conflict-ignoring inserts

Limitations

See Also