Compatibility

PizzaSQL is a relational database with a SQLite-inspired SQL dialect and partial PostgreSQL wire-protocol compatibility. It is not PostgreSQL, and it is not a drop-in SQLite replacement.

This page describes behavior verified in the current source and tests. The SQLite column uses modern SQLite 3.39 or newer as its reference, and the PostgreSQL column uses PostgreSQL 14. Optional SQLite extensions may behave differently.

Status key

Status	Meaning
Supported	Implemented end to end. Important differences are noted.
Partial	Usable for a subset of the reference behavior.
Unsupported	Rejected or not implemented.
Unsafe	Syntax may be accepted, but behavior is incorrect or not enforced.
Unverified	Expected or claimed, but not covered by reliable automated evidence.

Positioning

Dimension	PizzaSQL
SQL dialect	SQLite-inspired, with documented differences
Wire protocol	Partial PostgreSQL v3 simple and extended query protocols
Type system	Dynamic values with partial SQLite-style affinity
Storage	Versioned binary rows in PizzaKV
Transactions	Optimistic compare-batch DML transactions; DDL is non-transactional
Planner	Heuristic index selection, otherwise scans

Important limitations

Do not rely on the following behavior yet:

• CHECK and foreign-key declarations may be accepted but are not enforced. UNIQUE constraints and unique indexes are enforced on the core write paths, but only through validating scans — this is not a durable uniqueness claim and does not imply full SQLite or PostgreSQL concurrency semantics. UNIQUE is materialized only when a table is created; tables created before this support are not retroactively enforced after an upgrade.
• Composite primary keys retain only their first column.
• RIGHT JOIN, FULL JOIN, JOIN ... USING, and NATURAL JOIN may parse but do not execute correctly.
• DDL changes such as CREATE TABLE are not rolled back by ROLLBACK.
• GROUP_CONCAT and TOTAL are not safe to execute.
• Result column type metadata is only reported for single-table direct column projections; expressions, joins, and unknown columns are still reported as TEXT.

Unsupported syntax normally returns an error. The items above are called out separately because accepting syntax without enforcing its semantics can cause incorrect application behavior.

Queries

Capability	PizzaSQL	SQLite	PostgreSQL
Basic SELECT, expressions, and aliases	Supported	Supported	Supported
WHERE	Supported	Supported	Supported
DISTINCT	Supported	Supported	Supported
ORDER BY, LIMIT, and OFFSET	Supported	Supported	Supported
ORDER BY ... NULLS FIRST/LAST	Supported	Supported	Supported
GROUP BY and HAVING	Supported; permissive SQLite-style grouping	Supported	Supported; stricter grouping rules
INNER JOIN	Supported	Supported	Supported
LEFT JOIN	Supported	Supported	Supported
CROSS JOIN and comma joins	Supported	Supported	Supported
RIGHT JOIN and FULL JOIN	Unsafe; parsed but not executed correctly	Supported since 3.39	Supported
JOIN ... ON	Supported	Supported	Supported
JOIN ... USING	Unsafe; join condition is not applied	Supported	Supported
NATURAL JOIN	Unsafe; join columns are not derived	Supported	Supported
Scalar subqueries	Supported; multiple rows use the first row	Supported; multiple rows use the first row	Multiple rows produce an error
Correlated subqueries	Supported	Supported	Supported
EXISTS and IN (SELECT ...)	Supported	Supported	Supported
Subqueries in FROM	Supported	Supported	Supported
UNION and UNION ALL	Supported	Supported	Supported
INTERSECT and EXCEPT	Supported; distinct form only	Supported	Supported, including ALL
Common table expressions (WITH)	Supported	Supported	Supported
Recursive common table expressions	Supported	Supported	Supported
Window functions	Partial; ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) only	Supported	Supported

Writes

