SQL Reference — Maree-DB Docs

DDL — Data Definition Language

Statement	Description
`CREATE DATABASE name`	Create a new database (schema). Supports `SHARD BY` extension for distributed deployments.
`CREATE TABLE name (...)`	Standard SQL:2023 table creation with full constraint support (PK, FK, UNIQUE, CHECK, NOT NULL).
`CREATE TABLE ... SHARD BY (col)`EXT	Create a sharded table distributed across cluster nodes by the specified column. Enterprise only.
`CREATE INDEX`	B-Tree index (default), hash index (`USING HASH`), GIN index for JSONB (`USING GIN`), advanced vector index (`USING VECTOR`).
`CREATE VIEW`	Standard SQL views. Updatable views supported where unambiguous.
`CREATE MATERIALIZED VIEW`	Pre-computed views with `REFRESH MATERIALIZED VIEW`. Concurrent refresh supported.
`ALTER TABLE`	Add/drop/modify columns, add/drop constraints, rename. All operations are online (no table lock for most operations).
`DROP TABLE / DATABASE / INDEX / VIEW`	Standard drop with `IF EXISTS` and `CASCADE` support.
`TRUNCATE TABLE`	Fast table truncation. Transactional (can be rolled back).

DML — Data Manipulation Language

Statement	Description
`SELECT`	Full SQL:2023 SELECT with CTEs (`WITH`), window functions, lateral joins, grouping sets, recursive CTEs.
`INSERT`	`INSERT INTO ... VALUES`, `INSERT INTO ... SELECT`, `INSERT OR REPLACE`, `ON DUPLICATE KEY UPDATE` (MySQL compat), `ON CONFLICT DO UPDATE` (PG compat).
`UPDATE`	Standard UPDATE with `JOIN` support (`UPDATE t1 JOIN t2 ...`), `RETURNING` clause.
`DELETE`	Standard DELETE with multi-table join support, `RETURNING` clause.
`MERGE`	SQL:2023 MERGE (upsert) with WHEN MATCHED / WHEN NOT MATCHED clauses.
`CALL procedure()`	Stored procedure execution.
`DO $$ ... $$`	Anonymous procedure block (PostgreSQL-compatible).

Query Features

Feature	Description
Window functions	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `LAG()`, `LEAD()`, `FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE()`, `NTILE()`, all aggregates as window functions.
Common Table Expressions	Non-recursive and recursive CTEs. Recursive CTEs replace `CONNECT BY` for hierarchical data (Oracle migration).
JSON/JSONB	`->`, `->>` operators, `#>` path access, `jsonb_set()`, `jsonb_agg()`, JSONPath predicates in WHERE clauses, GIN-indexed JSONB.
Full-text search	`MATCH(col) AGAINST('term')` (MySQL compat), `to_tsvector() @@ to_tsquery()` (PG compat).
Geospatial	Point, LineString, Polygon types. `ST_Distance()`, `ST_Within()`, `ST_Intersects()`. R-Tree spatial index.
EXPLAIN / EXPLAIN ANALYZE	Query plan inspection with actual runtime statistics. Use to diagnose slow queries.
Prepared statements	Named and positional parameters. Strongly typed, immune to SQL injection.
Stored procedures	PL/SQL-compatible procedural language. Existing Oracle stored procedures translated automatically by the migration tool.

Maree-DB SQL Extensions

These functions are unique to Maree-DB and not part of SQL:2023.

