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:
- Python:
psycopg2,asyncpg,sqlalchemy - Rust:
tokio-postgres,sqlx - Go:
pgx,database/sqlwithlib/pq - Java: JDBC PostgreSQL driver
- Node.js:
pg,knex
What's Supported
ScramDB supports a broad, PostgreSQL-compatible SQL surface across transactional and analytical workloads, on one live copy of your data:
| Category | Details |
|---|---|
| SELECT with joins | Inner, left, right, full outer, cross, and LATERAL joins, plus subqueries and IN / EXISTS |
| CTEs | WITH, recursive WITH RECURSIVE, and writable CTEs (INSERT / UPDATE / DELETE inside WITH) |
| Aggregate functions | SUM, COUNT, AVG, MIN, MAX, STDDEV, VARIANCE, STRING_AGG, and more, with FILTER |
| Window functions | ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE, aggregate windows, and frame clauses |
| 80+ scalar functions | String, math, date/time, JSONB, and array functions - see Functions |
| Expressions | CASE, BETWEEN, LIKE/ILIKE, COALESCE, NULLIF, IN, EXISTS |
| Set operations | UNION, INTERSECT, EXCEPT (and their ALL variants) |
| GROUPING SETS | GROUPING SETS, ROLLUP, CUBE |
| DML | INSERT, UPDATE, DELETE, TRUNCATE, COPY, INSERT ... ON CONFLICT, MERGE, UPDATE ... FROM, DELETE ... USING, RETURNING |
| DDL | CREATE / DROP / ALTER TABLE, indexes (including partial and expression indexes), views, materialized views, schemas, sequences, and table partitioning |
| Prepared statements | PREPARE / EXECUTE |
| Cursors | DECLARE / FETCH / CLOSE |
| Stored procedures and functions | CREATE FUNCTION / CREATE PROCEDURE in PL/pgSQL |
| Triggers | Row, statement, INSTEAD OF, and event triggers |
| Transactions | Read Committed, Repeatable Read, and Serializable isolation, with SAVEPOINT / ROLLBACK TO |
| Row locking | SELECT ... FOR UPDATE / FOR SHARE, including SKIP LOCKED |
| Data types | UUID, JSONB, ARRAY, ENUM, DOMAIN, TIMESTAMPTZ, TIME, NUMERIC, and more - see Data Types |
| Security | Role-based access control (RBAC), row-level security, and schemas with search_path |
| Vector similarity | Built-in embeddings functions (cosine, dot) over JSON-array vectors |
| User-defined functions | Write UDFs in JavaScript, TypeScript, Rust, Go, C, C++, Python, and Ruby, plus 15 built-in analytics packages |
| Recovery and branching | Point-in-time recovery (CREATE DATABASE ... FROM src AT TIMESTAMP ...) and instant database branching (CREATE DATABASE ... CLONE ...) |
| GPU acceleration | Query acceleration on NVIDIA, AMD, and Apple Metal |
| Distributed cluster | Serializable transactions across a multi-node cluster |
Quick Links
📊 Data Types
PostgreSQL type compatibility table
📝 Statements
DDL and DML statement reference
⚙️ Functions
80+ scalar, aggregate, and window functions