PostgreSQL: the primary dialect#

Scythe documents PostgreSQL as its "primary and most complete dialect," with the broadest feature coverage of any engine:

• Enums — CREATE TYPE ... AS ENUM (...) parsed and mapped to enum::name.
• Composite types — CREATE TYPE ... AS (...) mapped to composite::name.
• Arrays — text and integer arrays mapped to parameterized array types.
• JSONB/JSON — mapped to json; typed JSON available via the @json annotation.
• Nested aggregates — json_agg(alias.*), jsonb_agg(alias.*), row_to_json(alias.*), to_json(alias.*), and to_jsonb(alias.*) over a relation resolve to a struct scythe synthesizes from that relation's columns, on PostgreSQL and CockroachDB only. Among all backends, only rust-sqlx, rust-tokio-postgres, go-pgx, and python-psycopg3 actually decode the result into that struct at runtime — the rest keep a plain json mapping.
• Views — resolved through their underlying table definitions.
• Domains — CREATE DOMAIN resolved to its base type, with NOT NULL propagated.
• Range types — int4range, tstzrange, and others mapped to range<T>.
• Network types — INET, CIDR, MACADDR mapped to inet.

PostgreSQL type mapping#

See the Neutral Type Reference for the complete scalar list and every dialect-specific exception.

Composite type decoding limitation#

Decoding a nullable composite column at runtime — not just generating the correct type annotation for it — only works on four of the fifteen PostgreSQL backends: rust-sqlx, rust-tokio-postgres, java-jdbc, and kotlin-jdbc. On the other eleven, the struct type is declared correctly, but the driver's raw value is assigned straight through without parsing, so the generated type annotation doesn't match what the driver actually returns at runtime. Avoid relying on composite columns if your backend isn't one of these four.

Placeholders and DML#

PostgreSQL queries use positional $N placeholders ($1, $2, …). RETURNING is fully supported for :one and :many on INSERT/UPDATE/DELETE, ON CONFLICT (upsert) is fully supported, and SERIAL/BIGSERIAL columns are automatically marked NOT NULL.

Verifying against a live database#

scythe check --database-url <postgres-url> is PostgreSQL-only in both directions: type verification needs the extended query protocol's describe step (each query is prepared server-side, never executed, so it's safe to run against production), and schema-drift detection reads pg_catalog directly through tokio-postgres. See the SC-VER* and SC-DRF* rule tables in the CLI reference.

Related#

• Configuration — the full engine field and alias table.
• Type Inference — how nullability is derived once a column's base type is resolved.