CREATE TABLE

CREATE TABLE <name> (
  <channel> <TYPE> [FILL HOLD|NONE] [STALENESS <duration>|INFINITE] [INDEX],
  ...
) [WITH (MUTABLE | RETENTION <duration> [, ENTITY UUID])];

This declares a timeseries table — the first of the three families (rows and kv have their own DDL). entity and ts are implicit on every timeseries table and cannot be declared. The schema is not set in stone: columns can be added, tuned, renamed and dropped later with ALTER TABLE.

Types

Accepted spellings	Stored as
DOUBLE, FLOAT8, FLOAT64, F64, REAL	f64
TEXT, STRING, VARCHAR, SYMBOL	string
BOOL, BOOLEAN	f64 (semantic bool)
INT, INTEGER, INT64, I64, BIGINT, INT8	f64 (semantic int)
GEO, POINT	i64 (semantic point)
BYTES, BYTEA, BLOB, BINARY	bytes (raw blob)

Anything else (JSONB, arrays, timestamps-as-types...) is rejected: the storage kinds are fixed at f64, string, i64 and bytes, and the catalog layers semantic types (BOOL, INT, GEO) over them. The core never grows a new physical kind; flexibility lives in code.

BOOL does not break that freeze — it is a catalog-level 0/1 view over f64. Writes accept true/false/1/0 and reject anything else; reads surface real booleans (t/f over pg-wire, true/false in JSON). Storage cost is negligible: the Gorilla/XOR encoding stores an unchanged value in one bit, so a digital signal costs ~1 bit per sample.

INT is the same idea for whole numbers: writes reject fractional values, reads surface real integers (int8 over pg-wire, no .0 in JSON). An f64 holds integers exactly up to 2⁵³, far beyond any counter or code this engine will meet; declare INT for the intent and the clean rendering, not for range.

GEO is the same idea for points: one WGS84 position per value, written and read as 'lat,lon' text. Internally the point is a single 64-bit cell (~2 cm resolution — finer than GPS), so a vehicle trail compresses like any integer stream and spatial filters run as integer ranges. It unlocks GEO_BBOX / GEO_DWITHIN in WHERE, and FILL HOLD on a geo column reads as "last known position".

Channel attributes

• FILL HOLD — state channel; last value carries forward on reads
• FILL NONE — event channel; never filled (default)
• STALENESS 60 s — with HOLD: held values older than this read as null; default INFINITE
• INDEX — text/bytes columns: a secondary exact index. Every sealed partition records value→block postings, so WHERE col = '...' answers from the index instead of scanning — the shape a unique key column (a row_key, an order id) wants. The index covers partitions written after the declaration; declare it at CREATE (or re-import) for full coverage — a partially covered probe silently falls back to the scan, so it is never wrong, only slower.

Table options

• WITH (MUTABLE) — last-write-wins per entity, DELETE allowed, no retention
• WITH (RETENTION 90 d) — immutable table: partitions older than the window are dropped; toggle later with ALTER TABLE … SET RETENTION
• WITH (ENTITY UUID) — the wall: writes whose entity is not a UUID are refused; register devices in a rows table and use its engine-generated id as the entity
• WITH (FLEXIBLE) — schema-on-write: an INSERT naming a column the table has not seen adds it (inferred from the value) instead of failing. The default is strict — unknown columns are refused. Toggle later with ALTER TABLE … SET FLEXIBLE|STRICT.
• WITH (SYNC) — stamp every ingested fragment with the hidden x_seq sequence and expose the sync feed: clients (the rule engine first among them) drain exactly what arrived since their cursor. Off by default; toggle later with ALTER TABLE … SET SYNC [OFF]. Options combine: WITH (RETENTION 30 d, FLEXIBLE, SYNC).

MUTABLE and RETENTION are mutually exclusive.

For registries and other current-state data, see CREATE ROWS TABLE — the second table family.

Examples

CREATE TABLE telemetry (
  speed  DOUBLE FILL HOLD STALENESS 60 s,
  status TEXT   FILL HOLD,
  event  TEXT   FILL NONE
) WITH (RETENTION 90 d);

CREATE TABLE positions (lat DOUBLE, lon DOUBLE) WITH (MUTABLE, ENTITY UUID);