SQL & Postgres — Reference

Lookup material for the SQL subset. For prose and examples see the Guide; to get started, the Quickstart.

Operator & function quick reference

Everything the subset accepts, at a glance. Anything not listed is rejected loudly (with SQLSTATE 0A000 / 42601), never silently ignored.

Category	Supported
Comparison	=, <> / !=, <, <=, >, >=
Set / range	IN (…), NOT IN (…), BETWEEN a AND b
Null	IS NULL, IS NOT NULL (= NULL rejected — use IS NULL)
Text match	LIKE 'ap%' (prefix) or exact, NOT LIKE — no %ap, %ap%, _, or ILIKE
Full-text	col @@ 'text' or to_tsvector(col) @@ plainto_tsquery('text') → tokenising $match on an analysed column. See Guide
Boolean logic	AND, OR, NOT, parentheses
Arithmetic	+, -, *, /, parentheses, precedence
String fns	|| (concat), concat, upper, lower, length
Scalar fns	coalesce(a, b, …), cast(x AS int|bigint|text|numeric|…)
Aggregates	count(*), count(DISTINCT c), sum, avg, min, max — usable inside expressions
Prediction	predict(col) (argmax), predictions(col) ≡ predict(col, probabilities => true) (distribution); PREDICTION/PREDICTIONS aliases — inference in SQL; not with GROUP BY. See the Guide
Recommend	SELECT * FROM recommend('from','target', goal => '…', given => '…', k => N) — set-returning function → (value, p). See the Guide
Relate	SELECT * FROM relate('from', fields => '…', to => '…', k => N) — set-returning function → (related, lift, info, n). See the Guide
Clauses	WHERE, GROUP BY (single col), HAVING, ORDER BY … ASC|DESC (single key), LIMIT, OFFSET
Join	INNER JOIN … ON <base>.<fk> = <joined>.<pk>, along a declared link

Not supported yet (rejected with a clear error, never silently ignored): multiple ORDER BY keys, outer/cross/multi-table JOIN (and joins whose ON is not a declared link), multi-column GROUP BY, per-group min/max, UNION, substring/suffix LIKE and ILIKE, subqueries, and schema-qualified/quoted identifiers. = NULL / <> NULL are rejected with a hint to use IS NULL / IS NOT NULL (matching Postgres's three-valued logic). (INSERT, CREATE TABLE, and CREATE VIEW are supported over the Postgres wire protocol — see Writes — but not on the read-only /api/v2/_sql endpoint.)

Error codes (SQLSTATE)

Failures are reported with a standard PostgreSQL SQLSTATE, so a Postgres client or connector reacts the way it would to a real Postgres — the message is always specific, never a swallowed empty result. Over the wire protocol the code rides in the ErrorResponse; over REST it's in the error JSON.

SQLSTATE	Name	When
23505	unique_violation	INSERT of a duplicate primary key with no (or DO NOTHING-less) ON CONFLICT
42P07	duplicate_table	CREATE TABLE / CREATE VIEW of a name that exists (without IF NOT EXISTS / OR REPLACE)
42P01	undefined_table	SELECT / INSERT / UPDATE / DELETE / ALTER / DROP on a missing table or view
42701	duplicate_column	ALTER TABLE ADD COLUMN of a column that exists
42703	undefined_column	reference to a column that doesn't exist (ALTER DROP, unknown SET/select column)
0A000	feature_not_supported	a valid-Postgres construct Aito doesn't implement yet (outer join, subquery, ILIKE, …)
42601	syntax_error	a parse error, or an argument the subset rejects (= NULL, '%ap%', a type mismatch)
XX000	internal_error	an unexpected server-side failure

IF [NOT] EXISTS turns the otherwise-erroring redundant case (42P07 / 42P01 / 42701 / 42703) into a silent no-op, exactly as in Postgres.

Limitations

• JOINs follow existing links only: an INNER JOIN along a declared link works (it projects through the link — see the Guide), so a tool that sees the foreign key can run the join. Arbitrary joins on non-link columns, and outer/cross/multi-table joins, are still rejected — so a postgres_fdw remote join pushdown across two foreign tables isn't supported (join them locally, or disable join pushdown on the foreign server).
• Writes — INSERT (with ON CONFLICT), CREATE TABLE, COPY … FROM STDIN (bulk load), DROP/ALTER TABLE ADD|DROP COLUMN, DELETE, and UPDATE work over the wire protocol (see Writes); the /api/v2/_sql REST endpoint is read-only.
• Views — CREATE [OR REPLACE] VIEW v AS SELECT <cols / renames / *> FROM src [WHERE <equality…>] and DROP VIEW [IF EXISTS] v work over the wire protocol (see Views). A view is a materialised, from-able collection, not stored SELECT text, so SELECT … FROM v — including a query-time WHERE/ORDER BY/GROUP BY and even predict/@@ — runs on it, and a query WHERE composes on top of the view's baked filter. The view stays current via incremental refresh. v1 limits: a single source (no UNION/JOIN in the definition), and a baked WHERE of equality conditions only (col = v [AND …]); a range/IN/LIKE in a view definition, or an aggregate/GROUP BY, is rejected loud at CREATE VIEW (do it at query time against the view instead).
• Inference — predict/predictions (categorical label columns; not analysed-text/multi-value), full-text @@, recommend(...), and relate(...) are in SQL. Still JSON-only: the raw learned-ranking blends (similarity × contextual $p) — those live in the JSON _query API.
• Metadata depth — no composite/unique/check constraints, sequences, indexes, or functions; unrecognised catalog queries return empty rather than erroring, so deep tool features may show nothing.
• Wire protocol — no SCRAM channel binding (-PLUS) or client-certificate auth; TLS is optional, not enforced. Cursors (DECLARE/FETCH) are read-only and materialize the whole result up front (fine for the connectors above, not a streaming cursor over a huge table).

Related

• Query Reference — the JSON query language _sql lowers to.
• Relationships, Links & Joins — how a SQL JOIN maps to an Aito link.
• Environments & Auth — API keys and environments.