The postgresscanner extension allows DuckDB to directly read data from a running Postgres instance. The data can be queried directly from the underlying Postgres tables, or read into DuckDB tables.

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

CALL POSTGRES_ATTACH('');

POSTGRES_ATTACH takes a single required string parameter, which is the libpq connection string. For example you can pass 'dbname=postgresscanner' to select a different database name. In the simplest case, the parameter is just ''. There are three additional named parameters:

• source_schema the name of a non-standard schema name in Postgres to get tables from. Default is public.
• overwrite whether we should overwrite existing views in the target schema, default is false.
• filter_pushdown whether filter predicates that DuckDB derives from the query should be forwarded to Postgres, defaults to false.

The tables in the database are registered as views in DuckDB, you can list them with

PRAGMA show_tables;

Then you can query those views normally using SQL.

If you prefer to not attach all tables, but just query a single table, that is possible using the POSTGRES_SCAN table-producing function, e.g.

SELECT * FROM POSTGRES_SCAN('', 'public', 'mytable');

POSTGRES_SCAN takes three string parameters, the libpq connection string (see above), a Postgres schema name and a table name. The schema name is often public.

To build, type

make

To run, run the bundled duckdb shell:

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

Then, load the Postgres extension like so:

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

Copyright 2022 DuckDB Labs BV [[email protected]].

This project is licensed under the GNU General Public License (LICENSE-GPL). Alternative licensing options are available from DuckDB Labs.