PostgreSQL

import { Aside } from ‘@astrojs/starlight/components’;

SQLAnvil generates idiomatic PostgreSQL DDL/DML. A user who has never touched BigQuery should never encounter BigQuery-specific concepts (NOT ENFORCED primary keys, OPTIONS(...) table options, MERGE dialect).

Connection

In workflow_settings.yaml:

warehouse:
  kind: postgres
  host: db.example.com
  port: 5432
  database: analytics
  user: sqlanvil_writer
  password: ${PG_PASSWORD}   # env var interpolation supported
  ssl: require               # disable | allow | prefer | require | verify-full
  defaultSchema: public

Tables

SQLAnvil creates Postgres tables using a transactional drop-and-recreate pattern — no BigQuery CREATE OR REPLACE TABLE:

-- definitions/orders.sqlx
config {
  type: "table"
}

SELECT
  order_id,
  customer_id,
  created_at
FROM ${ref("raw_orders")}

Generated SQL (simplified):

BEGIN;
DROP TABLE IF EXISTS public.orders;
CREATE TABLE public.orders AS SELECT ...;
COMMIT;

Incremental tables (upserts)

Postgres uses INSERT ... ON CONFLICT — not BigQuery’s MERGE:

-- definitions/events.sqlx
config {
  type: "incremental",
  uniqueKey: ["event_id"]
}

SELECT event_id, user_id, event_timestamp, event_name
FROM ${ref("raw_events")}
${when(incremental(), `WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM ${self()})`)}

Generated SQL for incremental runs:

INSERT INTO public.events (event_id, user_id, event_timestamp, event_name)
SELECT ...
ON CONFLICT (event_id) DO UPDATE SET
  user_id = EXCLUDED.user_id,
  event_timestamp = EXCLUDED.event_timestamp,
  event_name = EXCLUDED.event_name;

Postgres-specific config (postgres: block)

The `postgres:` action config block is available starting with the Postgres adapter release. See the roadmap for status.

config {
  type: "table",
  postgres: {
    tablespace: "fast_ssd",
    fillfactor: 80,
    partition: {
      kind: "range",
      columns: ["order_date"]
    },
    indexes: [
      {
        name: "ix_orders_customer",
        columns: ["customer_id"],
        method: "btree"
      },
      {
        name: "ix_orders_search",
        columns: ["description"],
        method: "gin"
      }
    ]
  }
}
SELECT ...

postgres: field reference

Field	Type	Description
tablespace	string	Postgres tablespace for the table
fillfactor	number	Storage fillfactor (1–100)
unlogged	boolean	Create as UNLOGGED TABLE (faster writes, not crash-safe)
partition.kind	"range" | "list" | "hash"	Declarative partitioning strategy
partition.columns	string[]	Partition key columns
indexes	Index[]	Indexes to create after the table
with_data	boolean	WITH DATA vs WITH NO DATA for materialized views

Index field reference

Field	Type	Description
name	string	Index name
columns	string[]	Indexed columns
method	"btree" | "hash" | "gin" | "gist" | "brin"	Index access method
where	string	Partial index predicate (e.g. "status = 'active'")
unique	boolean	Create a UNIQUE index
include	string[]	Non-key columns to include (covering index)

Native partitioning

config {
  type: "table",
  postgres: {
    partition: { kind: "range", columns: ["order_date"] }
  }
}
SELECT order_id, customer_id, order_date FROM ${ref("raw_orders")}

Generates:

CREATE TABLE public.orders (
  order_id   BIGINT,
  customer_id BIGINT,
  order_date  DATE
) PARTITION BY RANGE (order_date);

BigQuery's `partitionBy` / `clusterBy` fields are **not** valid for Postgres targets. SQLAnvil will emit a compilation error if they are used with `warehouse.kind: postgres`. Use `postgres.partition` instead.

workflow_settings.yaml — Postgres fields

Field	Required	Description
host	✓	Database host
port	✓	Database port (default: 5432)
database	✓	Database name
user	✓	Database user
password	✓	Password — use ${ENV_VAR} for secrets
ssl		SSL mode: disable, allow, prefer, require, verify-full
defaultSchema	✓	Default schema for actions (e.g. public)