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

0sql never connects to Postgres. The fields above are carried as metadata for your own application, which runs the SQL it gets back. 0sql never uses them. If a password does land in datasources.yml, zsql deploy strips secret keys from the file, so nothing sensitive reaches the service.

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