To run a query directly on a running PostgreSQL database, the postgres extension is required.

Installation and Loading

The extension can be installed using the INSTALL SQL command. This only needs to be run once.

INSTALL postgres; 

To load the postgres extension for usage, use the LOAD SQL command:

LOAD postgres; 

Usage

After the postgres extension is installed, tables can be queried from PostgreSQL using the postgres_scan function:

-- scan the table "mytable" from the schema "public" in the database "mydb" SELECT * FROM postgres_scan('host=localhost port=5432 dbname=mydb', 'public', 'mytable'); 

The first parameter to the postgres_scan function is the PostgreSQL connection string, a list of connection arguments provided in {key}={value} format. Below is a list of valid arguments.

Alternatively, the entire database can be attached using the ATTACH command. This allows you to query all tables stored within the PostgreSQL database as if it was a regular database.

-- Attach the Postgres database using the given connection string ATTACH 'host=localhost port=5432 dbname=mydb' AS test (TYPE postgres); -- The table "tbl_name" can now be queried as if it is a regular table SELECT * FROM test.tbl_name; -- Switch the active database to "test" USE test; -- List all tables in the file SHOW TABLES; 

For more information see the PostgreSQL extension documentation.