Search Shortcut cmd + k | ctrl + k

- Installation
- Documentation

- Getting Started
- Connect

- Overview
- Concurrency

- Data Import

- Overview
- Data Sources
- CSV Files

- Overview
- Auto Detection
- Reading Faulty CSV Files
- Tips

- JSON Files

- Overview
- Creating JSON
- Loading JSON
- Writing JSON
- JSON Type
- JSON Functions
- Format Settings
- Installing and Loading
- SQL to / from JSON
- Caveats

- Multiple Files

- Overview
- Combining Schemas

- Parquet Files

- Overview
- Metadata
- Encryption
- Tips

- Partitioning

- Hive Partitioning
- Partitioned Writes

- Appender
- INSERT Statements

- Client APIs

- Overview
- C

- Overview
- Startup
- Configuration
- Query
- Data Chunks
- Vectors
- Values
- Types
- Prepared Statements
- Appender
- Table Functions
- Replacement Scans
- API Reference

- C++
- CLI

- Overview
- Arguments
- Dot Commands
- Output Formats
- Editing
- Autocomplete
- Syntax Highlighting

- Dart
- Go
- Java
- Julia
- Node.js (Neo)

- Overview

- Node.js

- Overview
- API Reference

- Python

- Overview
- Data Ingestion
- Conversion between DuckDB and Python
- DB API
- Relational API
- Function API
- Types API
- Expression API
- Spark API
- API Reference
- Known Python Issues

- R
- Rust
- Swift
- Wasm

- Overview
- Instantiation
- Data Ingestion
- Query
- Extensions

- ADBC
- ODBC

- Overview
- Linux Setup
- Windows Setup
- macOS Setup
- Configuration

- SQL

- Introduction
- Statements

- Overview
- ANALYZE
- ALTER TABLE
- ALTER VIEW
- ATTACH and DETACH
- CALL
- CHECKPOINT
- COMMENT ON
- COPY
- CREATE INDEX
- CREATE MACRO
- CREATE SCHEMA
- CREATE SECRET
- CREATE SEQUENCE
- CREATE TABLE
- CREATE VIEW
- CREATE TYPE
- DELETE
- DESCRIBE
- DROP
- EXPORT and IMPORT DATABASE
- INSERT
- LOAD / INSTALL
- PIVOT
- Profiling
- SELECT
- SET / RESET
- SET VARIABLE
- SUMMARIZE
- Transaction Management
- UNPIVOT
- UPDATE
- USE
- VACUUM

- Query Syntax

- SELECT
- FROM and JOIN
- WHERE
- GROUP BY
- GROUPING SETS
- HAVING
- ORDER BY
- LIMIT and OFFSET
- SAMPLE
- Unnesting
- WITH
- WINDOW
- QUALIFY
- VALUES
- FILTER
- Set Operations
- Prepared Statements

- Data Types

- Overview
- Array
- Bitstring
- Blob
- Boolean
- Date
- Enum
- Interval
- List
- Literal Types
- Map
- NULL Values
- Numeric
- Struct
- Text
- Time
- Timestamp
- Time Zones
- Union
- Typecasting

- Expressions

- Overview
- CASE Statement
- Casting
- Collations
- Comparisons
- IN Operator
- Logical Operators
- Star Expression
- Subqueries

- Functions

- Overview
- Aggregate Functions
- Array Functions
- Bitstring Functions
- Blob Functions
- Date Format Functions
- Date Functions
- Date Part Functions
- Enum Functions
- Interval Functions
- Lambda Functions
- List Functions
- Map Functions
- Nested Functions
- Numeric Functions
- Pattern Matching
- Regular Expressions
- Struct Functions
- Text Functions
- Time Functions
- Timestamp Functions
- Timestamp with Time Zone Functions
- Union Functions
- Utility Functions
- Window Functions

- Constraints
- Indexes
- Meta Queries

- Information Schema
- Metadata Functions

- DuckDB's SQL Dialect

- Overview
- Indexing
- Friendly SQL
- Keywords and Identifiers
- Order Preservation
- PostgreSQL Compatibility

- Samples

- Configuration

- Overview
- Pragmas
- Secrets Manager

- Extensions