Function / Syntax	Description
`VERIFY()`EXT	Run the 5,574 self-verification checks and return a result set. `SELECT * FROM VERIFY()` returns component, status, duration for each check.
`HEALTH_CHECK()`EXT	Return current server health: memory usage, active connections, WAL lag, replication status, TamperLock chain length.
`tamperlock_verify_chain()`EXT	Verify the cryptographic integrity chain and return block-by-block verification results.
`tamperlock_proof(txn_id)`EXT	Return a cryptographic inclusion proof for a specific transaction. The proof can be verified offline using the server's public key.
`tamperlock_report(start, end)`EXT	Generate a signed Ed25519 forensic audit report for all transactions in a date range.
`AI_AGENT(prompt)`EXT	Execute a natural language query via the Maree-DB AI SQL agent. Returns a standard result set. Example: `SELECT * FROM AI_AGENT('show me the top 10 customers by revenue this month')`.
`SHARD BY (col)`EXT	Clause on CREATE TABLE to distribute rows across cluster nodes by a partition column. Enterprise only.
`DATA_VAULT_HUB(...)`EXT	Insert a business key into a Data Vault 2.0 Hub with auto-generated hash key and load date.
`DATA_VAULT_LINK(...)`EXT	Create a relationship in a Data Vault 2.0 Link table.
`DATA_VAULT_SAT(...)`EXT	Insert a satellite record with automatic load-end-date management on prior records.
`TIME_BUCKET(interval, col)`EXT	Group time-series data into uniform time buckets. Example: `TIME_BUCKET('1 hour', measured_at)`.
`FIRST(col)` / `LAST(col)`EXT	Aggregate functions that return the first or last value in a group, ordered by insertion order. Useful for time-series.
`APPROX_COUNT_DISTINCT(col)`EXT	Fast approximate distinct count using a probabilistic algorithm. Orders of magnitude faster than `COUNT(DISTINCT col)` on large datasets.

Data Types

Numeric

TINYINT, SMALLINT, INT / INTEGER, BIGINT, DECIMAL(p,s) / NUMERIC(p,s), FLOAT, DOUBLE / DOUBLE PRECISION, REAL. Auto-increment: AUTO_INCREMENT (MySQL) or SERIAL / BIGSERIAL (PG).

String

CHAR(n), VARCHAR(n), TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT. All string columns are UTF-8. Collation is configurable per column.

Date and Time

DATE, TIME, DATETIME, TIMESTAMP, TIMESTAMP WITH TIME ZONE, INTERVAL. Timezone-aware arithmetic. NOW(), CURRENT_TIMESTAMP, DATE_ADD(), EXTRACT().

JSON

JSON — validated JSON stored as text. JSONB — binary JSON with GIN indexing, path operators (->, ->>, #>), and JSONPath predicates.

Binary

BINARY(n), VARBINARY(n), BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, BYTEA (PG compat).

Vector EXT

VECTOR(n) — fixed-length float32 vector for embedding storage. Supports cosine similarity (cosine_distance()) and Euclidean distance (l2_distance()) with advanced vector index for approximate nearest-neighbour search.

Other

BOOLEAN / BOOL, UUID, ENUM('a','b','c'), SET('a','b'), BIT(n), GEOMETRY / POINT / LINESTRING / POLYGON.

Multi-Model SQL

Maree-DB stores relational, document, key-value, vector, time-series, and graph data in one engine. You can query across all models in a single SQL statement.

Multi-model JOIN example

-- Join a relational table with a JSONB document collection

SELECT

u.name,

u.email,

p.data->>'plan' AS subscription_plan,

kv.value AS last_session

FROM users u

JOIN profiles p ON p.user_id = u.id

LEFT JOIN kv_store kv ON kv.key = CONCAT('session:', u.id)

WHERE u.created_at > NOW() - INTERVAL '30 days'

ORDER BY u.name;

Vector nearest-neighbour search

SELECT id, title, cosine_distance(embedding, $1) AS dist

FROM articles

ORDER BY dist ASC

LIMIT 10;

-- $1 is a VECTOR(1536) query embedding

Compliance Functions

Built-in SQL functions for GDPR, HIPAA, PCI-DSS, SOC 2, and Australian Privacy Act compliance. Full reference in the Security page.

Function	Standard	Role Required
`mareedb_gdpr_erase(table, col, val)`	GDPR Art. 17	gdpr_admin
`mareedb_gdpr_report(identifier)`	GDPR Art. 15	gdpr_admin
`mareedb_gdpr_pii_map()`	GDPR Art. 30	dpo
`mareedb_hipaa_access_report(start, end)`	HIPAA §164.312(b)	hipaa_admin
`mareedb_pci_tokenise(pan)`	PCI-DSS v4.0 §3.5.1	payment_processor
`mareedb_pci_detokenise(token)`	PCI-DSS v4.0	pci_admin
`mareedb_soc2_evidence(start, end)`	AICPA TSC 2017	auditor
`mareedb_iso27001_controls()`	ISO/IEC 27001:2022	auditor
`mareedb_privacy_au_residency_check()`	Privacy Act 1988 APP 8	privacy_officer