SQL Reference

ScramDB is a PostgreSQL wire-compatible SQL database. Connect with psql, any PostgreSQL driver, or any BI tool that supports PostgreSQL.

Compatibility

ScramDB implements the PostgreSQL wire protocol (v3) and supports standard SQL syntax. You can connect using:

psql -h localhost -p 5432 scramdb

Or any PostgreSQL driver:

ScramDB supports a comprehensive subset of SQL with full OLAP capabilities:

Category	Status	Details
SELECT with joins, subqueries, CTEs	✅	Inner, Left, Right, Full, Semi, Anti-Semi
Aggregate functions	✅	SUM, COUNT, AVG, MIN, MAX, STDDEV, VARIANCE, STRING_AGG
Window functions	✅	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, + aggregate windows
72 scalar functions	✅	String, math, date/time - see Functions
Expressions	✅	CASE, BETWEEN, LIKE/ILIKE, COALESCE, NULLIF, IN, EXISTS
DML	✅	INSERT, UPDATE, DELETE, TRUNCATE, COPY FROM/TO
DDL	✅	CREATE/DROP TABLE, CREATE INDEX, ALTER TABLE, CREATE VIEW
Set operations	✅	UNION, INTERSECT, EXCEPT (+ ALL variants)
CTEs	✅	Non-recursive WITH clauses
GROUPING SETS	✅	GROUPING SETS, ROLLUP, CUBE
Transactions	✅	READ COMMITTED, REPEATABLE READ, SERIALIZABLE
Vector search	✅	pgvector-compatible `VECTOR(n)` type
Prepared statements	⚠️ Planned	PREPARE / EXECUTE
Recursive CTEs	⚠️ Planned	WITH RECURSIVE
Stored procedures	❌	Not planned
Triggers	❌	Not planned

PostgreSQL type compatibility table

DDL and DML statement reference

72 scalar, aggregate, and window functions