SQL & Postgres — Reference

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

Aito's SQL is a projection of the v2 JSON query language, not a separate engine: a statement is parsed, lowered to the same query JSON the _query endpoint accepts, and run by the same engine. That is why the correspondence table below is exact rather than approximate, and why an operator with no SQL spelling is a missing spelling, not a missing capability — the same query is always available as JSON.

Statements

Synopses of every statement the parser accepts. Anything else is rejected with a SQLSTATE, never silently ignored. SELECT runs on both the REST endpoint and the wire protocol; every write statement is wire-protocol only — /api/v2/_sql is read-only.

SELECT

SELECT <expr> [AS alias] [, …]
  FROM <table>
  [ INNER JOIN <table2> ON <table>.<fk> = <table2>.<pk> ]
  [ WHERE <condition> ]
  [ GROUP BY <column> ]
  [ HAVING <condition> ]
  [ ORDER BY <key> [ASC | DESC]
  | ORDER BY <vector-column> <-> '[<v1>, <v2>, …]' ]
  [ LIMIT <n> ] [ OFFSET <n> ]

One table plus at most one INNER JOIN, which must follow a declared link. GROUP BY takes a single column and ORDER BY a single key. A bare SELECT with no LIMIT returns all rows (not a default page), matching Postgres.

Text search — the tsquery constructors

Postgres's four constructors mean four different things, and Aito honours the difference:

to_tsquery's operators are translated (& → juxtaposition, | → OR, ! → negation). websearch_to_tsquery's syntax is already Aito's — quoted phrases, -term, juxtaposition for AND — apart from the lowercase word or, which is rewritten outside quotes only, so the phrase "cat or dog" keeps its or.

Refused rather than dropped: to_tsquery's <-> adjacency operator (write phraseto_tsquery('a b') or a "quoted phrase") and its weights/prefixes (term:*, term:A).

Text search with highlighting — aito.search

SELECT id, name, highlight FROM aito.search('products', 'name', 'milk') LIMIT 5;

The search query type: a text match ranked by relevance. It is what hosts the three explanation projections, which are refused on an ordinary row query — so col @@ 'text' (a plain filter) cannot produce them, and this function can:

You ask for them by selecting them, not by a flag — SELECT * returns the plain columns and computes no projection. (The engine has no wildcard in a select list, so the columns have to be named anyway; naming what you want is what SQL already does.)

Similar rows — aito.knn / aito.nn

Rank rows by feature similarity to an exemplar (text fields score by BM25):

SELECT id FROM msgs WHERE aito.knn('text', 'sim card') LIMIT 5;
SELECT id FROM msgs WHERE aito.nn('text', 'sim card', threshold => 0.5);

This is a condition spelled as a function, and it mirrors the JSON operator directly — $knn takes near: {column: exemplar}, so the SQL takes the column and the exemplar. It is Aito-specific, so it lives in the aito schema.

Not to be confused with <->. The vector search operators measure distance on an embedding column; these measure feature similarity to an example row's value, and need no vector column.

LIMIT pages it, not k. In the JSON operator k applies only on a predict query, where the k nearest neighbours vote; on a row query it is ignored. Writing k => 2 here is therefore refused rather than silently doing nothing — use LIMIT.

Vector search — ORDER BY col <-> '[…]'

Nearest-neighbour search is spelled the way pgvector spells it, so a client that already emits it needs no change:

SELECT id, name FROM products
 WHERE category = 'shoes'
 ORDER BY embedding <=> '[0.12, -0.03, 0.88]'
 LIMIT 10;

All three pgvector distance operators are accepted — <-> (L2), <=> (cosine), <#> (inner product) — and against an Aito vector column they order identically. A cosine column's vectors are unit-normalised on ingest, and for unit vectors L2 distance is a monotone function of cosine similarity, so the ranking is the same whichever you write. The metric itself comes from the column's own similarity setting, not from the operator: the operator picks the spelling your client knows, not the maths. A '[…]'::vector cast is accepted and ignored, since the type comes from the column.

