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 as DATE_TRUNC('month', x); parts with EXTRACT(year FROM x).
  • CTEs for the per-fact subqueries and a FULL OUTER JOIN when 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 and date_part are all fine in expression.sql.
  • Schema-qualified tables go in physical_name (sales.orders); it is emitted verbatim in FROM.

Next Steps