---
title: Compatibility
description: How PizzaSQL differs from SQLite and PostgreSQL — the honest gap list, including features that parse but do nothing.
---

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](/internals/postgres-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](/sql-reference/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](/engine/transactions/) and [Concurrency](/engine/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.