PostgreSQL Compatibility

Connect with psql, JDBC, psycopg, or any PostgreSQL driver, no changes. ScramDB speaks the PostgreSQL wire protocol, so the tools, drivers, and BI clients you already use work as-is. There is no ScramDB SDK to learn and no custom connector to install.

ScramDB is a UTAP database (Unified Transactional Analytical Processing). Transactions, analytics, and AI or vector queries all run on one live copy of your data. The PostgreSQL wire protocol is how you talk to it.

psql -h localhost -p 5432 scramdb

Any client that speaks PostgreSQL protocol v3 connects the same way:

• Python: psycopg2, psycopg3, asyncpg, SQLAlchemy
• Java / JVM: the PostgreSQL JDBC driver (pgjdbc)
• Go: pgx, database/sql with lib/pq
• Node.js: node-postgres (pg), knex
• Rust: tokio-postgres, sqlx
• BI and tooling: anything that connects to PostgreSQL

The tables below list capabilities that are implemented and covered by ScramDB's test suite. Where ScramDB behaves differently from stock PostgreSQL, the difference is called out in Behavior differences.

---

Protocol and drivers

Capability	Details
Wire protocol v3	Full simple query protocol and extended query protocol
Extended query protocol	Parse, Bind, Describe, and Execute, with text and binary parameter and result formats
Prepared statements	PREPARE / EXECUTE / DEALLOCATE, plus $1 style bind parameters over the wire
Cursors	DECLARE a cursor, FETCH (including NEXT, ALL, and BACKWARD), and CLOSE
Authentication	SCRAM-SHA-256 password authentication, with pg_hba.conf style host rules
Catalog introspection	pg_catalog and information_schema queries, so drivers and BI tools that inspect the catalog work
Server parameters	SHOW and SET for session settings, including statement_timeout

---

Transactions and isolation

Capability	Details
Transaction control	BEGIN / COMMIT / ROLLBACK
MVCC isolation levels	READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The default is READ COMMITTED, the same as PostgreSQL
Serializable isolation	Serializable Snapshot Isolation (SSI) with read-set validation, which prevents write-skew anomalies
Setting the level	SET TRANSACTION ISOLATION LEVEL, SET SESSION CHARACTERISTICS, inline BEGIN ... ISOLATION LEVEL, and SHOW TRANSACTION ISOLATION LEVEL
Savepoints	SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT, including nested and shadowed names
Two-phase commit	PREPARE TRANSACTION, COMMIT PREPARED, and ROLLBACK PREPARED, with pg_prepared_xacts introspection. Prepared transactions survive client disconnect
Row locking	SELECT ... FOR UPDATE and FOR SHARE, with NOWAIT and SKIP LOCKED
Table locking	LOCK TABLE in all lock modes, with NOWAIT
Advisory locks	pg_advisory_lock, pg_try_advisory_lock, pg_advisory_unlock, pg_advisory_unlock_all, and the _xact_ and _shared variants, in one-key and two-key forms

---

Security

Capability	Details
Roles	CREATE ROLE / CREATE USER with attributes such as LOGIN and NOLOGIN
Privileges	GRANT and REVOKE on tables, enforced at plan time. A denied statement returns SQLSTATE 42501
Column-level privileges	GRANT and REVOKE on specific columns, unioned with table-level grants
Role membership	GRANT role TO role, with inheritance flowing transitively through the membership graph. Grant cycles are rejected
Session role switching	SET ROLE, SET SESSION AUTHORIZATION, and RESET, gated by membership
Predefined roles	pg_read_all_data and pg_write_all_data
Row-level security	CREATE / ALTER / DROP POLICY, ALTER TABLE ... ENABLE / DISABLE / FORCE ROW LEVEL SECURITY, with USING and WITH CHECK enforcement and bypass semantics for the table owner

---

Programmability

