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

安装和加载

postgres 扩展会在首次使用时从官方扩展存储库中自动加载。如果您想手动安装和加载它，请运行

INSTALL postgres;
LOAD postgres;

连接

要使 DuckDB 可以访问 PostgreSQL 数据库，请使用带有 postgres 或 postgres_scanner 类型的 ATTACH 命令。

要以读写模式连接到运行在 localhost 上的 PostgreSQL 实例的 public 模式（schema），请运行

ATTACH '' AS postgres_db (TYPE postgres);

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

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

默认情况下，所有模式（schema）都会被附加。当处理大型实例时，仅附加特定模式可能会很有用。这可以通过使用 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 是临时的。可以使用 CREATE PERSISTENT SECRET 命令持久化 secrets。持久化的 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);

警告：避免直接在连接字符串中包含凭据。如果发生连接错误，完整的连接字符串（包括您的凭据）可能会打印到终端输出中。为了更好的安全性，请使用 DuckDB 管理的 secrets 来存储凭据。

通过环境变量配置

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 数据库中创建表、导入数据以及进行其他修改。

这使您能够使用 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 二进制 wire 编码。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)');

设置

该扩展公开了以下配置参数。

架构（Schema）缓存

为了避免持续从 PostgreSQL 获取架构数据，DuckDB 会缓存架构信息（例如表名、列名等）。如果通过与 PostgreSQL 实例的其他连接对架构进行了更改（例如向表中添加了新列），则缓存的架构信息可能会过时。在这种情况下，可以执行 pg_clear_cache 函数来清除内部缓存。

CALL pg_clear_cache();

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