SQL Grammar & Types

ReferenceSQLTypes

AetheriusDB accepts a strict, precisely typed SQL grammar. Every keyword has one meaning, every literal one representation, and every column maps to a single machine type. Where other dialects are loose (numeric overflow, implicit casts, NULL in arithmetic), AetheriusDB picks one predictable rule and keeps it — so a query has exactly one meaning, and that meaning is what runs.

Primitive types

Category	Types	Notes
Integers	TINYINT, SMALLINT, INTEGER, BIGINT, HUGEINT	Signed, 8–128 bit. No surprise widening.
Floating point	REAL, DOUBLE	IEEE-754, no implicit widening.
Fixed precision	DECIMAL(p, s)	Exact arithmetic — use for money.
Temporal	DATE, TIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE	Time-zone aware.
Text	VARCHAR, TEXT
Boolean	BOOLEAN
Identifier	UUID	16-byte. Accepts a string literal (dashed or 32-hex); no server-side generator.
Document	JSONB, JSON	Binary (JSONB) or exact-text (JSON). Operators -> ->> #> #>> @> <@ ? ?| ?&; casts ::jsonb / ::json. No jsonb_* functions yet.
Composite	TENSOR(n)	Fixed-dimension vector.

Binary payloads (BLOB/BYTEA): not yet a live column type. The Binary representation and blob arena exist internally but are not wired to the SQL ingest path, so a BLOB column declaration is rejected at parse time. Store binary as base64/hex in a TEXT column for now.

Each declared type maps 1:1 to one machine representation with one set of arithmetic rules — there are no implicit double-precision accidents.

Recursive CTEs and window functions

The grammar covers the relational features analytics depends on: recursive CTEs for hierarchical walks and window functions with frames for ranking and rolling aggregates. Ranking, offset (LAG / LEAD), and rolling aggregate windows stay fast instead of degrading to row-by-row evaluation.

-- A typed table with explicit NOT NULL annotations
CREATE TABLE activity (
  user_id     BIGINT NOT NULL,
  parent_id   BIGINT,                      -- nullable: root has no parent
  score       DECIMAL(10, 2),              -- exact arithmetic
  recorded_at TIMESTAMP WITH TIME ZONE
);

-- Recursive CTE + window aggregate + correct NULL handling
WITH RECURSIVE tree (root_id, user_id, depth) AS (
  SELECT user_id, user_id, 0
  FROM   activity
  WHERE  parent_id IS NULL
  UNION ALL
  SELECT t.root_id, a.user_id, t.depth + 1
  FROM   activity a
  JOIN   tree t ON a.parent_id = t.user_id
)
SELECT
  root_id,
  user_id,
  depth,
  AVG(score) OVER (
    PARTITION BY root_id
    ORDER BY recorded_at
    ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
  ) AS rolling_avg,
  COUNT(score) OVER (PARTITION BY root_id) AS non_null_scores
FROM tree
JOIN activity USING (user_id);

COUNT(score) follows the standard SQL rule that NULL scores don’t count.

Supported window functions

Every function below works with OVER (PARTITION BY … ORDER BY …) and, where a frame applies, with ROWS/RANGE frames.

Category	Functions
Ranking	ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE(n)
Offset / positional	LAG(expr[, offset[, default]]), LEAD(…), FIRST_VALUE, LAST_VALUE, NTH_VALUE(expr, n)
Aggregate over a window	SUM, AVG, MIN, MAX, COUNT(*), COUNT(expr), COUNT(DISTINCT expr), STDDEV, VARIANCE

• RANK shares a rank on ties and leaves gaps; DENSE_RANK shares on ties with no gaps.
• PERCENT_RANK and CUME_DIST return a fraction in [0, 1].
• STDDEV / VARIANCE are the sample (N−1) forms and return NULL for a frame with fewer than two non-null values — matching the GROUP BY aggregates.

Supported frames: ROWS and RANGE, with bounds UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. With an ORDER BY and no explicit frame, the default is UNBOUNDED PRECEDING to CURRENT ROW; without ORDER BY, the whole partition.

Not yet supported

The GROUPS frame mode and the EXCLUDE clause are not yet parsed. As window functions, PERCENTILE / APPROX_PERCENTILE / APPROX_COUNT OVER (…) and the population STDDEV_POP / VAR_POP variants are not yet available (the sample STDDEV / VARIANCE are). These are tracked in the project’s future-scope notes.

Named window clauses

Define a window once in a WINDOW clause and reference it by name from any number of window functions, instead of repeating the OVER (…) spec. The clause sits after HAVING / QUALIFY and before ORDER BY.

SELECT
  region,
  SUM(amount)  OVER w                    AS running_total,
  RANK()       OVER w                    AS rnk,
  AVG(amount)  OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM   sales
WINDOW w AS (PARTITION BY region ORDER BY day);

• OVER <name> reuses a named window verbatim.
• OVER (<name> …) inherits the named window’s PARTITION BY / ORDER BY and extends it — e.g. adding a frame, as in rolling_avg above.
• One WINDOW clause may define several windows, and a window may build on an earlier one by name (WINDOW w AS (…), w2 AS (w ORDER BY …)).

Index DDL

The CREATE INDEX surface accepts the following forms. The index method after USING (PTI, BTREE, HASH, HNSW, IVFFLAT) is a free-form identifier resolved at execution time — it is not a reserved word — and may appear either before or after the column list. Omitting USING defaults to BTREE.

CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name>
  ON [<schema>.]<table> [USING <method>] ( <col> [, ...] ) [USING <method>]
  [INCLUDE ( <agg>(<col> | *) [, ...] )]   -- covering aggregates (USING PTI only)
  [WITH ( <key> = <value> [, ...] )]        -- index tuning (e.g. HNSW/IVFFLAT)
  [WHERE <predicate>];                      -- partial index

DROP INDEX [IF EXISTS] <name>;
ALTER INDEX [IF EXISTS] <name> RENAME TO <new_name>;
REINDEX [TABLE | INDEX] <name>;

• INCLUDE aggregates: SUM, COUNT, AVG, MIN, MAX, APPROX_COUNT (only COUNT(*) may omit a column). Valid on USING PTI only.
• WITH options: HNSW takes m / ef_construction / ef_search; IVFFLAT takes lists / probes. Unknown keys fail at parse time.

The graph index DDL is a separate statement family:

CREATE GRAPH INDEX [IF NOT EXISTS] <name> ON LINKS (
  [<schema>.]<parent>(<col>) -> [<schema>.]<child>(<col>) CARDINALITY '1:1' | '1:N'
  [, ...]
);

DROP GRAPH INDEX [IF EXISTS] <name>;

LINKS and CARDINALITY are contextual identifiers; link keys are single-column integer axes (BIGINT / TIMESTAMP). See Graph Index and Advanced Indexing for semantics.

Strict NULL and cast rules

• Three-valued NULL logic is followed exactly (NULL compares as unknown).
• No implicit cross-type casts. Comparing a BIGINT with TEXT is rejected by the parser.

Tip

If you reach for an implicit cast, write it explicitly: CAST(... AS BIGINT). The plan stays unambiguous and reuses its compiled kernel cleanly.

Heads up

Floating-point arithmetic follows IEEE-754, including NaN and its rules. Use DECIMAL(p, s) for money or anywhere binary-float rounding would cause problems.

Define Your Data →

Tables, types, and schema-as-code.

Query Data →

The query surface and performance.

Configuration & Availability →

Feature flags and defaults.