SQL & Postgres — Quickstart

⚠️ Early access. The SQL surface and the PostgreSQL wire protocol are new and deliberately minimal — a small SELECT subset, not a full Postgres. Great for evaluation and connecting existing tools; not yet production-hardened.

Aito v2 speaks a small, PostgreSQL-compatible subset of SQL. It runs the same engine as the JSON _query API — a statement is parsed and lowered to the equivalent v2 query, so a SELECT returns the same rows as its JSON equivalent. Parity by construction: the same statement is valid Postgres and valid here.

▶️ Try it now — no setup: the interactive SQL console runs read-only SQL against a live demo dataset right in your browser.

Two ways to query

Over REST — POST /api/v2/_sql with the SQL statement as the body:

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

The response is the standard v2 result JSON — identical to the equivalent _query response. This endpoint is read-only.

Over the Postgres wire protocol — point psql, a JDBC/ODBC tool, psycopg, or a Postgres connector (ClickHouse, DuckDB, postgres_fdw) at Aito and query it like any Postgres database. The password is your Aito API key; the user is ignored; the database selects the environment:

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

The listener is off by default — enable it with PGWIRE_ENABLED=true (see the Guide). Unlike the read-only REST endpoint, the wire protocol also accepts writes (INSERT, CREATE TABLE, COPY, …).

First queries

Plain relational SQL — projection, filter, sort, group, aggregate:

SELECT name, category, price FROM products WHERE price > 2 ORDER BY price DESC LIMIT 10;

SELECT category, count(*) AS n, avg(price) AS avg_price
FROM products GROUP BY category ORDER BY n DESC;

…and Aito's inference, right in SQL — predict(col) is the argmax, predictions(col) the ranked distribution (e.g. route an invoice to a GL code):

SELECT ProductName, predict(GLCode) FROM invoices WHERE ProductName = 'Cloud Services';
SELECT predictions(GLCode)          FROM invoices WHERE ProductName = 'Cloud Services';

Next

  • SQL Guide — the full surface: the SELECT subset, joins along a link, prediction, writes, auth/TLS, and the Postgres connectors (ClickHouse / DuckDB / postgres_fdw).
  • SQL Reference — the operator & function table, SQLSTATE error codes, and current limitations.
  • SQL console — run read-only SQL against the live demo.
  • Query Reference — the JSON query language _sql lowers to (and where recommend / search / learned ranking live).