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
SQL DDL (CREATE, ALTER, DROP)	Near-full	No partitioning, 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, GIN and HNSW	GiST/Hash/SP-GiST/BRIN are rejected
Transactions	Full	READ COMMITTED and REPEATABLE READ enforced; 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	9 built-in	http, fs9, pg_cron, vector, embedding, uuid-ossp, hstore, parquet, zhparser (plus pgcrypto and plpgsql as metadata-only shims)
PL/pgSQL	Partial	Basics supported, including CONTINUE and dynamic EXECUTE; exception handling and nested blocks require a DO block; no WHILE / FOREACH loops or cursors
Replication	None	No logical or streaming 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 a different case: PostgreSQL rejects it, DB9 accepts it and silently resolves to the last definition — see DML
Recursive CTEs (WITH RECURSIVE)	Supported, up to 1,000 iterations
Data-modifying CTEs (WITH w AS (INSERT ... RETURNING ...))	Supported over the PostgreSQL wire protocol. Over the HTTP SQL API the data-modifying CTE must be the first definition in the WITH list, otherwise the connection is dropped; see the caution below
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
UPDATE with WHERE, subqueries, RETURNING	Supported
DELETE with WHERE, subqueries, RETURNING	Supported
CASE expressions	Supported
Row-constructor comparisons ((a,b) = (c,d))	Supported — =, <>, <, <=, > and >= follow PostgreSQL’s NULL rules. Two forms do not: a row tested against a literal list with IN / NOT IN returns true/false where the result should be unknown, so a filter can return rows PostgreSQL excludes; and IS [NOT] DISTINCT FROM raises 42883 when a row holds a bare NULL literal opposite a typed value or column. 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

A data-modifying CTE must come first over the HTTP SQL API

Over the HTTP SQL API — which is what db9 db sql uses — a WITH list containing a data-modifying CTE (INSERT/UPDATE/DELETE … RETURNING) works only when that CTE is the first definition. If any other definition precedes it, the request fails with error: connection closed:

SQL

-- Fails over the HTTP SQL API
WITH src AS (SELECT id FROM staging WHERE ready),
     ins AS (INSERT INTO target SELECT id FROM src RETURNING id)
SELECT count(*) FROM ins;
-- error: connection closed

-- Works: the data-modifying CTE is first, with the helper inlined into it
WITH ins AS (INSERT INTO target SELECT id FROM staging WHERE ready RETURNING id)
SELECT count(*) FROM ins;

The statement is aborted, so no rows are written — but no SQLSTATE is returned either.

Note that simply moving the definition later is not a fix: a WITH definition cannot reference one declared after it, so ins would then fail with 42P01 relation "src" does not exist. Inline the helper into the data-modifying CTE as above, or run the statement over the PostgreSQL wire protocol (psql, ORMs, any pg driver), where every ordering works.

Two row-comparison forms still diverge on NULL

Row comparison operators follow PostgreSQL’s NULL rules: (1,NULL) = (1,2), (1,NULL) <> (1,2) and (1,NULL) < (1,3) are all NULL, as they should be. Testing a row against a literal list of rows with IN or NOT IN is the exception — DB9 resolves the undecided case to a definite answer instead of unknown:

Expression	PostgreSQL	DB9
(1,NULL) IN ((1,2))	NULL	false
(1,NULL) NOT IN ((1,2))	NULL	true
(1,NULL) IN ((1,NULL))	NULL	true
(1,NULL) IN ((2,3))	false	false
(1,NULL) NOT IN ((2,3))	true	true

A field pair that decides the comparison behaves correctly; only the undecided case diverges. So a NOT IN filter returns rows PostgreSQL excludes, with no error:

SQL

-- rows: (1, a=1, b=2), (2, a=1, b=NULL), (3, a=2, b=2)
SELECT id FROM rc WHERE (a,b) NOT IN ((1,2));     -- PostgreSQL: 3    DB9: 2, 3
SELECT id FROM rc WHERE NOT ((a,b) IN ((1,2)));   -- PostgreSQL: 3    DB9: 2, 3