WHERE filters the candidates, not the results. This is the part worth being precise about: the filter is applied before the search, so LIMIT 10 means "the 10 nearest among the shoes". A database that filters afterwards answers "the 10 nearest overall, then whichever are shoes" — which quietly returns fewer than 10, or none at all.

ORDER BY … DESC is refused: it asks for the farthest rows, and nearest-neighbour search has no such reading. Ordering by a vector distance cannot be combined with another sort key.

Still JSON-only: a score floor (having/threshold), selecting the similarity score as a column, and the vector-as-evidence operators for prediction. See Vector search for those.

INSERT

INSERT INTO <table> [ (<column> [, …]) ] VALUES (<value> [, …]) [, (…) …]
  [ ON CONFLICT [ (<column>) ] DO NOTHING
  | ON CONFLICT [ (<column>) ] DO UPDATE SET <column> = <value> [, …] ]

A conflict is a duplicate primary key; without an ON CONFLICT clause it raises 23505.

UPDATE / DELETE

UPDATE <table> SET <column> = <value> [, …] [ WHERE <condition> ]

DELETE FROM <table> [ WHERE <condition> ]

Omitting WHERE affects every row, as in Postgres.

CREATE TABLE

CREATE TABLE [ IF NOT EXISTS ] <table> (
    <column> <type> [ NOT NULL | NULL ] [ PRIMARY KEY ]
                    [ COLLATE "<analyzer>" ]
                    [ REFERENCES <table2> (<column2>) ]
  [ , … ]
  [ , PRIMARY KEY (<column>) ]
  [ , FOREIGN KEY (<column>) REFERENCES <table2> (<column2>) ]
)

The column clauses are not decoration — they declare the Aito data model. COLLATE picks the text analyzer and REFERENCES creates a real link that JOIN, predict and recommend can navigate. See Data types.

ALTER TABLE / DROP

ALTER TABLE <table> ADD [COLUMN] [IF NOT EXISTS] <column> <type> [NOT NULL | NULL]
ALTER TABLE <table> DROP [COLUMN] [IF EXISTS] <column>

DROP TABLE [IF EXISTS] <table>
DROP VIEW  [IF EXISTS] <view>

CREATE VIEW

CREATE [OR REPLACE] VIEW <view> AS
  SELECT <columns | *> FROM <source> [ WHERE <equality> [AND …] ]

A view is a materialised, from-able collection kept current by incremental refresh — not stored SELECT text. See Views for the v1 limits (single source; equality-only baked WHERE).

COPY

COPY <table> [ (<column> [, …]) ] FROM STDIN
  [ WITH ( FORMAT csv|text|binary
         , DELIMITER '<c>' , NULL '<s>' , QUOTE '<c>' , HEADER ) ]

Bulk load over the wire protocol — the fast path for large ingests. COPY … TO (export) is not supported and is refused explicitly.

Session, transaction & probe statements

Transactions are real. BEGIN / START TRANSACTION, COMMIT / END and ROLLBACK / ABORT do what they say: writes inside a block are buffered, and COMMIT applies them together. ROLLBACK discards them.

BEGIN;
INSERT INTO items (id, name) VALUES (1, 'a');
INSERT INTO items (id, name) VALUES (2, 'b');
COMMIT;                     -- both rows land together, or neither

• Read-your-writes. Inside a block, queries see the block's own uncommitted writes on top of the state at BEGIN.
• COMMIT merges; it does not clobber. The buffered writes are replayed onto the current state, so a row written by someone else between your BEGIN and COMMIT survives.
• A failed statement poisons the block with SQLSTATE 25P02, and every later statement is refused until COMMIT or ROLLBACK — as in Postgres. COMMIT of a poisoned block rolls back.
• Isolation is roughly READ COMMITTED. Independent rows merge cleanly; a read-modify-write racing a concurrent change to the same row is last-writer-wins per row, so write skew is possible.

