SELECT

Read queries, including streaming (STREAM), historical (HISTORICAL), and temporal (AS OF) forms.

One-shot read

SELECT market, amount FROM trades WHERE amount > 1000;
SELECT count() FROM balances WHERE balance > 0;

Predicates include IS NULL / IS NOT NULL:

SELECT * FROM readings WHERE value IS NOT NULL;

Streaming read

STREAM SELECT opens a live subscription (snapshot, then incremental changes):

STREAM SELECT market, amount FROM trades WHERE amount > 1000;
STREAM SELECT count() FROM trades;

See Streaming reads for details and the SELECT STREAM CLI spelling.

Historical read

HISTORICAL SELECT reads over history as a one-shot; STREAM HISTORICAL replays then follows the live tail.

HISTORICAL SELECT * FROM plain;
HISTORICAL SELECT ts, price FROM ex ORDER BY ts;
STREAM HISTORICAL SELECT price FROM px;

Temporal read (AS OF)

Reconstruct state as of a point in time on either the system-time or valid-time axis:

SELECT chain_id, name FROM token_state FOR SYSTEM_TIME AS OF 42;
SELECT id, a FROM t FOR VALID_TIME AS OF 20 ORDER BY b;

EXPLAIN

Prefix any read with EXPLAIN to see the plan. It composes with the streaming and temporal forms:

EXPLAIN SELECT chain_id, name FROM token_state FOR SYSTEM_TIME AS OF 42;
EXPLAIN STREAM HISTORICAL SELECT price FROM px;
EXPLAIN HISTORICAL SELECT ts, price FROM ex ORDER BY ts;

Nearest-neighbor (ANN) read

An ORDER BY <distance>(col, $q) LIMIT k over a vector(N) column with an HNSW index rewrites to the VectorTopK operator. The query vector is a '[...]' literal.

SELECT id, cosine_distance(emb, '[1,0,0,0,0,0,0,0]')
FROM emb_t
ORDER BY cosine_distance(emb, '[1,0,0,0,0,0,0,0]')
LIMIT 3;

See Vector search for the full path.

Views & materialized views

CREATE VIEW v AS SELECT * FROM token_state FOR SYSTEM_TIME AS OF 42;
CREATE MATERIALIZED VIEW mv AS SELECT * FROM token_state FOR SYSTEM_TIME AS OF 42;