DuckDB Postgres extension

The Postgres extension allows DuckDB to directly read and write data from a running Postgres database instance. The data can be queried directly from the underlying Postgres database. Data can be loaded from Postgres tables into DuckDB tables, or vice versa.

Reading Data from Postgres

To make a Postgres database accessible to DuckDB use the ATTACH command:

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);

The ATTACH command takes as input a libpq connection string - which is a set of key=value pairs separated by spaces. Below are some example connection strings and commonly used parameters. A full list of available parameters can be found in the Postgres documentation.

dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10

Name	Description	Default
host	Name of host to connect to	localhost
hostaddr	Host IP address	localhost
port	Port Number	5432
user	Postgres User Name	[OS user name]
password	Postgres Password
dbname	Database Name	[user]
passfile	Name of file passwords are stored in	~/.pgpass

The tables in the file can be read as if they were normal DuckDB tables, but the underlying data is read directly from Postgres at query time.

D SHOW ALL TABLES;
┌───────────────────────────────────────┐
│                 name                  │
│                varchar                │
├───────────────────────────────────────┤
│ uuids                                 │
└───────────────────────────────────────┘
D SELECT * FROM postgres_db.uuids;
┌──────────────────────────────────────┐
│                  u                   │
│                 uuid                 │
├──────────────────────────────────────┤
│ 6d3d2541-710b-4bde-b3af-4711738636bf │
│ NULL                                 │
│ 00000000-0000-0000-0000-000000000001 │
│ ffffffff-ffff-ffff-ffff-ffffffffffff │
└──────────────────────────────────────┘

For more information on how to use the connector, refer to the Postgres documentation on the website.

Building & Loading the Extension

The DuckDB submodule must be initialized prior to building.

git submodule init
git pull --recurse-submodules

To build, type

make

To run, run the bundled duckdb shell:

 ./build/release/duckdb -unsigned  # allow unsigned extensions

Then, load the Postgres extension like so:

LOAD 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension';

Testing

Requires a running Postgres instance reachable via libpq defaults (e.g. psql works without extra flags). Create the test database schema:

PGHOST=localhost PGPORT=5432 PGUSER=<user> PGPASSWORD=<password> ./create-postgres-tables.sh

The extension is loaded from ~/.duckdb/extensions/<version>/<platform>/, copied there on first load and not overwritten on reruns. Create a symlink so the latest build is always used:

DUCKDB_VERSION=$(ls build/reldebug/repository/)
DUCKDB_PLATFORM=$(ls build/reldebug/repository/${DUCKDB_VERSION}/)
ln -sf "$(pwd)/build/reldebug/extension/postgres_scanner/postgres_scanner.duckdb_extension" \
  ~/.duckdb/extensions/${DUCKDB_VERSION}/${DUCKDB_PLATFORM}/postgres_scanner.duckdb_extension

Run all tests:

POSTGRES_TEST_DATABASE_AVAILABLE=1 LOCAL_EXTENSION_REPO=./build/release/repository \
  ./build/release/test/unittest --autoloading available

Run a single test:

POSTGRES_TEST_DATABASE_AVAILABLE=1 LOCAL_EXTENSION_REPO=./build/release/repository \
  ./build/release/test/unittest --autoloading available test/sql/storage/attach_simple.test

Note: debug and release launchers and extensions should not be mixed — use build/reldebug consistently if building with make reldebug.