Savepoints work for partial rollback:

BEGIN;
INSERT INTO items (id, name) VALUES (1, 'kept');
SAVEPOINT s1;
INSERT INTO items (id, name) VALUES (2, 'undone');
ROLLBACK TO SAVEPOINT s1;   -- row 2 dropped, row 1 still pending
COMMIT;                     -- row 1 lands

Rolling back to a savepoint also recovers a poisoned block, so a failed statement need not lose the whole transaction. RELEASE [SAVEPOINT] s forgets a savepoint while keeping its writes; rolling back to a savepoint that does not exist is an error (3B001), never a silent full rollback.

SET is still accepted and ignored — including SET search_path, so the aito. schema cannot be put on a search path; qualify explicitly when you need the qualified form. SHOW is likewise answered for driver compatibility.

DISCARD ALL really discards. It drops this connection's prepared statements, portals and open cursors, and clears any pending transaction state — which matters because a connection pooler runs it between users and reads the returned tag as proof the connection is clean. Cursors in particular hold a materialised result set, so leaving them would leave the previous user's rows readable by name. As in Postgres it is refused inside a transaction block (25001). DISCARD PLANS / SEQUENCES / TEMP are accepted and genuinely do nothing — there is no plan cache, there are no sequences, and there are no temp tables.

Cursors (DECLARE / FETCH) are read-only and materialise the whole result up front.

A batch is one implicit transaction, as in Postgres: if a later statement fails, the earlier ones do not stand. This holds on both protocols — a multi-statement simple query, and an extended-protocol sequence up to its Sync (which is what most drivers, including pgjdbc, actually send, because they split a multi-statement string client-side). A string carrying its own BEGIN/COMMIT is the client managing the block and runs as written, and a single statement still commits on its own.

Data types

CREATE TABLE maps Postgres types onto Aito's data model. The mapping is the declaration — the type you choose decides how a column can be queried and how inference treats it.

Any other type — uuid, json, … — is rejected at CREATE TABLE with a message listing what is supported; nothing is silently coerced. A column is nullable unless it is declared NOT NULL or is part of the primary key.

text vs varchar — the choice that matters

This is the one mapping worth deliberate thought, and it is deliberately not Postgres's distinction (where both are the same type with a length check):

• varchar is a category. Whole values are compared, so it is what you want for a status, a country code, a product id — and what predict learns to output.
• text is analysed. Values are tokenised, so @@ full-text search works over them and inference reasons about individual words — what you want for a description, a note, a subject line.

Getting it wrong is silent at write time and disappointing at query time: @@ on a varchar column has no tokens to match.

vector(n) — embeddings

CREATE TABLE docs (id varchar PRIMARY KEY, embedding vector(384));
INSERT INTO docs (id, embedding) VALUES ('a', '[0.1, 0.2, …]');

The dimension is part of the type, not a display width like varchar(255) — a column of unknown width could not validate what is written to it — so a bare vector is refused. Values are written with pgvector's literal syntax, and a value of the wrong width is refused at ingest rather than padded or truncated. Search them with ORDER BY … <-> '[…]'.

COLLATE — choosing the analyzer, or the vector metric

description text COLLATE "english"

The collation names the text analyzer (english, finnish, … ) rather than a sort order — so stemming and stop-words match the language of the content.

On a vector column it names the similarity metric — cosine (the default) or dotProduct:

embedding vector(384) COLLATE \"dotProduct\"

That is not a stretch of the word: a collation is the rule by which a column's values are compared, and for a vector column the comparison rule is exactly the metric. An unknown analyzer or metric is refused, naming the ones that exist.

REFERENCES — declaring a link

product_id varchar REFERENCES products(id)

Creates an Aito link, which is what makes INNER JOIN … ON work, and what lets predict/recommend reason across tables. A foreign key here is a navigable relationship, not just a constraint.

Timestamps