Capability	PizzaSQL	SQLite	PostgreSQL
Single-row and multi-row INSERT	Supported	Supported	Supported
INSERT ... SELECT	Supported	Supported	Supported
UPDATE	Supported	Supported	Supported
DELETE	Supported	Supported	Supported
INSERT OR IGNORE, REPLACE, and related SQLite conflict modes	Supported	Supported	Different syntax
ON CONFLICT DO NOTHING	Supported for primary-key and unique-index targets	Supported for unique constraints	Supported for unique constraints
ON CONFLICT DO UPDATE	Supported for primary-key and unique-index targets, including excluded	Supported for unique constraints	Supported for unique constraints
RETURNING	Supported for INSERT, UPDATE, and DELETE	Supported since 3.35	Supported
TRUNCATE	Unsupported	Unsupported	Supported

Schema and constraints

Capability	PizzaSQL	SQLite	PostgreSQL
CREATE TABLE and DROP TABLE	Supported	Supported	Supported
CREATE TABLE IF NOT EXISTS	Supported	Supported	Supported
ALTER TABLE ADD/DROP/RENAME	Partial	Partial	Supported
Altering column type, default, or nullability	Unsupported	Limited	Supported
CREATE INDEX and DROP INDEX	Partial; indexes are rebuilt from stored rows	Supported	Supported
CREATE INDEX IF NOT EXISTS	Supported in current source; deployed versions may differ	Supported	Supported
Views	Partial; not persisted across executor restarts	Persistent	Persistent
Triggers	Unsupported	Supported	Supported
Single-column primary keys	Supported	Supported	Supported
Composite primary keys	Unsafe; only the first column is retained	Supported	Supported
NOT NULL	Supported	Supported	Supported
UNIQUE constraints	Partial; enforced on core write paths via validating scans	Supported	Supported
Unique indexes	Partial; enforced on core write paths via validating scans	Supported	Supported
CHECK constraints	Unsafe; parsed but not enforced	Supported	Supported
Foreign keys	Unsafe; parsed but not enforced	Supported when enabled	Supported
Literal defaults	Partial	Supported	Supported
Expression defaults	Partial	Supported	Supported
DEFAULT CURRENT_TIMESTAMP	Unsafe; does not evaluate per inserted row	Supported	Supported
AUTOINCREMENT	Partial; accepted without distinct SQLite semantics	Supported	Not applicable
Identity and serial columns	Unsupported	Not applicable	Supported
Generated columns	Partial; stored generated columns only	Supported	Supported
Implicit rowid	Partial	Supported	Unsupported

Types and expressions

PizzaSQL accepts many familiar type names but does not implement PostgreSQL’s strict type system. Declared precision and scale are not enforced, and values are not consistently coerced to a column’s declared affinity when inserted.

Capability	PizzaSQL	SQLite	PostgreSQL
Integer and floating-point values	Supported	Supported	Supported
Text values	Supported	Supported	Supported
Boolean values	Supported	Integer convention	Supported
Binary/BLOB values	Partial	Supported	Supported as BYTEA
SQLite type affinity	Partial; affinity names accepted without complete coercion	Supported	Not applicable
Strict PostgreSQL types	Unsupported	Unsupported	Supported
NUMERIC(p,s) precision enforcement	Unsupported	Unsupported	Supported
Native date and time types	Unsupported; values remain dynamically typed	Unsupported	Supported
JSON values, functions, and operators	Partial; JSON1 functions are supported, but there is no native JSON type or JSON operators	Available with SQLite JSON support	Supported with JSON and JSONB
Arrays, UUIDs, enums, and intervals	Partial; UUID is accepted as a text alias with no native PostgreSQL UUID semantics	Unsupported as native types	Supported
CAST to integer, real, and text	Supported	Supported	Supported
Other CAST targets	Partial; many targets are no-ops	Affinity based	Strictly typed
PostgreSQL :: casts	Unsupported	Unsupported	Supported
SQL three-valued NULL logic	Supported	Supported	Supported
LIKE	Supported; case-insensitive	Case-insensitive by default	Case-sensitive; ILIKE is separate
Collations	Unsupported	Supported	Supported
Bitwise, regular-expression, and JSON operators	Unsupported	Partial	Supported

