Backend compatibility

Four SQL dialects ship in the engine: PostgreSQL, DuckDB, ClickHouse and SQLite. Every divergent SQL fragment routes through a dialect contract, and this page is the cell-by-cell record of what each one does.

Two things frame everything below.

data.svc serves PostgreSQL only. ClickHouse, DuckDB and SQLite are complete engine backends — pool, driver, transactions, compiler, dialect — but pool-config validation or the pool-factory lookup rejects them, so they are not reachable from a tenant datastore. See Datastores and database pools for the three chokepoints. The PostgreSQL column is what runs; the other three are the portability reference and the library-embedding contract.

A dialect declaration is not a gate. The compiler consults a strict subset of the dialect surface. Everything else is a statement of position with no consumer. Each section below is marked Emitted — the compiler routes through it, so the difference is live — or Declared — the dialect states a value and no code path reads it.

What the compiler consults

On the query path the compiler calls exactly these dialect methods:

Consulted for	Method
Every identifier	QuoteIdent
Field, sort, group and relation names	ValidateIdent
Every bound argument	Placeholder
LIKE / ILIKE comparisons	LikeOp
Containment comparisons	ContainsOp
In-list and array values	ArrayLiteral
Relative-time values	Now, TimeExpr
LIMIT / OFFSET	WriteLimitOffset
WITH / WITH RECURSIVE	CTEPrefix
RETURNING	WriteReturning
Upsert	WriteUpsert
Soft delete	SoftDeleteSetClause
match comparisons	FullTextMatch
Raw database errors	Sanitise

DDL emission is a separate interface, consumed by the migration planner — CreateTable, AddColumn, AlterColumn, CreateIndex, MapType. See Migrations.

Caution

Capabilities.Require — the method whose whole purpose is to refuse a query the backend cannot run — has no call site in the engine. Neither do SupportsUpsert, SupportsReturning, SupportsRowLock, SupportsHardDelete, NullsOrdering, ResolveOp, ResolveAgg, BulkThreshold, NativeBulkProtocol, ClassifyAlter or RewriteHints. Nothing rejects a recursive-CTE, upsert or RETURNING query on a dialect that declares no support for it. The failure surfaces as a raw database error at execution.

Identifiers and lexical rules

Emitted.

	PostgreSQL	DuckDB	ClickHouse	SQLite
Identifier quoting	"name"	"name"	`name`	"name"
Embedded delimiter escape	""	""	``	""
Identifier length enforced	63 bytes	None	None	None
Empty identifier	Rejected	Rejected	Rejected	Rejected
NUL byte in identifier	Rejected	Rejected	Rejected	Rejected
Leading/trailing whitespace	Rejected	Rejected	Rejected	Rejected
Placeholder	$1	$1	?	?
Named parameters	Declared supported	Declared supported	Declared unsupported	Declared unsupported

Every identifier is quoted, so the validation rules catch only what quoting cannot make safe: a NUL byte terminates the identifier, and PostgreSQL silently truncates past 63 bytes, which merges two distinct names into one column. PostgreSQL is the only dialect with a length rule. ClickHouse’s backend capability record declares a 255-byte ceiling, but its ValidateIdent does not read it, so no length is checked there.

The length rule is live. A field, sort key, group key or relation name longer than 63 bytes is rejected by the compiler’s safety guard before any SQL is built:

identifier "…" exceeds PostgreSQL 63-byte limit (NAMEDATALEN)

Named-parameter support is declared and never read — the compiler emits positional placeholders on all four dialects.

Literals

Emitted for arrays; declared for the rest — the compiler binds values as arguments rather than inlining them, so the literal renderers matter only on the paths that inline.

	PostgreSQL	DuckDB	ClickHouse	SQLite
NULL	NULL	NULL	NULL	NULL
Boolean	TRUE / FALSE	TRUE / FALSE	1 / 0	1 / 0
String	'foo', ' doubled	same	same	same
Array	ARRAY[…]	ARRAY[…]	[…]	JSON text '["a","b"]'
JSON	'…'::jsonb	'…'	'…'	'…'
Bytes	E'\\x…'	'\x…'::BLOB	unhex('…')	x'…'
Cast	(expr)::type	(expr)::type	CAST(expr AS type)	CAST(expr AS type)

