skaidb v0.234.0

skaidb query syntax

The SQL surface skaidb accepts. It's a subset of SQL — one statement per call, no subqueries or CTEs (joins, UNION, aggregates, prepared statements, and embedded-engine transactions are supported; see below). Rows are schema-less documents keyed by a declared primary key; any field not present reads as NULL.

Maintenance: this document is the source of truth for the query language. Whenever the parser/grammar changes — a new statement, clause, operator, literal form, function, or type — update this file in the same change.

Statements

A <table> reference may be qualified by a database: <database> . <table> (e.g. shop.orders). An unqualified table resolves against the connection's current database (see Databases below).

-- DML upsert
INSERT INTO <t> (...) [OVERRIDING SYSTEM VALUE] VALUES (...)
       [ON CONFLICT DO NOTHING                       -- keep existing row; affected 0
      | ON CONFLICT DO UPDATE SET <col> = <expr>, ...] -- merge into existing row
       [RETURNING * | <expr> [AS <alias>] [, ...]]   -- the rows as they landed

-- DDL
CREATE TABLE [IF NOT EXISTS] <table> (PRIMARY KEY (<col> [, <col> ...])
       [, <col> [NOT NULL] [DEFAULT <expr>] [CHECK (<expr>)]]  -- column item (no type: see below)
       [, <col> {SERIAL | BIGSERIAL | GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(<seq-option> ...)]}
              [NOT NULL] [CHECK (<expr>)]]           -- auto-increment column (owned sequence)
       [, <col> GENERATED ALWAYS AS (<expr>) STORED [NOT NULL] [CHECK (<expr>)]]  -- generated column
       [, [CONSTRAINT <name>] CHECK (<expr>)]
       [, [CONSTRAINT <name>] FOREIGN KEY (<col> [, ...]) REFERENCES <parent> (<col> [, ...])
            [ON DELETE {RESTRICT | NO ACTION | CASCADE | SET NULL}]
            [ON UPDATE {RESTRICT | NO ACTION | CASCADE | SET NULL}] ...])
       [WITH (ttl = <duration>,               -- rows expire after this age
              witness = <bool>,               -- mirror to witness nodes (default true)
              replication = <n>,              -- per-table RF override
              nodes = ['<alias-or-id>', ...],  -- pin the whole table to these members
              toast_threshold = <bytes>,      -- store big fields out-of-line (value-TOAST)
              partition_by = 'range(<col>, <interval>)', -- range partitioning (see below)
              cluster_by = (<col> [, ...]),   -- physical sort + identity (see below)
              distribute_by = (<col> [, ...]))] -- place by this leading PK prefix:
                                              -- rows sharing it share a replica set
                                              -- (fixed at CREATE)
CREATE TIMESERIES TABLE [IF NOT EXISTS] <table>
       (SERIES KEY (<label> [, <label> ...])
        [, RETENTION <duration>] [, OOO <duration>])
CREATE ROLLUP [IF NOT EXISTS] <name> ON <ts-table> BUCKET <duration>
       [RETENTION <duration>]
CREATE ROLLUP [IF NOT EXISTS] <name> ON <event-table> BUCKET <duration>
       [BY (<dim> [, <dim> ...])]
       AGGREGATE (count(*)|count(<col>)|sum(<col>)|min(<col>)|max(<col>) AS <alias> [, ...])
       [REFRESH <duration>] [RETENTION <duration>]
       -- event-table form: pre-aggregated bucket table over a
       -- partitioned/clustered regular table (see below)
SHOW PARTITIONS <table>                 -- a range-partitioned table's partitions:
                                        -- start/end, rows, disk bytes, serving
DROP   TABLE [IF EXISTS] <table>
       -- cascades to every derived index on the table (secondary, search, vector)
CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <table> (<component> [, ...]) [WITH (global = true)] [WHERE <expr>]
  -- <component> := <path>[[]] | (<expr>)   -- an expression component; WHERE = a partial index
       -- WITH (global = true): value-sharded index — equality probes route to the value's
       -- replica set instead of scattering; ranges keep the scatter path (GLOBAL_INDEXES.md)
DROP   INDEX [IF EXISTS] <name>
CREATE VECTOR INDEX [IF NOT EXISTS] <name> ON <table> (<path>) DIM <n> [USING <metric>] [QUANTIZED] [EMBED]
DROP   VECTOR INDEX [IF EXISTS] <name>
ALTER  VECTOR INDEX <name> SET (ef = <n>)   -- live recall/latency tuning
CREATE GEO INDEX [IF NOT EXISTS] <name> ON <table> (<point-path>)
       -- Morton/Z-order index: geo_distance / geo_bbox prune to a neighborhood
