SQL & Postgres — Guide

The full SQL surface. New here? Start with the Quickstart; for the operator table, SQLSTATE codes, and limitations see the Reference.

The SQL subset

SELECT  <select_list>
FROM    <table>
[[INNER] JOIN <table> ON <base>.<fk> = <joined>.<pk>]
[WHERE  <condition>]
[GROUP BY <column>]
[HAVING <aggregate condition>]
[ORDER BY <column> [ASC|DESC]]
[LIMIT  <int>]
[OFFSET <int>]

select list — *, columns, a scalar expression, an aggregate, or a prediction:
- arithmetic — price * 2, price - cost AS margin, (a - b) * 2 (+ - * /, parens, precedence);
- scalar functions — coalesce(a, b), cast(e AS int|text|…), upper, lower, length, concat, and || (string concat);
- prediction — predict(col) infers the most likely value of col for each row (the argmax); predictions(col) returns the full ranked distribution ([{"value":…,"p":…}, …] as JSON). This is Aito's inference in SQL — see Prediction in SQL below. col may be a link path; prediction can't be combined with GROUP BY (it is a per-row projection);
- aggregates — count(*), count(DISTINCT col), sum(col), avg(col), min(col), max(col), usable inside an expression (sum(x) + 1, max(p) - min(p), sum(p) / count(*)) — a single-row aggregate query whose expressions are evaluated in memory over the one aggregate hit. Mixing a per-row column with the aggregates (needs GROUP BY), or GROUP BY/DISTINCT with an aggregate expression, is rejected loudly.
Columns may be qualified (orders.id); any item may be renamed with AS alias. A constant SELECT with no FROM (SELECT 1 AS a, SELECT now()) is answered as a one-row probe.
where — column predicates (=, <>, <…, IN, LIKE, IS NULL, BETWEEN) and scalar-expression predicates (WHERE price * 2 > 20, WHERE lower(name) = 'apple', WHERE price * 2 IN (200, 400), WHERE upper(name) LIKE 'A%'). A computed predicate — comparison, IN/NOT IN, or LIKE/NOT LIKE — is evaluated in memory (it can't use an engine-side filter), so it scans the selection; LIMIT applies after it. Combine with AND / OR / NOT and parentheses.
LIMIT / OFFSET — a SELECT with no LIMIT returns all matching rows (Postgres semantics), not a default page of 10; LIMIT n [OFFSET m] paginates.
GROUP BY — a single column: SELECT color, count(*), avg(price) FROM products GROUP BY color. count(*) is the per-group row count; sum/avg aggregate per group; WHERE filters before grouping; ORDER BY <agg alias> and LIMIT rank/paginate the groups.
HAVING — filters the grouped rows on an aggregate condition (after grouping, before ORDER BY/LIMIT): … GROUP BY color HAVING count(*) > 1 AND avg(price) > 20. References count(*), an aggregate from the SELECT list (by call or alias), or the grouped column, combined with AND/OR/NOT.
literals — integers, decimals, single-quoted strings ('' escapes a quote), TRUE/FALSE, NULL. Keywords are case-insensitive.

LIKE supports a trailing-% prefix ('ap%') or an exact string only — Aito has no substring/suffix operator, so '%ap' / '%ap%' / _ are rejected.

Prefix LIKE works on plain string / keyword columns (an id, a category code). It does not work on an analysed text column such as a product name: there LIKE 'milk%' is rejected ($startsWith is not defined on analysed text). For free text over such a column, use full-text search (below), which tokenises and analyses. The full operator list is in the Reference.

Full-text search — `@@`

Search an analysed text column with Postgres's own full-text operator @@, which lowers to the engine's tokenising $match:

SELECT name, price FROM products WHERE name @@ 'milk';                    -- shorthand
SELECT name, price FROM products WHERE to_tsvector(name) @@ plainto_tsquery('milk');  -- full Postgres form

Both forms are identical — Aito tokenises/analyses the text itself, so the *_tsquery builder (plainto_tsquery, to_tsquery, websearch_to_tsquery) doesn't change the result. @@ composes with ordinary predicates (WHERE name @@ 'milk' AND price < 1.50). It filters (rows containing the tokens); relevance ranking (ts_rank) is a follow-up. @@ can't be combined with a computed/in-memory predicate in the same WHERE (there's no faithful in-memory tokeniser) — split them. It's the smart counterpart to LIKE; once your instance runs a build with @@, you can try it in the SQL console.

JOIN along a link

A SQL JOIN maps onto an Aito link — a foreign-key column addressed by a dotted path (product.name). An INNER JOIN along an existing link is therefore not a row-combining cartesian product but a thin skin over that link-path projection, with no content copied (it's one of the four spellings of the same join):

SELECT orders.id, products.name, orders.qty
FROM orders JOIN products ON orders.product = products.id
WHERE products.category = 'dairy' ORDER BY orders.id

The base-table side of the ON names the link column (orders.product); references to the joined table (products.name) follow that link. WHERE, ORDER BY, and aggregates may reference joined columns. It runs the identical query as the JSON form {"from":"orders","select":["id","product.name","qty"], "where":{"product.category":"dairy"},"orderBy":"id"}. Only an INNER JOIN whose ON is a declared link is supported — outer/cross/multi-table joins are rejected (see Limitations).

Prediction in SQL; learned ranking is JSON-only

Prediction is available in SQL via predict(col) / predictions(col) — Aito's classifier, right in a SELECT:

-- argmax: route an invoice to a GL code
SELECT ProductName, predict(GLCode) FROM invoices WHERE ProductName = 'Cloud Services';

-- the full ranked distribution, with probabilities
SELECT predictions(GLCode) FROM invoices WHERE ProductName = 'Cloud Services';
-- → [{"value":"E002","p":0.98}, {"value":"E001","p":0.006}, …]

predict(col) is the argmax; predictions(col) is the ranked distribution — and predictions(col) ≡ predict(col, probabilities => true), so a flag or the plural name both give the distribution. PREDICTION / PREDICTIONS (uppercase) are accepted aliases. The evidence is the row's ordinary WHERE. col may be a link path. Prediction projects per row, so it can't be combined with GROUP BY. It works on a categorical label column (GLCode, Processor, a single-valued string category); analysed-text and multi-value columns aren't supported.

ORDER BY is a plain column sort, so the raw learned-ranking blends (e.g. rank by similarity × contextual probability, orderBy: {"$multiply": [{"$similarity": …}, {"$p": {"$context": …}}]}) and contextual $p columns still have no SQL syntax — reach for the JSON _query / _search API for those. But prediction (above), full-text search (@@), recommend (recommend(...)), and relate (relate(...)) are all in SQL — see below.

recommend(...) is a set-returning function: it ranks a target column's values toward a goal and returns (value, p) rows — Aito's recommendation, in SQL. Because it produces a relation, it goes in FROM:

SELECT * FROM recommend(
  'impressions',                  -- the source relation
  'product',                      -- the target column (often a link) to recommend
  goal  => 'purchase = true',     -- the outcome to optimize for (required)
  given => 'context = ''0_7_1''', -- optional evidence / conditioning
  k     => 5                      -- top-k (default 10)
);
-- → value | p   (candidates ranked by P(goal | target = value, given))

goal and given are ordinary WHERE-predicate strings (the full =, <, IN, AND/OR, … vocabulary), so goal => 'purchase = true AND returned = false' works. goal is required; the outer SELECT may only be * or the value / p columns, and there's no relational tail (WHERE/GROUP BY/…) — the arguments carry the whole query. It lowers to the JSON recommend ({"from":…,"recommend":…,"goal":…,"where":given,"select":["$value","$p"]}), the same engine as the JSON API. A set-returning function is valid Postgres grammar (a function in FROM), so it reads cleanly for any Postgres client.

Relate

relate(...) finds statistical relations to a condition — which values co-occur with an outcome more than chance — and returns relation-stat rows (related, lift, info, n). Like recommend, it's a set-returning function in FROM:

-- relate specific fields to a condition
SELECT * FROM relate('impressions', fields => 'product', to => 'purchase = true', k => 5);

-- relate ALL fields to the condition (the v1 shorthand)
SELECT * FROM relate('impressions', to => 'purchase = true');
-- → related | lift | info | n     (e.g. related = {"product":"…"}, lift > 1 = related)

to is the target condition (a WHERE-predicate string, required). With fields it relates those columns to to ({relate:[fields], where:to}); without fields it relates all fields to the condition ({relate: to}). lift > 1 means the value co-occurs with the condition more than chance; info is the mutual information. The outer SELECT must be *.

Postgres wire protocol (pgwire)

Existing Postgres clients connect and run the SQL subset with no code change.

Enabling it

The listener is off by default (it's a query-executing port). Enable it with environment variables on the Aito instance:

PGWIRE_ENABLED=true      # turn the listener on
PGWIRE_PORT=5432         # default 5432

It binds to the same address as the HTTP server.

Connecting

The password is your Aito API key; the user is ignored; the database selects the environment (postgres / aito / empty → the master env, otherwise the env of that name).

# psql
psql "host=$AITO_HOST port=5432 dbname=aito user=aito password=$AITO_API_KEY"

# JDBC
jdbc:postgresql://$AITO_HOST:5432/aito     # user=aito, password=<API key>

# psycopg
psycopg.connect("host=$AITO_HOST port=5432 dbname=aito user=aito password=$AITO_API_KEY")

Both the simple and extended query protocols work, so plain statements and parameterized prepared statements (? / $1) run.

Writes (`INSERT`, `CREATE TABLE`, …)

Unlike the read-only /api/v2/_sql endpoint, the wire protocol accepts writes, so a Postgres-native tool can write into Aito:

INSERT INTO inventory (sku, name, stock) VALUES ('A1', 'Widget', 5), ('B2', 'Gadget', 12);

-- upsert on the collection's declared primary key:
INSERT INTO inventory (sku, name, stock) VALUES ('A1', 'Widget', 99)
  ON CONFLICT (sku) DO UPDATE;                             -- whole-row upsert (or DO NOTHING)
INSERT INTO inventory (sku, name, stock) VALUES ('A1', 'Widget', 99)
  ON CONFLICT (sku) DO UPDATE SET stock = EXCLUDED.stock;  -- column-list upsert

The column list is required; values are coerced to the declared column types (a NULL literal stores an unset field, read back as NULL); a type mismatch fails loud. ON CONFLICT … DO NOTHING | DO UPDATE [SET …] resolves on the declared primary key: DO NOTHING keeps the existing row, DO UPDATE replaces the whole row, and DO UPDATE SET c = v, c2 = EXCLUDED.c2, … is the standard Postgres column-list upsert (applies the assignments, preserving unlisted columns; values are literals or EXCLUDED.<col>). A duplicate key with no ON CONFLICT fails with SQLSTATE 23505. Parameterized INSERT … VALUES (?, …) works in both protocols.

CREATE TABLE lets a connector create its target table first:

CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price NUMERIC);
CREATE TABLE IF NOT EXISTS items (id INTEGER);   -- no-op if it exists

It creates a rep2 collection; Postgres column types map to Aito types (integer→Int, bigint→Long, text/varchar→String, numeric/double precision→Decimal, boolean, date), columns are nullable unless NOT NULL/primary-key, and PRIMARY KEY (inline or composite) is enforced on insert. IF NOT EXISTS is a no-op; a re-create fails with SQLSTATE 42P07.

COPY … FROM STDIN is the bulk-load path (pgjdbc's CopyManager, psql \copy), in both Postgres text and CSV formats:

COPY items (id, name, price) FROM STDIN WITH (FORMAT csv, HEADER true);

Options: FORMAT text|csv, DELIMITER, NULL, QUOTE, HEADER. CSV quoting (embedded delimiter/newline, doubled quotes) and NULL handling work; each value is coerced to the declared column type (uncoercible → loud SQLException); the tag is COPY <count>. COPY works over both the simple and extended query protocols, so drivers that issue COPY via Parse/Bind/Execute (e.g. pg8000) bulk-load too.

COPY (<query>) TO STDOUT is the read side — how libpq-based analytical engines stream a table out. Both the Postgres text format and the binary format (… TO STDOUT (FORMAT binary), which DuckDB requires) are produced, so those engines read Aito tables directly (see Connector compatibility below).

DROP TABLE and ALTER TABLE ADD|DROP COLUMN handle schema evolution:

DROP TABLE IF EXISTS items;
ALTER TABLE items ADD COLUMN note TEXT;       -- NULL for existing rows
ALTER TABLE items DROP COLUMN IF EXISTS note;

IF [NOT] EXISTS makes the redundant case a no-op; otherwise a missing table is 42P01, a duplicate column 42701, a missing column 42703. Only single-column ADD/DROP (not RENAME / ALTER COLUMN TYPE).

DELETE removes rows selected by the WHERE (all rows if omitted); UPDATE replaces the named columns in the matching rows:

DELETE FROM items WHERE price > 100;
UPDATE items SET price = 0, note = NULL WHERE price < 0;

The WHERE (shared by DELETE/UPDATE) is the same subset as SELECT; tags are DELETE <count> / UPDATE <count>; a missing table is 42P01. SET col = NULL unsets a nullable column; an unknown/duplicated SET column or a type mismatch fail loud. Not yet: column constraints beyond NOT NULL/PRIMARY KEY.

Views — `CREATE VIEW`

CREATE VIEW defines a derived relation you can query like a table:

CREATE VIEW active_customers AS
  SELECT id, name, tier FROM customers WHERE city = 'NYC';

SELECT * FROM active_customers ORDER BY id;          -- only the NYC rows
CREATE OR REPLACE VIEW active_customers AS
  SELECT id, name FROM customers WHERE city = 'NYC' AND tier = 'gold';
DROP VIEW IF EXISTS active_customers;

Unlike a Postgres view (stored SELECT text, re-planned per query), an Aito view is a materialised, from-able collection — so it is more than query sugar:

A query-time WHERE composes on the view's baked filter, and ORDER BY / GROUP BY / aggregates run on the view directly:
```
SELECT tier, count(*) FROM active_customers GROUP BY tier;
```
Inference and full-text search run on a view — because a view is a collection, predict(...) / predictions(...), @@, recommend(...), and relate(...) all work against the view, not just base tables. "A CREATE VIEW you can run a prediction and a BM25 search on" is the differentiator.

The view stays current automatically (incremental refresh on source change — no REFRESH needed). OR REPLACE swaps the definition in place; DROP VIEW [IF EXISTS] removes it (a missing view is 42P01 without IF EXISTS); a name that already exists without OR REPLACE is 42P07.

v1 scope (fail loud, never silently degraded): a view projects columns (col / col AS alias / *) from a single source, with an optional baked WHERE of equality conditions (col = v [AND …]). A range / IN / LIKE in the view definition, or an aggregate / GROUP BY / JOIN / UNION, is rejected at CREATE VIEW with a clear error — run those at query time against the view instead. (UNION ALL and link JOIN view definitions are planned follow-ups.)

Authentication & TLS

SCRAM-SHA-256 — the API key is proven by challenge-response and never sent in the clear. (When API-key auth is disabled on the instance, the connection is open — same posture as the REST API.)
TLS — off by default; configure a keystore to enable it, then clients can connect with sslmode=require:
```
PGWIRE_TLS_ENABLED=true
PGWIRE_TLS_KEYSTORE=/path/server.jks
PGWIRE_TLS_KEYSTORE_PASSWORD=…
```
TLS is optional (like stock Postgres): with a keystore configured the server offers SSL but still accepts plaintext.

Schema browsing & relationships

A minimal synthesized pg_catalog answers the introspection queries tools run, so schema browsing works: listing tables and their columns/types (getTables/getColumns), and — usefully — links between collections surface as SQL foreign keys (getImportedKeys/getExportedKeys), with a link target reported as the referenced table's primary key. So tools like DBeaver or Metabase can show how your collections relate.

Connector compatibility

Beyond drivers, several Postgres connectors read Aito directly — they introspect the catalog and stream tables over the wire:

Connector	What works
`psql`, pgjdbc (JDBC), psycopg / pg8000	connect, browse (`\dt` / `\d`), read, write, bulk-load
ClickHouse — `postgresql()` table function / `PostgreSQL` engine	introspect, read, filter, ClickHouse-side aggregate, and write-back
DuckDB — `postgres_scanner` (`ATTACH … (TYPE postgres)`)	introspect, read, filter, aggregate, `count(*)`, `CREATE TABLE AS SELECT`
PostgreSQL — `postgres_fdw` (`IMPORT FOREIGN SCHEMA`)	a foreign table per collection; per-table read, filter, aggregate, `CREATE TABLE AS SELECT`

To make these work, the wire layer handles the dialect and session behaviour each connector emits — double-quoted and schema-qualified names ("public"."t"), GROUP BY/ORDER BY ordinals, NULLS FIRST|LAST, COLLATE, SELECT NULL row-count probes, cursors (DECLARE/FETCH), binary COPY … TO STDOUT, and the version/settings probes clients run on connect.

Worked examples

In every case the password is your Aito API key, user is aito (ignored), and dbname selects the environment. Enable the listener first (PGWIRE_ENABLED=true, port 5432).

ClickHouse — read and write through the postgresql() table function (or a PostgreSQL-engine table):

-- read + filter (pushed to Aito)
SELECT name, price
FROM postgresql('aito-host:5432', 'aito', 'products', 'aito', '<API key>')
WHERE price > 10;

-- persistent table backed by the Aito collection
CREATE TABLE products_ch (name String, price Float64)
ENGINE = PostgreSQL('aito-host:5432', 'aito', 'products', 'aito', '<API key>');

DuckDB — ATTACH the whole environment via postgres_scanner:

INSTALL postgres; LOAD postgres;
ATTACH 'host=aito-host port=5432 dbname=aito user=aito password=<API key>'
  AS aito (TYPE postgres);

SELECT category, count(*) FROM aito.products GROUP BY category;
CREATE TABLE local_products AS SELECT * FROM aito.products;   -- materialize locally

PostgreSQL (postgres_fdw) — import every collection as a foreign table, then query them like local tables:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER aito FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'aito-host', port '5432', dbname 'aito');
CREATE USER MAPPING FOR CURRENT_USER SERVER aito
  OPTIONS (user 'aito', password '<API key>');

IMPORT FOREIGN SCHEMA public FROM SERVER aito INTO public;   -- one foreign table per collection
SELECT name, price FROM products WHERE price > 10;

A cross-table remote join pushdown (FROM a JOIN b) across two foreign tables isn't supported — join the imported tables locally, or SET use_remote_estimate = off and disable join pushdown on the server. Joins along an Aito link work (see JOIN along a link).

SQL Reference — operator table, SQLSTATE codes, limits.
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.