SQL reference

What the gateway ufgw accepts today, statement by statement, and the exact error you get for everything else. The subset is small on purpose; nothing outside it is approximated.

Every item marked not yet (0A000) fails at once with SQLSTATE 0A000 and a message naming the feature. The order in which the gaps are planned is on SQL and psql. For a worked session see the Quick start.

Statements

Statement	Form	State
`CREATE TABLE`	`CREATE TABLE [IF NOT EXISTS] name (col type [NOT NULL] [PRIMARY KEY], …, [PRIMARY KEY (cols)]) [WITH (storage = row \| columnar)]`. Needs an operator credential (`42501` otherwise). A primary key is required (`0A000` without one). Schema `public` only. Returns `CREATE TABLE`; an existing table without `IF NOT EXISTS` is `42P07`.	Built
`INSERT`	`INSERT INTO name [(cols)] VALUES (…), (…)`. Constants, casts of constants, negated constants and `$n` parameters only. Unlisted columns are NULL. An existing or repeated primary key fails the whole statement with `23505`. Returns `INSERT 0 n`.	Built
`UPDATE`	`UPDATE name SET col = expr, … [WHERE cond]`. Row tables only. Applied as one atomic step at the node. Returns `UPDATE n`.	Built
`DELETE`	`DELETE FROM name [WHERE cond]`. Row tables only. Returns `DELETE n`.	Built
`SELECT`	See the clauses below. Returns `SELECT n`.	Built
`INSERT … SELECT`, `ON CONFLICT`, `RETURNING`, `WITH` in DML		not yet (0A000)
`UPDATE … FROM`, `DELETE … USING`, `SET col = DEFAULT`, assignments to parts of a column, changing a primary key column		not yet (0A000)
`UPDATE`/`DELETE` on columnar tables	Columnar tables are append-only.	not yet (0A000)
`BEGIN`/`COMMIT`/`ROLLBACK`, `DROP`, `ALTER`, `CREATE INDEX`, `SET`, `SHOW`, `EXPLAIN`, `COPY`, `TRUNCATE` and every other statement kind	Named in the message as "Kind statements".	not yet (0A000)
Several statements in one query string	Allowed when all of them are `SELECT`; refused when any writes, because there are no transactions yet to undo a half-applied string. In a prepared statement, more than one statement is `42601`.	partly

CREATE TABLE details

Element	State
Column constraints `NOT NULL`, `NULL`, `PRIMARY KEY`; table constraint `PRIMARY KEY (a, b)`	Built
`UNIQUE`, `CHECK`, `DEFAULT`, `REFERENCES` and other constraints	not yet (0A000)
`WITH (storage = columnar)`; `storage = row` is the default; any other option	Built / not yet (0A000)
Inheritance, partitions, typed tables, schemas other than `public`, array columns	not yet (0A000)
Identifiers	Table and column names: letters, digits and single underscores, first character not a digit, up to 63 characters (`42601` otherwise). `tenant_id` is reserved (`42939`); a repeated column is `42701`; two primary keys `42P16`; a key column that does not exist `42703`.

SELECT clauses

Clause	Supported	State
Select list	Columns, `table.column`, aliases, expressions, aggregates, `` and `t.` (never the tenant key). `*` inside an expression is not yet (0A000).	Built
`FROM`	Tables with aliases; comma joins; `[INNER] JOIN … ON`; `LEFT [OUTER] JOIN … ON`. The same alias twice is `42712`.	Built
`RIGHT`/`FULL` joins, `NATURAL`, `USING`, subqueries in `FROM`, `SELECT` without `FROM`, `VALUES` as a query		not yet (0A000)
`WHERE`	Any boolean expression below; aggregates are not allowed here (`42803`).	Built
`GROUP BY`	Columns, expressions or select-list positions. A non-grouped, non-aggregated column in the select list is `42803`.	Built
Aggregates	`count(*)`, `count(x)`, `sum`, `avg`, `min`, `max`, one argument each, also in `ORDER BY`. `DISTINCT`, `FILTER`, `ORDER BY` inside an aggregate and `OVER` are not yet (0A000); nested aggregates are `42803`.	Built
`ORDER BY`	Names, aliases, positions or expressions, `ASC`/`DESC`. NULLs sort last ascending. `NULLS FIRST/LAST` and `USING` are not yet (0A000).	Built
`LIMIT`, `OFFSET`	A non-negative integer constant, `$n`, or `NULL`. A negative value is `2201W`/`2201X`; a non-constant expression is not yet (0A000).	Built
`HAVING`, `DISTINCT`, `WITH`, `UNION`/`INTERSECT`/`EXCEPT`, window functions, `FOR UPDATE/SHARE`, `SELECT INTO`, `FETCH … WITH TIES`, subqueries anywhere		not yet (0A000)

