First queries

Create append, keyed, and attribute tables, insert rows, run a streaming read, and define a continuous transform.

This walkthrough uses trading-flavored data. Every statement here follows the engine's own test SQL shapes.

1. An append table

Append tables keep every row in arrival order — the base for history and for feeding streams and transforms.

CREATE TABLE trades (
  id      UInt64 NOT NULL,
  market  String NOT NULL,
  amount  UInt64 NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO trades (id, market, amount) VALUES
  (1, 'BTC-USD', 4120),
  (2, 'ETH-USD', 2880);

SELECT market, amount FROM trades WHERE amount > 1000;

2. A keyed table

Keyed tables collapse to the latest value per primary key, giving O(1) current-state reads and exact counts. DELTA (keep = 'latest') pins the live value per key.

CREATE TABLE balances (
  address  String NOT NULL,
  balance  UInt256,
  PRIMARY KEY (address)
) SETTINGS storage_policy = 'disk_data'
  DELTA (keep = 'latest');

-- exact count of holders, always correct
SELECT count() FROM balances WHERE balance > 0;

Reads reflect the writes that just landed (read-your-writes). There is no cache to invalidate between the write and the next read.

3. An attribute table

Attribute tables model each attribute as its own relation and reconstruct entity state as of any point in time.

CREATE TABLE acct (
  id UInt64,
  ATTRIBUTE (bal UInt64, tier String)
);

INSERT INTO acct (id, bal, tier) VALUES
  (1, 100, 'gold'),
  (2, 200, 'silver');

-- current wide row per entity
SELECT id, bal, tier FROM acct;

4. A streaming read

STREAM SELECT opens a live subscription: it delivers the current result, then keeps emitting changes as new rows arrive. It does not terminate on its own.

STREAM SELECT market, amount FROM trades WHERE amount > 1000;

The nyxsql CLI advertises the equivalent SELECT STREAM … spelling for follow-live reads. The engine's tests use STREAM SELECT …. See Streaming reads for STREAM HISTORICAL (replay-then-follow).

5. A continuous transform

A transform is a standing pipeline that writes into a target table and stays fresh through PSI routing. Its body must project the target's primary key.

CREATE TRANSFORM t_a INTO k AS SELECT id, v FROM src;

Manage transforms with SHOW TRANSFORMS, ALTER TRANSFORM … PAUSE | RESUME, and DROP TRANSFORM — see Continuous transforms.

Core concepts — how these pieces fit together.
SQL reference — full statement syntax and types.