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. Tables may be aliased — FROM orders o / FROM orders AS o, on either side of a JOIN — and o.col resolves as the qualified column. As in Postgres, an alias shadows the table name: after FROM orders o, orders.id is an error (use o.id). This is the form BI tools and ORMs emit. 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).

Aito's functions live in an aito schema

Aito's own SQL 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 the canonical one. The bare name is an alias we offer while nothing else claims it — and that 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(), and the server cannot see it. Schema-qualifying is Postgres's own answer to this, the same way pg_trgm, PostGIS and MADlib coexist.

Practically: use bare names interactively, and prefer aito.-qualified names in saved queries, views and BI tools, where a later extension install shouldn't change what your query means.

Postgres's own functions are not in this schema. coalesce, cast, upper, lower, length, concat and the aggregates are Postgres's 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 it always has; only a name directly followed by ( is read as a possibly-qualified function. The aito schema is visible in pg_namespace / information_schema.schemata, so \dn lists it — but the functions themselves are not yet introspectable (pg_proc is unimplemented, so a tool's function list is empty for every schema).

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.

A prediction column without a LIMIT returns 10 rows. predictions(col) runs one inference per returned row — the row's own evidence, the predicted field held out — so an unbounded SELECT predictions(x) FROM big_table is a row-at-a-time model scan: at ~100 ms per row, a thousand rows is a hundred seconds. The default of 10 is the same convention as Aito's JSON _query, Elasticsearch's size, and this dialect's own recommend / relate / search / match (all k default 10), so prediction is no longer the odd one out:

SELECT id, predictions(GLCode) FROM invoices;             -- 10 rows
SELECT id, predictions(GLCode) FROM invoices LIMIT 500;   -- 500 rows — explicit wins
SELECT id, GLCode FROM invoices;                          -- ALL rows — no prediction column

An explicit LIMIT always wins, above or below the default. A SELECT with no prediction column is untouched and still returns every row — a sync or BI tool reading a whole table is unaffected.

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.

Predicting for a row that does not exist

predict(col) above is a projection: it annotates the rows your query returns. That means it answers "what is the GL code for these invoices?" — and when the WHERE matches nothing, it correctly returns nothing:

SELECT id, predict(GLCode) FROM invoices
WHERE ProductName = 'Quantum Widgets';
-- 0 rows: no such invoice exists, so there is no row to annotate

Often the question is the other way round: "an invoice like this just arrived — what GL code should it get?" There is no row yet. For that, put predict in FROM and state the evidence with given:

SELECT * FROM predict('invoices', 'GLCode',
                      given => 'ProductName = ''Quantum Widgets''
                                AND Amount = 4200');
-- value | p
-- E002  | 0.71

Nothing needs to match — the evidence is stated, not looked up, so you always get an answer. This is the form to use for scoring an incoming record, a what-if, or a form field the user is still filling in.

predictions(...) is the same query returning the ranked distribution instead of just the top answer:

SELECT * FROM predictions('invoices', 'GLCode',
                          given => 'ProductName = ''Quantum Widgets''', k => 3);
-- value | p
-- E002  | 0.71
-- E001  | 0.18
-- E014  | 0.06

predict(...) defaults to one row (the argmax), predictions(...) to ten. Add why => true for the explanation factor tree behind each candidate, exactly as on recommend:

SELECT value, p, why FROM predict('invoices', 'GLCode',
                                  given => 'ProductName = ''Quantum Widgets''',
                                  why => true);

Which form do I want?

	SELECT predict(col) … WHERE …	SELECT * FROM predict('t','col', given => …)
the question	"fill this column in for the rows I have"	"what would this be, for a row I don't have?"
evidence	the row itself + the WHERE	the given string
no match	0 rows	still answers
returns	your columns, one row each	(value, p[, why])

match('t','col', …) is an older name for predictions(...) and runs the identical query; both given => and where => are accepted on it.

Recommend

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 on by default and binds to the same address as the HTTP server — localhost unless you set BIND_ADDRESS. So a server you just started already speaks SQL on port 5432, and the Docker image publishes it.

PGWIRE_ENABLED=false     # turn the listener off
PGWIRE_PORT=5432         # default 5432

Permissions follow the key you connect with. A read-write API key gets a read-write session; a read-only key can query but cannot INSERT, UPDATE, DELETE, CREATE, DROP or COPY … FROM — those are refused with SQLSTATE 42501 (insufficient_privilege), exactly as the HTTP API refuses a read-only key on a write endpoint.

Running without authentication. If API-key auth is disabled, every connection is accepted with no credential and gets full read-write SQL. The listener therefore refuses to bind a non-loopback address in that state unless you set PGWIRE_ALLOW_UNAUTHENTICATED=true, which records that the open port is intentional. (It is an explicit flag rather than an automatic check because a process cannot tell whether its bind is reachable: a container must bind 0.0.0.0 to be reachable through docker -p at all, and cannot see whether the host published that port.)

Choosing a database

On a single-database server the dbname you connect with selects an environment — postgres, aito or an empty name mean the master env, and any other name is the env of that name.

On a multi-database server (one deployment hosting several databases, served over HTTP as /db/<name>/…) dbname selects the database, optionally with an env after a dot:

psql "host=$AITO_HOST port=5432 dbname=acme user=aito password=$AITO_API_KEY"
psql "host=$AITO_HOST port=5432 dbname=acme.sandbox user=aito password=$AITO_API_KEY"

The password is that database's own API key — each database has its own, and a key for one database does not open another. A session is confined to the database it named: another database's tables are not reachable from it, exactly as over HTTP.

Two behaviours worth knowing:

• An unknown database and a wrong key fail identically. Naming a database that does not exist is reported as an authentication failure, not as "no such database", so a reachable port cannot be used to discover which databases exist.
• A session does not outlive its database. If the database is deleted while you are connected, the session is terminated (SQLSTATE 57P01) rather than left running against something that is gone. Reconnect to continue.

On a multi-database server the listener is not started unless PGWIRE_ENABLED is set explicitly — exposing one SQL port across several databases is a deliberate operator decision.

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.

dbname on a multi-database server — the error you will hit once

On a multi-database server (<host>/db/<name>/), dbname selects the database, not the environment. Get it wrong and you get:

psql: error: FATAL: password authentication failed

That is deliberate: an unknown database and a wrong key are reported identically, so a reachable port cannot be used to enumerate which databases exist. The security decision is right, and the consequence is that a user with a perfectly good key spends their time debugging the key.

So: if authentication fails and you are confident in the key, check the database name first. It is the more common of the two causes, and the error cannot tell you which one you hit.

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 — and lets you declare Aito's model, not just a relational shell:

CREATE TABLE customers (id VARCHAR PRIMARY KEY, segment VARCHAR);

CREATE TABLE tickets (
  id        VARCHAR PRIMARY KEY,
  customer  VARCHAR REFERENCES customers(id),   -- a link
  body      TEXT,                               -- analysed prose
  notes     TEXT COLLATE "fi",                  -- analysed with the Finnish analyzer
  status    VARCHAR                             -- a categorical value
);
CREATE TABLE IF NOT EXISTS items (id INTEGER);  -- no-op if it exists

varchar is categorical, text is analysed. This is the distinction Postgres developers already draw — varchar for a short bounded identifier, text for prose — so Aito adopts it rather than inventing a marker:

SQL	Aito	means
varchar, char(n)	String	exact-match value: ids, codes, enums, statuses
text	Text (analysed)	prose: searchable with @@, usable as free-text evidence for predict
text COLLATE "fi"	Text with that analyzer	COLLATE is where Postgres already puts language-specific text behaviour

Only an analysed column can be @@-searched or used as free-text evidence; @@ on a varchar is refused rather than silently answering. On an analysed column a bare = (and @@) decomposes the value per token into evidence — matching a word of the prose, not the whole string byte-for-byte.

REFERENCES declares a link. customer VARCHAR REFERENCES customers(id) creates the link Aito's inference walks, and JOIN … ON tickets.customer = customers.id is supported because that link exists. Introspection already reports links as foreign keys (getImportedKeys), so a schema now round-trips: create it, read it back, create it again. Table-level FOREIGN KEY (c) REFERENCES t(c2) is the same thing; the target column must be named.

The rest maps as you would expect (integer→Int, bigint→Long, 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. The listener is on by default on port 5432; use a read-write key for the examples that write.

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;

Related

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