Compatibility Matrix

DB9 implements a PostgreSQL-compatible SQL engine over TiKV distributed storage. Most PostgreSQL clients, ORMs, and drivers work without changes. This page documents where DB9 matches PostgreSQL, where it diverges, and what is not available.

Use this matrix when evaluating DB9 for a new project or migrating an existing PostgreSQL application.

Summary

Category	Coverage	Notes
SQL DML (SELECT, INSERT, UPDATE, DELETE)	Full	JOINs, CTEs, window functions, subqueries, upsert, RETURNING; no MERGE
SQL DDL (CREATE, ALTER, DROP)	Near-full	Partitioning is RANGE on a timestamp/timestamptz key only; no table inheritance or foreign data wrappers
Data types	20+ types	All common types incl. INET; no XML, CIDR/MACADDR; range types limited to a partial INT4RANGE
Indexes	B-tree, Hash, GIN and HNSW	Hash covers integer and OID columns only; GiST/SP-GiST/BRIN are rejected
Transactions	Near-full	READ COMMITTED and REPEATABLE READ enforced, but some write conflicts fail at COMMIT with 40001 instead of at the statement; SERIALIZABLE is downgraded to REPEATABLE READ
Built-in functions	200+	String, math, date/time, JSON/JSONB, array, aggregate, window, FTS
Wire protocol	pgwire v3	Simple Query, Extended Query, COPY, prepared statements
ORM compatibility	99%+	Prisma, Drizzle, Sequelize, Knex, TypeORM, GORM, SQLAlchemy tested
System catalogs	50+ views	pg_catalog, information_schema, cron schema
Extensions	Fixed built-in set	http, fs9, pg_cron, vector, embedding, uuid-ossp (uuid_generate_v4() only), parquet, zhparser (plus hstore, pgcrypto and plpgsql as metadata-only shims); no community installs
PL/pgSQL	Partial	Basics supported, including CONTINUE, dynamic EXECUTE, exception handling and nested blocks; no WHILE / FOREACH loops or cursors
Replication	Partial	Logical decoding from replication slots (wal2json through SQL, pgoutput streamed over the replication protocol); no CREATE SUBSCRIPTION and no physical replication

SQL

DML and Queries

Feature	Status
SELECT with FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/OFFSET	Supported
DISTINCT / DISTINCT ON	Supported
JOINs (INNER, LEFT, RIGHT, FULL OUTER, CROSS)	Supported
Subqueries (correlated, scalar, EXISTS, IN)	Supported, including inside a LATERAL body
CTEs (WITH … AS)	Supported, including a WITH inside a LATERAL body and a name reused across scopes of one statement. A duplicate name within one WITH list is rejected with 42712, as in PostgreSQL
Recursive CTEs (WITH RECURSIVE)	Supported, up to 1,000 iterations
Data-modifying CTEs (WITH w AS (INSERT ... RETURNING ...))	Supported over both the PostgreSQL wire protocol and the HTTP SQL API, in any position in the WITH list
Set operations (UNION, INTERSECT, EXCEPT)	Supported
Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)	Supported
INSERT with VALUES, SELECT, DEFAULT, RETURNING	Supported
INSERT ON CONFLICT (upsert)	Supported
MERGE	Not supported — every form, including WITH ... MERGE, EXPLAIN MERGE and PREPARE ... AS MERGE, fails with 0A000 (MERGE is not supported) before any table is read. Write upserts with INSERT ... ON CONFLICT
UPDATE with WHERE, subqueries, RETURNING	Supported
DELETE with WHERE, subqueries, RETURNING	Supported
CASE expressions	Supported
Row-constructor comparisons ((a,b) = (c,d))	Supported — =, <>, <, <=, > and >=, and IN / NOT IN against a list of rows, follow PostgreSQL’s NULL rules. Two forms fail where PostgreSQL returns a result: IS [NOT] DISTINCT FROM with a bare NULL literal opposite a typed value or column (42804), and a VALUES list on the right of IN / NOT IN (42883). See the caution below
COPY (CSV, TEXT)	Supported for the COPY <table> [(columns)] form (BINARY format not supported). The query form COPY (SELECT ...) TO STDOUT is rejected with 0A000
EXPLAIN / EXPLAIN ANALYZE	Supported (ANALYZE adds summary runtime stats — actual rows, execution time, KV counters — but no per-operator timing; FORMAT JSON is honored). Plan rows are returned over pgwire only — the HTTP SQL API returns an empty result
LATERAL joins	Supported, including correlated bodies, a WITH clause inside the body, and correlated subqueries inside the body

Two row-comparison forms fail where PostgreSQL returns a result

Row comparisons follow PostgreSQL’s NULL rules: (1,NULL) = (1,2), (1,NULL) <> (1,2) and (1,NULL) < (1,3) are all NULL, and so are (1,NULL) IN ((1,2)) and (1,NULL) NOT IN ((1,2)). Two other spellings raise an error where PostgreSQL returns a result.

Row IS DISTINCT FROM / IS NOT DISTINCT FROM fails with 42804 (cannot compare dissimilar column types) when a row holds a bare NULL literal opposite a typed value or column — (1,NULL) IS DISTINCT FROM (1,2), or ROW(a,b) IS NOT DISTINCT FROM ROW(1,NULL) — where PostgreSQL returns a result. Comparing columns that happen to contain NULLs works, and so does casting the literal: (1,NULL::int) IS DISTINCT FROM (1,2) returns true, as in PostgreSQL.

A VALUES list is not accepted on the right of IN / NOT IN — (a,b) NOT IN (VALUES (1,2)) fails with 42883 function values(integer, integer) does not exist. Write the rows as a list, NOT IN ((1,2), (9,9)), or wrap the VALUES in a subquery: NOT IN (SELECT x, y FROM (VALUES (1,2)) v(x, y)).

DDL