Row 2 is returned because its NULL comparison resolves to a definite answer instead of unknown. Written as a subquery, as a <> comparison, or behind IS NOT NULL guards, the same test matches PostgreSQL:

SQL

SELECT id FROM rc WHERE (a,b) NOT IN (SELECT 1, 2);   -- 3, on both engines
SELECT id FROM rc WHERE (a,b) <> (1,2);               -- 3, on both engines
SELECT id FROM rc WHERE a IS NOT NULL AND b IS NOT NULL
  AND (a,b) NOT IN ((1,2));                           -- 3, on both engines

For a list of several rows, use a subquery (NOT IN (SELECT 1, 2 UNION ALL SELECT 9, 9)) or join <> comparisons with AND. A VALUES list is not accepted on the right-hand side — (a,b) NOT IN (VALUES (1,2)) fails with 42883 function VALUES(integer, integer) does not exist — but the same list works as a subquery: NOT IN (SELECT x, y FROM (VALUES (1,2)) v(x, y)).

Guarding the tested columns is not enough when the list holds a NULL: (a,b) NOT IN ((1,NULL)) returns 1, 3 on DB9 against 3 on PostgreSQL even with both guards in place. The subquery form handles it correctly — (a,b) NOT IN (SELECT 1, NULL::int) returns 3 on both engines.

Single-column IN / NOT IN lists are unaffected — 1 NOT IN (2, NULL) is correctly NULL.

Separately, row IS DISTINCT FROM / IS NOT DISTINCT FROM fails with 42883 operator does not exist: record = record 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.

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
Column constraints (PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK)	Supported
Foreign keys (CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION)	Supported — all five actions behave as declared, 23503 on violation, and TRUNCATE of a referenced table is refused — but dropping the referenced table bypasses the check entirely, see the caution below
Generated columns	Supported
SERIAL / BIGSERIAL	Supported
CREATE/DROP INDEX (B-tree, GIN, HNSW)	Supported
CREATE INDEX CONCURRENTLY	Supported
Expression indexes, partial indexes	Supported
CREATE/DROP VIEW	Supported
CREATE/DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW	Supported
CREATE/DROP SEQUENCE	Supported
ALTER SEQUENCE	Partial (only OWNER TO / OWNED BY; all other clauses — RESTART, INCREMENT BY, RENAME TO, SET SCHEMA, … — raise 0A000. Use SETVAL() to reposition)
DROP SEQUENCE dependency checks	Not supported (a sequence used by a column default can be dropped, breaking the table)
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 only the enum-value forms and RENAME TO, and DROP TYPE does not cascade and does not check array-column or return-type dependents, 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 loops, CASE statements, EXECUTE, exception handlers or nested blocks (CREATE TRIGGER refuses it — 0A000, or 42601 for WHILE and FOREACH), and FOR EACH STATEMENT runs once per row, see Advanced SQL — Triggers
TRUNCATE	TRUNCATE t / TRUNCATE TABLE t only — the multi-table form and every optional clause (CASCADE, RESTRICT, ONLY, RESTART/CONTINUE IDENTITY) are rejected (42601). A table that another table’s foreign key references cannot be truncated (0A000), as in PostgreSQL, see the caution below
Row-Level Security (ENABLE/DISABLE/FORCE RLS)	Supported
CREATE/ALTER/DROP POLICY	Supported
Table partitioning (RANGE, LIST, HASH)	Not supported
Table inheritance	Not supported
Foreign data wrappers (FDW)	Not supported
Tablespaces	Not supported (TiKV manages storage placement)

Foreign keys do not survive a dropped parent

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)

The two escapes PostgreSQL’s own hint suggests — TRUNCATE parent, child and TRUNCATE parent CASCADE — are both rejected as syntax errors (42601). Empty the referencing table first and remove the parent’s rows with DELETE, or drop the foreign key before truncating. A self-referencing foreign key does not block TRUNCATE.