Capability	Details
Functions	CREATE FUNCTION in PL/pgSQL and in SQL, called with SELECT fn(...)
Procedures	CREATE PROCEDURE and CALL, including transaction control (COMMIT and ROLLBACK) inside a procedure body
Anonymous blocks	DO blocks
Arguments	Named and $n positional arguments, argument coercion, and STRICT null-in null-out handling
Exception handling	EXCEPTION blocks inside function and procedure bodies
Row triggers	BEFORE and AFTER triggers on INSERT, UPDATE, and DELETE, able to skip or rewrite the affected row
Statement triggers	BEFORE and AFTER statement-level triggers, including transition tables and TRUNCATE triggers
INSTEAD OF triggers	INSTEAD OF triggers on views
Event triggers	ddl_command_start, ddl_command_end, and sql_drop, with WHEN TAG IN (...) filtering

---

SQL features

Capability	Details
Joins	Inner, left, right, full, semi, and anti-semi joins, LATERAL joins, plus subqueries
Common table expressions	WITH clauses
Writable CTEs	WITH cte AS (INSERT / UPDATE / DELETE ... RETURNING ...) consumed by the main statement
Window functions	16 functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and SUM / COUNT / AVG / MIN / MAX over a window
Upsert	INSERT ... ON CONFLICT DO NOTHING and DO UPDATE, with excluded.*, a WHERE clause, and RETURNING on both arms
DML	INSERT, UPDATE, DELETE, MERGE, TRUNCATE, UPDATE ... FROM, DELETE ... USING, and RETURNING
Bulk load	COPY ... FROM and COPY ... TO, in CSV and PostgreSQL binary format, including COPY FROM STDIN and COPY TO STDOUT
Set operations	UNION, INTERSECT, and EXCEPT, with ALL variants
Grouping	GROUP BY, GROUPING SETS, ROLLUP, and CUBE
Aggregates	Standard aggregates plus STRING_AGG and ARRAY_AGG
Expressions	CASE, BETWEEN, LIKE / ILIKE, COALESCE, NULLIF, IN, and EXISTS
Constraints	PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, and DEFAULT
Views	Standard views and materialized views
Schemas	CREATE SCHEMA and schema-qualified names

---

Data types

Type	Notes
SMALLINT, INTEGER, BIGINT	Native 16, 32, and 64-bit integers
REAL, DOUBLE PRECISION	IEEE 754 single and double precision
NUMERIC / DECIMAL(p,s)	128-bit decimal with precision and scale
BOOLEAN
VARCHAR(n), TEXT, CHAR(n)	UTF-8 encoded
DATE, TIME
TIMESTAMP, TIMESTAMPTZ	Microsecond precision, with and without time zone
INTERVAL
UUID	Native type, with text and binary wire formats
BYTEA	Binary strings
JSONB	Native storage with containment and path operators and the JSONB function set
ARRAY	Arrays of scalar element types, with ARRAY[...] and '{...}' literals, subscripting, ARRAY_AGG, and UNNEST
ENUM	CREATE TYPE ... AS ENUM, ordered by definition order, with pg_enum introspection
Composite types	CREATE TYPE ... AS (...), with ROW(...) construction and field access
Domains	CREATE DOMAIN with CHECK, DEFAULT, and NOT NULL
Vector similarity	The built-in embeddings functions over JSON-array vectors (no native vector type)

---

Behavior differences

ScramDB aims to match PostgreSQL semantics. The following points are where current behavior differs, so there are no surprises.

• DDL is not transactional. CREATE, ALTER, and DROP take effect immediately. They are not undone by a later ROLLBACK, and a statement error after a DDL change does not roll that change back. In PostgreSQL most DDL runs inside the transaction.
• Advisory lock keys are literals. The pg_advisory_lock family accepts integer literals or bind parameters as the lock key. A key computed from a column expression (for example pg_advisory_lock(id) FROM t) is not supported.
• Array element types are scalar. Arrays of scalar types such as integer and text are supported. Arrays whose elements are composite, enum, or domain types are not yet supported.

For features that are not yet available, see Limitations.