Feature	Status
CREATE/ALTER/DROP TABLE	Supported
CREATE TABLE AS / SELECT INTO	Supported — but the new table gets an extra _rowid primary key column that is visible in SELECT *, see DDL. WITH [NO] DATA is not parsed (42601). Once a replication slot has been created in the database, _rowid can no longer be dropped from a table that holds rows (55000), and a wal2json slot receives the rows these statements load even though the new table is in no publication, see Logical decoding
Column constraints (PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK)	Supported — DEFERRABLE and INITIALLY DEFERRED are accepted on PRIMARY KEY and UNIQUE and recorded in pg_constraint, but the check still runs row by row: a statement that passes through a temporary duplicate (UPDATE t SET x = x + 1) fails with 23505, with or without SET CONSTRAINTS ... DEFERRED
Foreign keys (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION)	Supported — all five actions behave as declared, 23503 on violation, but a referencing table without a primary key cannot declare ON DELETE SET NULL / SET DEFAULT or an ON UPDATE CASCADE / SET NULL / SET DEFAULT action (0A000 cannot create foreign key ... because it does not have a primary key; PostgreSQL accepts them), and TRUNCATE or DROP TABLE of a referenced table is refused without CASCADE, see the caution below. A DEFERRABLE foreign key is checked at COMMIT when it is INITIALLY DEFERRED or after SET CONSTRAINTS ALL DEFERRED (or SET CONSTRAINTS <name> DEFERRED); a violation then fails the COMMIT with 23503 and the transaction rolls back. SET CONSTRAINTS ... IMMEDIATE restores the per-statement check
Generated columns	Supported
SERIAL / BIGSERIAL	Supported
CREATE/DROP INDEX (B-tree, Hash, GIN, HNSW)	Supported — see Indexes for each method’s coverage
CREATE INDEX CONCURRENTLY	Supported
Expression indexes, partial indexes	Supported
CREATE/DROP VIEW	Supported
CREATE/DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW	Supported — WITH [NO] DATA is not parsed (42601), and a materialized view cannot be added to a publication (42809; PostgreSQL refuses it too, with 22023), so a replication slot never receives its rows, see Logical decoding
CREATE/DROP SEQUENCE	Supported
ALTER SEQUENCE	Supported — RESTART [WITH], INCREMENT BY, MINVALUE / MAXVALUE, START WITH, CACHE, [NO] CYCLE, AS, OWNER TO, OWNED BY and RENAME TO all take effect. SET SCHEMA, SET LOGGED and SET UNLOGGED are rejected with a bare internal error (0A000)
DROP SEQUENCE dependency checks	Supported (2BP01 while a column default or identity column depends on the sequence). DROP SEQUENCE ... CASCADE removes a dependent column default silently, see DDL — Other DDL
CREATE/ALTER TYPE (enum)	Supported, with gaps — enum values compare and sort by label text rather than declaration order (silently), see Advanced SQL — Custom Types. ALTER TYPE supports the enum-value forms, RENAME TO, RENAME ATTRIBUTE and OWNER TO; SET SCHEMA and the attribute-changing forms are rejected with 0A000 (ALTER TYPE ... is not supported). DROP TYPE refuses while a column or an array column uses the type (2BP01), and DROP TYPE CASCADE clears a function-argument dependency but not a column one. A function that returns the type (RETURNS t, t[] or SETOF t) also blocks DROP TYPE (2BP01) and is dropped by CASCADE; a RETURNS TABLE (...) column of the type is not checked, see DDL — Other DDL
CREATE/DROP SCHEMA	Supported
CREATE/DROP FUNCTION (PL/pgSQL)	Supported
CREATE/DROP TRIGGER (BEFORE/AFTER, INSERT/UPDATE/DELETE)	Supported, with gaps — a BEFORE trigger function may not use counted FOR loops, bare LOOPs, CASE statements, EXECUTE, exception handlers or nested blocks (CREATE TRIGGER refuses it — 0A000, or 42601 for WHILE and FOREACH); a FOR r IN SELECT … LOOP query loop is allowed, but only over a declared record variable. FOR EACH STATEMENT, row-level WHEN (...), UPDATE OF and trigger arguments (TG_NARGS / TG_ARGV) are supported; TRUNCATE, INSTEAD OF and constraint triggers, transition tables, WHEN on statement triggers, and a WHEN condition that references a temporal column, now() or current_user are rejected (0A000), see Advanced SQL — Triggers
CREATE/ALTER/DROP EVENT TRIGGER	Supported for ddl_command_start, ddl_command_end and sql_drop (table_rewrite and login are rejected, 0A000). While an enabled event trigger exists, most DDL is refused (0A000) — views, sequences, foreign keys, ALTER TABLE ... DROP COLUMN, GRANT and more. Over the HTTP SQL API, disable the trigger in a separate request before running the migration — see Advanced SQL — Event triggers
TRUNCATE	TRUNCATE t / TRUNCATE TABLE t, optionally with RESTART IDENTITY or CONTINUE IDENTITY and with CASCADE or RESTRICT — all as in PostgreSQL: the identity clauses reset or preserve the table’s sequences, and CASCADE also empties every table whose foreign key references it. The multi-table form and ONLY are rejected (42601). A table that another table’s foreign key references cannot be truncated without CASCADE (0A000), as in PostgreSQL, see the caution below
Row-Level Security (ENABLE/DISABLE/FORCE RLS)	Supported, with or without ONLY, see RLS
CREATE/ALTER/DROP POLICY	Supported
Table partitioning	Partial — PARTITION BY RANGE on a timestamp or timestamptz key, with PARTITION OF ... FOR VALUES FROM (...) TO (...). Rows route to their partition, and a row no partition covers fails with 23514. Any other key type fails with 0A000 RANGE partitioning currently requires a timestamp or timestamptz key; LIST, HASH, DEFAULT partitions, ATTACH PARTITION and DETACH PARTITION are syntax errors (42601). Only plain INSERT and SELECT go through the parent — INSERT ... ON CONFLICT fails with 0A000 ON CONFLICT on RANGE relations is not yet supported on the parent and on its partitions. UPDATE and DELETE on the parent fail with 0A000 UPDATE and DELETE on RANGE parents are not yet supported, TRUNCATE with 0A000 (TRUNCATE on partitioned table "..." is not supported while it has partitions) and COPY ... FROM with 0A000 (COPY FROM into partitioned table "..." is not yet supported; copy into its partitions or use INSERT) — run them against a partition, where every write is checked against the partition’s bounds (23514 new row for relation "..." violates partition constraint). Rows never move between partitions: a foreign-key ON UPDATE CASCADE or ON DELETE SET DEFAULT that would carry a row’s key into another partition fails with 23514, where PostgreSQL moves the row. A partitioned table cannot have an identity column — creating its first partition fails with 0A000 partition identity-column inheritance is not yet supported; declare the column BIGSERIAL instead. An index declared on the parent before its partitions exist is created on each partition; CREATE INDEX on a parent that already has partitions fails with 0A000. Once a partitioned table exists, querying pg_partitioned_table fails with 0A000
Table inheritance	Not supported
Foreign data wrappers (FDW)	Not supported
Tablespaces	Not supported (TiKV manages storage placement)

A referenced table cannot be truncated without CASCADE

Foreign keys are enforced for ordinary DML: a missing parent is rejected with 23503, and the referential actions behave as declared — RESTRICT and NO ACTION (the default) refuse to delete a referenced row, while CASCADE, SET NULL and SET DEFAULT propagate to the referencing rows, on ON UPDATE as well as ON DELETE.

TRUNCATE on a referenced table is refused, as in PostgreSQL — even when the referencing table is empty:

SQL

CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child  (id INT PRIMARY KEY, pid INT REFERENCES parent(id));

TRUNCATE parent;
-- ERROR: cannot truncate a table referenced in a foreign key constraint (0A000)

Of the two escapes PostgreSQL’s hint suggests, TRUNCATE parent CASCADE works — it empties child too, and every table that references child in turn — while the multi-table form TRUNCATE parent, child is a syntax error (42601). A self-referencing foreign key does not block TRUNCATE.

DROP TABLE on a referenced table is refused for the same reason — 2BP01 cannot drop table parent because other objects depend on it, as in PostgreSQL. DROP TABLE parent CASCADE drops the referencing table’s foreign key along with the parent, leaving the child intact and queryable with its remaining constraints.

Transactions

Feature	Status
BEGIN / COMMIT / ROLLBACK	Supported
SAVEPOINT / RELEASE / ROLLBACK TO	Supported
READ COMMITTED isolation	Supported — statement-level snapshots, as in PostgreSQL. Default level.
REPEATABLE READ isolation	Supported — transaction-level snapshot
SERIALIZABLE isolation	Downgraded to REPEATABLE READ, see note below
READ UNCOMMITTED isolation	Supported — behaves as READ COMMITTED, as in PostgreSQL
SET LOCAL (transaction-scoped settings)	Supported
READ ONLY transactions	Supported — DML and DDL writes are rejected with SQLSTATE 25006
DEFERRABLE transactions	Not supported (requires SERIALIZABLE)
Advisory locks (pg_advisory_lock family)	Supported (node-local; not coordinated across multiple db9-server processes)

SERIALIZABLE is silently downgraded

A READ COMMITTED transaction takes a fresh snapshot per statement and sees concurrent commits, as in PostgreSQL. A REPEATABLE READ transaction holds one snapshot for its lifetime, taken when the transaction starts (PostgreSQL takes it at the first query). Some write conflicts surface at COMMIT as 40001 instead of at the statement: under REPEATABLE READ, an UPDATE, DELETE or SELECT ... FOR UPDATE on a row another transaction changed after the snapshot, and an INSERT ... ON CONFLICT DO UPDATE on a row it updated or a key it inserted. Other cases differ in outcome. Under REPEATABLE READ, ON CONFLICT DO NOTHING against a row updated or deleted after the snapshot raises no error and inserts nothing, and ON CONFLICT DO UPDATE against a row deleted after the snapshot, or a plain INSERT of its key, inserts the row and then fails at COMMIT with 40001 where PostgreSQL commits it. Under READ COMMITTED, ON CONFLICT DO NOTHING does not wait for another transaction’s uncommitted UPDATE or DELETE of the conflicting row, so it inserts nothing even when that DELETE commits; PostgreSQL waits and inserts. A plain INSERT of a key another transaction inserted fails with 23505 at the statement, as in PostgreSQL. See Transactions.

SERIALIZABLE is not implemented. TiKV provides snapshot isolation, not PostgreSQL’s serializable snapshot isolation (SSI), so anomalies that only SSI prevents — write skew in particular — are not detected. What happens when you ask for it depends on how you connect:

SQL

-- PostgreSQL wire protocol (psql, ORMs, any pg driver): accepted with a warning
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ
SHOW transaction_isolation;  -- repeatable read — the level actually in effect