One statement still bypasses the check rather than being refused, where PostgreSQL refuses it: DROP TABLE on a referenced table succeeds, and the orphaned constraint then makes every pg_constraint scan in the database fail. See DDL — DROP statements for the details and the way out.

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

READ COMMITTED and REPEATABLE READ behave as they do in PostgreSQL — a READ COMMITTED transaction takes a fresh snapshot per statement and sees concurrent commits, and a REPEATABLE READ transaction holds one snapshot for its lifetime.

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 with explicit row locks (SELECT ... FOR UPDATE) or a unique constraint that makes the conflicting write fail.

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, up to precision 1000
TEXT		Variable-length, no limit
VARCHAR(n)	CHARACTER VARYING
CHAR(n)	CHARACTER	Stored as VARCHAR internally
BYTEA		Binary data
DATE
TIME		Without time zone
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.		1-dimensional arrays of any supported type
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
Multi-dimensional arrays	Only 1-dimensional arrays; an INT[][] column is catalogued as text[] and rejects every INSERT (42846)
Money
Bit string (BIT, VARBIT)	Accepted: BIT(n) and VARBIT store and return bit strings, but an over-length value is silently truncated instead of raising; BIT(1) becomes BOOLEAN. Both BIT VARYING spellings are rejected (42601) — see the caution below

Three of these are written without complaint

Most rows above fail loudly. XML, MONEY, CIDR, MACADDR, and every range type except INT4RANGE are rejected at CREATE TABLE with type "..." does not exist (42704). Multi-dimensional arrays get as far as CREATE TABLE — the column is catalogued as text[] — but every INSERT into such a column is rejected (42846), one-dimensional values included, so nothing wrong can be stored.

Three entries have neither safeguard. The DDL succeeds and so does the write, so the problem surfaces later as wrong data or a misleading error.

BIT / VARBIT — bit-string content is validated: a value that is not made up of 0s and 1s is rejected with invalid input syntax for type bit(n) / varbit (22P02). The declared length never raises. BIT(n) silently reshapes every value to exactly n bits — short input is right-padded with zeros, long input truncated — where PostgreSQL raises 22026 for both. VARBIT(n) leaves a short value alone, as PostgreSQL does, but silently truncates anything longer than n, where PostgreSQL raises 22001:

SQL

CREATE TABLE b (id INT PRIMARY KEY, v BIT(8));
INSERT INTO b VALUES (1, '10101010');       -- stored as 10101010
INSERT INTO b VALUES (2, '101');            -- accepted, stored as 10100000  (PostgreSQL: 22026)
INSERT INTO b VALUES (3, '1010101010101');  -- accepted, stored as 10101010  (PostgreSQL: 22026)
INSERT INTO b VALUES (4, 'hello!!!');       -- rejected, 22P02

CREATE TABLE vb (id INT PRIMARY KEY, v VARBIT(8));
INSERT INTO vb VALUES (1, '101');           -- stored as 101, same as PostgreSQL
INSERT INTO vb VALUES (2, '1010101010101'); -- accepted, stored as 10101010  (PostgreSQL: 22001)

Silent truncation is the hazard to plan around: every row survives the load and the corruption only shows up as wrong bit patterns later.

A varbit column is safe by accident: pg_dump writes it as bit varying(n), which DB9 cannot parse (42601 — both the bare BIT VARYING and the BIT VARYING(n) spellings are rejected). A bit(n) column is not — pg_dump writes it as bit(n) verbatim, exactly the spelling that is accepted and silently reshaped. B'1010' bit-string literals, as emitted by pg_dump --inserts, are accepted.

One catalog view disagrees with the others. information_schema.columns and pg_typeof() both report the true type (bit / bit varying), but format_type() reports text — so psql’s \d and anything else built on format_type() will not show these columns as bit strings. Use information_schema.columns to find them. The bytes on disk are the ASCII characters of the bit string, which surfaces if you cast: v renders as 10101010 but v::text renders as \x3130313031303130.

