Skip to main content

SELECT

SELECT * | <cols> FROM <table>
  [WHERE entity = '...' [AND ts ...]]
  [LATEST ON ts PARTITION BY entity]
  [ORDER BY ts [ASC|DESC]]
  [LIMIT <n>] [OFFSET <n>];

Range reads (immutable tables)​

Rows are reconstructed: one row per timestamp where any selected channel has a real sample; other cells filled per channel policy (forward-fill). Selecting fewer channels therefore returns fewer rows — the timestamp union is over what you asked for.

SELECT entity, ts, speed, event FROM telemetry
  WHERE entity = 'veh-1' AND ts BETWEEN 1700000000000 AND 1700086400000;

Without an entity filter the scan covers every entity, ordered by ts across all of them.

Device shadow​

SELECT * FROM telemetry LATEST ON ts PARTITION BY entity [WHERE entity = '...'];

One row per entity: each channel at its stream tail, ts = the newest sample among the selected channels. Fully deleted entities do not appear.

Mutable tables​

A plain SELECT on a MUTABLE table always resolves to the current version per entity (same shape as LATEST ON). Version history is not a query surface — it is merge fuel.

Counting​

SELECT count(*) gives a row count. On rows tables it is the live current-state count (optionally filtered), resolved in one linear pass. On timeseries tables, counting is windowed — count(...) lives under SAMPLE BY, never bare.

Ordering, limits, pagination​

ORDER BY ts (only ts) with ASC/DESC, then the pg pagination pair — LIMIT n [OFFSET m], either clause order:

SELECT ts, speed FROM telemetry WHERE entity = 'veh-1' ORDER BY ts DESC LIMIT 100;
SELECT ts, speed FROM telemetry WHERE entity = 'veh-1' LIMIT 100 OFFSET 200;  -- page 3

OFFSET works on every table family (a kv scan pages the same way) and is what dashboard tools and ORMs generate; for large tables prefer the keyset idiom (WHERE ts > last here, WHERE x_rev > cursor on rows tables) — it stays cheap at any depth and no page shifts under concurrent writes.