Documentation / SQL reference

SQL reference

What the engine accepts today, followed by the places where NusaDB deliberately behaves differently from what you may expect.

Data types

The network, bit-string and range types are real types rather than aliases over text: they compare, sort and expose their own operators and functions.

Defining objects

CREATE, ALTER and DROP are supported for tables, indexes, views, materialized views, schemas, databases, sequences, domains, types, triggers, functions and procedures. Table constraints cover primary keys, uniqueness, foreign keys with referential actions, checks, not-null and defaults, including generated columns.

CREATE TABLE orders (
  id        BIGSERIAL PRIMARY KEY,
  customer  BIGINT REFERENCES customers (id) ON DELETE CASCADE,
  total     NUMERIC(12,2) CHECK (total >= 0),
  tax       NUMERIC(12,2) GENERATED ALWAYS AS (total * 0.11) STORED,
  tags      TEXT[],
  doc       JSONB
);

CREATE INDEX orders_recent ON orders (customer)
  WHERE total > 100;                -- partial index

CREATE INDEX orders_lower ON orders ((lower(doc->>'ref')));  -- expression index

Queries

Inner, left, right and full joins; lateral joins; scalar, EXISTS and quantified subqueries; UNION, INTERSECT and EXCEPT; DISTINCT and DISTINCT ON; GROUP BY with GROUPING SETS, ROLLUP and CUBE; HAVING; FILTER on aggregates; window functions with named windows and ROWS/RANGE/GROUPS frames; common table expressions including WITH RECURSIVE; TABLESAMPLE; WITH ORDINALITY; and FETCH FIRST … WITH TIES.

WITH RECURSIVE chain AS (
  SELECT id, manager, 1 AS depth FROM staff WHERE manager IS NULL
  UNION ALL
  SELECT s.id, s.manager, c.depth + 1
  FROM staff s JOIN chain c ON s.manager = c.id
)
SELECT depth, count(*) FROM chain GROUP BY depth ORDER BY depth;

Changing data

INSERT with ON CONFLICT DO NOTHING or DO UPDATE, UPDATE … FROM, DELETE … USING, MERGE with matched, not-matched and not-matched-by-source clauses, TRUNCATE with CASCADE and RESTART IDENTITY, and RETURNING on all of them.

MERGE INTO stock t
USING shipment s ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET qty = 0;

Where NusaDB chooses differently

These are decisions, not gaps. Each one is deliberate and each has a reason.

Text sorts by byte value

Comparison and ordering are bytewise, equivalent to a C collation, and COLLATE "C" or "POSIX" is accepted while locale collations are refused. Byte order is deterministic and does not change when a system library is upgraded. Locale collations can silently invalidate an index when the library's ordering rules change. The practical effect is that uppercase letters sort before lowercase.

NUMERIC division has a fixed scale

Division produces a fixed scale rather than deriving one from the operands. The value is the same; only how many digits are kept differs. Round explicitly when a specific scale matters.

Materialized views are snapshots

A materialized view holds the result from the last REFRESH. Incremental maintenance is opt-in with WITH (incremental = true), and asking for it on a body that cannot be maintained incrementally is an error rather than a silent downgrade to a frozen snapshot.

JSON is normalised

JSON and JSONB both store a parsed value, so key order and insignificant whitespace from the input text are not preserved. Keep the original text in a TEXT column when byte fidelity matters, such as a signed payload.

TIMESTAMP and TIMESTAMPTZ assign both ways

A value of either type can be assigned to a column of the other, and the instant passes through unchanged. Equality between the two types is still refused: this widened assignment, not comparison.

A query over its memory budget fails rather than spilling

With --work-mem set, a stage that materialises more than the budget is failed with an error naming the limit, the bytes involved and how to raise it. The server stays up. This keeps one large query from making every other query slow, at the cost of not finishing it.

Session variables

SET accepts the engine's own parameters and application-defined variables that carry a class prefix, such as SET myapp.tenant = '7'. An unrecognised parameter name is an error rather than a silent no-op, and a read-only parameter reports that it cannot be changed. SHOW reads them back and RESET ALL clears what the session set.

Not accepted in this release

These are recognised and refused with a clear message rather than half-implemented: temporary tables, cursors, PREPARE/EXECUTE as statements, FOR UPDATE NOWAIT, advisory locks, OVERLAPS, index methods other than B-tree and HNSW, and locale collations. Setting a session time zone is not available either. The session time zone is fixed at UTC.