ClickHouse booleans are UInt8, so 1/0 is the canonical form. SQLite has no boolean storage class — also 1/0. SQLite has no array type at all; an array-typed field is TEXT holding JSON, and the dialect renders a JSON array string rather than an ARRAY[…] constructor.

Time anchors and intervals

Emitted. Relative-time values in the query DSL compile through TimeExpr.

Anchor	PostgreSQL	DuckDB	ClickHouse	SQLite
Now	NOW()	CURRENT_TIMESTAMP::TIMESTAMP	now()	CURRENT_TIMESTAMP
Today	CURRENT_DATE	CURRENT_DATE	today()	date('now')
Yesterday	CURRENT_DATE - INTERVAL '1 day'	same	yesterday()	date('now', '-1 day')
Start of week	DATE_TRUNC('week', CURRENT_DATE)	same	toStartOfWeek(today())	date('now', 'weekday 0', '-6 days')
Start of month	DATE_TRUNC('month', CURRENT_DATE)	same	toStartOfMonth(today())	date('now', 'start of month')
Start of year	DATE_TRUNC('year', CURRENT_DATE)	same	toStartOfYear(today())	date('now', 'start of year')
Offset form	<anchor> ± INTERVAL '<n> <unit>'	same	<anchor> ± INTERVAL <n> <UNIT> — unquoted	datetime(<anchor>, '±<n> <unit>')
Timezone storage	UTC (timestamptz)	Naive (TIMESTAMP)	Local-aware (DateTime('TZ'))	Naive (ISO-8601 text)

DuckDB maps “now” to CURRENT_TIMESTAMP::TIMESTAMP rather than NOW() deliberately: bare NOW() returns TIMESTAMPTZ, which DuckDB refuses to insert into a TIMESTAMP column without an explicit cast.

The timezone-storage mode is declared, not enforced. Nothing warns when a UTC-stored column is compared against a naive one.

Operators

Emitted for LIKE/ILIKE and containment; declared for the rest.

	PostgreSQL	DuckDB	ClickHouse	SQLite
LIKE	LIKE	LIKE	LIKE	LIKE
Case-insensitive match	ILIKE	ILIKE	ILIKE	LIKE — no ILIKE
Containment	@>	none	none	none
Range overlap	&&	none	none	none
Regex operators	Declared	Declared	Declared	Not supported
Vector distance <->	With pgvector	No	No	No
Trigram similarity %	With pg_trgm	No	No	No

SQLite’s LIKE is case-insensitive for ASCII by default, so the dialect returns LIKE for both modes and leaves case behaviour to the column’s collation. SQLite reports no regex support — a regex operator needs a user-defined function the driver does not install.

Caution

On DuckDB, ClickHouse and SQLite the containment operator resolves to an empty string, and the compiler emits it verbatim: field value, with the operator missing. A contains comparison on those three produces a syntax error at the database, not a compile-time refusal. Only PostgreSQL has a token for it.

Pagination and null ordering

Emitted for limit and offset; declared for null ordering.

	PostgreSQL	DuckDB	ClickHouse	SQLite
LIMIT n	Yes	Yes	Yes	Yes
OFFSET m	Yes	Yes	Yes	Yes
Emission order	LIMIT then OFFSET	same	same	same
ORDER BY required for OFFSET	No	No	No	No
Null ordering	Explicit NULLS FIRST/LAST	Explicit	Ascending nulls last, descending nulls first	Explicit

All four emit the same LIMIT n OFFSET m shape. ClickHouse is the only one whose null placement is driven by defaults instead of explicit syntax, and since nothing reads the declaration the compiler emits no NULLS clause anywhere.

CTEs

Emitted.

	PostgreSQL	DuckDB	ClickHouse	SQLite
WITH	WITH	WITH	WITH	WITH
WITH RECURSIVE	WITH RECURSIVE	WITH RECURSIVE	Not supported	WITH RECURSIVE
Writable CTEs	Yes	Yes	No	Yes

Asking for a recursive CTE on ClickHouse emits WITH followed by a comment marking the feature unsupported — the dialect’s defensive fallback for a caller that skipped the capability check. Since no caller performs that check, this is the path a recursive query actually takes on ClickHouse, and the database rejects the result.

