Using DuckDB with PostgreSQL
DuckDB is an embedded SQL database known for high-performance analytical processing. Unlike PostgreSQL, which is a full-featured relational database management system, DuckDB is optimized for analytical queries in local environments. Integrating DuckDB with PostgreSQL enables you to leverage DuckDB’s analytical strengths while using PostgreSQL for data storage, transaction management, and complex relational tasks.
Syntax for Using DuckDB with PostgreSQL
DuckDB can access PostgreSQL data sources via its Foreign Data Wrapper (FDW). The FDW allows you to query data from PostgreSQL databases directly within DuckDB, using SQL statements as if the data were local.
This setup often requires installing both databases and configuring the FDW within DuckDB to establish a connection to the PostgreSQL server.
Setting Up DuckDB with PostgreSQL
Step 1: Install DuckDB and PostgreSQL
Install both databases if they’re not already installed.
# Install DuckDB (Linux example) pip install duckdb # Install PostgreSQL (Linux example) sudo apt-get install postgresql postgresql-contrib
Step 2: Connect DuckDB to PostgreSQL with FDW
To access PostgreSQL data from within DuckDB, configure the FDW by loading the DuckDB extension:
-- Load the DuckDB PostgreSQL extension INSTALL postgres_scanner; LOAD postgres_scanner;
Step 3: Query PostgreSQL Data in DuckDB
After loading the PostgreSQL extension, DuckDB allows you to query tables from a PostgreSQL database.
-- Query PostgreSQL data from DuckDB
SELECT * FROM postgres_scan(
'host=localhost dbname=mydatabase user=myuser password=mypassword',
'public',
'my_table'
);
Example Workflow: DuckDB Querying PostgreSQL Data
1. Install PostgreSQL Scanner Extension
Use the INSTALL postgres_scanner; and LOAD postgres_scanner; commands to enable PostgreSQL connections.
2. Connect to PostgreSQL and Query Data
Using postgres_scan, you can define the connection string, schema, and table name. This method retrieves data directly from PostgreSQL and processes it within DuckDB.
Code:
-- Load PostgreSQL data into DuckDB
SELECT * FROM postgres_scan(
'host=127.0.0.1 dbname=test_db user=test_user password=test_pass',
'public',
'employees'
);
3. Analyze the Retrieved Data in DuckDB
DuckDB provides high-speed data analysis capabilities, so you can perform complex analytical queries on PostgreSQL data efficiently:
Code:
-- Analytical query example
SELECT department, AVG(salary) AS avg_salary
FROM postgres_scan(
'host=127.0.0.1 dbname=test_db user=test_user password=test_pass',
'public',
'employees'
)
GROUP BY department;
Explanation of Key Steps and Benefits
By combining PostgreSQL and DuckDB, users can store and manage structured data in PostgreSQL while leveraging DuckDB for high-performance analysis, benefiting from the strengths of each system.
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4