SHOW transaction_isolation reports the level DB9 applied, not the one you asked for, so reading it back is a reliable runtime check for the downgrade. The same holds for SET TRANSACTION ISOLATION LEVEL SERIALIZABLE inside an open transaction.

Over the HTTP SQL API (as used by db9 db sql), the same request is rejected outright instead:

ERROR: SERIALIZABLE isolation level is not supported. Use REPEATABLE READ or READ COMMITTED instead.

If your application depends on true serializability, enforce it yourself: take SELECT ... FOR UPDATE locks on rows that the conflicting transactions also update (the table needs a primary key — see Row locks), or add a unique constraint that makes the conflicting write fail. At the downgraded level the transaction that loses a SELECT ... FOR UPDATE race fails at COMMIT with 40001 rather than at the statement, so retry on that error at COMMIT as well as at the statement. A unique-constraint conflict fails the INSERT with 23505, as in PostgreSQL. A row that is only locked and never changed raises no conflict at this level, as in PostgreSQL.

Data Types

Supported

Type	Aliases	Notes
BOOLEAN	BOOL
SMALLINT	INT2	Stored as INT4 internally
INTEGER	INT, INT4
BIGINT	INT8
REAL	FLOAT4	Stored as FLOAT8 internally
DOUBLE PRECISION	FLOAT8
NUMERIC	DECIMAL	With precision and scale. A declared precision up to 1000 is accepted, but a value holds at most 28–29 significant digits (largest magnitude 79228162514264337593543950335) and 28 decimal places: a larger literal fails with 22P02, arithmetic beyond it with 22003, and decimal places beyond that capacity are rounded off without an error. 'NaN' and 'Infinity' are rejected (22P02). A declared precision and scale are enforced as in PostgreSQL — NUMERIC(5,2) rejects 1234.5 with 22003 (numeric field overflow) and rounds 12.345 to 12.35. See Data types
TEXT		Variable-length, no limit
VARCHAR(n)	CHARACTER VARYING
CHAR(n)	CHARACTER	Stored as VARCHAR internally
BYTEA		Binary data
DATE
TIME		Without time zone
TIMETZ	TIME WITH TIME ZONE	Basic support — store, compare, ORDER BY, index, min/max, and COPY. Input needs a numeric UTC offset written as ±HH or ±HH:MM (12:34:56+02, 08:00-05:30); a value with no offset (12:34), a +0530 offset or a zone abbreviation (12:34 PST) fails with 22P02, and a DEFAULT CURRENT_TIME or DEFAULT now() column default fails with 42804. Casts to or from TIME and TIMESTAMPTZ (42846), +/- with an INTERVAL and AT TIME ZONE (42883) are not available, and EXTRACT returns NULL for every field, see EXTRACT
TIMESTAMP		Without time zone, microsecond precision
TIMESTAMPTZ		With time zone, microsecond precision
INTERVAL
JSON		Stored as text
JSONB		Canonicalized (sorted keys, normalized whitespace)
UUID
INET		IPv4/IPv6 host or network address; equality and ordering only — no network operators/functions, no CIDR
BOOLEAN[] / INT[] / TEXT[] / etc.		Arrays of any supported type; multi-dimensional values are stored and read back — see below
BIT(n) / VARBIT(n)	BIT VARYING	Length and content validated as in PostgreSQL; BIT(1) and bare BIT become BOOLEAN — see below
TSVECTOR		Full-text search document representation
TSQUERY		Full-text search query
VECTOR(n)		pgvector-compatible; for HNSW indexes and distance operators
NAME		PostgreSQL identifier type

Not Supported

Type	Notes
XML
CIDR / MACADDR	Network address types; INET is supported (equality/ordering only)
Range types (INT4RANGE, TSRANGE, etc.)	INT4RANGE is accepted but only partly implemented — see the caution below. INT8RANGE, NUMRANGE, DATERANGE, TSRANGE, and TSTZRANGE do not exist
Composite types (user-defined row types)	Declarable and writable via a text literal, but unreadable field-wise — see the caution below. Enum types are supported via CREATE TYPE, but sort by label text rather than declaration order, see Advanced SQL — Custom Types
Large objects (OID-based streaming)	BYTEA is available for binary data
Money

INT4RANGE and composite types pass CREATE TABLE but are only partly usable

Most rows above fail loudly at CREATE TABLE: XML, MONEY, CIDR, MACADDR, and every range type except INT4RANGE are rejected with type "..." does not exist (42704).

INT4RANGE and composite types have no such safeguard. The DDL succeeds, so the gaps surface later.

INT4RANGE — this type does exist. It validates its input, round-trips correctly, and the int4range(lo, hi) constructor, the overlap operator &&, and the containment operators @> and <@ (against an element or another range) work as expected. The accessor functions do not exist: lower(), upper() and isempty() all fail with 42883:

SQL

SELECT lower('[1,10)'::int4range);  -- ERROR (42883)   PostgreSQL returns 1
SELECT upper('[1,10)'::int4range);  -- ERROR (42883)   PostgreSQL returns 10

If you need the bounds in SQL, store them as two plain columns (lo, hi) instead.

Composite types — CREATE TYPE ... AS (...) and a column of that type both succeed. Only the ROW(...) constructor is rejected (42804); a composite text literal — the form pg_dump emits — is accepted without any validation of shape, arity, or content, and the value can then never be read back field-wise:

SQL

CREATE TYPE ct AS (a TEXT, b INT);
CREATE TABLE ctab (id INT PRIMARY KEY, c ct);
INSERT INTO ctab VALUES (1, ROW('x', 1));          -- ERROR (42804)
INSERT INTO ctab VALUES (2, '("x",1)');            -- accepted
INSERT INTO ctab VALUES (3, 'zzz not composite');  -- accepted
INSERT INTO ctab VALUES (4, '(1,2,3,4,5)');        -- accepted, wrong arity
SELECT (c).a FROM ctab;                            -- ERROR: internal error (XX000)

Flatten composite columns into ordinary scalar columns before migrating.

Multi-dimensional arrays and bit strings

Both of these were previously listed as unsupported and are now in the supported table above. Each is usable, with the divergences noted here:

BIT / VARBIT — length and content are both validated. BIT(n) rejects any value that is not exactly n bits with 22026 bit string length N does not match type bit(n); VARBIT(n) leaves a short value alone and rejects an over-length one with 22001 bit string too long for type bit varying(n); a value that is not 0s and 1s is rejected with 22P02. PostgreSQL 16 raises the same SQLSTATEs, and the 22026 and 22001 messages are identical to DB9’s; only the 22P02 text differs (PostgreSQL says "h" is not a valid binary digit). All four spellings parse — BIT(n), VARBIT(n), bare BIT VARYING and BIT VARYING(n) — so pg_dump output loads unchanged, and B'1010' literals are accepted. information_schema.columns, pg_typeof() and format_type() all agree on the type name and v::text renders the bit string — though all three drop the length modifier (bit, not bit(8), and character_maximum_length is NULL) even though the length is enforced on write.

BIT(1) and bare BIT are the one divergence left: both are created as BOOLEAN, where PostgreSQL keeps them as bit(1). They round-trip '1'/'0' as true/false and reject anything longer with 22P02 invalid input syntax for type boolean, so single-flag columns migrate cleanly but report as boolean in the catalog. One consequence: a B'1' bit-string literal, as pg_dump --inserts emits, is rejected for those two with 42804 column "v" is of type boolean but expression is of type bit(1) — plain pg_dump, whose COPY data is 1/0, loads fine. B'1010' into a wider BIT(n) is accepted.

Multi-dimensional arrays — an INT[][] column stores and returns them. As in PostgreSQL the column is catalogued as integer[] (PostgreSQL does not record dimensionality either), and one-, two- and three-dimensional values all round-trip. Subscripting (v[1][1]), array_length(v, n) per dimension, unnest() (which flattens to scalars), and the rejection of ragged input all match PostgreSQL, though the two ragged forms are reported differently: a literal ('{{1,2},{3}}', the form pg_dump emits) gives 22P02 invalid input syntax for type array against PostgreSQL’s 22P02 malformed array literal, while the ARRAY[...] constructor gives 2202E multidimensional arrays must have array expressions with matching dimensions — the same SQLSTATE and message as PostgreSQL, plus a DB9-only type suffix. array_ndims() and array_dims() are still missing — both fail with 42883 in either call position.