Mutations

Emitted for RETURNING, upsert and soft delete; declared for the rest.

	PostgreSQL	DuckDB	ClickHouse	SQLite
INSERT	Yes	Yes	Yes	Yes
INSERT … RETURNING	Yes	Yes	Emits nothing	Yes (3.35+)
UPDATE … WHERE	Yes	Yes	Async mutation	Yes
DELETE … WHERE	Yes	Yes	Declared unsupported	Yes
Upsert	ON CONFLICT … DO UPDATE SET, EXCLUDED.<col>	same	Error at emission	ON CONFLICT … DO UPDATE SET, excluded.<col>
Soft-delete timestamp	NOW()	CURRENT_TIMESTAMP::TIMESTAMP	now()	CURRENT_TIMESTAMP
Generated-key strategy	RETURNING	RETURNING	None	RETURNING
Bulk threshold	1000 rows	5000	100	5000
Native bulk protocol	COPY	Appender API	Native protocol	Multi-row VALUES

ClickHouse is the one dialect that refuses rather than mis-emits: WriteUpsert returns clickhouse: upsert not supported (use ReplacingMergeTree table engine instead), and the compiler propagates it as upsert: <error>. Its RETURNING writer is a no-op, so a returning-shaped insert silently produces a statement with no RETURNING clause and the caller gets no row back.

Soft delete and row history are the largest functional gap. ClickHouse declares no history strategies and no soft-delete support because its updates and deletes are asynchronous background mutations — see Row history and Datastores.

Row locking

Declared. Nothing in the engine emits a lock clause.

	PostgreSQL	DuckDB	ClickHouse	SQLite
FOR UPDATE / FOR SHARE	Yes	No	No	No
FOR NO KEY UPDATE / FOR KEY SHARE	Yes	No	No	No
SKIP LOCKED	SKIP LOCKED	None	None	None
Row-level locking	Yes	No	No	No

DuckDB is MVCC with snapshot isolation and has no row locks; ClickHouse has none; SQLite locks per transaction (BEGIN IMMEDIATE) rather than per row.

Aggregation

Declared. The dialect aggregate catalogs have no consumer — the aggregation vocabulary the API accepts is the query DSL’s, not these.

	PostgreSQL	DuckDB	ClickHouse	SQLite
COUNT / SUM / AVG / MIN / MAX	Yes	Yes	Yes	Yes
String concatenation	STRING_AGG(f, sep)	STRING_AGG(f, sep)	arrayStringConcat(groupArray(f), sep)	group_concat(f, sep)
Percentile	PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY f)	same	quantile(p)(f)	Aborts with an error
Array aggregate	ARRAY_AGG	ARRAY_AGG, LIST	groupArray	—
Boolean aggregate	BOOL_AND / BOOL_OR / EVERY	BOOL_AND / BOOL_OR	min / max	—
Standard deviation	STDDEV, STDDEV_POP, STDDEV_SAMP	same	stddevSamp, stddevPop	stddev
Variance	VAR_POP, VAR_SAMP, VARIANCE	same	varPop, varSamp	variance
Correlation / covariance	CORR, COVAR_POP, COVAR_SAMP	CORR	corr	—
Regression	REGR_SLOPE, REGR_INTERCEPT	—	—	—
Bitwise	BIT_AND, BIT_OR	—	—	—
JSON aggregate	JSON_AGG, JSONB_AGG, JSONB_OBJECT_AGG	—	—	—
Cardinality	—	—	uniq, uniqExact	—
Total	—	—	—	TOTAL

SQLite has no percentile function. The dialect renders a statement that raises percentile not supported on sqlite at execution rather than emitting silently wrong SQL. ClickHouse maps boolean aggregates to min/max because its booleans are UInt8, where min is AND and max is OR over {0,1}.

JSON

Declared.

	PostgreSQL	DuckDB	ClickHouse	SQLite
Path access	f->'a'->'b'	same	JSONExtractRaw(f, 'a', 'b')	json_extract(f, '$.a.b')
Path access as text	f->'a'->>'b'	same	JSONExtractString(f, 'a', 'b')	json_extract(f, '$.a.b')
Containment	f @> …	json_contains(f, …)	JSONHas(f, …)	EXISTS(SELECT 1 FROM json_each(f) …)
Storage	jsonb	JSON (string-backed)	String	TEXT