- Overview
- Core Extensions
- Community Extensions
- Working with Extensions
- Versioning of Extensions
- Arrow
- AutoComplete
- AWS
- Azure
- Delta
- Excel
- Full Text Search
- httpfs (HTTP and S3)

- Overview
- HTTP(S) Support
- Hugging Face
- S3 API Support
- Legacy Authentication Scheme for S3 API

- Iceberg
- ICU
- inet
- jemalloc
- MySQL
- PostgreSQL
- Spatial

- Overview
- Function Reference
- R-Tree Indexes
- GDAL Integration

- SQLite
- Substrait
- TPC-DS
- TPC-H
- VSS

- Guides

- Overview
- Data Viewers

- Tableau
- CLI Charting with YouPlot

- Database Integration

- Overview
- MySQL Import
- PostgreSQL Import
- SQLite Import

- File Formats

- Overview
- CSV Import
- CSV Export
- Directly Reading Files
- Excel Import
- Excel Export
- JSON Import
- JSON Export
- Parquet Import
- Parquet Export
- Querying Parquet Files

- Network and Cloud Storage

- Overview
- HTTP Parquet Import
- S3 Parquet Import
- S3 Parquet Export
- S3 Iceberg Import
- S3 Express One
- GCS Import
- Cloudflare R2 Import
- DuckDB over HTTPS / S3

- Meta Queries

- Describe Table
- EXPLAIN: Inspect Query Plans
- EXPLAIN ANALYZE: Profile Queries
- List Tables
- Summarize
- DuckDB Environment

- ODBC

- ODBC Guide

- Performance

- Overview
- Environment
- Import
- Schema
- Indexing
- Join Operations
- File Formats
- How to Tune Workloads
- My Workload Is Slow
- Benchmarks

- Python

- Installation
- Executing SQL
- Jupyter Notebooks
- SQL on Pandas
- Import from Pandas
- Export to Pandas
- Import from Numpy
- Export to Numpy
- SQL on Arrow
- Import from Arrow
- Export to Arrow
- Relational API on Pandas
- Multiple Python Threads
- Integration with Ibis
- Integration with Polars
- Using fsspec Filesystems

- SQL Editors

- DBeaver SQL IDE

- SQL Features

- AsOf Join
- Full-Text Search
- query and query_table Functions

- Snippets

- Create Synthetic Data
- Sharing Macros

- Glossary of Terms
- Browse Offline

- Operations Manual

- Overview
- Limits
- Non-Deterministic Behavior
- Embedding DuckDB
- DuckDB's Footprint

- Files Created by DuckDB
- Gitignore for DuckDB
- Reclaiming Space

- Securing DuckDB

- Overview
- Securing Extensions

- Development

- DuckDB Repositories
- Testing

- Overview
- sqllogictest Introduction
- Writing Tests
- Debugging
- Result Verification
- Persistent Testing
- Loops
- Multiple Connections
- Catch

- Profiling
- Release Calendar
- Building

- Overview
- Build Instructions
- Build Configuration
- Building Extensions
- Platforms
- Troubleshooting

- Benchmark Suite

- Internals

- Overview
- Storage Versions and Format
- Execution Format
- Pivot

- Sitemap
- Why DuckDB
- Media
- FAQ
- Code of Conduct
- Live Demo

PostgreSQL Extension

The `postgres` extension allows DuckDB to directly read and write data from a running PostgreSQL database instance. The data can be queried directly from the underlying PostgreSQL database. Data can be loaded from PostgreSQL tables into DuckDB tables, or vice versa. See the official announcement for implementation details and background.

## Installing and Loading

The `postgres` extension will be transparently autoloaded on first use from the official extension repository. If you would like to install and load it manually, run:

    INSTALL postgres;
    LOAD postgres;

## Connecting

To make a PostgreSQL database accessible to DuckDB, use the `ATTACH` command with the `POSTGRES` or `POSTGRES_SCANNER` type.

To connect to the `public` schema of the PostgreSQL instance running on localhost in read-write mode, run:

    ATTACH '' AS postgres_db (TYPE POSTGRES);

To connect to the PostgreSQL instance with the given parameters in read-only mode, run:

    ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, READ_ONLY);

By default, all schemas are attached. When working with large instances, it can be useful to only attach a specific schema. This can be accomplished using the `SCHEMA` command.

    ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, SCHEMA 'public');

### Configuration

