PostgreSQL

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

Non-secret settings go in workflow_settings.yaml (committed); the connection — including the password — goes in .df-credentials.json (gitignored, never committed):

workflow_settings.yaml

warehouse: postgres
defaultDataset: analytics            # the schema your models build into
defaultAssertionDataset: sqlanvil_assertions
sqlanvilCoreVersion: 1.24.0          # pin the release you installed — `sqlanvil init` writes this

// .df-credentials.json  (gitignored)
{
  "host": "db.example.com",
  "port": 5432,
  "database": "analytics",
  "user": "sqlanvil_writer",
  "password": "your-password",
  "sslMode": "require",
  "defaultSchema": "public"
}

sslMode accepts disable | allow | prefer | require | verify-ca | verify-full. Pass a different credentials path at run time with --credentials <path>.

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)

config {
  type: "table",
  postgres: {
    tablespace: "fast_ssd",
    fillfactor: 80,
    partition: {
      kind: 0,               // numeric enum: RANGE=0, LIST=1, HASH=2
      columns: ["order_date"]
    },
    indexes: [
      {
        name: "ix_orders_customer",
        columns: ["customer_id"]
        // method omitted → btree (BTREE=0, HASH=1, GIN=2, GIST=3, BRIN=4)
      },
      {
        name: "ix_orders_search",
        columns: ["description"],
        method: 2              // 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	numeric enum: 0 RANGE, 1 LIST, 2 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	numeric enum: 0 BTREE, 1 HASH, 2 GIN, 3 GIST, 4 BRIN (omit for btree)	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: 0, columns: ["order_date"] }   // RANGE=0, LIST=1, HASH=2
  }
}
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);

Note

BigQuery’s partitionBy / clusterBy fields are not valid for Postgres targets. Use postgres.partition / postgres.indexes instead.

Configuration reference

workflow_settings.yaml (committed):

Field	Required	Description
warehouse	✓	postgres
defaultDataset	✓	Default schema for actions (e.g. public)
defaultAssertionDataset		Schema for generated assertions
sqlanvilCoreVersion	✓	Pin the @sqlanvil/core version (e.g. 1.0.1)

.df-credentials.json (gitignored — the PostgresConnection):

Field	Required	Description
host	✓	Database host
port	✓	Database port (default: 5432)
database	✓	Database name
user	✓	Database user
password	✓	Password
sslMode		disable, allow, prefer, require, verify-ca, verify-full
defaultSchema		Connection search-path schema (e.g. public)