A timestamp column stores a point in time, normalised to UTC on write. That normalisation is the point: an offset-bearing literal is converted, not stored verbatim, so '2026-08-21T09:00:00+03:00' and '2026-08-21T06:00:00Z' are the same stored instant and can never sort or range-filter against each other incorrectly. An unparseable value is rejected, never stored wrong. Values are kept at millisecond precision, and the column reports the timestamptz OID.

CREATE TABLE events (id integer PRIMARY KEY, ts timestamp);
SELECT id FROM events WHERE ts > '2026-08-21T09:30:00+03:00';   -- any offset works

now() and intervals. now() / current_timestamp may be used in a WHERE, with interval arithmetic:

SELECT id FROM events WHERE ts > now() - interval '30 days';
SELECT id FROM events WHERE ts BETWEEN now() - interval '2 weeks' AND now();
SELECT id FROM events WHERE ts < now() - interval '1 year 2 months';

now() is a constant for the whole statement (Postgres semantics) and the interval folds at parse time, so the query stays a pure function of the data and the submission time. Units: year, month, week, day, hour, minute, second (singular or plural, and compound as above). Month and year arithmetic is calendar-correct. An unknown unit is refused.

Buckets and parts. date_trunc(unit, ts) truncates down to a unit and returns a timestamp; EXTRACT(field FROM ts) reads a single component:

SELECT date_trunc('month', ts) AS m FROM events;      -- every August instant → 2026-08-01T00:00:00Z
SELECT id FROM events WHERE EXTRACT(HOUR FROM ts) = 9;

date_trunc units are year, quarter, month, week (ISO, Monday), day, hour, minute, second. EXTRACT fields include YEAR, QUARTER, MONTH, DAY, DOY, DOW/ISODOW, HOUR, MINUTE.

Derived columns. Declaring ts timestamp also materialises its derived parts as real, queryable columns — ts.year, ts.quarter, ts.month, ts.weekOfMonth, ts.day, ts.weekday, ts.dayOfWeek, ts.hour, ts.minute, ts.dayOfYear. They appear in the schema, can be selected and filtered like any column, and — the reason they exist — predict and relate use them as features and targets, so cyclic structure ("orders spike on Saturday afternoons", "this job fails Saturday night") is learned out of the box instead of hand-rolled:

SELECT id FROM events WHERE "ts.weekday" = 6;         -- Saturdays (ISO 1=Mon … 7=Sun)
SELECT predict(category) FROM events WHERE "ts.hour" = 9;

The timestamp is authoritative: the derived cells are always recomputed from ts, so they cannot drift out of step with it.

Operator & function quick reference

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

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 table names (public.t). Double-quoted identifiers are supported, and Aito's own functions accept the aito. schema. = 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.)

Aito function signatures

What each of Aito's own functions takes and produces. Generated from the same declaration the parser builds its argument errors from, so an error message and this table cannot disagree.

Date ranges

A date column compares in calendar order:

SELECT id FROM orders WHERE placed > '2026-03-01';
SELECT id FROM orders WHERE placed >= '2026-01-01' AND placed <= '2026-03-31';

Before this, a date was queryable by extracted parts (EXTRACT(MONTH FROM placed) = 1) and by equality, but not by range — so a calendar-aligned bucket was expressible and an arbitrary window was not. "Last quarter", "since the incident", "the 90 days after each install" and a train/test split by time all need a range.

The bound is an ISO yyyy-MM-dd string. A malformed bound is refused, not treated as "no rows":

SELECT id FROM orders WHERE placed > 'last march';
-- ERROR: $gt on a DateType column expects an ISO date (yyyy-MM-dd), got 'last march'

Ranges on a timestamp column work the same way and additionally accept a full ISO-8601 instant with an offset — see Timestamps.

The two predicts

predict names two different operations. Which one you get is decided by where you write the call, and the difference only shows up when nothing in the table matches your evidence.

Per row — predict(col) in the SELECT list. Evidence is the row itself plus the query's WHERE. It annotates the rows the query returns:

SELECT id, predict(category) FROM invoices
WHERE vendor = 'Acme' AND amount = 150;
-- one row out per matching invoice

Hypothetical — FROM predict('t','col', given => …). Evidence is stated in given rather than read off a row, so there is nothing to match and it always returns an answer:

SELECT * FROM predict('invoices', 'category',
                      given => 'vendor = ''Acme'' AND amount = 42');
-- value    | p
-- hardware | 0.4691

The distinction matters because these behave differently in exactly the case you are most likely to care about — an invoice you have not seen:

predict(...) is the argmax and returns one row; predictions(...) is the same query returning the ranked distribution (k defaults to 10). Add why => true to either for the explanation factor tree, as on recommend. match(...) is the v1 name for predictions(...) and runs the identical query.

Highlight markup — startSel / stopSel

highlight returns the matched field with the query's tokens marked. By default the engine picks the markup; startSel and stopSel set it:

SELECT id, highlight
FROM aito.search('products', 'description', 'red shoe',
                 startSel => '<mark>', stopSel => '</mark>');

Either may be given alone; the other keeps its default. These are Postgres's own ts_headline option names, taken as named arguments rather than as its options string ('StartSel=<b>, StopSel=</b>') — a string would be a second grammar inside a literal, with its own parse errors, which nothing could check for you.

ts_headline(...) itself is not implemented, and is deliberately left unclaimed rather than bound to something that behaves differently. It is a scalar over a document and a tsquery; the natural home for it is a plain WHERE col @@ 'text' query rather than the search(...) table function, which already knows both.

The negative tags (negPreTag / negPostTag) have no SQL spelling. They mark evidence against a candidate, which is a $why concept — a search hit's tokens either matched or are not there. They remain available in the JSON { "$highlight": { … } } form.

Function names and the aito schema

Aito's own functions — predict, predictions, recommend, relate — answer to two spellings:

SELECT aito.predict(GLCode) FROM invoices;   -- canonical
SELECT predict(GLCode)      FROM invoices;   -- alias, while the name is free

The qualified form is canonical. The bare name is an alias offered only while nothing else claims it — which can never be a guarantee, because whether a name is taken depends on what your client has installed: CREATE EXTENSION pg_trgm is one statement away from defining similarity(). Schema-qualifying is Postgres's own answer to this, the way pg_trgm, PostGIS and MADlib coexist.

Use bare names interactively; prefer aito.-qualified names in saved queries, views and BI tools, where a later extension install should not change what a query means. (SET search_path is a no-op, so qualify explicitly.)

Postgres's own functions are not in this schema. coalesce, cast, upper, lower, length, concat and the aggregates are Postgres functions that Aito implements faithfully — sharing the name is the compatibility promise — so they are called unqualified, and aito.coalesce(…) is an error.