The `ATTACH` command takes as input either a `libpq` connection string or a PostgreSQL URI.

Below are some example connection strings and commonly used parameters. A full list of available parameters can be found in the PostgreSQL documentation.

    dbname=postgresscanner
    host=localhost port=5432 dbname=mydb connect_timeout=10

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

An example URI is `postgresql://username@hostname/dbname`.

### Configuring via Secrets

PostgreSQL connection information can also be specified with secrets. The following syntax can be used to create a secret.

    CREATE SECRET (
        TYPE POSTGRES,
        HOST '127.0.0.1',
        PORT 5432,
        DATABASE postgres,
        USER 'postgres',
        PASSWORD ''
    );

The information from the secret will be used when `ATTACH` is called. We can leave the Postgres connection string empty to use all of the information stored in the secret.

    ATTACH '' AS postgres_db (TYPE POSTGRES);

We can use the Postgres connection string to override individual options. For example, to connect to a different database while still using the same credentials, we can override only the database name in the following manner.

    ATTACH 'dbname=my_other_db' AS postgres_db (TYPE POSTGRES);

By default, created secrets are temporary. Secrets can be persisted using the `CREATE PERSISTENT SECRET` command. Persistent secrets can be used across sessions.

#### Managing Multiple Secrets

Named secrets can be used to manage connections to multiple Postgres database instances. Secrets can be given a name upon creation.

    CREATE SECRET postgres_secret_one (
        TYPE POSTGRES,
        HOST '127.0.0.1',
        PORT 5432,
        DATABASE postgres,
        USER 'postgres',
        PASSWORD ''
    );

The secret can then be explicitly referenced using the `SECRET` parameter in the `ATTACH`.

    ATTACH '' AS postgres_db_one (TYPE POSTGRES, SECRET postgres_secret_one);

### Configuring via Environment Variables

PostgreSQL connection information can also be specified with environment variables. This can be useful in a production environment where the connection information is managed externally and passed in to the environment.

    export PGPASSWORD="secret"
    export PGHOST=localhost
    export PGUSER=owner
    export PGDATABASE=mydatabase

Then, to connect, start the `duckdb` process and run:

    ATTACH '' AS p (TYPE POSTGRES);

## Usage

The tables in the PostgreSQL database can be read as if they were normal DuckDB tables, but the underlying data is read directly from PostgreSQL at query time.

    SHOW ALL TABLES;

name
uuids

    SELECT * FROM uuids;

u
6d3d2541-710b-4bde-b3af-4711738636bf
NULL
00000000-0000-0000-0000-000000000001
ffffffff-ffff-ffff-ffff-ffffffffffff

It might be desirable to create a copy of the PostgreSQL databases in DuckDB to prevent the system from re-reading the tables from PostgreSQL continuously, particularly for large tables.

Data can be copied over from PostgreSQL to DuckDB using standard SQL, for example:

    CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

## Writing Data to PostgreSQL

In addition to reading data from PostgreSQL, the extension allows you to create tables, ingest data into PostgreSQL and make other modifications to a PostgreSQL database using standard SQL queries.

This allows you to use DuckDB to, for example, export data that is stored in a PostgreSQL database to Parquet, or read data from a Parquet file into PostgreSQL.

Below is a brief example of how to create a new table in PostgreSQL and load data into it.

    ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
    CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);
    INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

Many operations on PostgreSQL tables are supported. All these operations directly modify the PostgreSQL database, and the result of subsequent operations can then be read using PostgreSQL. Note that if modifications are not desired, `ATTACH` can be run with the `READ_ONLY` property which prevents making modifications to the underlying database. For example:

    ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES, READ_ONLY);

Below is a list of supported operations.

### `CREATE TABLE`

    CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);

### `INSERT INTO`

    INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

### `SELECT`

    SELECT * FROM postgres_db.tbl;

id | name
--- | ---
42 | DuckDB

### `COPY`

You can copy tables back and forth between PostgreSQL and DuckDB:

    COPY postgres_db.tbl TO 'data.parquet';
    COPY postgres_db.tbl FROM 'data.parquet';

These copies use PostgreSQL binary wire encoding. DuckDB can also write data using this encoding to a file which you can then load into PostgreSQL using a client of your choosing if you would like to do your own connection management:

    COPY 'data.parquet' TO 'pg.bin' WITH (FORMAT POSTGRES_BINARY);

