Compatibility

PizzaSQL is SQLite-flavoured SQL behind a PostgreSQL wire protocol. It is not PostgreSQL, and it is not a drop-in SQLite either. This page is the master gap list. Read it before porting queries.

Positioning

Dimension	PizzaSQL
SQL dialect	SQLite-compatible
Wire protocol	PostgreSQL v3.0 (simple + extended)
Type system	SQLite affinity (dynamic, permissive)
Storage	JSON rows in a key-value store
Concurrency	Global per-database lock, undo-log transactions (no MVCC)
Planner	None — heuristic index selection, otherwise full scan

Versus PostgreSQL

Not implemented (rejected at parse time, not silently mishandled):

• WITH / CTEs
• Window functions (OVER, PARTITION BY, ROW_NUMBER, …)
• RETURNING
• Schemas and roles (CREATE SCHEMA, GRANT, REVOKE, roles)
• SERIAL, BIGSERIAL, UUID, ARRAY, ENUM, and other rich types. JSONB is accepted as a type name but has NUMERIC affinity; it is not a native JSON type.
• TRUNCATE, ILIKE, SIMILAR TO, ~ regex operators
• Sequences (CREATE SEQUENCE, nextval)
• DISTINCT ON, LATERAL
• COPY and \copy bulk protocol paths

Because the wire is Postgres, drivers may issue introspection queries (information_schema, pg_catalog, SELECT version(), SHOW server_version) on connect. PizzaSQL emulates a small subset of these so tools don’t choke on startup — see PostgreSQL protocol — but the underlying engine has no such catalog.

Versus SQLite

Implemented SQLite features:

• INTEGER PRIMARY KEY rowid aliasing, rowid/oid/_rowid_
• Type affinity rules
• INSERT OR REPLACE/IGNORE/FAIL/ABORT
• PRAGMA table_info, table_list, database_list, version
• Date/time functions with SQLite-style modifiers

SQLite features that are missing:

• Triggers, WITHOUT ROWID, WITH/CTEs, window functions, RETURNING
• GLOB (keyword exists but x GLOB p is a parse error)
• ATTACH/DETACH beyond the basic alias mechanism (see below)
• Full PRAGMA coverage (only the four above)

Features that parse but do nothing

This is the dangerous category — SQL that looks like it works but silently behaves differently:

Feature	What actually happens
RIGHT JOIN, FULL [OUTER] JOIN	returns an empty result
NATURAL JOIN, USING (cols)	join condition ignored (becomes a cross join)
UNIQUE column/table constraint	parsed, discarded, not enforced
CHECK (...)	parsed, discarded, never evaluated
FOREIGN KEY ... REFERENCES	parsed, discarded, not enforced
CREATE UNIQUE INDEX	definition stored, uniqueness not enforced
AUTOINCREMENT	parsed, stored, no behaviour
LIKE ... ESCAPE 'x'	ESCAPE ignored
ORDER BY ... COLLATE	no collations; COLLATE is not recognized

Functions that parse but return NULL

Several names are in the engine’s function catalog but not implemented. They parse cleanly and then return NULL: ltrim, rtrim, ceil, floor, mod, iif, quote, total, group_concat, last_insert_rowid, changes, total_changes. See Functions.

Type and coercion differences

• Columns are not strictly typed; VARCHAR(n) length is ignored.
• || does not propagate NULL (NULL || 'x' → 'x').
• Integer division truncates toward zero; division by zero yields NULL, not an error.
• NULL ordering/sorting follows the engine’s compare, which treats NULL as smaller than any value.

Transaction and concurrency differences

• No MVCC, no read snapshot isolation. A transaction takes a database-wide lock; other statements on that database block until commit or rollback. See Transactions and Concurrency.
• DDL (CREATE/DROP/ALTER) is not undo-logged — rolling back a transaction does not undo DDL performed inside it.
• The managed HTTP query endpoint rejects transaction statements; the managed PostgreSQL proxy forwards them.

Managed service differences

The managed proxy layers additional restrictions on top of the engine:

• API keys with scopes (read, write, alter_table, drop_table) gate every statement; a key without the matching scope gets 403.
• TLS is declined by the proxy — connect with sslmode=disable.
• The database name in a connection string is org/db (the slash is part of the name and must be percent-encoded in a URI).
• Transaction statements are rejected on the HTTP query/execute endpoints.

None of these are PizzaSQL engine limitations; they are properties of the managed routing layer described on the home page.

Advice for porting

1. Rewrite RIGHT/FULL joins as LEFT joins.
2. Replace USING/NATURAL with explicit ON conditions.
3. Move uniqueness and referential integrity into your application layer.
4. Don’t use AUTOINCREMENT for anything semantic — it’s a no-op.
5. Keep transactions short: they hold a database-wide lock.
6. Verify any function you rely on is in the implemented list, not just the declared list.