BIT(1) is the exception, and a benign one: it is created as BOOLEAN, round-trips '1' and '0' as true/false, and rejects anything longer at 22P02. Bare BIT behaves the same way, and is what pg_dump writes as bit(1) — so single-flag bit columns migrate cleanly and need no conversion.

Convert the wider bit-string columns before exporting: use BYTEA for binary data, or an INTEGER/BIGINT bitmask for flags.

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 gaps are in the accessor functions: isempty() is unavailable, and lower() / upper() resolve to the text case-folding functions instead of the range bound accessors — so they silently return the range unchanged rather than its bounds:

SQL

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

Because the failure is silent, prefer two plain columns (lo, hi) over INT4RANGE.

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 (0A000)

Flatten composite columns into ordinary scalar columns before migrating.

Indexes

Type	Status	Notes
B-tree	Full	Default. Point, range, bounded-range, in-list, expression, partial indexes
GIN	Full	JSONB containment (@>), full-text search (@@), array operators. Only TSVECTOR, JSONB and array columns are indexable — any other column type fails with a bare XX000, see below
HNSW	Near-full	Approximate k-NN over VECTOR columns. Builds on both transports and the planner uses it — but only for an inlined literal probe with a LIMIT and no WHERE clause: an inline embedding(...) call, a driver-bound parameter, or any filter falls back to an exact scan. 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
GiST	Not supported	CREATE INDEX is rejected
Hash	Not supported	CREATE INDEX is rejected
SP-GiST	Not supported	CREATE INDEX is rejected
BRIN	Not supported	CREATE INDEX is rejected

btree, gin, and hnsw are the only usable access methods, and pg_am lists exactly those three 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, hash, spgist, brin — named and rejected
ERROR: access method "gist" is not supported (0A000)
HINT:  Only btree, 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, Hash, SP-GiST and BRIN give 0A000 with the HINT above, while every other spelling gives 42704 — despite none of the four appearing in pg_am. IVFFlat falls in the second group: HNSW is the only vector index type.

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 the statement fails with a bare internal error, which does not say that the type is the problem:

SQL

CREATE INDEX ON docs USING gin (body);     -- body is TEXT
ERROR: internal error   (XX000)

PostgreSQL reports 42704 data type text has no default operator class for access method "gin" here, and DB9’s own hnsw path produces that clearer error, so the bare XX000 is specific to GIN. If you hit it, check the column’s type first — it is not a sign that GIN is unavailable. 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 (no-PK and composite-PK tables fail with a bare XX000), 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. 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 fully functional for containment queries (@>). Queries using @> on GIN-indexed columns use index scans instead of sequential scans.

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	Not supported
Streaming 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, to_timestamp, 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	uuid_generate_v4 and related functions
Type conversion	cast, to_char, to_number, to_date, to_timestamp
Conditional	coalesce, nullif, greatest, least
HTTP (scalar)	http_get, http_post, http_put, http_delete 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

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 -

Every operator above works with a literal. With an untyped bind parameter — the shape most drivers emit from a raw parameterised query — ->, ->>, #>, #>>, #-, @>, <@ and || infer the parameter’s type from the operator, as PostgreSQL does. The key-existence operators and - (delete key) 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
?| ?&	42P18	$1::text[]
- (delete key)	42883 operator does not exist: jsonb - jsonb	$1::text

jsonb_extract_path() and jsonb_extract_path_text() fail the same way (42P18) on an untyped key argument and take the same $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 ? 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: sql parser error: Expected an SQL statement, found: FOREACH)
Cursor operations (FOR…IN CURSOR)	Not supported
Exception handling (BEGIN…EXCEPTION)	DO blocks only
Dynamic SQL (EXECUTE, including INTO and USING)	Supported, in both a CREATE FUNCTION body and a DO block
Nested BEGIN…END blocks	DO blocks only

