PostgreSQL
The postgres connector reads a PostgreSQL database with read-only SELECT queries and table reads, and writes
tables from DuckDB and Spark job outputs. Use it to pull rows into a source, or to load a job's results into
PostgreSQL.
Install
sw connectors -i postgres
Connect
Use the connection keys (the common form) or one DSN secret, not both.
kind: source
name: postgres_demo
spec:
config:
pg_host: { type: string, default: localhost }
start_date: { type: date, default: "-7d" }
secrets:
pg_password: { type: string }
auth:
provider: postgres
host: "{{ config.pg_host }}"
database: shop
user: reader
password: "{{ secrets.pg_password }}"
sslmode: require
statement_timeout: 5min
| Key | What it is | Required / default |
|---|---|---|
host | A host name, an IP address or a unix socket directory | Required (with keys) |
port | The port | 5432 |
database | The database (a source also accepts libpq's name, dbname) | Required (with keys) |
user | The role. Use one that can only read | Required (with keys) |
password | The role's password. Secret | Optional |
sslmode | libpq's sslmode: disable, allow, prefer, require, verify-ca or verify-full | Optional |
options | More libpq connection parameters, for example {connect_timeout: "10", application_name: streamwright} | Optional |
statement_timeout | The server's statement timeout, for every statement on every engine: seconds, or 500ms, 30s, 5min, 1h | Optional |
dsn | A postgresql:// URL or libpq key=value pairs, instead of the keys above. Secret | Optional |
With a DSN secret, for example when the whole connection string is one managed secret:
kind: source
name: postgres_demo
spec:
secrets:
pg_dsn: { type: string }
auth:
provider: postgres
dsn: "{{ secrets.pg_dsn }}"
A password inside a DSN is redacted (***) in logs and errors too.
Read data
A request uses service: database and one of two methods:
| Method | Arguments | What it does |
|---|---|---|
query | query, params | Runs one read-only SELECT. $name placeholders take their values from params |
table | table, schema (default public), columns, where | Reads a table: some or all columns, and the rows that match every where condition |
incremental:
cursor_field: updated_at
start: "{{ config.start_date }}"
window: 1d
requests:
- name: raw_orders
operation:
service: database
method: query
arguments:
query: |
SELECT id, total::VARCHAR AS total, updated_at
FROM public.orders
WHERE updated_at >= $since
params: { since: "{{ window.start }}" }
transform:
mode: page
steps:
- name: orders
select: |
SELECT
(record->>'id')::BIGINT AS order_id,
(record->>'total')::DECIMAL(18,2) AS total,
(record->>'updated_at')::TIMESTAMPTZ AS updated_at
FROM raw_orders
export:
orders:
step: orders
primary_key: [order_id]
- The stream reads one day of orders at a time (
window: 1d). $sinceis a bound parameter: its value comes fromparamsand is never pasted into the query text.
In jobs, a DuckDB job uses a read step, and a Spark job the same operation: as a source stream:
steps:
- name: orders # DuckDB job
connection: shop
read: query # or read: table, with table: and schema:
query: SELECT id, total, updated_at FROM orders
| Engine | Where a query runs | Arguments |
|---|---|---|
| source | In DuckDB, over the attached database (DuckDB SQL) | query, params, table, schema, columns, where |
| DuckDB job | In PostgreSQL (PostgreSQL SQL) | query, or table and schema |
| Spark job | In PostgreSQL, over JDBC (PostgreSQL SQL). $name values become escaped literals | All of the above, plus options (JDBC options such as fetchsize) |
Write data
DuckDB and Spark job outputs write a step's rows into a table. In a DuckDB job, a missing table is created from the data.
outputs:
- name: save_orders # DuckDB job
connection: shop
step: orders
table: analytics.orders
mode: merge
primary_key: [id]
In a Spark job, the output uses operation: {service: database, method: write, arguments: {table, mode, ...}}.
| Mode | What it does | Engines |
|---|---|---|
append | Adds the rows | DuckDB, Spark |
overwrite | Replaces the table | DuckDB |
merge | Updates the rows that match on primary_key and inserts the rest (INSERT ... ON CONFLICT, so the key needs a unique constraint) | DuckDB, Spark |
delete | Deletes the rows that match on primary_key | Spark |
On Spark, update lists the columns a merge updates (default: every column but the key). merge and delete load a
staging table and run one statement in one transaction. Without staging_table the run creates and drops a
temporary one, which needs CREATE and DROP privileges in the target schema.
Good to know
- Read-only queries. On
streamwright run, a query is checked before it runs: one statement, no writes, and no system catalogs (pg_catalog,information_schema, DuckDB's own views), files or table functions other thanunnest,rangeandgenerate_series. The database is attached read-only. - Types. Decimals stay exact. Dates and timestamps arrive as text (timestamps with time zone in UTC), so cast them in your SQL.
- Job state. A DuckDB job can keep its state in PostgreSQL (
runtime.state.connection); installpip install "streamwright-postgres[state]". For Spark jobs, installpip install "streamwright-postgres[spark]". - Spark connection options. libpq options become JDBC properties under their pgjdbc names (
connectTimeout,ApplicationName,gssEncMode). - Vendor arguments. libpq connection options pass through unchanged. On Spark, a connection key or
database.*argument the connector does not declare becomes a JDBC driver property (for examplefetchsize,queryTimeout).
See also
- DuckDB jobs: outputs.
- Starter template:
examples/sources/readers/postgres_demo.