A dot still means a link path. orders.customer.name navigates a link, as always; only a name directly followed by ( is read as a possibly-qualified function.

Result column names

A SQL query answers under SQL column names, on both transports. SELECT sum(price) returns a column called sum whether you read it over /api/v2/_sql or over the PostgreSQL wire protocol; SELECT * FROM recommend(...) returns value and p; AS renames as you would expect.

The last row matters for anything that introspects over the wire protocol: SELECT * names the columns the SCHEMA declares, so a column that is unset on some rows still appears (as NULL), and LIMIT 0 describes the full row shape rather than returning no columns at all. Over /api/v2/_sql the answer is JSON, where a row simply omits a key it has no value for — the same convention _query uses, so an absent key reads as null.

The right-hand column is what the equivalent JSON query returns, and it is unchanged: POST /api/v2/_query speaks the JSON vocabulary. Only the SQL surfaces use SQL names.

JSON ↔ SQL correspondence

Every v2 JSON query operator and how it is spelled in SQL. Because SQL lowers into that JSON, this table is the mapping the engine actually uses.

Three kinds of entry are worth reading carefully:

• operator — SQL's own grammar already spells it ($gt is >), so there is no function to learn.
• none — deliberately not in SQL. The learned-ranking blends are a composable algebra of probability lifts, and ORDER BY has no syntax for it; use the JSON query API.
• implied — you never write it; a SQL construct produces it. recommend(..) yields the p column, GROUP BY lowers to a get.
• — — a spelling that should exist and does not yet.

Every operator is listed: declaring a SQL answer — including “none, because …” — is required of each one, so this table has no silent omissions.

Catalog & introspection

Enough of pg_catalog and information_schema is answered for Postgres tools to browse an Aito database: pg_class, pg_attribute, pg_namespace, pg_type, pg_database, pg_settings, pg_roles, pg_description, pg_tables and the information_schema views behind them — so \dt, \d table, JDBC getTables/getColumns and the equivalents in DBeaver, DuckDB and ClickHouse work.

Two schemas are reported: public, holding the tables, and aito, holding Aito's functions.

Functions are introspectable. pg_proc lists Aito's functions, so \df in psql and the function browsers in DBeaver, DataGrip and Metabase show predict, predictions, recommend, search, match, relate, patterns, knn, nn, cluster and semantic, each with its arguments and whether it returns a row set. The rows are generated from the same declaration that produces the signature table above, so a function cannot appear in one and be missing from the other.

Catalog completion is necessarily shallower than that table: an argument that carries a condition or a column list is a quoted string (goal => 'churn = true'), which in catalog terms is just text. Unrecognised catalog queries return an empty result rather than an error, so an unusual tool may show nothing rather than fail.

Warnings and notices

Some statements succeed but carry a note worth reading — most usefully, a WHERE on a column the collection does not declare. That condition is satisfiable by no row, so the query correctly returns nothing; without the note, a typo is indistinguishable from an empty table.

SELECT id FROM products WHERE colour = 'red';   -- 'color' is the column

• Over /api/v2/_sql, the response body carries a warnings array — { code, message, severity, field } — alongside hits, exactly as the JSON query API returns it.
• Over pgwire, each note arrives as a PostgreSQL NoticeResponse: psql prints it, JDBC exposes it via Statement.getWarnings(), psycopg via connection.notices. A warning is SQLSTATE 01000, an informational note 00000, and the Aito code (e.g. where.unknown_field) travels in the notice's Detail field.

Notices are additive: a statement that produces none is unchanged, so a client that never reads them behaves exactly as before.

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.

0A000 means narrower, 42601 means wrong. The distinction is load-bearing for federating clients: postgres_fdw, DuckDB and SQLAlchemy fall back to client-side execution on 0A000, but surface the query as broken on 42601. So a construct Aito simply doesn't implement yet reports 0A000 — asking a tool to do it locally — while 42601 is reserved for SQL that is not valid anywhere.

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

Limitations

• Date parts compare with = only. Day-of-week works in both Postgres spellings: Aito stores ISO-8601 (1=Monday … 7=Sunday), which ISODOW matches exactly, so DOW (0=Sunday … 6=Saturday) is converted — DOW = 0 and ISODOW = 7 both mean Sunday. A date part in an UPDATE/DELETE WHERE, or in HAVING, is refused rather than evaluated by a second implementation that might read it differently.

• Table aliases shadow the table name, as in Postgres. FROM orders o and FROM orders AS o both work, on either side of a JOIN, and o.col resolves as the qualified column. Once a table is aliased the original name stops being a valid qualifier — SELECT orders.id FROM orders o is an error naming the alias to use, which is what Postgres does. (Being additive instead would accept queries Postgres rejects and teach a portable-looking habit that is not portable.) One alias cannot bind two tables.

• 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.

curl -s "$AITO_INSTANCE/api/v2/_sql" \
  -H "x-api-key: $AITO_API_KEY" -H "Content-Type: text/plain" \
  --data "SELECT id FROM products WHERE price > 10"

• 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.