PL/pgSQL host differences

A DO block is a more capable PL/pgSQL host than a CREATE FUNCTION body. Exception handling and nested blocks run inside DO but are rejected in a function body with 0A000 (... requires a Session-owned interactive DO host).

Feature	CREATE FUNCTION body	DO block
CASE statements	Supported	Supported
Exception handling (BEGIN ... EXCEPTION)	0A000	Supported
Dynamic SQL (EXECUTE, including INTO and USING)	Supported	Supported
Nested BEGIN ... END blocks	0A000	Supported
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.

The 0A000, 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 in a DO block follow PostgreSQL rollback semantics: 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).

CASE with no matching branch

PostgreSQL raises CASE_NOT_FOUND (20000) when no CASE branch matches and no ELSE is present. DB9 falls through silently instead, leaving the target variable NULL. Always include an explicit ELSE branch.

Other procedural languages (PL/Python, PL/Perl, PL/v8) are not supported.

ORM and Driver Compatibility

DB9 is tested against major ORMs with a combined pass rate above 99%:

ORM / Driver	Tested Version	Pass Rate	Notes
Prisma	5.7+	100% (89/89)	Binary wire protocol; $queryRaw for advanced SQL
Drizzle	0.29+	100% (75/75)	Type-safe queries; full query builder support
Sequelize	6.35+	100% (87/87)	Raw queries for window/CTE features
Knex.js	3.1+	100% (97/97)	Full query builder, window functions, CTEs
TypeORM	0.3.17+	98% (147/150)	3 tests skipped (schema introspection edge cases)
node-postgres (pg)	8.11+	Full	Native pgwire client
SQLAlchemy	2.0+	Tested	JSONB operators, RETURNING, transaction patterns
GORM (Go)	1.25+	Tested	CRUD, transactions, foreign keys

Known ORM limitations:

• Schema introspection queries may return incomplete results for some ORMs that rely heavily on information_schema
• Some ORMs assume PostgreSQL-specific system functions that are not yet implemented

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). Every scan of this view fails with XX000 while a dangling foreign key exists — see the DDL caution
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 — no tgtype column; read the declared timing and row/statement level from information_schema.triggers
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_user_tables	Stub	Returns rows but statistics columns are zeros
pg_stat_statements	Not available
pg_publication	Writable, but inert	A bare CREATE PUBLICATION p; succeeds on both transports and the row persists here — but nothing is replicated. FOR/WITH clauses are rejected with 0A000. See the caution below
pg_publication_rel	Writable, but inert	ALTER PUBLICATION ... ADD/DROP/SET TABLE records membership here and it persists — but nothing is replicated
pg_publication_namespace	Stub (empty)	ALTER PUBLICATION ... ADD TABLES IN SCHEMA is rejected with 0A000, so this is never populated
pg_replication_slots	Stub (empty)	19 columns, including conflicting plus the PostgreSQL 17 additions failover/synced (two_phase_at and inactive_since are absent). No slot can be created — pg_create_logical_replication_slot() exists (in the FROM clause only) but ends at 55000 logical replication is not provisioned for this database. See the caution below
pg_subscription	Not available	CREATE SUBSCRIPTION is not parsed at all

A publication can be configured, and it still does nothing

You can now build what looks like a complete CDC configuration, and none of it replicates anything:

SQL

CREATE PUBLICATION my_pub;              -- CREATE PUBLICATION
ALTER PUBLICATION my_pub ADD TABLE t;   -- ALTER PUBLICATION
SELECT * FROM pg_publication_rel;       -- the membership row is really there
SELECT * FROM pg_replication_slots;     -- the view exists, and is empty

Every one of those statements succeeds on both transports and the state persists. DB9 still has no logical replication, so no change is ever streamed. Do not read any of this as working CDC. DROP PUBLICATION removes the inert object.