DROP   GEO INDEX [IF EXISTS] <name>
CREATE SEARCH INDEX [IF NOT EXISTS] <name> ON <table> (<path> [, <path> ...])
       [WITH (<option> = <literal> [, <option> = <literal> ...])]
       -- options are global (analyzer, refresh_ms) or per-column (<path>.<option>)
DROP    SEARCH INDEX [IF EXISTS] <name>
REBUILD SEARCH INDEX <name>
ALTER   SEARCH INDEX <name> SET (<option> = <literal> [, ...])
        -- query-time options only: synonyms, refresh_ms,
        -- <col>.search_analyzer, <col>.boost (applied live, no reindex)
CREATE STREAM [IF NOT EXISTS] <name> ON <table> WHEN (<predicate>)
       [WITH (start = 'now' | 'earliest' [, retention = '<duration>'])]
       -- a standing filter over the table's writes; see STREAMS.md
DROP   STREAM [IF EXISTS] <name>
SHOW   STREAMS   -- (name, table, predicate, consumers, lag)
CREATE PROCEDURE [IF NOT EXISTS] <name>(<param> <TYPE> [, ...])
       BEGIN <step>; [<step>; ...] [EXCEPTION WHEN <cond> THEN <step>; ...] END
       -- a step is a SQL statement, a nested CALL, or control flow:
       --   DECLARE <v> <TYPE> [DEFAULT <expr>] | DECLARE <c> CURSOR FOR <select>
       --   SET <v> = <expr> | SELECT ... INTO <v> [, <v>] FROM ...
       --   IF <c> THEN ... [ELSEIF <c> THEN ...] [ELSE ...] END IF
       --   WHILE <c> DO ... END WHILE | FOR <v> IN (<select>) DO ... END FOR
       --   OPEN <c> | FETCH <c> INTO <v> [, <v>] | CLOSE <c> | LEAVE
       --   RETURN [<expr> | {<k>: <expr>, ...}] | BEGIN ... END
       -- see PROCEDURES.md
DROP   PROCEDURE [IF EXISTS] <name>
CALL   <name>(<expr> [, <expr> ...])   -- returns the last statement's result set
SHOW   PROCEDURES  -- (name, params, definer, created)
CREATE JOB [IF NOT EXISTS] <name>
       ON SCHEDULE { EVERY '<duration>' | CRON '<expr>' }
       CALL <procedure>(<expr> [, ...]) [WITH (<option> = <literal>, ...)]
       -- options: timezone (UTC or a fixed +HH:MM offset; CRON only),
       -- catchup, timeout, budget, ttl, result_cap; see PROCEDURES.md
DROP   JOB [IF EXISTS] <name>
SHOW   JOBS  -- (name, source, procedure, definer, owner, next_run,
             --  last_run, last_status, last_error, failures)
CREATE TRIGGER [IF NOT EXISTS] <name> ON <table> WHEN (<predicate>)
       CALL <procedure>(<expr> [, ...]) [WITH (<option> = <literal>, ...)]
       -- a job whose source is a change stream; owns a hidden stream
DROP   TRIGGER [IF EXISTS] <name>   -- removes the trigger AND its stream
SHOW   TRIGGERS  -- (name, table, predicate, procedure, definer, stream,
                 --  cursor, last_status, last_error, failures)
SUGGEST '<text>' ON <index> [COLUMN <col>] [LIMIT <n>]
EXPLAIN SCORE <select> FOR <pk-literal>
EXPLAIN <statement>
ALTER  TABLE <table> RENAME TO <new_table>
ALTER  TABLE <table> RENAME COLUMN <from> TO <to>
ALTER  TABLE <table> ADD [CONSTRAINT <name>] FOREIGN KEY (<col> [, ...])
       REFERENCES <parent> (<col> [, ...]) [ON DELETE <action>] [ON UPDATE <action>]
       [NOT VALID]                           -- skip checking existing rows
ALTER  TABLE <table> ADD [CONSTRAINT <name>] CHECK (<expr>) [NOT VALID]
ALTER  TABLE <table> VALIDATE CONSTRAINT <name>   -- FOREIGN KEY or CHECK
ALTER  TABLE <table> DROP CONSTRAINT [IF EXISTS] <name>
ALTER  TABLE <table> ALTER [COLUMN] <col> SET NOT NULL     -- checks existing rows first
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP NOT NULL
ALTER  TABLE <table> ALTER [COLUMN] <col> SET DEFAULT <expr>
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP DEFAULT
ALTER  TABLE <table> ALTER [COLUMN] <col> ADD GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY
       [(<seq-option> ...)]                  -- existing rows must already have the column
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP IDENTITY [IF EXISTS]
ALTER  TABLE <table> ALTER [COLUMN] <col> ADD GENERATED ALWAYS AS (<expr>) STORED  -- rewrites every row
ALTER  TABLE <table> ALTER [COLUMN] <col> SET EXPRESSION AS (<expr>)               -- rewrites every row
ALTER  TABLE <table> ALTER [COLUMN] <col> DROP EXPRESSION [IF EXISTS]              -- values stay as data

