The postgres Extension

The postgres Extension: Querying PostgreSQL from DuckDB

The postgres extension attaches a running PostgreSQL 1,289 database as a DuckDB 61,228 catalog. DuckDB then reads its tables with PostgreSQL's binary COPY protocol, in parallel slices of the heap, and runs joins and aggregates in its own engine; postgres_query sends a query to PostgreSQL verbatim instead. This script reads OLTP vs OLAP's mart from l2-pg, asks PostgreSQL directly for a partition count, and copies the date dimension into booknest.duckdb:

483-postgres.sql: PostgreSQL's mart from inside DuckDBSQL

ATTACH 'host=localhost port=32543 dbname=booknest user=postgres password=booknest'
  AS pg (TYPE postgres, READ_ONLY);
.timer on
SELECT genre, sum(gross_amount) AS gross FROM pg.mart.sales
WHERE status <> 'cancelled' GROUP BY ALL ORDER BY gross DESC LIMIT 2;
SELECT * FROM postgres_query('pg', 'SELECT count(*) FROM mart.sales_m
                                    WHERE order_date >= ''2026-06-01''');
CREATE OR REPLACE TABLE dim_date AS FROM pg.mart.dim_date;

Output

│   genre    │  gross   │
│  varchar   │  double  │
├────────────┼──────────┤
│ Technology │ 934530.5 │
...
Run Time (s): real 0.527 user 0.043043 sys 0.057493
...
│  7546 │
...
Run Time (s): real 0.065 user 0.000000 sys 0.014083

The totals match DuckDB's own copy, but the type changed: mart.sales.gross_amount is a numeric with no declared precision (BookNest's Sales Mart), which DuckDB maps to DOUBLE. Declare precision on columns that leave the database, or cast. Pulling 137,944 rows took about half a second, so copy tables you query repeatedly (about 70 ms for the 730-row dim_date). Attached without READ_ONLY it also writes, making DuckDB a handy data mover. Keep passwords out of scripts with CREATE SECRET (TYPE postgres, ...) or the PG* environment variables.

MENU