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

安装和加载

postgres 扩展将在首次使用时从官方扩展仓库透明地自动加载。如果您想手动安装和加载它，请运行

INSTALL postgres;
LOAD postgres;

连接

要使 PostgreSQL 数据库对 DuckDB 可访问，请使用 ATTACH 命令，并指定 postgres 或 postgres_scanner 类型。

要在读写模式下连接到 localhost 上运行的 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

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

通过 Secrets 配置

PostgreSQL 连接信息也可以通过secrets指定。可以使用以下语法创建 secret。

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

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

ATTACH '' AS postgres_db (TYPE postgres);

我们可以使用 PostgreSQL 连接字符串来覆盖单个选项。例如，要连接到不同的数据库但仍使用相同的凭据，我们可以通过以下方式只覆盖数据库名称。

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

默认情况下，创建的 secrets 是临时的。Secrets 可以使用CREATE PERSISTENT SECRET 命令持久化。持久化 secrets 可以在不同的会话中重复使用。

管理多个 Secrets

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

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

然后可以在 ATTACH 中使用 SECRET 参数显式引用该 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);

用法

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

SHOW ALL TABLES;

SELECT * FROM uuids;

可能需要将 PostgreSQL 数据库复制到 DuckDB 中，以防止系统不断地从 PostgreSQL 重新读取表，特别是对于大型表。

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

CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

将数据写入 PostgreSQL

除了从 PostgreSQL 读取数据外，该扩展还允许您使用标准 SQL 查询在 PostgreSQL 中创建表、将数据导入 PostgreSQL 并对 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;

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;

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;

这返回

ROLLBACK;
SELECT * FROM postgres_db.tmp;

这返回一个空表。

在 PostgreSQL 中运行 SQL 查询

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

postgres_execute 函数

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

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

设置

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

模式缓存

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

CALL pg_clear_cache();

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