-- Sequences (database-scoped counters; <seq-option> is any of
--   INCREMENT [BY] <n> | MINVALUE <n> | NO MINVALUE | MAXVALUE <n> | NO MAXVALUE
--   | START [WITH] <n> | CACHE <n> | CYCLE | NO CYCLE)
CREATE SEQUENCE [IF NOT EXISTS] <name> [<seq-option> ...] [OWNED BY <table>.<col> | OWNED BY NONE]
ALTER  SEQUENCE [IF EXISTS] <name> [<seq-option> ...] [OWNED BY ...] [RESTART [WITH <n>]]
DROP   SEQUENCE [IF EXISTS] <name> [, <name> ...]

-- Common table expressions (statement-scoped views; SELECT statements only)
WITH <name> [(<col> [, ...])] AS (<select>) [, ...] <select>
-- Enum types and domains (named constraint bundles a column item names)
CREATE TYPE [IF NOT EXISTS] <name> AS ENUM ('<v>' [, ...])
ALTER  TYPE <name> ADD VALUE [IF NOT EXISTS] '<v>'
DROP   TYPE [IF EXISTS] <name> [, ...]
CREATE DOMAIN [IF NOT EXISTS] <name> [AS] [NOT NULL] [DEFAULT <expr>] [CHECK (<expr over VALUE>)]
DROP   DOMAIN [IF EXISTS] <name> [, ...]
-- and in CREATE TABLE a column item may name one: <col> <type> [NOT NULL] [DEFAULT ...] ...

-- Views (a stored SELECT; a materialized view stores the result)
CREATE [OR REPLACE] VIEW [IF NOT EXISTS] <name> [(<col> [, ...])] AS <select>
DROP   VIEW [IF EXISTS] <name> [, <name> ...]
CREATE MATERIALIZED VIEW [IF NOT EXISTS] <name> [(<col> [, ...])]
       [WITH (refresh = '<interval>')] AS <select> [WITH [NO] DATA]
REFRESH MATERIALIZED VIEW <name>            -- re-runs the body; affected = new row count
DROP   MATERIALIZED VIEW [IF EXISTS] <name> [, <name> ...]
SELECT nextval('<seq>')                      -- also in a DEFAULT and an INSERT's VALUES
SELECT setval('<seq>', <n> [, <is_called>])  -- FROM-less SELECT only

-- DML
INSERT INTO <table> (<col> [, <col> ...]) VALUES (<expr> | DEFAULT, ...) [, (<expr>, ...) ...]
UPDATE <table> SET <path> = <expr> [, <path> = <expr> ...] [WHERE <expr>]
DELETE FROM <table> [WHERE <expr>]

-- Query
SELECT <expr> [[AS] <alias>] [, ...]   -- no FROM: constant projection, one row
                                       -- (`SELECT 1` liveness probe; no other
                                       --  clause may follow; `*` needs a table)
SELECT [DISTINCT | DISTINCT ON (<expr> [, ...])] <select-item> [, <select-item> ...]
FROM <table> [[AS] <alias>]
[ <join> ... ]
[NEAREST (<path>, <query-vector>, <k>)]
[WHERE <expr>]
[RANK BY RRF [(<constant>)]]            -- hybrid: fuse NEAREST + WHERE-search by RRF
[RERANK [ON <col>] [WITH '<model>'] [QUERY '<text>'] [TOP <n>]]
                                        -- second-stage cross-encoder reranking
[GROUP BY <expr> [, <expr> ...] [TOP <k> BY <expr> [ASC|DESC]]]
[HAVING <expr>]
[ { UNION | INTERSECT | EXCEPT } [ALL] <select> ... ]     -- applied left to right
[ORDER BY <expr> [ASC|DESC] [, <expr> [ASC|DESC] ...]]
[LIMIT <n>] [OFFSET <n>]
[AFTER (<sort-value>, <pk-value>)]      -- deep pagination: keyset cursor (search queries)

<join> := [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS] JOIN <table> [[AS] <alias>] [ON <expr>]

-- Transactions (see note)
BEGIN [TRANSACTION]                       -- embedded / standalone driver connections
BEGIN ATOMIC PARTITION (<col> = <literal> [, ...])  -- cluster: single-shard txn
COMMIT [TRANSACTION]
ROLLBACK [TRANSACTION]

-- Databases (see note)
CREATE DATABASE [IF NOT EXISTS] <name>
DROP   DATABASE [IF EXISTS] <name>
USE    [DATABASE] <name>