SQLite path segments that are not identifier-safe are bracket-quoted ($["a b"]) rather than dot-appended.

Full-text search

Emitted, but unreachable. The engine installs the full-text writer as the compiler’s match hook, so a match comparison compiles through the dialect on all four backends. Nothing produces that comparison — neither query form has a spelling for match, and no other caller builds one (see Query DSL). The table is what each dialect would emit, not what any request reaches.

	PostgreSQL	DuckDB	ClickHouse	SQLite
Native full-text	to_tsvector(f) @@ plainto_tsquery(v)	No	No	With fts5
Fallback match	—	f ILIKE '%' || v || '%'	f ILIKE '%' || v || '%'	f LIKE '%' || v || '%'

PostgreSQL is the only dialect with a real full-text expression. The other three degrade to a substring match — same result shape, different semantics and no index use.

Schema qualification

Declared for the connection convention; the physical namespace itself is applied by the service. See Physical schema qualification.

	PostgreSQL	DuckDB	ClickHouse	SQLite
Default schema	public	main	default	main
Qualified form	"schema"."table"	"schema"."table"	`schema`.`table`	"schema"."table"
Qualification suppressed when	schema empty	schema empty	schema empty	schema empty or main
Cross-schema joins	Yes	Yes	Yes (cross-database)	Yes
Per-connection convention	SET search_path	SET schema	USE database	None — a named schema is refused
Multi-schema per database	Yes	Yes	Databases instead	Attached databases

Only the PostgreSQL provider keeps the schema prefix in compiled SQL. The ClickHouse, DuckDB and SQLite providers install an adapter that suppresses it, so schema-per-tenant isolation is a PostgreSQL-only mechanism. SQLite goes further and issues no per-connection statement at all: setting a tenant schema is a no-op for main and an error for anything else — schema-per-tenant is not supported on SQLite; use file-per-tenant instead.

Compiler feature flags

Declared. The full flag set, as each dialect answers it. Extension-gated cells are marked with the extension that flips them.

Flag	PostgreSQL	DuckDB	ClickHouse	SQLite
recursive-cte	Yes	Yes	No	Yes
lateral-join	Yes	Yes	No — ARRAY JOIN instead	No
grouping-sets	Yes	Yes	No	No
rollup-cube	Yes	Yes	Yes — WITH ROLLUP/CUBE	No
distinct-on	Yes	Yes	No	No
window-frames	Yes	Yes	Yes	Yes
materialized-view	Yes	No	Yes	No
partial-index	Yes	No	No	Yes
expression-index	Yes	Yes	No	Yes
generated-column	Yes	Yes	Yes — MATERIALIZED/ALIAS	Yes
row-level-security	Yes	No	Yes — native row policies	No
triggers	Yes	No	No	No
stored-procedures	Yes	No	No	No
transactional-ddl	Yes	Yes	No	Yes
savepoints	Yes	No	No	Yes
listen-notify	Yes	No	No	No
cursors	Yes	No	No	No
streaming	Yes	No	Yes — LiveView	No
geo-spatial	With postgis	With spatial	No	No
vector-ops	With pgvector	No	No	No
json-ops	Yes	Yes	Yes	Yes
array-ops	Yes	Yes	Yes	No
full-text-search	Yes	No	No	With fts5

Note

Two capability systems describe the same backends and they disagree. The flags above belong to the compiler dialect; a second set belongs to the backend driver and drives the boot-time schema-refusal gates in Datastores. Three cells differ: ClickHouse declares row-level-security and generated-column here but neither at the driver level, and DuckDB declares savepoints at the driver level but not here. The driver set is the one the refusal gates read; neither set is read on the query path. RLS as this block implements it is PostgreSQL-only — see Row-level security.

Extensions on PostgreSQL

datastores[].extensions is the declared list. On PostgreSQL each known name overlays types, operators and functions onto the dialect’s registries; unknown names are dropped silently.