Indexes

Type	Status	Notes
B-tree	Near-full	Default. Point, range, bounded-range, in-list, expression, partial indexes. One divergence: an index entry must encode to under ~6,963 bytes, so a long value that PostgreSQL would compress into the index is rejected with 54000 — see CREATE INDEX
GIN	Full	JSONB containment (@>), full-text search (@@), array operators. Only TSVECTOR, JSONB and array columns are indexable — any other column type fails with 42704, as in PostgreSQL. Used for a literal or bound-parameter probe — for @@, a tsquery value or a *_tsquery() call that names its configuration; a probe computed by an expression falls back to a sequential scan. See below
HNSW	Near-full	Approximate k-NN over VECTOR columns. Builds on both transports and the planner uses it — for a literal or bound-parameter probe with a LIMIT and no WHERE clause; any filter falls back to an exact scan, and an inline embedding(...) probe runs once per row, failing with 22023 once the table has more than 100 rows. The indexed column must declare a width (VECTOR(1024), not bare VECTOR) and the table needs a single-column primary key — see the note below
Hash	Partial	Equality lookups on integer and OID columns. Single-column, non-unique indexes only — see below
GiST	Not supported	CREATE INDEX is rejected
SP-GiST	Not supported	CREATE INDEX is rejected
BRIN	Not supported	CREATE INDEX is rejected

btree, hash, gin, and hnsw are the only usable access methods, and pg_am lists exactly those four plus heap. An unusable method is always rejected at CREATE INDEX time — never accepted-then-ignored — but it comes back as one of two different errors, and which one tells you whether the method is merely unsupported or entirely unknown:

-- gist, spgist, brin — named and rejected
ERROR: access method "gist" is not supported (0A000)
HINT:  Only btree, hash, gin, and hnsw indexes are currently supported.

-- anything else, including ivfflat — not a known name at all
ERROR: access method "ivfflat" does not exist (42704)

So GiST, SP-GiST and BRIN give 0A000 with the HINT above, while every other spelling gives 42704 — despite none of the three appearing in pg_am. IVFFlat falls in the second group: HNSW is the only vector index type.

A hash index is built and used for equality — EXPLAIN SELECT * FROM t WHERE n = 42 reports Index Scan using <index> on t — and, as in PostgreSQL, is ignored for range predicates. It carries three DDL-time restrictions beyond PostgreSQL’s, each a distinct 0A000:

CREATE INDEX ON t USING hash (name);        -- name is TEXT
ERROR: hash indexes currently support only integer and OID columns (0A000)

CREATE UNIQUE INDEX ON t USING hash (id);
ERROR: access method "hash" does not support unique indexes (0A000)

CREATE INDEX ON t USING hash (a, b);
ERROR: access method "hash" does not support multicolumn indexes (0A000)

SMALLINT, INTEGER, BIGINT and OID columns are all indexable; TEXT and BOOLEAN are not. Use a B-tree for everything else — it covers equality as well, so hash is a narrowing choice, not a strictly better one.

A fourth divergence has no error to announce it. The hash index answers a single-value equality predicate (n = 42, n IN (42)), but a multi-element IN list falls back to a sequential scan — a btree index on the same column still produces an Index Scan, and PostgreSQL 16 serves the same predicate from its hash index via a bitmap scan. Results stay correct; only the plan degrades.

A GIN index can only be built over a column type that has a GIN operator class — TSVECTOR, JSONB, and array types such as TEXT[] or INT[]. On any other column type — TEXT, JSON, INTEGER — the statement fails as it does in PostgreSQL:

CREATE INDEX ON docs USING gin (body);     -- body is TEXT
ERROR: data type text has no default operator class for access method "gin"   (42704)
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

There is no pg_trgm, so GIN cannot accelerate LIKE on a TEXT column; use full-text search over a TSVECTOR column instead.

HNSW indexes carry extra structural requirements: the indexed column must declare its width (VECTOR(1024), not bare VECTOR), the table needs a single-column primary key (a table with no primary key or a composite one fails with 0A000 HNSW indexes require a single-column primary key), and an INTEGER/BIGINT primary key must hold only non-negative values (a negative one at build time gives 22023). UUID and TEXT primary keys are fine. Partial (WHERE) and multi-column HNSW indexes are rejected (0A000). Building over a bare VECTOR column that holds rows drops the connection rather than returning an error — see Vector Search.