Types

Every column type maps to one Ultra IR type and is sent to clients with the PostgreSQL type OID shown, so drivers decode it as they decode PostgreSQL's.

SQL type	Also written	Ultra IR	OID	Notes
`smallint`	`int2`	Int 16	21
`integer`	`int`, `int4`	Int 32	23
`bigint`	`int8`	Int 64	20
`real`	`float4`	Float 32	700	Text output as PostgreSQL 12+ prints it (shortest exact digits).
`double precision`	`float8`	Float 64	701
`numeric(p,s)`	`decimal(p,s)`	Decimal p,s	1700	Precision 1–38 (`22P02` outside). `numeric` without a precision is not yet (0A000).
`text`	`varchar`	String	25	`varchar(n)` length limits are not yet (0A000).
`boolean`	`bool`	Bool	16	Input `t/f`, `true/false`, `yes/no`, `y/n`, `on/off`, `1/0`.
`uuid`		Uuid	2950	With or without hyphens.
`date`		Date	1082	`YYYY-MM-DD`. Infinity is `22008`.
`timestamp`		Timestamp (µs)	1114	`YYYY-MM-DD[ \|T]HH:MM[:SS[.ffffff]]`; keeps microseconds. `timestamp(p)` is not yet (0A000); `timestamptz` is not yet (0A000).
`bytea`		Bytes	17	Hex input `'\x…'`.
Arrays, `json`/`jsonb`, `interval`, `time`, `serial` and every other type				not yet (0A000)

No silent lossy conversion

These rules come from the Ultra IR type system and PostgreSQL's assignment rules; the gateway applies them to literals and parameters, the node to every expression.

A literal takes the type of its partner (amount > 10 compares as numeric(12,2); placed = '2024-03-01' as date). A literal that does not fit its partner's type is compared at a common wider type, never rounded.
An integer that does not fit the column is 22003; a decimal with more integer digits than the column's precision allows is 22003; extra decimal places are rounded half away from zero, as PostgreSQL rounds.
Integer × integer, + and − widen within integers; an integer with a numeric becomes numeric(digits, 0), so numeric(12,2) * 2 keeps scale 2. Mixed integer/numeric comparisons use a common supertype; anything with a float becomes double precision.
Explicit casts: between numeric types, and between date and timestamp. A cast that cannot represent the value is an error, never a NULL or a wrapped value. Other casts are not yet (0A000).
A number or boolean literal against a text column, or text against a number, is 42883 ("operator does not exist"), as in PostgreSQL. Text that does not parse as the partner type is 22P02.
Numeric literals: more than 38 digits after the point is not yet (0A000). Exponent literals keep their scale (1e-5 has scale 5).
Integer overflow is 22003 and division by zero 22012 at run time, never wrap-around or Infinity.

Operators and expressions

