PostgreSQL Adapter
Generate PostgreSQL SQL from your model. adapter: postgres is also the dialect the running TPC-DS example uses.
What the adapter emits
- Identifiers in
"double quotes"; bare column names in expressions are lowercased. - Date literals as
'2026-01-01'::DATE; grain truncation asDATE_TRUNC('month', x); parts withEXTRACT(year FROM x). - CTEs for the per-fact subqueries and a
FULL OUTER JOINwhen a query blends measures from two facts.
A monthly revenue projection comes back in this shape:
SELECT
DATE_TRUNC('month', T1."d_date")::DATE AS "Month(Date)",
sum(T0."ss_net_paid") AS "Store Net Paid"
FROM
store_sales T0
JOIN date_dim T1
ON T0.ss_sold_date_sk = T1.d_date_sk
GROUP BY
DATE_TRUNC('month', T1."d_date")::DATE
Configuration
warehouse:
adapter: postgres
name: Production Warehouse
tier: hot
host: db.example.com
port: 5432 # default 5432
database: analytics
schema: public
username: analyst
# password: never here; zsql deploy strips it if present
Connection fields
The server never connects to Postgres: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.
zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It takes username and a password, with sslmode and ca_file for TLS. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips password and the other secret keys from the file if present. See Connecting to a warehouse.
Notes
- Expressions are Postgres SQL.
percentile_cont(0.5) within group (order by x),string_agg,::casts anddate_partare all fine inexpression.sql. - Schema-qualified tables go in
physical_name(sales.orders); it is emitted verbatim inFROM.
Next Steps
- Datasources, tiers and secrets
- Managing Datasources