postgres 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.
Loading the Extension
In order to use the Postgres extension it must first be installed and loaded. This can be done using the following commands:
INSTALL postgres; LOAD postgres;
To make a Postgres database accessible to DuckDB, use the
-- load all data from "public" schema of the postgres instance running on localhost into the schema "main" CALL POSTGRES_ATTACH(''); -- attach the database with the given schema, loading tables from the source schema "public" into the target schema "abc" CALL postgres_attach('dbname=postgres user=postgres host=127.0.0.1', source_schema='public', sink_schema='abc');
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_schemathe name of a non-standard schema name in Postgres to get tables from. Default is
sink_schemathe schema name in DuckDB to create views. Default is
overwritewhether we should overwrite existing views in the target schema, default is
filter_pushdownwhether filter predicates that DuckDB derives from the query should be forwarded to Postgres, defaults to
The tables in the database are registered as views in DuckDB, you can list them as follows:
Then you can query those views normally using SQL.
Querying individual tables
If you prefer to not attach all tables, but just query a single table, that is possible using the
POSTGRES_SCAN 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
filter_pushdown use the
See the repo for the source code of the extension, or the official announcement for implementation details and background.