-- Users, roles, grants (see Access control note)
CREATE USER [IF NOT EXISTS] <name> PASSWORD '<password>'
CREATE USER [IF NOT EXISTS] <name> GSSAPI      -- external Kerberos principal,
                                               -- no local secret; <name> is the
                                               -- principal, e.g. "user@REALM"
                                               -- (double-quote it — it contains
                                               -- '@' and '.')
ALTER  USER <name> PASSWORD '<password>'
DROP   USER [IF EXISTS] <name>
CREATE ROLE [IF NOT EXISTS] <name>
DROP   ROLE [IF EXISTS] <name>
GRANT  <privilege> ON { <table> | DATABASE <db> | TOPIC '<filter>' | * } TO <role>
REVOKE <privilege> ON { <table> | DATABASE <db> | TOPIC '<filter>' | * } FROM <role>
GRANT  ROLE <role> TO <user>
REVOKE ROLE <role> FROM <user>
SHOW GRANTS [FOR <role>]

-- Introspection (read-only catalog)
SHOW TABLES
SHOW CREATE TABLE <table>                     -- the table's DDL + its indexes + constraints, replayable
SHOW INDEXES
SHOW CONSTRAINTS [ON <table>]                 -- NOT NULL / DEFAULT / IDENTITY / GENERATED / CHECK / FOREIGN KEY, one view
SHOW FOREIGN KEYS [ON <table>]                -- foreign keys declared on / referencing a table
SHOW SEQUENCES                                -- every sequence of the current database + its counter
SHOW VIEWS                                    -- views and materialized views of the current database, dependency order
SHOW TYPES                                    -- enum types and domains of the current database (SHOW DOMAINS is the same)
SHOW STATUS
SHOW DATABASES

-- Admin control plane (network server only; reads need MONITOR on *,
-- mutations need ADMIN on *; ADMIN implies MONITOR)
SHOW CLUSTER                                  -- ring, members, epoch, liveness
SHOW CONFIG [LIKE '<pattern>']                -- flattened keys, secrets masked
SET  CONFIG <section.field> = <literal>       -- live-mutable keys apply instantly (ADMIN)
SHOW SLOW QUERIES [LIMIT <n>]                 -- masked slow-query sample
SHOW QUERIES                                  -- statements running NOW on this node
                                              -- (id, user, db, via, elapsed_ms,
                                              --  rows_examined, sql)
SHOW MAINTENANCE                              -- background jobs running NOW on this
                                              -- node (id, kind, target, elapsed_ms,
                                              -- progress): anti-entropy passes,
                                              -- content-digest builds, index/vector/
                                              -- geo backfills, search catch-ups,
                                              -- hint drains, flush builds,
                                              -- compactions, embed drains
KILL QUERY <id>                               -- cooperatively terminate a running
                                              -- statement by its SHOW QUERIES id (ADMIN);
                                              -- it fails with "terminated by KILL QUERY"
                                              -- at its next scan tick
RESET OOM COUNTER [ON '<node>']               -- zero the OOM-kill counter reported in
                                              -- node_stats/host stats, on every member
                                              -- AND registered witness, or on one node
                                              -- by its id / witness id (ADMIN); returns
                                              -- (node, absorbed, status) — witnesses
                                              -- apply on their next pull cycle
                                              -- (absorbed = NULL, status "stamped")
REPAIR CLUSTER                                -- one anti-entropy pass
RECLAIM                                       -- drop unowned keys/series
ALTER CLUSTER ADD    NODE '<host:port>'
ALTER CLUSTER REMOVE NODE '<id>'
ALTER CLUSTER SET NAME '<name>'               -- rename the cluster (ADMIN)
ALTER NODE '<alias|dotted|id>' SET NAME '<n>' -- rename a member/witness alias (ADMIN)
ALTER TABLE <table> SET (ttl = <dur>          -- live TTL change (0 clears)
                       | witness = <bool>     -- toggle witness mirroring
                       | replication = <n>    -- online placement transition
                       | nodes = ['<ref>',..] -- online pin change
                       | toast_threshold = <bytes> -- value-TOAST on new writes (0 = off)
                       | placement_finalized = true)  -- operator escape hatch
ALTER TABLE <ts-table> SET (retention = <dur> -- live retention change (0 clears)
                          | ooo = <dur>)      -- live out-of-order window change

-- Session (binary-protocol connections)
SET CONSISTENCY { ONE | QUORUM | ALL }        -- per-connection override
SET SCAN BUDGET ROWS <n> [BYTES <n>]          -- session scan limits (tightening-only)
SET SCAN BUDGET DEFAULT                       -- clear the session override

-- Backup & restore (ADMIN)
BACKUP TO '<path>'        -- crash-consistent copy of this node's data dir
BACKUP CLUSTER TO '<path>'  -- every member backs up + one shared cut instant
RESTORE FROM '<path>'     -- embedded / single node only; old data kept aside
RESTORE FROM '<path>' TO TIMESTAMP '<when>'   -- point-in-time recovery