Queries fall back to an exact sequential scan when no usable index exists, so all distance operators (<->, <=>, <#>) and functions return correct results either way. See pgvector for details.

GIN indexes on JSONB columns are functional for containment queries (@>), and — as with HNSW — for a literal or a driver-bound parameter alike. A probe computed by an expression is not recognised: @> against jsonb_build_object(...) plans as a Seq Scan with KV Table Scan Pairs equal to the row count, exactly as if the index were not there, and the same holds for array containment against ARRAY[lower(...)] or a subquery — and against ARRAY['t7']::text[], although ARRAY['t7'] and '{t7}'::text[] are indexed. Full-text has one extra trap: @@ is indexed against a tsquery value or a to_tsquery / plainto_tsquery / websearch_to_tsquery call that names its configuration as a literal — a call that omits the configuration or binds it as a parameter is not recognised as a probe, see Full-Text Search. Results stay correct either way, so nothing signals the fallback — check EXPLAIN ANALYZE rather than assuming the index is in play.

Wire Protocol

Feature	Status
pgwire v3	Supported
Simple Query (text)	Supported
Extended Query (Parse, Bind, Describe, Execute)	Supported
Binary parameter encoding	Supported
COPY (CSV, TEXT)	Supported for the COPY <table> [(columns)] form (BINARY format not supported); COPY (SELECT ...) TO STDOUT is rejected
Prepared statements	Supported
Portals	Supported
Multiple result sets	Supported
SCRAM-SHA-256 authentication	Supported (at pgwire layer)
LISTEN / NOTIFY	Supported (notifications are delivered on commit to sessions holding an open connection; LISTEN requires a persistent pgwire session, so the stateless HTTP SQL API can send NOTIFY but cannot receive)
Logical replication protocol	Partial — IDENTIFY_SYSTEM, CREATE_REPLICATION_SLOT ... LOGICAL, START_REPLICATION (for a pgoutput slot) and DROP_REPLICATION_SLOT work; physical slots are rejected, and pg_recvlogical fails before it streams anything. See Logical decoding
Streaming (physical) replication	Not supported

Functions

DB9 implements 200+ built-in functions across these categories:

Category	Examples
String	upper, lower, concat, substring, replace, trim, split_part, regexp_match, format
Math	abs, ceil, floor, round, sqrt, power, log, random, trunc, trig functions
Date/Time	now, date_trunc, date_part, extract, age, to_char (a fixed set of temporal patterns only — see below), to_timestamp (epoch argument only), make_date
Aggregate	count, sum, avg, min, max, string_agg, array_agg, json_agg, bool_and/or
Window	row_number, rank, dense_rank, lag, lead, first_value, last_value, ntile
JSON/JSONB	jsonb_build_object, jsonb_set, jsonb_extract_path, jsonb_array_elements, jsonb_each, jsonb_typeof, to_jsonb, row_to_json
Array	array_length, array_agg, unnest, array_append, array_cat, array_position, string_to_array
Full-text search	to_tsvector, to_tsquery, plainto_tsquery, ts_rank, ts_headline, setweight
UUID	gen_random_uuid, uuid_generate_v4, uuidv7 — the other uuid-ossp generators (uuid_generate_v1, v1mc, v3, v5) and constants (uuid_nil, uuid_ns_*) fail with 42883
Type conversion	cast, :: casts, to_char (formatting only), to_timestamp(epoch), to_hex, to_json/to_jsonb
Conditional	coalesce, nullif, greatest, least
HTTP (scalar)	http_get, http_post, http_put, http_delete, http_head, http_patch and the generic http(method, url, ...), returning JSONB
Document chunking	CHUNK_TEXT — table-valued function for RAG pipelines
Storage	db9_refresh_storage_stats — trigger storage scan
System	current_user, current_database, current_schema, pg_typeof, version

No string-to-value parsing functions; to_char interprets only a few temporal patterns

TO_DATE(text, text), TO_NUMBER(text, text) and the two-argument TO_TIMESTAMP(text, text) are not available — each fails with ERROR: function ... does not exist (42883). to_date and to_number do appear in pg_proc, but those rows are shims with no implementation. Parse with casts instead: '2026-09-25'::date, '2026-09-25 14:11:12'::timestamp, '1234.56'::numeric. Only the one-argument TO_TIMESTAMP(epoch) exists.

In the other direction, to_char works — its numeric formats match PostgreSQL, including FM, group separators and PR — but for temporal values it substitutes only YYYY, MM, DD, HH24, MI, SS and MS. Any other pattern is left uninterpreted without an error, so output is silently wrong: HH12, AM, MON and Month are copied through as letters, while DDD and SSSS are partly matched and yield wrong numbers. See SQL Functions — TO_CHAR for the full pattern list.

JSON/JSONB Operators

Operator	Description	Status
->	Get JSON object field by key (returns JSON)	Supported
->>	Get JSON object field by key (returns text)	Supported
#>	Get element by path (returns JSON)	Supported
#>>	Get element by path (returns text)	Supported
@>	Contains	Supported
<@	Contained by	Supported
?	Key exists	Supported
?|	Any key exists	Supported
?&	All keys exist	Supported
||	Concatenate	Supported
-	Delete key	Supported
#-	Delete path	Supported

Cast a bound key for ?|, ?& and jsonb_extract_path()

Every operator above works with a literal. With an untyped bind parameter — the shape most drivers emit from a raw parameterised query — ->, ->>, #>, #>>, #-, @>, <@, ||, ? and - (delete key) infer the parameter’s type from the operator, as PostgreSQL does. The any/all key-existence operators ?| and ?& do not, and fail where PostgreSQL resolves the parameter:

SQL

SELECT data ? $1 FROM t;            -- works
SELECT data ?| $1 FROM t;           -- 42P18  could not determine data type of parameter $1
SELECT data ?| $1::text[] FROM t;   -- works

Add an explicit cast, which is also valid PostgreSQL, so the same query works against both:

Operator	Untyped $1	Cast to use
?| ?&	42P18	$1::text[]

jsonb_extract_path() and jsonb_extract_path_text() fail the same way (42P18) on an untyped key argument and take a $1::text cast.

Use the cast that matches the operator’s signature. A wrong cast is rejected with 42883, as in PostgreSQL — data #> $1::text and data ->> $1::text[] both are — with one exception: data ? $1::text[] returns false with no error, where PostgreSQL raises 42883.

A parameter on the value side is unaffected — WHERE data->>'name' = $1 binds normally.

Whether you hit this depends on how your driver sends parameters. Verified against DB9:

Client	Bound key for jsonb_extract_path_text() without a cast
pg (node-postgres), Knex, Drizzle	fails — sends the parameter untyped
Sequelize bind	fails — sends the parameter untyped
Sequelize replacements	unaffected — inlined as literals, never a parameter
Prisma on its Rust query engine (default through v6)	unaffected — types every parameter
Prisma v7, or any version using a driver adapter	fails — delegates to pg

The cast is harmless everywhere, so it is the portable choice if you may switch drivers.

PL/pgSQL

Feature	Status
DECLARE / BEGIN / END blocks	Supported
Variable assignment (:=)	Supported
SELECT INTO	Supported
IF / THEN / ELSIF / ELSE / END IF	Supported
FOR loops (query iteration and integer range)	Supported
PERFORM (execute without result)	Supported
EXIT (loop termination, plain and EXIT WHEN <condition>)	Supported
CONTINUE (plain and CONTINUE WHEN <condition>)	Supported
RAISE (NOTICE, WARNING, ERROR)	Supported
RETURN / RETURN NEXT / RETURN QUERY	Supported
RETURNS TABLE syntax	Supported
CASE statements (in PL/pgSQL)	Supported
WHILE loops	Not supported
FOREACH … IN ARRAY	Not supported — 42601 (syntax error at or near "FOREACH")
Cursor operations (FOR…IN CURSOR)	Not supported
Exception handling (BEGIN…EXCEPTION)	Supported, in both a CREATE FUNCTION body and a DO block
Dynamic SQL (EXECUTE, including INTO and USING)	Supported, in both a CREATE FUNCTION body and a DO block
Nested BEGIN…END blocks	Supported, in both hosts — a nested block may not open its own DECLARE section (42601)

PL/pgSQL host differences

A CREATE FUNCTION body and a DO block now support the same feature set, and what one host rejects the other rejects the same way:

Feature	CREATE FUNCTION body	DO block
CASE statements	Supported	Supported
Exception handling (BEGIN ... EXCEPTION)	Supported	Supported
Dynamic SQL (EXECUTE, including INTO and USING)	Supported	Supported
Nested BEGIN ... END blocks	Supported	Supported
A nested block with its own DECLARE section	42601	42601
WHILE and FOREACH loops	42601	42601
Cursor operations	42601 / 42704	42601 / 42704

EXECUTE takes a literal, a variable, or an expression as its command string, and INTO and USING behave as in PostgreSQL — EXECUTE 'SELECT $1 * 2' INTO n USING p works in both hosts.

Only the outermost block may declare variables. A nested block that opens its own DECLARE section is rejected with 42601 (syntax error at or near "<type>", naming the declared type) in both hosts; hoist those declarations to the top-level DECLARE, which is where PL/pgSQL scoping puts them for the whole routine anyway.

The WHILE and FOREACH errors are raised when the function is called; CREATE FUNCTION itself succeeds. Cursors fail earlier, when the cursor is declared — c CURSOR FOR ... with 42601 (unexpected token in PL/pgSQL type declaration: FOR) and c refcursor with 42704 (type "refcursor" does not exist) — so a function that declares one is never created.

Exception handlers follow PostgreSQL rollback semantics in both hosts: statements that ran before the exception are rolled back, and only the handler’s effects persist.

Multi-line DECLARE entries parse correctly in both hosts: a declaration whose type or value spans several lines yields the same value as PostgreSQL (v int := 1 / + 2; is 3, and v int / := 42; is 42).

Other procedural languages (PL/Python, PL/Perl, PL/v8) are not supported. CREATE FUNCTION with LANGUAGE plpythonu, plperl, plv8, c or any other language DB9 does not have fails with 42704 (language "plpythonu" does not exist) and nothing is added to pg_proc — PostgreSQL raises the same error for a language that is not installed. LANGUAGE internal fails with 0A000 (user-defined routines in language "internal" are not supported). Port such functions to plpgsql or sql.

ORM and Driver Compatibility

The following full-suite results were measured on 2026-10-03 against server build 637464c98, running db9-server/orm-tests:

ORM / Driver	Version run	Result	Notes
node-postgres (pg)	8.16	104/104	Native pgwire client
TypeORM	0.3.28	202/202	None skipped
Knex.js	3.1	109/109	Full query builder, window functions, CTEs
Prisma	5.22	107/107	Binary wire protocol; $queryRaw for advanced SQL
Sequelize	6.37	104/104	Raw queries for window/CTE features
Drizzle	0.29	93/93	Type-safe queries; full query builder support
Kysely	0.28, 0.29.7	50/50	CRUD, DDL/transactions, prepared, JSON, vector
SQLAlchemy	2.0+	Tested	JSONB operators, RETURNING, transaction patterns
GORM (Go)	1.31	Tested	CRUD, transactions, foreign keys, AutoMigrate

Additional guide checks on 2026-10-20 used TypeORM 1.1.2, Prisma 6.19.3, Drizzle 0.45.4 (with drizzle-kit 0.31.11), and Sequelize 6.37.8. These covered the documented setup, CRUD and repeated schema-tool runs, including the failures described in each guide; they do not replace the historical full-suite counts above. Use the ORM guides for current examples.

These suites exercise queries at runtime. Schema tooling is a separate question, and it splits — see below.

ORM schema and migration tooling

The dividing line is creating a schema versus reading an existing one back. Creating works almost everywhere; introspection is where DB9 diverges, so several tools succeed once against an empty database and fail on every run after that.

Tool	Works	If not, why
Knex schema builder (createTable/alterTable/columnInfo)	Yes	—
TypeORM migration runner (runMigrations, undoLastMigration)	Yes	—
GORM AutoMigrate (including adding columns)	Yes	—
Sequelize sync() / sync({ force: true })	Yes, unless the model has an ENUM column	for a model with an ENUM column only the first sync() succeeds — see the sync({ alter: true }) row below; sync({ force: true }) keeps working
Kysely schema builder (createTable/alterTable/indexes)	Yes	—
prisma generate	Yes	needs no database connection
Alembic / Flask-Migrate upgrade, downgrade, current	Yes	—
Django makemigrations / migrate (including ALTER)	Yes	—
Django inspectdb	Yes	models are generated for your tables; the output also carries three # Unable to inspect table stubs for pg_cron’s tables, and CharField without max_length — see the Django guide
TypeORM synchronize: true	First run only	every second run drops the default of each generated id column, so inserts fail with 23502 until the next run restores it — DB9 reports the default as nextval(CAST('…' AS pg_catalog.regclass)), which TypeORM does not recognise as a serial. See the TypeORM guide
prisma db push	First run only, if a model has an @default(autoincrement()) id	succeeds on an empty schema. With lower-case table names (@@map), every later run fails with relation "<table>_id_seq" already exists and applies nothing; with Prisma’s default upper-case table names ("User"), the second run exits 0 but re-points id at a new sequence starting at 1, so inserts into a table with rows fail with 23505 — see the Prisma guide. DB9 reports the serial default as nextval(CAST('…' AS pg_catalog.regclass)), which Prisma does not recognise as its own sequence, so it plans to create the sequence again. Models whose ids use @default(uuid()) push repeatedly, including schema changes (measured with Prisma 6.19.3)
drizzle-kit push	First run only, into a schema with no tables	the first push into a schema that has no tables yet creates them. A push into a schema that already has tables — so every push after the first — fails and applies nothing: its introspection reads pg_stat_user_indexes for each existing table, and DB9 does not implement that view. drizzle-kit 0.31.11 exits with status 1 without printing an error; 0.31.8 prints error: relation "pg_stat_user_indexes" does not exist but exits with status 0, so a script that checks the exit code sees success. drizzle-kit generate followed by drizzle-kit migrate works, including a later ADD COLUMN migration (measured with drizzle-kit 0.31.11 and drizzle-orm 0.45.4)
Sequelize sync({ alter: true }), describeTable()	Yes, unless the model has an ENUM column	with an ENUM column these — and every plain sync() after the first — throw TypeError: text.replace is not a function: DB9 returns the enum’s labels as a text[], which Sequelize cannot parse. Only sync({ force: true }) keeps working
Kysely Migrator, introspection.getTables()	No	both fail with 42P01 relation "cron.job" does not exist: the introspection query calls pg_get_serial_sequence() for every table it lists, including pg_cron’s cron.job, and DB9 cannot resolve that call although the table itself is readable (measured with Kysely 0.29.6 and 0.29.7)
Alembic / Flask-Migrate revision --autogenerate	With a table filter	runs on psycopg2 and on psycopg (v3), but needs an include_name hook limiting it to your models’ tables, or every migration tries to drop pg_cron’s tables and those of your other schemas — see the SQLAlchemy guide
SQLAlchemy MetaData.reflect(), autoload_with	Yes	on both psycopg2 and psycopg (v3). An unfiltered reflect() also returns pg_cron’s tables and those of your other schemas; pass only=[...] to limit it
prisma db pull	Yes	an @default(autoincrement()) id reads back as @default(dbgenerated("nextval(CAST('public.<table>_id_seq' AS pg_catalog.regclass))")), for the same reason as the db push row (measured with Prisma 6.19.3)
prisma migrate dev	No	shadow database name contains hyphens, and DB9 rejects any database name containing a hyphen, dot or space with 42602 invalid database name (P3014). With a shadow database whose name has none of those, the first run succeeds and a later one goes wrong like db push: it fails with P3018 (relation "<table>_id_seq" already exists) for lower-case table names, and for upper-case table names it exits 0 and records a migration that re-points id at a new sequence

The blocker is not information_schema

It is natural to assume schema tooling fails because information_schema is thin. It is not: every information_schema view DB9 implements works, and the tools that lean on them — Knex’s schema builder and columnInfo() — work fine. (Of the tools that fail only from their second run, TypeORM’s synchronize and prisma db push trip on the spelling of a serial column’s default, not on a missing view; drizzle-kit push trips on the statistics view below.)

What the failing tools have in common is that each reaches past information_schema into pg_catalog for something DB9 does not implement:

• Statistics views. Only part of the pg_stat* family resolves. The _user_ and _sys_ index and I/O views — pg_stat_user_indexes, pg_statio_user_tables, pg_statio_user_indexes — are absent (42P01), although the _all_ variants (pg_stat_all_indexes, pg_statio_all_tables, pg_statio_all_indexes) exist.
• Namespace-visibility functions. pg_table_is_visible(), pg_type_is_visible(), pg_collation_is_visible() and pg_function_is_visible() exist in SELECT f(...) position only — all four report 42883 in SELECT * FROM f(...). pg_opclass_is_visible() is absent in both positions. pg_table_is_visible() also answers true for every table in every schema, whatever the search_path — the cron schema’s job, job_run_details and running_jobs, and tables in schemas you created — although an unqualified reference to one of them still fails with 42P01. A tool that lists “visible” tables, as SQLAlchemy and Alembic do, therefore sees them all as belonging to the default schema.
• Catalog column types. pg_enum.enumlabel is text rather than name. DB9 also records one row per NOT NULL constraint in pg_constraint with contype = 'n', which PostgreSQL does only from version 18 while DB9 reports server_version 17.0.
• Database names. CREATE DATABASE works, and the admin role is a superuser — but any name containing a hyphen, dot or space fails with invalid database name: <name> (42602). Quoting is not the trigger: "select" and "RvMixedCase" both succeed, and the mixed-case name keeps its case. This is what stops Prisma’s shadow database, whose name embeds a hyphenated UUID.

So “my ORM’s query layer works but its migration tool does not” is the expected shape on DB9, and the fix is per-tool rather than a single missing feature.

System Catalogs

DB9 implements 50+ virtual tables across pg_catalog, information_schema, and extension schemas.

pg_catalog

View	Status	Notes
pg_class	Supported	Relations (tables, views, indexes, sequences)
pg_attribute	Supported	Column definitions
pg_index	Supported	Index metadata
pg_constraint	Supported	Constraints (PK, FK, UNIQUE, CHECK)
pg_type	Supported	Data types
pg_proc	Supported	Functions and procedures
pg_namespace	Supported	Schemas
pg_roles / pg_user	Supported	Users and roles
pg_database	Supported	Database metadata
pg_sequence	Supported	Sequence state
pg_attrdef	Supported	Column defaults
pg_extension	Supported	Installed extensions
pg_am	Supported	Access methods
pg_trigger	Supported	Trigger definitions, including the tgtype bitmask and trigger arguments (tgnargs, tgargs)
pg_depend / pg_description	Supported	Object dependencies and comments
pg_settings	Supported	Server and db9.* runtime parameters; category is always NULL and short_desc is sparse
pg_stat_activity	Node-local	17 columns with live values (state, query, backend_start, wait events), but only for sessions on the server node your connection landed on — not a database-wide list
pg_stat_database	Partial, node-local	One row per database; for the connected database numbackends, transaction and tuple counters are populated — per server node, so two connections can read different totals; block, timing and session counters are NULL
pg_stat_user_tables / pg_stat_all_tables	Stub	One row per user table, but every counter is NULL
pg_stat_all_indexes / pg_statio_all_tables / pg_statio_all_indexes	Stub	Identifying columns only; every counter is NULL
pg_stat_replication	Node-local	A client streaming from a replication slot appears only to sessions on the server node it is connected to, so the view is usually empty; read pg_replication_slots.active instead
pg_stat_ssl	Node-local	One row per session on the server node your connection landed on, with that connection’s real TLS state (ssl, version, cipher, bits)
pg_stat_statements	Not available
pg_sequences	Supported	PostgreSQL 17’s columns, one row per sequence. last_value is NULL until the first nextval(), and with CACHE above 1 it can run ahead of the values handed out so far, as in PostgreSQL
pg_stat_user_indexes	Not available	drizzle-kit push reads this for each existing table, so every push after the first fails. The other _user_ and _sys_ index and I/O views (pg_statio_user_tables, pg_statio_user_indexes, pg_stat_sys_tables, …) are absent too
pg_publication	Supported	CREATE PUBLICATION p;, ... FOR TABLE t [, ...] and ... FOR TABLE ONLY t, with any WITH (publish = ...) value, succeed on both transports and persist here. The publish value is recorded but does not filter what a slot captures. FOR ALL TABLES and FOR TABLES IN SCHEMA are rejected with 0A000. See Logical decoding
pg_publication_rel	Supported	ALTER PUBLICATION ... ADD/DROP/SET TABLE records membership here, and membership decides which tables’ changes a slot captures — except the rows CREATE TABLE ... AS and SELECT ... INTO load, which a wal2json slot receives for a table in no publication, see Logical decoding
pg_publication_namespace	Stub (empty)	CREATE PUBLICATION ... FOR TABLES IN SCHEMA and ALTER PUBLICATION ... ADD TABLES IN SCHEMA are both rejected with 0A000, so this is never populated
pg_replication_slots	Supported	One row per logical slot. 19 columns, including conflicting plus the PostgreSQL 17 additions failover/synced (two_phase_at and inactive_since are absent). See Logical decoding
pg_subscription	Stub (empty)	18 columns — the PostgreSQL 16 set plus the PostgreSQL 17 addition subfailover. CREATE SUBSCRIPTION is not parsed at all (42601), so this is never populated; pg_subscription_rel does not exist

Logical decoding (CDC)

DB9 can be the source of a change stream: a logical replication slot captures the inserts, updates, deletes and truncates on published tables, and you read them either by polling SQL functions (wal2json slots) or by streaming over the replication protocol (pgoutput slots). DB9 cannot be a logical-replication subscriber — CREATE SUBSCRIPTION is not parsed (42601) — so you cannot replicate from another PostgreSQL server into DB9.

Creating a replication slot turns on Realtime capture for good

The first logical replication slot created in a database — through the SQL function or the replication protocol — turns on what DB9 calls Realtime capture for that database, and it stays on after the slot and its publication are dropped. From then on these statements fail:

• ALTER TABLE ... ADD PRIMARY KEY on a table that already holds rows, inside or outside a transaction — including the post-data step of a pg_dump import
• dropping the primary key of a table that holds rows — ALTER TABLE ... DROP CONSTRAINT <table>_pkey, or ALTER TABLE ... DROP COLUMN _rowid on a table made by CREATE TABLE ... AS or SELECT ... INTO
• ALTER TYPE ... RENAME VALUE for an enum value that stored rows hold

each with:

ERROR: unjournaled restore or physical row rewrite is not supported while Realtime capture is enabled   (55000)

A publication on its own does not turn it on. Load data and create keys before you create the first slot, or declare the primary key in CREATE TABLE (or add it while the table is empty). CREATE TABLE ... AS, SELECT ... INTO, CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW keep working while capture is on.

Slot creation can fail

Slot creation has been intermittent: the call that returns in under a second on one database has, on other databases and at other times, failed with XX000 internal error, run until the statement timeout, or reported 55000 logical replication is not provisioned for this database. The examples below require successful slot provisioning. Set a statement_timeout before you create a slot, check pg_replication_slots afterwards, and if the call failed, retry it later.

With the SQL functions, the round trip looks like this:

SQL

CREATE TABLE orders (id INT PRIMARY KEY, status TEXT);
CREATE PUBLICATION orders_pub FOR TABLE orders;
SELECT * FROM pg_create_logical_replication_slot('orders_slot', 'wal2json');

INSERT INTO orders VALUES (1, 'new');
UPDATE orders SET status = 'paid' WHERE id = 1;

A few seconds later, read the changes, then drop the slot when you no longer need it:

SQL

SELECT data FROM pg_logical_slot_get_changes('orders_slot', NULL, NULL, 'format-version', '2');

SELECT pg_drop_replication_slot('orders_slot');
DROP PUBLICATION orders_pub;

The pg_logical_slot_get_changes() call returns one row per change:

{"action":"I","columns":[{"name":"id","type":"integer","value":1},{"name":"status","type":"text","value":"new"}],"schema":"public","table":"orders"}
{"action":"U","columns":[{"name":"id","type":"integer","value":1},{"name":"status","type":"text","value":"paid"}],"identity":[{"name":"id","type":"integer","value":1}],"schema":"public","table":"orders"}

This works the same over the PostgreSQL wire protocol and the HTTP SQL API. What to expect:

• Only wal2json, only format-version 2. Each row has lsn, xid and data, and data is one wal2json version-2 change: action I, U, D or T (truncate), with columns for new values and identity for the replica-identity columns of an update or delete. No transaction begin/commit rows are emitted. pg_logical_slot_get_changes() consumes what it returns; pg_logical_slot_peek_changes() takes the same arguments and leaves it in the slot.
• Only tables in a publication when the change is written — and the rows CREATE TABLE ... AS loads. A write to a table that is in no publication is never emitted, even if the table is added later. The exception is the rows that CREATE TABLE ... AS or SELECT ... INTO loads into its new table: they are emitted as I changes, _rowid column included, even though the table is in no publication; later writes to that table follow the publication rule. Materialized views are never emitted. The publication’s publish option is not applied: with publish = 'insert', updates and deletes are captured too, so filter on action in the consumer if you need to.
• A delay of several seconds. A read straight after the write returns no rows; the changes appear a few seconds later.

The errors you can hit:

Call	Result
SELECT * FROM pg_create_logical_replication_slot(s, 'pgoutput')	22023 only the wal2json output plugin is available through SQL
SELECT * FROM pg_create_logical_replication_slot(s, 'test_decoding')	22023 output plugin "test_decoding" is not supported
SELECT * FROM pg_logical_slot_get_changes(s, NULL, NULL) (or ..._peek_changes)	22023 wal2json format-version 2 must be requested explicitly
SELECT * FROM pg_logical_slot_get_changes(s, NULL, NULL, 'format-version', '1')	22023 only wal2json format-version 2 is supported
SELECT pg_drop_replication_slot(s) for a slot that does not exist	42704 replication slot "<s>" does not exist on a provisioned database; 55000 logical replication is not provisioned for this database if provisioning has not completed

Each slot function resolves in only one call position and reports 42883 does not exist in the other:

Function	SELECT f(...)	SELECT * FROM f(...)
pg_create_logical_replication_slot(s, plugin)	42883 does not exist	works
pg_logical_slot_get_changes(s, ...)	42883 does not exist	works
pg_logical_slot_peek_changes(s, ...)	42883 does not exist	works
pg_drop_replication_slot(s)	works	42883 does not exist
pg_create_physical_replication_slot(...)	42883	42883 — genuinely absent

PostgreSQL accepts either position for all of these, so the idiomatic SELECT pg_create_logical_replication_slot('s', 'wal2json') is exactly the spelling that misleadingly answers “does not exist”. Which position works is per-function and not predictable from whether the function is set-returning; separately, most scalar functions cannot be called in FROM position at all — see call position.

Over the replication protocol, a connection made with replication=database is accepted. IDENTIFY_SYSTEM answers with a system identifier, timeline and LSN; CREATE_REPLICATION_SLOT <name> LOGICAL pgoutput creates a slot that START_REPLICATION streams, and DROP_REPLICATION_SLOT removes it. Measured with psycopg2’s LogicalReplicationConnection and the options proto_version '1' and publication_names, inserts, updates and deletes arrived as begin / relation / change / commit messages within about a second. A wal2json slot cannot be streamed (55000 replication slot "<name>" does not use pgoutput), and a pgoutput slot cannot be read with the SQL functions (55000 replication slot "<name>" does not use wal2json). Physical slots are rejected. pg_recvlogical fails before it streams anything, at SHOW data_directory_mode (unrecognized configuration parameter "data_directory_mode"), so use a client library’s replication API. Right after a streaming client disconnects, the slot can still be reported as active, and dropping it fails with 55006 until it is released; retry the drop a little later.

pg_stat_replication is node-local: a streaming client appears only to sessions on the server node it is connected to, so the view is usually empty; read pg_replication_slots.active instead.

ALTER PUBLICATION ... ADD TABLES IN SCHEMA is rejected with 0A000 only ALTER PUBLICATION table membership is supported, and two CREATE PUBLICATION shapes are syntax errors:

CREATE PUBLICATION p FOR TABLE t (id);           -- column list
ERROR: syntax error at or near "(id)"  (42601)

CREATE PUBLICATION p FOR TABLE t WHERE (id > 0); -- row filter
ERROR: syntax error at or near "WHERE"  (42601)

ALTER TABLE ... REPLICA IDENTITY is accepted — DEFAULT, FULL, NOTHING and USING INDEX are all recorded in pg_class.relreplident — and enforced as in PostgreSQL. With FULL, the identity of an update carries every old column value. On a table that belongs to a publication, REPLICA IDENTITY NOTHING makes UPDATE and DELETE fail:

ERROR: cannot update table "public.t" because it does not have a replica identity and publishes updates   (55000)

INSERT still works, as do UPDATE and DELETE once the table is removed from the publication or its identity is set back to DEFAULT or FULL. A table that is in no publication is unaffected by NOTHING. USING INDEX does not accept the table’s primary-key index (42704 index "<table>_pkey" for table does not exist); name a separately created unique index instead.

A dump that carries publications restores unchanged. The sequence pg_dump emits for a published, RLS-protected table —

SQL

CREATE TABLE t (id integer NOT NULL, v text);
ALTER TABLE t ENABLE ROW LEVEL SECURITY;
ALTER TABLE ONLY t FORCE ROW LEVEL SECURITY;
CREATE PUBLICATION p WITH (publish = 'insert, update, delete, truncate');
ALTER PUBLICATION p ADD TABLE ONLY t;

— runs clean end to end. What needs checking before you cut over is each consumer of those publications, against the behaviour above: polling a wal2json slot through SQL, and streaming a pgoutput slot with proto_version '1', work; pg_recvlogical, test_decoding and physical slots do not, and DB9 cannot subscribe to another server. Creating the first slot also turns on Realtime capture, with the restrictions in the caution above.

information_schema

View	Status
columns	Supported
tables	Supported
schemata	Supported
table_constraints	Supported
key_column_usage	Supported
referential_constraints	Supported
check_constraints	Supported
constraint_column_usage	Supported
table_privileges	Supported
sequences	Supported
routines	Supported
triggers	Supported
views	Supported

The views above are the complete information_schema surface in DB9.

Extensions

DB9 includes 11 built-in extension descriptors — 8 functional extensions plus hstore, pgcrypto and plpgsql, which are metadata-only shims. None of the three registers a function of its own, and the hstore type that hstore adds to pg_type cannot be used, though PL/pgSQL itself is compiled into the server and works with or without CREATE EXTENSION plpgsql. Custom or third-party extensions cannot be installed.

Extension	Version	Default	Description
http	1.0.0	Yes	HTTP client (GET, POST, PUT, DELETE, PATCH, HEAD)
pg_cron	1.0.0	Yes	Job scheduler with cron expressions. Scheduling is disabled in the current release — cron.schedule() returns feature "cron" is unavailable (PreActivationSeal) (55000). Catalog views and cron.unschedule / cron.cancel resolve. See pg_cron
fs9	1.0.0	No	File system operations (read, write, list, glob)
vector	0.8.1	No	pgvector-compatible vector type and HNSW indexes
embedding	1.0.0	No	Built-in text embedding generation
uuid-ossp	1.1	No	uuid_generate_v4() only — a built-in, so it works with or without CREATE EXTENSION. uuid_generate_v1(), uuid_generate_v1mc(), uuid_generate_v3(), uuid_generate_v5(), uuid_nil() and uuid_ns_dns() / uuid_ns_url() fail with 42883
hstore	1.0	No	Metadata shim only — DB9 does not implement hstore values, so any use of the type fails with 0A000 (type "hstore" is not supported), installed or not; use JSONB. While it is registered, pg_dump cannot dump the database. See hstore
parquet	1.0.0	No	Parquet file import
zhparser	2.0.0	No	Chinese full-text search tokenizer
pgcrypto	1.3	No	Metadata shim only — registers no functions (see below)
plpgsql	1.0	No	Metadata shim; PL/pgSQL itself is compiled in

CREATE EXTENSION pgcrypto is accepted so Supabase and ORM bootstrap scripts run unchanged, but it provides no functions. crypt(), gen_salt(), hmac(), encrypt()/decrypt(), pgp_sym_encrypt() and gen_random_bytes() all fail with 42883. gen_random_uuid() and digest() work, but they are DB9 built-ins and need no extension.

Extensions not available: PostGIS, pg_partman, pg_stat_statements, pg_trgm, ltree, citext, and all other PostgreSQL contrib extensions. These raise 42704 extension is not available at CREATE EXTENSION time.

Not Supported

These PostgreSQL features are not available in DB9:

Feature	Category
MERGE (0A000 MERGE is not supported) — write upserts with INSERT ... ON CONFLICT	DML
LIST and HASH partitioning, and RANGE partitioning on any key other than timestamp / timestamptz	DDL
Table inheritance	DDL
Foreign data wrappers (FDW)	DDL
Tablespaces	DDL
Rules (CREATE RULE)	DDL
Logical replication into DB9 (CREATE SUBSCRIPTION is unparsed). DB9 can be a source — see Logical decoding	Replication
Physical (streaming) replication and physical replication slots	Replication
SERIALIZABLE isolation (true serializable)	Transactions
DEFERRABLE transactions	Transactions
Large objects (OID-based)	Data
XML type	Data
Network types (CIDR, MACADDR)	Data
Range types (except a partial INT4RANGE)	Data
PL/Python, PL/Perl, PL/v8	Languages
PL/pgSQL WHILE and FOREACH loops, and cursors	Languages
Custom extensions	Extensions
pg_dump of a database that has a partitioned table or a registered hstore extension, and parallel pg_dump -j (pg_export_snapshot() does not exist). Other databases dump serially, in every format, with pg_dump 17 or newer and -N extensions — see the rollback plan for the flags and for vector columns	Tools
pg_basebackup	Tools

Validation

You can verify compatibility for your specific use case:

SQL

-- List the types the engine knows about. This is NOT the list of types you can
-- declare a column with. Many of the names it returns are rejected by
-- CREATE TABLE: every array alias (`_int4`, `_text`, ...), the pseudo-types
-- (`any`, `anyarray`, `record`, `trigger`, `cstring`, `void`, ...), several
-- internal types (`xid`, `cid`, `tid`, `pg_lsn`, `pg_node_tree`), and `xml`,
-- which is listed here but not supported at all. Membership is not a
-- reliable signal in either direction — some equally internal-looking names
-- (`oid`, `regclass`, `aclitem`, even the pseudo-type `anyelement`) ARE
-- accepted. Use the Data Types tables above as the authoritative list, and
-- confirm any individual type with a throwaway CREATE TABLE.
SELECT typname FROM pg_type WHERE typnamespace = 11 ORDER BY typname;

-- Check available extensions
SELECT * FROM pg_extension;

-- Check installed functions ('f' = function, 'a' = aggregate, 'w' = window).
-- Note: pg_proc lists only catalog-registered functions. Many built-ins (UNNEST,
-- ARRAY_AGG, MIN, MAX, ...) run in the executor and never appear here — see /docs/sql/catalog/
SELECT proname, prokind FROM pg_proc WHERE pronamespace = 11 ORDER BY proname;

-- Check catalog coverage. pg_tables lists user schemas only, so query pg_class
-- to enumerate the pg_catalog relations:
SELECT n.nspname AS schema, c.relname AS catalog_table
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'pg_catalog'
ORDER BY c.relname;

-- Verify transaction isolation
SHOW transaction_isolation;  -- returns 'read committed' (the default)

-- SERIALIZABLE is not implemented. On the PostgreSQL wire protocol it is accepted
-- with a warning and downgraded; on the HTTP SQL API it returns an error.
-- SET TRANSACTION only takes effect inside a transaction block — issued standalone it
-- warns 25P01 "SET TRANSACTION can only be used in transaction blocks" and changes nothing:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ
SHOW transaction_isolation;  -- repeatable read
COMMIT;

Next Pages

• SQL Reference — detailed SQL syntax and function reference
• Architecture — how DB9’s SQL engine connects to TiKV storage
• Extensions — every built-in extension
• Limits and Quotas — operational limits and safety boundaries
• Production Checklist — evaluate compatibility gaps before going live
• Migrate from Neon — step-by-step Neon to DB9 migration
• Migrate from PostgreSQL — general PostgreSQL migration path