Extension	Types added	Operators added	Functions added
pgvector	vector, halfvec, sparsevec	<->, <=>, <#>	vector_dims, l2_distance, cosine_distance, inner_product, l1_distance, vector_norm
pg_trgm	—	%, <%, %>, <<%, %>>, <->	similarity, word_similarity, strict_word_similarity, show_trgm, show_limit
postgis	geometry, geography, box2d, box3d	&&, ~, @, <<, >>, &<, &>, <<|, |>>, &<|, |&>, <#>, ~=	The ST_* family — ST_AsText, ST_GeomFromText, ST_Distance, ST_DWithin, ST_Intersects, ST_Contains, ST_Within, ST_Buffer, ST_Area, ST_Length and more
citext	citext	—	—
uuid-ossp	—	—	uuid_generate_v1, v1mc, v3, v4, v5; uuid_nil, ns_dns, ns_url, ns_oid, ns_x500

DuckDB overlays spatial (geometry and geography types, a subset of the ST_* family) and icu (collation and timezone only — no compile-time surface). ClickHouse tracks declared extensions but overlays nothing: its features are built into the binary and configured through table engines and codecs.

Caution

Declaring two extensions that define the same operator token removes that token from the overlay entirely, with no error. <-> is defined by both pgvector (L2 distance) and pg_trgm (trigram distance); <#> by both pgvector (negative inner product) and postgis (box centroid distance). PostgreSQL dispatches on operand type, which a definition author cannot express, so the collision fails closed. Use the qualified function form — l2_distance(a, b) or similarity(a, b) — when both are installed.

The overlay reaches the dialect’s type registry. The operator overlay does not reach the query path at all: the method that would read it has no call site, so extension operators are not emittable from the query DSL under any configuration.

Migration safety

Declared. Each dialect classifies a field change as online (no row rewrite), rewrite (table or column rewritten) or downtime (table offline), and supplies operator hints. Nothing calls either method — the deployed planner uses its own risk gate, documented under Migrations.

Change	PostgreSQL	DuckDB	ClickHouse	SQLite
Type change, widening within a family	Online	Rewrite	Downtime	Rewrite
Type change, narrowing or family switch	Rewrite	Rewrite	Downtime	Rewrite
Nullability change	Online	Online	Rewrite	Rewrite
Default change	Online	Online	Online	Online
No field-level change	Online	Online	Online	Online

PostgreSQL’s widening set is string ↔ text, int → bigint, and float → float; every other type change classifies as a rewrite.

The hints each dialect carries for a rewrite are worth reading even though the planner does not print them:

Backend	Guidance for a type change
PostgreSQL	Full table rewrite, every row re-validated; blue-green (add column, dual-write, batch backfill, swap, drop) avoids the ACCESS EXCLUSIVE lock window
DuckDB	Columnar — the column file is regenerated; ALTER … TYPE blocks the database for the duration under the single-writer model
ClickHouse	Runs as an async mutation; track with system.mutations; I/O load doubles until it completes; on distributed tables coordinate with replication or dual-write to a new table
SQLite	No in-place type change — rebuild: create the new table, INSERT … SELECT, drop the old, rename

For nullability tightening, PostgreSQL’s fully-online route is ADD CONSTRAINT … CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, then ALTER COLUMN SET NOT NULL. SQLite and ClickHouse both rewrite.

Versions

Each backend declares supported, deprecated and end-of-life ranges, compared on major and minor only. data.svc never runs that check — the full table and the reason it is inert are in Datastores. No dialect branches on version — the version is carried for error text only. The version-gated declarations live in each backend’s capability record, which the library resolves when you embed it:

Gate	Backend	From
Transactions, snapshot isolation only	ClickHouse	24.8
Lightweight DELETE	ClickHouse	23.8
Bloom indexes	ClickHouse	24.1
Concurrent materialized-view refresh	ClickHouse	24.3
RETURNING	SQLite	3.35
STRICT tables	SQLite	3.37

The SQLite floor is 3.37 because STRICT tables were the intended baseline, but CREATE TABLE does not emit STRICT: the driver’s automatic timestamp scanning needs declared types (TIMESTAMP, DATETIME, DATE) that STRICT rejects, and working time scans won.

Related

• Datastores and database pools — how a backend is selected, and the driver-level capability declarations
• Types — the column type each logical type produces on each backend
• Query DSL — the operator and aggregation vocabulary the API accepts
• Migrations — the risk gate and refusals that do run
• Row-level security — how a rule becomes a WHERE clause