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

postgres 扩展允许 DuckDB 直接从运行的 PostgreSQL 数据库实例中读取和写入数据。数据可以直接从底层的 PostgreSQL 数据库中查询。数据可以从 PostgreSQL 表加载到 DuckDB 表中，反之亦然。有关实现细节和背景，请参阅 官方公告。

安装和加载

postgres 扩展将在首次使用时从官方扩展仓库中自动加载。如果您希望手动安装并加载它，请运行：

INSTALL postgres;
LOAD postgres;

连接

为了使PostgreSQL数据库对DuckDB可访问，请使用ATTACH命令与POSTGRES或POSTGRES_SCANNER类型。

要连接到在本地主机上运行的PostgreSQL实例的public模式，并以读写模式运行，请执行以下操作：

ATTACH '' AS postgres_db (TYPE POSTGRES);

要以只读模式使用给定的参数连接到 PostgreSQL 实例，请运行：

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

默认情况下，所有模式都会被附加。在处理大型实例时，仅附加特定模式可能很有用。这可以通过使用SCHEMA命令来实现。

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

配置

ATTACH 命令接受输入，可以是 libpq 连接字符串 或 PostgreSQL URI。

以下是一些示例连接字符串和常用参数。完整的可用参数列表可以在PostgreSQL 文档中找到。

dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10
名称 描述 默认值
dbname 数据库名称 [user]
host 要连接的主机名称 localhost
hostaddr 主机IP地址 localhost
passfile 存储密码的文件名 ~/.pgpass
password PostgreSQL 密码 (空)
port 端口号 5432
user PostgreSQL 用户名 当前用户

一个示例URI是 postgresql://username@hostname/dbname。

通过Secrets配置

PostgreSQL 连接信息也可以通过 secrets 来指定。以下语法可用于创建密钥。

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

当调用ATTACH时，将使用来自secret的信息。我们可以将Postgres连接字符串留空，以使用存储在secret中的所有信息。

ATTACH '' AS postgres_db (TYPE POSTGRES);

我们可以使用Postgres连接字符串来覆盖个别选项。例如，要连接到不同的数据库，同时仍然使用相同的凭据，我们可以仅以下列方式覆盖数据库名称。

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

默认情况下，创建的密钥是临时的。可以使用CREATE PERSISTENT SECRET命令来持久化密钥。持久化的密钥可以在多个会话中使用。

管理多个秘密

命名的密钥可用于管理连接到多个Postgres数据库实例。密钥在创建时可以指定名称。

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

然后可以使用ATTACH中的SECRET参数显式引用秘密。

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

通过环境变量配置

PostgreSQL 连接信息也可以通过环境变量来指定。 这在生产环境中非常有用，因为连接信息由外部管理并通过环境传递。

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

然后，要连接，启动duckdb进程并运行：

ATTACH '' AS p (TYPE POSTGRES);

Usage

PostgreSQL 数据库中的表可以像普通的 DuckDB 表一样读取，但底层数据在查询时直接从 PostgreSQL 读取。

SHOW ALL TABLES;
名称
uuids 
SELECT * FROM uuids;
u
6d3d2541-710b-4bde-b3af-4711738636bf
NULL
00000000-0000-0000-0000-000000000001
ffffffff-ffff-ffff-ffff-ffffffffffff

可能需要将PostgreSQL数据库的副本创建在DuckDB中，以防止系统不断从PostgreSQL重新读取表，特别是对于大表。

数据可以使用标准SQL从PostgreSQL复制到DuckDB，例如：

CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

将数据写入PostgreSQL

除了从PostgreSQL读取数据外，该扩展还允许您创建表、将数据导入PostgreSQL，并使用标准SQL查询对PostgreSQL数据库进行其他修改。

这允许您使用DuckDB来，例如，将存储在PostgreSQL数据库中的数据导出到Parquet，或将数据从Parquet文件读取到PostgreSQL中。

以下是一个简短的示例，展示了如何在 PostgreSQL 中创建一个新表并将数据加载到其中。

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');

支持对PostgreSQL表的许多操作。所有这些操作都会直接修改PostgreSQL数据库，然后可以使用PostgreSQL读取后续操作的结果。 请注意，如果不希望进行修改，可以使用READ_ONLY属性运行ATTACH，这样可以防止对底层数据库进行修改。例如：

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

以下是支持的操作列表。

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 名称
42 DuckDB

COPY

您可以在 PostgreSQL 和 DuckDB 之间来回复制表格：

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

这些副本使用PostgreSQL二进制线编码。 DuckDB也可以使用这种编码将数据写入文件，然后您可以使用您选择的客户端将其加载到PostgreSQL中，如果您希望自己管理连接：

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

生成的文件将等同于使用DuckDB将文件复制到PostgreSQL，然后使用psql或其他客户端从PostgreSQL转储它：

DuckDB:

COPY postgres_db.tbl FROM 'data.parquet';

PostgreSQL:

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

你也可以使用COPY FROM DATABASE语句创建数据库的完整副本：

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;

交易 

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

这将返回：

i
42 
ROLLBACK;
SELECT * FROM postgres_db.tmp;

这将返回一个空表。

在PostgreSQL中运行SQL查询

The postgres_query 表函数

postgres_query 表函数允许您在附加的数据库中运行任意读取查询。postgres_query 接受要执行查询的附加 PostgreSQL 数据库的名称以及要执行的 SQL 查询。查询的结果将被返回。单引号字符串通过重复单引号两次来转义。

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

例如：

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
SELECT * FROM postgres_query('postgres_db', 'SELECT * FROM cars LIMIT 3');
品牌 型号 颜色
法拉利 Testarossa 红色
阿斯顿·马丁 DB2 蓝色
宾利 慕尚 灰色

The postgres_execute 函数

postgres_execute 函数允许在 PostgreSQL 中运行任意查询，包括更新数据库模式和内容的语句。

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

设置

扩展暴露了以下配置参数。

Name Description Default
pg_array_as_varchar 将PostgreSQL数组读取为varchar - 允许读取混合维度的数组 false
pg_connection_cache 是否使用连接缓存 true
pg_connection_limit 最大并发PostgreSQL连接数 64
pg_debug_show_queries 调试设置：将所有发送到PostgreSQL的查询打印到标准输出 false
pg_experimental_filter_pushdown 是否使用过滤器下推（目前为实验性功能） false
pg_pages_per_task 每个任务中的页面数量 1000
pg_use_binary_copy 是否使用BINARY复制来读取数据 true
pg_null_byte_replacement 当向Postgres写入NULL字节时，用给定的字符替换它们 NULL
pg_use_ctid_scan 是否使用表ctids进行并行扫描 true

Schema Cache

为了避免需要不断从PostgreSQL获取模式数据，DuckDB会缓存模式信息——如表名、列名等。如果通过不同的连接对PostgreSQL实例的模式进行了更改，例如向表中添加了新列，缓存的模式信息可能会过时。在这种情况下，可以执行函数pg_clear_cache来清除内部缓存。

CALL pg_clear_cache();

已弃用 旧的 postgres_attach 函数已被弃用。建议切换到新的 ATTACH 语法。

关于此页面