Expression	Notes	State
`=`, `<>`, `!=`, `<`, `<=`, `>`, `>=`	Both sides must share an orderable type or both be numeric (`42804` otherwise).	Built
`+`, `-`, `*`, unary `-`/`+`	Integers, decimals and floats, with the widening above.	Built
`/`	Integer ÷ integer. Decimal division has no exact operator yet; float division would give Infinity/NaN where PostgreSQL raises an error, so it is refused too.	not yet (0A000)
`AND`, `OR`, `NOT`	Arguments must be boolean (`42804`).	Built
`IN (…)`, `NOT IN (…)`	Lists of constants or parameters; an empty list is `42601`.	Built
`BETWEEN`, `NOT BETWEEN`		Built
`IS NULL`, `IS NOT NULL`	SQL three-valued logic throughout: `NULL = NULL` is NULL, and `WHERE` keeps only rows where the condition is TRUE.	Built
`CAST(x AS type)`, `x::type`	Numeric ↔ numeric, `date` ↔ `timestamp`.	Built
`$n` parameters	Extended protocol only; typed by Parse or by the partner expression.	Built
`LIKE`, `ILIKE`, `SIMILAR TO`, `IS DISTINCT FROM`, `%`, `^`, `\|\|`, bitwise operators, `CASE`, `COALESCE`, `NULLIF`, `EXISTS`, `ANY`/`ALL`, array and JSON operators		not yet (0A000)
Functions other than the five aggregates (`upper`, `now`, `length`, `date_trunc`, …)		not yet (0A000)
Qualified names with more than two parts (`db.schema.table.col`)		not yet (0A000)

Protocol

Detail	Behaviour
Login	Startup message, then a password message carrying the token. The gateway verifies it, then opens a connection to `ufd` and logs in there with the same token; the node verifies it independently. A failure is a `FATAL` error with `28P01` (token refused, by gateway or node), `08006` (node unreachable) or `08P01` (a message that is not a password message).
Login timeout	10 seconds for the whole login. Until the token is verified, messages are limited to 16 KiB; afterwards to 16 MiB.
TLS	An `SSLRequest` is answered `S` and upgraded when the gateway has a certificate (TLS 1.2 or later), `N` otherwise. Without a certificate the gateway listens only on loopback addresses, so a token is never sent in the clear across a network.
Connection cap	`-max-conns`, default 200. The next connection gets `FATAL 53300` and is closed.
Simple query	One `Query` message: RowDescription with PostgreSQL OIDs, DataRow rows in text format, CommandComplete, ReadyForQuery (always idle, `I`: there are no transactions). An empty string is EmptyQueryResponse. Several `SELECT`s in one string each return their rows; the first error ends the string.
Extended query	Parse, Bind, Describe (statement or portal), Execute (with a row limit), Close, Flush, Sync. After an error, messages up to the next Sync are ignored, as in PostgreSQL. Portals live until Sync. A prepared statement holds exactly one statement and is bound again at every Bind with that Bind's values (so `LIMIT $1` and `VALUES ($1)` work, and a catalog change is picked up).
Parameter types	Parse may give an OID for any type in the table above (another OID is `0A000`); a parameter declared `numeric` (1700) is converted exactly to the column's type or refused. Untyped parameters take the type of their partner; a parameter with no partner is `42P18`. `$n` beyond the statement's parameters is `42P02`.
Formats	Text and binary, per parameter and per result column (format codes 0 and 1; others are `08P01`). Binary dates and timestamps count from 2000-01-01 as PostgreSQL's do; binary `numeric` uses PostgreSQL's base-10,000 encoding and is rounded once; NaN and infinities are refused (`22P03`, `22008`).
Catalog	Cached per session; refreshed when a table or column is not found and after a `40001` from the node, after which the statement is bound and run once more with the same parameters.
Node connection	One per session, over the node's Unix socket. If it fails, the session ends with `FATAL 08006`.
`pg_catalog`, `information_schema`, `SET`, `SHOW`, `LISTEN`, `COPY`, cancel requests, cursors	not yet (0A000) (the catalog tables are the next planned item, so that GUIs and ORMs can connect).

Limits

Limit	Value	When exceeded
Concurrent connections per gateway	200	`53300`; `-max-conns` changes it
Login time	10 s	connection closed
Message size before / after login	16 KiB / 16 MiB	connection closed
Prepared statements per session	1,000, 64 MiB of SQL	`53400`
Open portals until Sync	64	`53400`
Identifier length	63	`42601`
`numeric` precision	38	`22P02` in a type; `0A000` for a literal with more decimal digits
Query memory and result size	per-query budget at the node	`53200`; the message carries no byte counts, because they would include rows the requester cannot see
Columnar table fields	65,534	refused at creation

