⌘+k ctrl+k
1.1.3 (stable)
Search Shortcut cmd + k | ctrl + k
PostgreSQL Import

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.

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

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 PostgreSQL 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.

About this page