The wall is slot creation — but not because the functions are missing. Part of a wal2json-based slot API is present, and it is easy to conclude otherwise, because each 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	resolves → see below
pg_logical_slot_get_changes(s, ...)	42883 does not exist	resolves → see below
pg_drop_replication_slot(s)	resolves → see below	42883 does not exist
pg_logical_slot_peek_changes(...)	42883	42883 — genuinely absent
pg_create_physical_replication_slot(...)	42883	42883 — genuinely absent

pg_stat_replication is not a function and is absent as a relation (relation "pg_stat_replication" does not exist).

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.

Written in the position where each one resolves, every path still fails:

Call	Result
SELECT * FROM pg_create_logical_replication_slot(s, 'wal2json')	55000 logical replication is not provisioned for this database
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 pg_drop_replication_slot(s)	55000 logical replication is not provisioned for this database
SELECT * FROM pg_logical_slot_get_changes(s, NULL, NULL)	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 * FROM pg_logical_slot_get_changes(s, NULL, NULL, 'format-version', '2')	55000 logical replication is not provisioned for this database

55000 is object_not_in_prerequisite_state, not “feature absent” — so treat the wal2json surface as unreachable plumbing rather than something you can switch on from SQL. We know of no statement that provisions it, and no DB9 feature exposes it. Nothing here gives you CDC today.

The phrase “for this database” is the server’s own wording; these results were confirmed on a free-tier database.

CREATE SUBSCRIPTION is not parsed, and ALTER PUBLICATION ... ADD TABLES IN SCHEMA is rejected with 0A000 only ALTER PUBLICATION table membership is supported.

ALTER TABLE ... REPLICA IDENTITY is accepted — DEFAULT, FULL, NOTHING and USING INDEX are all recorded in pg_class.relreplident — but, like the publication, it has no effect while nothing is replicated. 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.

The clauses pg_dump actually emits are rejected, so a restored dump fails loudly rather than silently:

ERROR: CREATE PUBLICATION FOR/WITH clauses are not supported yet  (0A000)

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	Not available

information_schema.views is not implemented — querying it fails with relation "views" does not exist (42P01). To list views, use pg_views, or filter information_schema.tables on table_type:

SQL

SELECT viewname FROM pg_views WHERE schemaname = 'public';

-- Portable alternative:
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'VIEW';

The twelve Supported views above are the complete information_schema surface in DB9.

Extensions

DB9 includes 11 built-in extension descriptors — 9 functional extensions plus pgcrypto and plpgsql, which are metadata-only shims that register no functions. 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 generation functions (functions are built-in; extension is a metadata shim)
hstore	1.0	No	Key-value store type (metadata shim with limited semantics)
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
Table partitioning (RANGE, LIST, HASH)	DDL
Table inheritance	DDL
Foreign data wrappers (FDW)	DDL
Tablespaces	DDL
Rules (CREATE RULE)	DDL
Logical replication (CREATE SUBSCRIPTION is unparsed; a bare CREATE PUBLICATION is accepted but inert — see pg_publication above)	Replication
Streaming replication	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 run against a DB9 database, in any format (use db9 db dump, or COPY over pgwire)	Tools
pg_basebackup	Tools

Validation

You can verify compatibility for your specific use case:

SQL

-- Check supported types
SELECT typname FROM pg_type WHERE typnamespace = 11 ORDER BY typname;

-- Check available extensions
SELECT * FROM pg_extension;

-- Check installed functions ('f' = function, 'p' = procedure, 'w' = window).
-- Note: pg_proc lists only catalog-registered functions. Many built-ins (SUM,
-- GENERATE_SERIES, UNNEST, ...) 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 ISOLATION LEVEL SERIALIZABLE;
-- WARNING: TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ

Next Pages

• SQL Reference — detailed SQL syntax and function reference
• Architecture — how DB9’s SQL engine connects to TiKV storage
• Extensions — guide to all 9 built-in extensions
• 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