The file produced will be the equivalent of copying the file to PostgreSQL using DuckDB and then dumping it from PostgreSQL using `psql` or another client:

DuckDB:

    COPY postgres_db.tbl FROM 'data.parquet';

PostgreSQL:

    \copy tbl TO 'data.bin' WITH (FORMAT BINARY);

You may also create a full copy of the database using the `COPY FROM DATABASE` statement:

    COPY FROM DATABASE postgres_db TO my_duckdb_db;

### `UPDATE`

    UPDATE postgres_db.tbl
    SET name = 'Woohoo'
    WHERE id = 42;

### `DELETE`

    DELETE FROM postgres_db.tbl
    WHERE id = 42;

### `ALTER TABLE`

    ALTER TABLE postgres_db.tbl
    ADD COLUMN k INTEGER;

### `DROP TABLE`

    DROP TABLE postgres_db.tbl;

### `CREATE VIEW`

    CREATE VIEW postgres_db.v1 AS SELECT 42;

### `CREATE SCHEMA` / `DROP SCHEMA`

    CREATE SCHEMA postgres_db.s1;
    CREATE TABLE postgres_db.s1.integers (i INTEGER);
    INSERT INTO postgres_db.s1.integers VALUES (42);
    SELECT * FROM postgres_db.s1.integers;

i
42

    DROP SCHEMA postgres_db.s1;

## `DETACH`

    DETACH postgres_db;

### Transactions

    CREATE TABLE postgres_db.tmp (i INTEGER);
    BEGIN;
    INSERT INTO postgres_db.tmp VALUES (42);
    SELECT * FROM postgres_db.tmp;

This returns:

i
42

    ROLLBACK;
    SELECT * FROM postgres_db.tmp;

This returns an empty table.

## Running SQL Queries in PostgreSQL

### The `postgres_query` Table Function

The `postgres_query` table function allows you to run arbitrary read queries within an attached database. `postgres_query` takes the name of the attached PostgreSQL database to execute the query in, as well as the SQL query to execute. The result of the query is returned. Single-quote strings are escaped by repeating the single quote twice.

    postgres_query(attached_database::VARCHAR, query::VARCHAR)

For example:

    ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
    SELECT * FROM postgres_query('postgres_db', 'SELECT * FROM cars LIMIT 3');

brand | model | color
--- | --- | ---
Ferrari | Testarossa | red
Aston Martin | DB2 | blue
Bentley | Mulsanne | gray

### The `postgres_execute` Function

The `postgres_execute` function allows running arbitrary queries within PostgreSQL, including statements that update the schema and content of the database.

    ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
    CALL postgres_execute('postgres_db', 'CREATE TABLE my_table (i INTEGER)');

## Settings

The extension exposes the following configuration parameters.

Name | Description | Default
--- | --- | ---
`pg_array_as_varchar` | Read PostgreSQL arrays as varchar - enables reading mixed dimensional arrays | `false`
`pg_connection_cache` | Whether or not to use the connection cache | `true`
`pg_connection_limit` | The maximum amount of concurrent PostgreSQL connections | `64`
`pg_debug_show_queries` | DEBUG SETTING: print all queries sent to PostgreSQL to stdout | `false`
`pg_experimental_filter_pushdown` | Whether or not to use filter pushdown (currently experimental) | `false`
`pg_pages_per_task` | The amount of pages per task | `1000`
`pg_use_binary_copy` | Whether or not to use BINARY copy to read data | `true`
`pg_null_byte_replacement` | When writing NULL bytes to Postgres, replace them with the given character | `NULL`
`pg_use_ctid_scan` | Whether or not to parallelize scanning using table ctids | `true`

## Schema Cache

To avoid having to continuously fetch schema data from PostgreSQL, DuckDB keeps schema information – such as the names of tables, their columns, etc. – cached. If changes are made to the schema through a different connection to the PostgreSQL instance, such as new columns being added to a table, the cached schema information might be outdated. In this case, the function `pg_clear_cache` can be executed to clear the internal caches.

    CALL pg_clear_cache();

> Deprecated The old `postgres_attach` function is deprecated. It is recommended to switch over to the new `ATTACH` syntax.

##### About this page

- Visit the related GitHub repository
- Report content issue
- Edit this page on GitHub