SQLSTATEs

Every error carries a PostgreSQL SQLSTATE, so drivers classify it as they classify PostgreSQL's. The gateway produces these; where a code is translated from a node error code (see Node protocol v0), the node code is given.

SQLSTATE	When
`0A000`	Feature not supported: everything marked above, with the feature named. Also an unsupported constant kind, cast, or parameter type OID.
`08006`	The node could not be reached at login, or the session's node connection was lost (FATAL).
`08P01`	Protocol violation: not a password message at login, an unexpected message, a wrong parameter or format count, or a format code other than 0 or 1.
`22000`	An evaluation error at the node that is not overflow or division by zero (node code 104), for example a failed cast.
`22003`	Numeric value out of range: a literal or parameter that does not fit its type, `LIMIT` beyond 64 bits, or integer overflow at run time (node code 104).
`22008`	A date or timestamp infinity in a binary parameter.
`22012`	Division by zero at run time (node code 104).
`22023`	Parameter values could not be encoded, or the node found the request invalid (node code 107).
`22P02`	Invalid text for a type: a bad date, UUID, number or boolean, or a `numeric(p,s)` declaration outside 1–38.
`22P03`	Malformed binary parameter data.
`2201W` / `2201X`	Negative `LIMIT` / `OFFSET`.
`23502`	NULL into a `NOT NULL` column, in `INSERT` or `UPDATE`.
`23505`	The primary key already exists for your tenant, or is repeated in the statement; nothing is written (node code 109).
`26000`	Bind or Describe of a prepared statement that does not exist.
`28000`	The node reports the connection as not logged in (node code 111).
`28P01`	The token was refused at login: bad signature, wrong `kid` or algorithm, wrong issuer or audience, expired or not yet valid, malformed claims.
`34000`	Execute or Describe of a portal that does not exist.
`40001`	The plan was built against an older catalog (node code 102). The gateway refreshes and retries once; a client sees it only if the retry is stale again.
`42501`	Not allowed: `CREATE TABLE` without an operator credential, a query or write with an operator credential, an `UPDATE` of a table with a column above your clearance, a `DELETE` of a table whose key is above it, or the node refusing a request for this identity (node codes 110 and 112).
`42601`	Syntax error from PostgreSQL's parser, a bad identifier, an empty `IN` list, `ORDER BY`/`GROUP BY` position out of range, an aggregate with the wrong number of arguments, or more than one statement in a prepared statement.
`42701`	A column name repeated in `CREATE TABLE`.
`42702`	Ambiguous column reference.
`42703`	No such column (also a primary key column that is not in the table).
`42712`	The same table alias used twice in `FROM`.
`42803`	Grouping error: a column neither grouped nor aggregated, an aggregate in `WHERE` or `GROUP BY`, or a nested aggregate.
`42804`	Datatype mismatch: comparing or combining types that have no common type, a non-boolean in `AND`/`OR`/`WHERE`, an aggregate over a type it does not take, unary minus on a non-number.
`42883`	No operator for these types, for example text compared with a number.
`42939`	The reserved column name `tenant_id`.
`42P01`	No such table, or a `FROM`-clause entry missing for an alias (node code 105).
`42P02`	A `$n` the statement does not have.
`42P03` / `42P05`	A named portal / prepared statement already exists.
`42P07`	The table already exists (node code 106).
`42P16`	More than one primary key in `CREATE TABLE`.
`42P18`	Nothing in the statement determines the type of a `$n`; give it a cast.
`53200`	The query went over its memory budget or result limits (node code 101).
`53300`	Too many clients (FATAL, before login).
`53400`	Too many prepared statements or open portals in this session.
`XX000`	Internal: the node's verifier rejected a plan the gateway built (node code 103), a malformed result row, or a node error with no mapping. These should not happen; each one is a bug to report.