Functions

Capability	PizzaSQL	SQLite	PostgreSQL
COUNT, SUM, AVG, MIN, and MAX	Supported	Supported	Supported
COUNT(DISTINCT ...)	Supported	Supported	Supported
GROUP_CONCAT	Unsafe; currently has a recursion failure	Supported	Use STRING_AGG
TOTAL	Unsafe; currently has a recursion failure	Supported	Unsupported
Common string functions	Partial	Supported	Supported
Common math functions	Partial	Supported	Supported
CEIL, FLOOR, and MOD	Unsafe; declared but currently return NULL	Supported	Supported
SQLite date and time functions	Supported	Supported	Different function set
last_insert_rowid(), changes(), total_changes()	Supported; session-local	Supported	Unsupported
PostgreSQL date and time functions	Unsupported	Unsupported	Supported
JSON functions	Partial; JSON1 scalar functions and json_group_array are supported	Available with SQLite JSON support	Supported
Window functions	Partial; ROW_NUMBER() only	Supported	Supported

Transactions

Capability	PizzaSQL	SQLite	PostgreSQL
BEGIN, COMMIT, and ROLLBACK for DML	Supported	Supported	Supported
Read-your-writes	Supported	Supported	Supported
Savepoints	Supported	Supported	Supported
Concurrent write conflict detection	Supported; optimistic serialization failures	Serialized writers	MVCC and serialization modes
Transactional DDL	Unsupported	Supported	Supported
Transactions through database.pizza HTTP query endpoints	Unsupported	Client dependent	Client dependent

PostgreSQL clients and wire protocol

PostgreSQL wire compatibility allows some PostgreSQL clients to connect. It does not make the SQL dialect or server behavior PostgreSQL-compatible.

Capability	PizzaSQL	SQLite	PostgreSQL
PostgreSQL v3 wire protocol	Partial	Not applicable	Native
Simple-query protocol	Supported	Not applicable	Supported
Extended-query protocol	Partial	Not applicable	Supported
Prepared statements	Partial; parameters are rewritten as SQL literals	Not applicable	Native
Text parameters and results	Supported	Not applicable	Supported
Binary parameters	Partial; limited boolean and integer support	Not applicable	Supported
Binary results	Unsupported	Not applicable	Supported
Correct result type OIDs	Partial; single-table direct column projections report typed OIDs, expressions and joins report TEXT	Not applicable	Supported
PostgreSQL TLS	Unsupported	Not applicable	Supported
PostgreSQL SCRAM and MD5 authentication	Unsupported	Not applicable	Supported
information_schema	Partial emulation	Unsupported	Supported
pg_catalog	Unsupported	Unsupported	Supported
Query cancellation	Unsupported	Not applicable	Supported
COPY	Unsupported	Not applicable	Supported
LISTEN and NOTIFY	Unsupported	Not applicable	Supported
Node.js pg	Basic use verified	Not applicable	Supported
psycopg, pgx, and lib/pq	Unverified	Not applicable	Supported
ORMs and migration frameworks	Unverified	Varies	Supported
psql schema introspection commands	Partial	Not applicable	Supported

Because the wire is PostgreSQL-compatible, drivers may issue introspection queries such as information_schema, pg_catalog, SELECT version(), or SHOW server_version on connection. PizzaSQL emulates a small subset of these so basic clients can start. SQLite-style migrators are also accommodated with a read-only sqlite_master/sqlite_schema emulation and the index_list, index_info, and table_xinfo pragmas. See PostgreSQL protocol.

Managed service differences

The managed database.pizza proxy layers additional behavior on top of PizzaSQL:

• API keys with read, write, alter_table, and drop_table scopes gate statements.
• PostgreSQL TLS is currently declined. Connections require sslmode=disable, so credentials and query traffic are not protected on an untrusted network.
• The PostgreSQL database name is org/db; the slash must be percent-encoded in a URI.
• Transaction statements are rejected by the managed HTTP query endpoints and forwarded by the PostgreSQL proxy.

SQLite compatibility evidence

The PizzaSQL repository includes the SQLite SQLLogicTest corpus and a custom runner. The corpus provides broad query coverage, but it does not currently prove complete SQLite compatibility:

• SQLite-specific conditional cases are not all selected correctly by the runner.
• Some executor and storage integration tests skip when PizzaKV is unavailable.
• The default SQLLogicTest target exercises HTTP rather than the PostgreSQL wire protocol.
• There is no committed CI result artifact supporting a 100% compatibility claim.

Compatibility claims should therefore be tied to explicit automated tests, not only to the presence or size of the corpus.

Deployed integration evidence

Gogs and Vikunja are dogfooding integrations for the SQLite dialect and PostgreSQL wire protocol. Both are deployed on database.pizza through the managed PostgreSQL proxy; transport TLS remains unavailable between the applications and the proxy, so that hop is loopback-only. Their local source-build workflows remain the primary integration targets for engine changes.

Gogs

An experimental Gogs fork drives PizzaSQL through a custom pgx-backed database/sql driver that emits SQLite-dialect SQL over the PostgreSQL wire. Its opt-in smoke test passes end to end against local engine binaries, covering:

• Full fresh-start schema installation and table creation through GORM and XORM, migrations seeding, and XORM Sync2.
• The install flow end to end: GET /install returns 200, POST /install redirects with 302, and the admin user is inserted and reachable through /user/login.
• User, repository, access-token, and permission writes through the public stores, cross-ORM reads, uniqueness rejection, and transaction rollback.
• Pool close/reopen and full engine-process plus PizzaKV restart against the same .pkvdb file, confirming installation and persistence.
• Core LFS object metadata: multiple objects per repository, the same OID in a different repository kept distinct, duplicate (repo_id, oid) rejection, and reads back with a real time.Time created_at.

This is not evidence of complete Gogs compatibility or of the Git LFS transfer path — actual Git LFS upload, SSH, and push have not been tested yet, and the full Gogs test suite has not passed against PizzaSQL.

Vikunja

An experimental Vikunja fork uses the same driver shape (a database/sql driver plus an XORM dialect mapping) but exercises a larger schema and different queries. Its full migration suite ran against a local engine, and registration, login, project CRUD, and task CRUD — create, update, list, delete, and the notification path — worked through the HTTP API. Supporting it required engine work for:

• Non-recursive and recursive common table expressions.
• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).
• ORDER BY ... NULLS FIRST/LAST.
• ON CONFLICT (cols) DO UPDATE on unique indexes, including excluded.
• Correlated EXISTS in a single-table WHERE.

There is no committed automated Vikunja integration test yet, so this is a manual validation rather than a repeatable gate.

Advice for porting

1. Use explicit INNER, LEFT, and CROSS joins with ON conditions.
2. Enforce checks and referential integrity in the application. Uniqueness is enforced on the core write paths, but keep application-level checks for anything with stricter concurrency or DDL needs.
3. Use a single-column primary key.
4. Avoid AUTOINCREMENT, expression defaults, and DEFAULT CURRENT_TIMESTAMP for semantic behavior.
5. Use COUNT, SUM, AVG, MIN, and MAX; verify other functions against the function reference.
6. Test your exact driver and migration tool before deploying an application.

Compatibility policy

• A feature is marked Supported only when parser, executor, storage, and public connection-path tests pass.
• Features that parse without enforcing their semantics are marked Unsafe.
• Unsupported features should fail clearly rather than silently producing a different result.
• Every compatibility regression should receive a test before it is fixed.
• This matrix should change in the same release as the behavior it describes.