SQL compatibility overview
AngaraBase implements a subset of PostgreSQL SQL via the pgwire protocol. The
principle is fail-closed: an unsupported construct returns an explicit
SQLSTATE (most often 0A000 feature_not_supported), not a silent incorrect
result. This page is a map of what is supported and how to interpret errors.
Standard SQL constructs
A matrix of what AngaraBase understands in queries. Supported — correct
semantics with pinned tests; Partial — works with limits (see note);
0A000 — an unsupported form returns a deterministic error.
Queries (SELECT)
| Construct | Status | Note |
|---|---|---|
SELECT … FROM … WHERE …, LIMIT, OFFSET | ✅ Supported | predicates: comparisons, IN, BETWEEN, IS [NOT] NULL, AND/OR/NOT, boolean column as predicate |
ORDER BY <col> / <expr> / ordinal | ✅ Supported | NULLS FIRST/LAST and COLLATE → 0A000; bad ordinal → 42P10 |
DISTINCT | ✅ Supported | DISTINCT ON (...) → 0A000 |
GROUP BY (incl. multi-key) · HAVING | ✅ Supported | aggregates COUNT/SUM/MIN/MAX/AVG; HAVING without GROUP BY too |
WITH (CTE, non-recursive) | ✅ Supported | CTE as a source in FROM; WITH RECURSIVE and data-modifying CTE → 0A000 (recursive — planned v0.7) |
Subqueries: IN (…), EXISTS/NOT EXISTS, scalar in SELECT | ✅ Supported | correlated supported |
Set ops: UNION, UNION ALL, INTERSECT, EXCEPT | ✅ Supported | hash-based dedup |
JOIN
| Construct | Status | Note |
|---|---|---|
INNER, LEFT [OUTER], RIGHT [OUTER], FULL [OUTER], CROSS | ✅ Supported | all five kinds |
| Join condition | ✅ ON <expr> | USING (...) and NATURAL JOIN → 0A000 |
LATERAL (…) | ✅ Supported | correlated derived table |
CROSS JOIN row budget | ⚠️ Partial | limit sql.join.max_cross_join_rows (default 1,000,000); overflow → 54000 |
Window functions (OVER (…))
| Function | Status | Note |
|---|---|---|
ROW_NUMBER, RANK, DENSE_RANK | ✅ Supported | PARTITION BY / ORDER BY |
LAG, LEAD, FIRST_VALUE, LAST_VALUE | ✅ Supported | |
SUM/COUNT/AVG/MIN/MAX OVER (…) | ✅ Supported | window aggregates |
NTILE, named windows (WINDOW clause) | ❌ 0A000 |
DDL / DML / types
Detailed tables and Expected SQLSTATE live on the section pages:
| Topic | Page | Status |
|---|---|---|
| Data types | data-types.md | Stable |
| DDL (CREATE, ALTER, DROP) | ddl.md | Stable |
| DML (INSERT, UPDATE, DELETE) | dml.md | Stable |
| Queries (CTE, JOIN, ORDER BY) | queries.md | Stable |
| Table partitioning | partitioning.md | Baseline |
Key DDL boundaries: CREATE MATERIALIZED VIEW is supported, plain CREATE VIEW
is planned (0A000), CREATE TRIGGER is a non-goal (0A000), FOREIGN KEY is
accepted as NOT ENFORCED.
SQLSTATE quick reference
| SQLSTATE | Name | Typical scenario |
|---|---|---|
0A000 | feature_not_supported | WITH RECURSIVE, JOIN … USING/NATURAL, NTILE, named windows, DISTINCT ON, plain CREATE VIEW (planned), CREATE TRIGGER (non-goal), complex RLS predicates |
54000 | program_limit_exceeded | CROSS JOIN over the budget sql.join.max_cross_join_rows (default 1,000,000) |
23514 | check_violation | Partition routing: no matching partition and no DEFAULT |
42809 | wrong_object_type | UPDATE/DELETE on append-only; PK/FK update under no_delete |
22023 | invalid_parameter_value | Invalid stats_level_max, break-glass TTL, setval out of [MINVALUE..MAXVALUE] |
42501 | insufficient_privilege | Missing roles for security operations, no SecurityContext |
25001 | active_sql_transaction | SET SESSION CONTEXT inside an active transaction |
42P10 | invalid_column_reference | ORDER BY ordinal out of column range |
2200H | sequence_generator_limit_exceeded | nextval past MAXVALUE/MINVALUE without CYCLE |
55000 | object_not_in_prerequisite_state | currval(seq) before any nextval in the current session |
42P07 | duplicate_object | CREATE SEQUENCE of an existing name without IF NOT EXISTS |
42P01 | undefined_table | DROP SEQUENCE / nextval / currval / setval on a missing sequence |
428C9 | generated_always | INSERT with explicit non-NULL into a GENERATED ALWAYS AS IDENTITY column |
Full list with context: Known issues.
Connecting ORMs and tools
ORMs and clients (DBeaver, psql, Hibernate, Django, etc.) run housekeeping
pg_catalog queries on connect. AngaraBase supports the basic pg_catalog
introspection these tools need to connect. If a client fails to connect, see
Client compatibility.
If a query hangs (no response), that is a defect on our side — please collect artifacts per Support and report it.
Built-in SQL functions
| Function | Signature | Description |
|---|---|---|
NOW() | () → timestamp | Current UTC time |
CURRENT_TIMESTAMP | () → timestamp | Alias for NOW() |
date_trunc(field, ts) | (text, timestamp) → timestamp | Truncate to field (second…millennium); unknown field → NULL |
set_config(name, val, local) | (text, text, bool) → text | Stub: returns val (Django compatibility) |
obj_description(oid, cat) | (oid, text) → text | Stub: returns NULL (Django introspection) |
Vector execution visibility
When the execution mode allows the vector path
(ANGARABASE_SQL_EXECUTION_MODE=auto for supported plans, or force_vector),
EXPLAIN shows vector operator names: VectorSeqScan, VectorFilter,
VectorProject, VectorHashJoin, VectorAgg. If AngaraVector does not support
the plan shape, row operators remain in EXPLAIN.
ANGARABASE_SQL_EXECUTION_MODE modes:
auto(default) — Stable vector only for fully supported plan shapes, else a deterministic row fallback;force_row— Stable always the row executor;force_vector— Experimental fail-closed withfeature_not_supportedif vector execution is impossible; for targeted testing, not for production workloads in0.6.x.
AngaraMemory table options
CREATE TABLE ... WITH (...) supports bounded in-memory tables:
storage='memory'— Baseline AngaraMemory table engine;durability='none'|'logged'|'snapshotted'— Baseline volatile/durable;max_rows=<n>— Stable hard row cap; overflow fails closed (54023);eviction_policy='error'|'fifo'— Experimental defaulterror;checkpoint_interval_ms=<n>— Baseline only withdurability='snapshotted'.
If max_rows is omitted, a bounded default from instance policy applies. With
durability='snapshotted' the DML hot path does not persist pages immediately —
the checkpoint worker schedules writes per checkpoint_interval_ms.