跳转到主内容
跳转到主内容

pg_clickhouse 参考文档

说明​

pg_clickhouse 是一个 PostgreSQL 扩展，可在 ClickHouse 数据库上远程执行查询，并包含 [外部数据包装器]。它支持 PostgreSQL 13 及以上版本，以及 ClickHouse 23 及以上版本。

快速开始​

体验 pg_clickhouse 最简单的方式是使用 Docker image，其中包含 标准的 PostgreSQL Docker 镜像，以及 pg_clickhouse 和 [re2][re2 扩展] 扩展：

docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
       -d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse psql -U postgres

请参阅教程，了解如何开始导入 ClickHouse 表并 下推查询。

用法​

CREATE EXTENSION pg_clickhouse;
CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');
CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'default');
CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA taxi FROM SERVER taxi_srv INTO taxi;

版本策略​

pg_clickhouse 的公开发布版本遵循[语义化版本控制]。

  • API 发生变更时，主版本号递增
  • 进行向后兼容的 SQL 变更时，次版本号递增
  • 仅有 binary 变更时，补丁版本号递增

安装后，PostgreSQL 会跟踪两种版本：

  • 库版本 (在 PostgreSQL 18 及更高版本中由 PG_MODULE_MAGIC 定义) 包含完整的语义化版本，可在 pgch_version() 函数的输出或 Postgres 的 pg_get_loaded_modules() 函数输出中查看。
  • 扩展版本 (在控制文件中定义) 仅包含主版本号和次版本号，可在 pg_catalog.pg_extension 表、pg_available_extension_versions() 函数的输出以及 \dx pg_clickhouse 中查看。

这实际上意味着，补丁版本号递增的发布版本 (例如 从 v0.1.0 升级到 v0.1.1) 会使所有已加载 v0.1 的数据库受益， 且无需运行 ALTER EXTENSION 即可获得升级带来的改进。

另一方面，次版本号或主版本号递增的发布版本 会附带 SQL 升级脚本，所有包含该扩展的现有数据库 都必须运行 ALTER EXTENSION pg_clickhouse UPDATE 才能获得 升级带来的改进。

DDL SQL 参考​

以下 SQL DDL 表达式适用于 pg_clickhouse。

CREATE EXTENSION​

使用 CREATE EXTENSION 向数据库添加 pg_clickhouse：

CREATE EXTENSION pg_clickhouse;

使用 WITH SCHEMA 将其安装到指定的 schema 中 (推荐) ：

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION​

使用 ALTER EXTENSION 修改 pg_clickhouse。示例：

  • 安装 pg_clickhouse 的新版本后，使用 UPDATE 子句：

    ALTER EXTENSION pg_clickhouse UPDATE;

  • 使用 SET SCHEMA 将该扩展移动到新的 schema：

    CREATE SCHEMA ch;
ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION​

使用 DROP EXTENSION 从数据库中删除 pg_clickhouse 扩展：

DROP EXTENSION pg_clickhouse;

如果有任何对象依赖于 pg_clickhouse，此命令会失败。使用 CASCADE 子句可将它们一并删除：

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER​

使用 CREATE SERVER 创建连接到 ClickHouse 服务器的 外部服务器。示例：

CREATE SERVER taxi_srv FOREIGN DATA WRAPPER clickhouse_fdw
       OPTIONS(driver 'binary', host 'localhost', dbname 'taxi');

支持的选项如下：

  • driver：要使用的 ClickHouse 连接驱动，可以是 "binary" 或 "http"。必填。
  • dbname：建立连接时要使用的 ClickHouse 数据库。默认为 "default"。
  • fetch_size：HTTP 流式传输的近似批次大小 (以字节为单位) 。批次 按行边界拆分。默认为 50000000 (50 MB) 。0 会禁用 流式传输并缓冲整个响应。foreign table 可以覆盖此 值。
  • host：ClickHouse服务器 的主机名。默认为 "localhost"；
  • port：连接到 ClickHouse服务器 时使用的端口。默认值 如下：
    • 如果 driver 为 "binary" 且 host 是 ClickHouse Cloud 主机，则为 9440
    • 如果 driver 为 "binary" 且 host 不是 ClickHouse Cloud 主机，则为 9004
    • 如果 driver 为 "http" 且 host 是 ClickHouse Cloud 主机，则为 8443
    • 如果 driver 为 "http" 且 host 不是 ClickHouse Cloud 主机，则为 8123

ALTER SERVER​

使用 ALTER SERVER 更改外部服务器。示例：

ALTER SERVER taxi_srv OPTIONS (SET driver 'http');

这些选项与 CREATE SERVER 中的相同。

DROP SERVER​

可使用 DROP SERVER 删除外部服务器：

DROP SERVER taxi_srv;

如果有任何其他对象依赖于该服务器，此命令将失败。使用 CASCADE 可 一并删除这些依赖项：

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING​

使用 CREATE USER MAPPING 将 PostgreSQL 用户映射为 ClickHouse 用户。例如，要在 通过 taxi_srv 外部服务器 连接时，将当前 PostgreSQL 用户映射到远程 ClickHouse 用户：

CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (user 'demo');

支持的选项如下：

  • user：ClickHouse 用户名。默认为 "default"。
  • password：ClickHouse 用户的密码。

ALTER USER MAPPING​

使用 ALTER USER MAPPING 修改用户映射的定义：

ALTER USER MAPPING FOR CURRENT_USER SERVER taxi_srv
       OPTIONS (SET user 'default');

这些选项与 CREATE USER MAPPING 中的选项相同。

DROP USER MAPPING​

使用 DROP USER MAPPING 移除用户映射：

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA​

使用 IMPORT FOREIGN SCHEMA 可将 ClickHouse 数据库中定义的所有表作为外部表导入到 PostgreSQL schema 中：

CREATE SCHEMA taxi;
IMPORT FOREIGN SCHEMA demo FROM SERVER taxi_srv INTO taxi;

使用 LIMIT TO 将导入范围限制为特定的表：

IMPORT FOREIGN SCHEMA demo LIMIT TO (trips) FROM SERVER taxi_srv INTO taxi;

使用 EXCEPT 排除相关表：

IMPORT FOREIGN SCHEMA demo EXCEPT (users) FROM SERVER taxi_srv INTO taxi;

pg_clickhouse 会拉取指定 ClickHouse 数据库 (上述示例中的 "demo") 中所有表的列表，拉取每个表的列定义， 并执行 CREATE FOREIGN TABLE 命令来创建 外部表。列将使用支持的数据 类型定义，并在可检测的情况下，使用 CREATE FOREIGN TABLE 支持的选项。

导入的标识符大小写保留

IMPORT FOREIGN SCHEMA 会对其导入的表名和列名执行 quote_identifier()， 这会为包含大写字母或空格的标识符添加双引号。 因此，此类表名和列名在 PostgreSQL 查询中必须用双引号括起来。 完全为小写且不包含空格字符的名称则无需加引号。

例如，给定以下 ClickHouse 表：

 CREATE OR REPLACE TABLE test
 (
     id UInt64,
     Name TEXT,
     updatedAt DateTime DEFAULT now()
 )
 ENGINE = MergeTree
 ORDER BY id;

IMPORT FOREIGN SCHEMA 会创建以下外部表：

 CREATE TABLE test
 (
     id          BIGINT      NOT NULL,
     "Name"      TEXT        NOT NULL,
     "updatedAt" TIMESTAMPTZ NOT NULL
 );

因此，查询必须正确使用引号，例如：

 SELECT id, "Name", "updatedAt" FROM test;

如果要创建名称不同或名称全部为小写 (因此 不区分大小写) 的对象，请使用 CREATE FOREIGN TABLE。

CREATE FOREIGN TABLE​

使用 CREATE FOREIGN TABLE 创建可查询 ClickHouse 数据库中数据的外部表：

CREATE FOREIGN TABLE acts (
    user_id    bigint NOT NULL,
    page_views int,
    duration   smallint,
    sign       smallint
) SERVER taxi_srv OPTIONS(
    table_name 'acts'
    engine 'CollapsingMergeTree'
);

支持的表选项包括：

  • database：远程数据库的名称。默认为为外部服务器 定义的数据库。
  • fetch_size：用于 HTTP 流式传输的近似批次大小 (以字节为单位) 。会覆盖 服务器级别的 fetch_size。默认为 50000000 (50 MB) 。0 会禁用 流式传输，并缓冲完整响应。
  • table_name：远程表的名称。默认为为外部表 指定的名称。
  • engine：ClickHouse 表使用的[表引擎]。对于 CollapsingMergeTree() 和 AggregatingMergeTree()，pg_clickhouse 会自动将参数应用于 在该表上执行的函数表达式。

对于每一列，请使用与远程 ClickHouse 数据类型相对应的数据类型。支持的列选项包括：

  • column_name：ClickHouse 侧列的名称，在反解析查询和 插入语句时，会优先使用它而不是 PostgreSQL 属性名。 这对于将未加引号的小写 PostgreSQL 列名映射到 区分大小写的 ClickHouse 列很有用，例如：

    CREATE FOREIGN TABLE hits (
    watchid    bigint   OPTIONS(column_name 'WatchID'),
    javaenable smallint OPTIONS(column_name 'JavaEnable'),
    title      text     OPTIONS(column_name 'Title')
) SERVER taxi_srv OPTIONS(table_name 'hits');

  • AggregateFunction：应用于 AggregateFunction Type 列的聚合函数名称。将数据类型映射为传递给该函数的 ClickHouse 类型， 并通过相应的列选项指定聚合函数名称， pg_clickhouse 会自动在计算该列的聚合函数后附加 Merge。

    CREATE FOREIGN TABLE test (
    column1 bigint  OPTIONS(AggregateFunction 'uniq'),
    column2 integer OPTIONS(AggregateFunction 'anyIf'),
    column3 bigint  OPTIONS(AggregateFunction 'quantiles(0.5, 0.9)')
) SERVER clickhouse_srv;

  • SimpleAggregateFunction：应用于 SimpleAggregateFunction Type 列的聚合函数名称。将数据类型映射为 传递给该函数的 ClickHouse 类型，并通过相应的 列选项指定聚合函数名称。

ALTER FOREIGN TABLE​

使用 ALTER FOREIGN TABLE 修改外部表的定义：

ALTER TABLE table ALTER COLUMN b OPTIONS (SET AggregateFunction 'count');

支持的表和列选项与 CREATE FOREIGN TABLE 中的相同。

DROP FOREIGN TABLE​

使用 DROP FOREIGN TABLE 删除外部表：

DROP FOREIGN TABLE acts;

如果有任何对象依赖于该 外部表，此命令将失败。 也可使用 CASCADE 子句将它们一并删除：

DROP FOREIGN TABLE acts CASCADE;

DML SQL 参考​

下面的 SQL DML 表达式可能会使用 pg_clickhouse。以下示例依赖于 这些 ClickHouse 表：

CREATE TABLE logs (
    req_id    Int64 NOT NULL,
    start_at   DateTime64(6, 'UTC') NOT NULL,
    duration  Int32 NOT NULL,
    resource  Text  NOT NULL,
    method    Enum8('GET' = 1, 'HEAD', 'POST', 'PUT', 'DELETE', 'CONNECT', 'OPTIONS', 'TRACE', 'PATCH', 'QUERY') NOT NULL,
    node_id   Int64 NOT NULL,
    response  Int32 NOT NULL
) ENGINE = MergeTree
  ORDER BY start_at;

CREATE TABLE nodes (
    node_id Int64 NOT NULL,
    name    Text  NOT NULL,
    region  Text  NOT NULL,
    arch    Text  NOT NULL,
    os      Text  NOT NULL
) ENGINE = MergeTree
  PRIMARY KEY node_id;

EXPLAIN​

EXPLAIN 命令可正常工作，但 VERBOSE 选项会触发输出 ClickHouse 的 "Remote SQL" 查询：

try=# EXPLAIN (VERBOSE)
       SELECT resource, avg(duration) AS average_duration
         FROM logs
        GROUP BY resource;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=64)
   Output: resource, (avg(duration))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT resource, avg(duration) FROM "default".logs GROUP BY resource
(4 rows)

该查询会通过 "Foreign Scan" 计划节点下推到 ClickHouse，即作为远程 SQL 执行。

SELECT​

使用 SELECT 语句对 pg_clickhouse 表执行查询，与查询其他任何表的方式相同：

try=# SELECT start_at, duration, resource FROM logs WHERE req_id = 4117909262;
          start_at          | duration |    resource
----------------------------+----------+----------------
 2025-12-05 15:07:32.944188 |      175 | /widgets/totem
(1 row)

pg_clickhouse 会尽可能将查询执行下推到 ClickHouse， 包括聚合函数。使用 EXPLAIN 可确定 下推的范围。以上述查询为例，所有执行都会下推到 ClickHouse

try=# EXPLAIN (VERBOSE, COSTS OFF)
       SELECT start_at, duration, resource FROM logs WHERE req_id = 4117909262;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Foreign Scan on public.logs
   Output: start_at, duration, resource
   Remote SQL: SELECT start_at, duration, resource FROM "default".logs WHERE ((req_id = 4117909262))
(3 rows)

pg_clickhouse 还会将同一远程服务器上各表之间的 JOIN 下推：

try=# EXPLAIN (ANALYZE, VERBOSE)
       SELECT name, count(*), round(avg(duration))
         FROM logs
         LEFT JOIN nodes on logs.node_id = nodes.node_id
        GROUP BY name;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=72) (actual time=3.201..3.221 rows=8.00 loops=1)
   Output: nodes.name, (count(*)), (round(avg(logs.duration), 0))
   Relations: Aggregate on ((logs) LEFT JOIN (nodes))
   Remote SQL: SELECT r2.name, count(*), round(avg(r1.duration), 0) FROM  "default".logs r1 ALL LEFT JOIN "default".nodes r2 ON (((r1.node_id = r2.node_id))) GROUP BY r2.name
   FDW Time: 0.086 ms
 Planning Time: 0.335 ms
 Execution Time: 3.261 ms
(7 rows)

如果不进行细致调优，与本地表进行连接会生成效率较低的查询。在此示例中，我们创建 nodes 表的本地副本，并与其连接，而不是连接远程表：

try=# CREATE TABLE local_nodes AS SELECT * FROM nodes;
SELECT 8

try=# EXPLAIN (ANALYZE, VERBOSE)
       SELECT name, count(*), round(avg(duration))
         FROM logs
         LEFT JOIN local_nodes on logs.node_id = local_nodes.node_id
        GROUP BY name;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=147.65..150.65 rows=200 width=72) (actual time=6.215..6.235 rows=8.00 loops=1)
   Output: local_nodes.name, count(*), round(avg(logs.duration), 0)
   Group Key: local_nodes.name
   Batches: 1  Memory Usage: 32kB
   Buffers: shared hit=1
   ->  Hash Left Join  (cost=31.02..129.28 rows=2450 width=36) (actual time=2.202..5.125 rows=1000.00 loops=1)
         Output: local_nodes.name, logs.duration
         Hash Cond: (logs.node_id = local_nodes.node_id)
         Buffers: shared hit=1
         ->  Foreign Scan on public.logs  (cost=10.00..20.00 rows=1000 width=12) (actual time=2.089..3.779 rows=1000.00 loops=1)
               Output: logs.req_id, logs.start_at, logs.duration, logs.resource, logs.method, logs.node_id, logs.response
               Remote SQL: SELECT duration, node_id FROM "default".logs
               FDW Time: 1.447 ms
         ->  Hash  (cost=14.90..14.90 rows=490 width=40) (actual time=0.090..0.091 rows=8.00 loops=1)
               Output: local_nodes.name, local_nodes.node_id
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=1
               ->  Seq Scan on public.local_nodes  (cost=0.00..14.90 rows=490 width=40) (actual time=0.069..0.073 rows=8.00 loops=1)
                     Output: local_nodes.name, local_nodes.node_id
                     Buffers: shared hit=1
 Planning:
   Buffers: shared hit=14
 Planning Time: 0.551 ms
 Execution Time: 6.589 ms

在这种情况下，我们可以不按本地列分组，而改为按 node_id 分组， 这样就能把更多聚合下推到 ClickHouse，之后再与查找表 join：

try=# EXPLAIN (ANALYZE, VERBOSE)
       WITH remote AS (
           SELECT node_id, count(*), round(avg(duration))
             FROM logs
            GROUP BY node_id
       )
       SELECT name, remote.count, remote.round
         FROM remote
         JOIN local_nodes
           ON remote.node_id = local_nodes.node_id
        ORDER BY name;
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=65.68..66.91 rows=490 width=72) (actual time=4.480..4.484 rows=8.00 loops=1)
   Output: local_nodes.name, remote.count, remote.round
   Sort Key: local_nodes.name
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=4
   ->  Hash Join  (cost=27.60..43.79 rows=490 width=72) (actual time=4.406..4.422 rows=8.00 loops=1)
         Output: local_nodes.name, remote.count, remote.round
         Inner Unique: true
         Hash Cond: (local_nodes.node_id = remote.node_id)
         Buffers: shared hit=1
         ->  Seq Scan on public.local_nodes  (cost=0.00..14.90 rows=490 width=40) (actual time=0.010..0.016 rows=8.00 loops=1)
               Output: local_nodes.node_id, local_nodes.name, local_nodes.region, local_nodes.arch, local_nodes.os
               Buffers: shared hit=1
         ->  Hash  (cost=15.10..15.10 rows=1000 width=48) (actual time=4.379..4.381 rows=8.00 loops=1)
               Output: remote.count, remote.round, remote.node_id
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               ->  Subquery Scan on remote  (cost=1.00..15.10 rows=1000 width=48) (actual time=4.337..4.360 rows=8.00 loops=1)
                     Output: remote.count, remote.round, remote.node_id
                     ->  Foreign Scan  (cost=1.00..5.10 rows=1000 width=48) (actual time=4.330..4.349 rows=8.00 loops=1)
                           Output: logs.node_id, (count(*)), (round(avg(logs.duration), 0))
                           Relations: Aggregate on (logs)
                           Remote SQL: SELECT node_id, count(*), round(avg(duration), 0) FROM "default".logs GROUP BY node_id
                           FDW Time: 0.055 ms
 Planning:
   Buffers: shared hit=5
 Planning Time: 0.319 ms
 Execution Time: 4.562 ms

“Foreign Scan” 节点现在会按 node_id 下推聚合，从而将必须从 Postgres 拉回的行数从 1000 行 (即全部 行) 减少到仅 8 行，每个节点对应一行。

PREPARE, EXECUTE, DEALLOCATE​

从 v0.1.2 起，pg_clickhouse 支持参数化查询，主要通过 PREPARE 命令创建：

try=# PREPARE avg_durations_between_dates(date, date) AS
       SELECT date(start_at), round(avg(duration)) AS average_duration
         FROM logs
        WHERE date(start_at) BETWEEN $1 AND $2
        GROUP BY date(start_at)
        ORDER BY date(start_at);
PREPARE

像平常一样，使用 EXECUTE 执行预处理语句：

try=# EXECUTE avg_durations_between_dates('2025-12-09', '2025-12-13');
    date    | average_duration
------------+------------------
 2025-12-09 |              190
 2025-12-10 |              194
 2025-12-11 |              197
 2025-12-12 |              190
 2025-12-13 |              195
(5 rows)
注意

参数化执行会导致 http 驱动 在 25.8 之前的 ClickHouse 版本中 无法正确处理 DateTime 的时区转换；该问题在[底层缺陷]被[修复]后才得到解决。请注意， PostgreSQL 有时即使不使用 PREPARE，也会采用参数化查询计划。对于任何 需要精确时区转换、且无法升级到 25.8 或更高版本的查询，请改用 二进制驱动程序。

与往常一样，pg_clickhouse 会将聚合操作下推，这一点可从 EXPLAIN 的详细输出中看出：

try=# EXPLAIN (VERBOSE) EXECUTE avg_durations_between_dates('2025-12-09', '2025-12-13');
                                                                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=36)
   Output: (date(start_at)), (round(avg(duration), 0))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT date(start_at), round(avg(duration), 0) FROM "default".logs WHERE ((date(start_at) >= '2025-12-09')) AND ((date(start_at) <= '2025-12-13')) GROUP BY (date(start_at)) ORDER BY date(start_at) ASC NULLS LAST
(4 rows)

请注意，它发送的是完整的日期值，而不是参数占位符。 前五次请求均是如此，如 PostgreSQL [PREPARE 说明]中所述。到第六次执行时，它会发送 ClickHouse {param:type} 风格的[查询参数]： 参数：

                                                                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=36)
   Output: (date(start_at)), (round(avg(duration), 0))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT date(start_at), round(avg(duration), 0) FROM "default".logs WHERE ((date(start_at) >= {p1:Date})) AND ((date(start_at) <= {p2:Date})) GROUP BY (date(start_at)) ORDER BY date(start_at) ASC NULLS LAST
(4 rows)

使用 DEALLOCATE 释放预处理语句：

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT​

使用 INSERT 命令将值插入远程 ClickHouse 表：

try=# INSERT INTO nodes(node_id, name, region, arch, os)
VALUES (9,  'Augustin Gamarra', 'us-west-2', 'amd64', 'Linux')
     , (10, 'Cerisier', 'us-east-2', 'amd64', 'Linux')
     , (11, 'Dewalt', 'use-central-1', 'arm64', 'macOS')
;
INSERT 0 3

COPY​

使用 COPY 命令将一批行插入远程 ClickHouse 表：

try=# COPY logs FROM stdin CSV;
4285871863,2025-12-05 11:13:58.360760,206,/widgets,POST,8,401
4020882978,2025-12-05 11:33:48.248450,199,/users/1321945,HEAD,3,200
3231273177,2025-12-05 12:20:42.158575,220,/search,GET,2,201
\.
>> COPY 3

⚠️ 批次 API 限制

pg_clickhouse 尚未支持 PostgreSQL FDW 的批量 insert API。因此，COPY 目前通过 INSERT 语句 插入记录。该问题将在后续版本中改进。

LOAD​

使用 LOAD 加载 pg_clickhouse 共享库：

try=# LOAD 'pg_clickhouse';
LOAD

通常不需要使用 LOAD，因为 Postgres 会在首次使用 pg_clickhouse 的任一功能 (函数、外部表等) 时自动加载 pg_clickhouse。

不过，在一种情况下，LOAD pg_clickhouse 可能会有用：在执行依赖这些参数的查询之前，先 SET pg_clickhouse 参数。

SET​

使用 SET 设置 pg_clickhouse 的自定义配置参数。

pg_clickhouse.session_settings​

pg_clickhouse.session_settings 参数用于配置应用于后续查询的 [ClickHouse 设置]。示例：

SET pg_clickhouse.session_settings = 'join_use_nulls 1, final 1';

默认值为 join_use_nulls 1, group_by_use_nulls 1, final 1。将其设置为空字符串，即可恢复为 ClickHouse服务器 的设置。

SET pg_clickhouse.session_settings = '';

其语法为由键/值对组成的逗号分隔列表，各键/值对之间用一个或 多个空格分隔。键必须对应于 ClickHouse Settings。对值中的空格、 逗号和反斜杠，使用反斜杠进行转义：

SET pg_clickhouse.session_settings = 'join_algorithm grace_hash\,hash';

或者使用单引号括起的值，以避免对空格和逗号进行转义；也可以考虑 使用[美元引号]，以避免必须使用双引号：

SET pg_clickhouse.session_settings = $$join_algorithm 'grace_hash,hash'$$;

如果你比较注重可读性，并且需要设置很多配置项，可以分多 行书写，例如：

SET pg_clickhouse.session_settings TO $$
    connect_timeout 2,
    count_distinct_implementation uniq,
    final 1,
    group_by_use_nulls 1,
    join_algorithm 'prefer_partial_merge',
    join_use_nulls 1,
    log_queries_min_type QUERY_FINISH,
    max_block_size 32768,
    max_execution_time 45,
    max_result_rows 1024,
    metrics_perf_events_list 'this,that',
    network_compression_method ZSTD,
    poll_interval 5,
    totals_mode after_having_auto
$$;

某些设置如果会干扰 pg_clickhouse 自身的运行，将被忽略。包括：

  • date_time_output_format：http 驱动要求其为 "iso"
  • format_tsv_null_representation：http 驱动要求使用默认值
  • output_format_tsv_crlf_end_of_line：http 驱动要求使用默认值

除此之外，pg_clickhouse 不会校验这些设置，而是会在每次查询时将其传递给 ClickHouse。因此，它支持各个 ClickHouse 版本的所有设置。

请注意，必须先加载 pg_clickhouse，才能设置 pg_clickhouse.session_settings；可以使用[共享库预加载]，或者 直接使用扩展中的某个对象以确保其已被加载。

pg_clickhouse.pushdown_regex​

pg_clickhouse.pushdown_regex 参数用于控制 pg_clickhouse 是否下推正则表达式函数和运算符。默认情况下会下推； 将此参数设为 false 可阻止下推：

SET pg_clickhouse.pushdown_regex = 'false';

详情请参见正则表达式。

ALTER ROLE​

使用 ALTER ROLE 的 SET 命令，可为特定角色预加载 pg_clickhouse 和/或设置其参数：

try=# ALTER ROLE CURRENT_USER SET session_preload_libraries = pg_clickhouse;
ALTER ROLE

try=# ALTER ROLE CURRENT_USER SET pg_clickhouse.session_settings = 'final 1';
ALTER ROLE

使用 ALTER ROLE 的 RESET 命令重置 pg_clickhouse 的预加载 和/或参数：

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

try=# ALTER ROLE CURRENT_USER RESET pg_clickhouse.session_settings;
ALTER ROLE

预加载​

如果每个或几乎每个 Postgres 连接都需要使用 pg_clickhouse， 可考虑使用[共享库预加载]自动加载它：

session_preload_libraries​

为 PostgreSQL 的每个新连接都会加载该共享库：

session_preload_libraries = pg_clickhouse

可用于在无需重启服务器的情况下应用更新：只需 重新连接即可。也可以通过 ALTER ROLE 为特定用户或角色单独设置。

shared_preload_libraries​

在启动时将共享库加载到 PostgreSQL 主进程中：

shared_preload_libraries = pg_clickhouse

有助于减少每个会话的内存占用和加载开销，但在库更新后需要重启 集群。

数据类型​

pg_clickhouse 将以下 ClickHouse 数据类型映射为 PostgreSQL 数据 类型。IMPORT FOREIGN SCHEMA 在导入列时，会使用 PostgreSQL 列类型中的第一个类型；其他类型可用于 CREATE FOREIGN TABLE 语句：

ClickHousePostgreSQL备注
Boolboolean
Datedate
Date32date
DateTimetimestamptz
Decimalnumeric
Float32real
Float64double precision
IPv4inet
IPv6inet
Int16smallint
Int32integer
Int64bigint
Int8smallint
JSONjsonb, json
Stringtext, bytea
UInt16integer
UInt32bigint
UInt64bigint当值大于 BIGINT 最大值时会报错
UInt8smallint
UUIDuuid

更多说明和详细信息见下文。

BYTEA​

ClickHouse 不提供与 PostgreSQL BYTEA 类型等效的类型，但允许将任意字节存储在 String 类型中。一般情况下，ClickHouse 字符串应映射到 PostgreSQL 的 TEXT 类型；若需使用二进制数据，则应映射到 BYTEA。示例：

-- Create clickHouse table with String columns.
SELECT clickhouse_raw_query($$
    CREATE TABLE bytes (
        c1 Int8, c2 String, c3 String
    ) ENGINE = MergeTree ORDER BY (c1);
$$);

-- Create foreign table with BYTEA columns.
CREATE FOREIGN TABLE bytes (
    c1 int,
    c2 BYTEA,
    c3 BYTEA
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Insert binary data into the foreign table.
INSERT INTO bytes
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the results.
SELECT * FROM bytes;

最终的 SELECT 查询将输出：

 c1 |                             c2                             |                 c3
----+------------------------------------------------------------+------------------------------------
  1 | \x1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | \xae3b28cde02542f81acce8783245430d
  2 | \x5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | \x23e7c6cacb8383f878ad093b0027d72b
  3 | \x53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | \x7e969132fc656148b97b6a2ee8bc83c1
  4 | \x4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | \x8ef30f44c65480d12b650ab6b2b04245
(4 rows)

请注意，如果 ClickHouse 列中存在空字节，使用 TEXT 列的外部表将无法输出正确的值：

-- Create foreign table with TEXT columns.
CREATE FOREIGN TABLE texts (
    c1 int,
    c2 TEXT,
    c3 TEXT
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Encode binary data as hex.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;

输出结果：

 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b
  3 | 53ac2c1fa83c8f64603fe9568d883331                         | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)

请注意，第二行和第三行包含被截断的值。这是因为 PostgreSQL 依赖以 nul 结尾的字符串，且不支持在字符串中使用 nul 字符。

尝试向 TEXT 列插入二进制值将会成功，并按预期运行：

-- Insert via text columns:
TRUNCATE texts;
INSERT INTO texts
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the data.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;

文本列将正确显示：


 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b0027d72b
  3 | 53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)

但如果按 BYTEA 读取，则不会：

# SELECT * FROM bytes;
 c1 |                                                           c2                                                           |                                   c3
----+------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------
  1 | \x5c783162663766306363383231643331313738363136613535613865306335323637373733353339376364646536663431353361396664336437 | \x5c786165336232386364653032353432663831616363653837383332343534333064
  2 | \x5c783566366539653132636438353932373132653633383031366634623161326537333233306565343064623439386330663062316463383431 | \x5c783233653763366361636238333833663837386164303933623030323764373262
  3 | \x5c783533616332633166613833633866363436303366653935363864383833333331303037643632383164653333306134623565373238663965 | \x5c783765393639313332666336353631343862393762366132656538626338336331
  4 | \x5c783465336332653463623735343261343531373361386461633933396464633462633735323032653334326562633736396230663564613266 | \x5c783865663330663434633635343830643132623635306162366232623034323435
(4 rows)
提示

一般来说，TEXT 列只用于存放编码后的字符串，BYTEA 列只用于存放二进制数据，切勿在两者之间来回切换。

函数与运算符参考​

函数​

这些函数提供了查询 ClickHouse 数据库的接口。

clickhouse_raw_query​

SELECT clickhouse_raw_query(
    'CREATE TABLE t1 (x String) ENGINE = Memory',
    'host=localhost port=8123'
);

通过其 HTTP 接口连接到 ClickHouse 服务，执行单个 查询，然后断开连接。可选的第二个参数用于指定连接 字符串，默认为 host=localhost port=8123。支持的连接 参数包括：

  • host：要连接的主机；必填。
  • port：要连接的 HTTP 端口；默认为 8123，除非 host 是 ClickHouse Cloud 主机，此时默认值为 8443
  • dbname：要连接的数据库名称。
  • username：连接时使用的用户名；默认为 default
  • password：用于身份验证的密码；默认为无密码

默认情况下，没有任何角色拥有此函数的 EXECUTE 权限；请考虑仅向确实需要执行临时 ClickHouse 查询的角色授予权限，例如专用的 ClickHouse 管理员角色：

适用于不返回记录的查询，但如果查询确实返回值， 则会以单个文本值的形式返回：

SELECT clickhouse_raw_query(
    'SELECT schema_name, schema_owner from information_schema.schemata',
    'host=localhost port=8123'
);

      clickhouse_raw_query
---------------------------------
 INFORMATION_SCHEMA      default+
 default default                +
 git     default                +
 information_schema      default+
 system  default                +

(1 row)

下推函数​

pg_clickhouse 会下推条件子句 (HAVING 和 WHERE 子句) 中使用的部分 PostgreSQL 内置函数。这部分函数与 ClickHouse 中的对应函数如下：

下推运算符​

  • 数组切片 (arr[L:U]): arraySlice
  • @> (数组包含) : hasAll
  • <@ (包含于数组) : hasAll
  • && (数组重叠) : hasAny
  • ~ (正则表达式匹配) : match
  • !~ (正则表达式不匹配) : match
  • ~* (不区分大小写的正则表达式不匹配) : match
  • !~* (不区分大小写的正则表达式不匹配) : match
  • ->> (将 JSON/JSONB 元素提取为文本) : 子列语法
  • -> (提取 JSON/JSONB) : toJSONString + 子列语法

自定义函数​

这些由 pg_clickhouse 创建的自定义函数可为部分在 PostgreSQL 中没有对应等价函数的 ClickHouse 函数提供外部查询下推。如果其中任何函数无法下推，就会引发异常。

扩展下推​

pg_clickhouse 可识别 select core 及第三方扩展中的函数，并将其下推为 ClickHouse 中的对应函数。

re2​

所有 [re2 扩展] 函数都可 1:1 下推到 ClickHouse：

intarray​

有一个 intarray 函数可下推至 ClickHouse：

fuzzystrmatch​

有两个 fuzzystrmatch 函数可下推到 ClickHouse：

下推类型转换​

对于兼容的数据类型，pg_clickhouse 会下推 CAST(x AS bigint) 这类类型转换。 对于不兼容的类型，下推会失败；例如，如果本例中的 x 是 ClickHouse 的 `UInt64``，ClickHouse 将拒绝执行该转换。

为了将类型转换下推到不兼容的数据类型，pg_clickhouse 提供了 以下函数。如果这些函数没有被下推，它们会在 PostgreSQL 中引发异常。

下推聚合函数​

以下 PostgreSQL 聚合函数可下推到 ClickHouse 执行。

自定义聚合函数​

这些由 pg_clickhouse 创建的自定义聚合函数，可对部分在 PostgreSQL 中没有对应实现的 ClickHouse 聚合函数提供外部查询下推支持。如果其中任何一个函数无法下推， 则会引发异常。

下推有序集合聚合​

这些[有序集合聚合函数]通过将其直接参数作为参数传递、将其 ORDER BY 表达式作为实参传递，映射到 ClickHouse 的[参数化 聚合函数]。例如，下面这个 PostgreSQL 查询：

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1;

对应以下 ClickHouse 查询：

SELECT quantile(0.25)(a) FROM t1;

请注意，非默认的 ORDER BY 后缀 DESC 和 `NULLS FIRST`` 不受支持，并且会报错。

下推窗口函数​

这些 PostgreSQL [窗口函数]可通过 OVER (PARTITION BY ... ORDER BY ...) 子句下推到 ClickHouse，并在适用时包含 窗口帧定义。

排名函数 (row_number、rank、dense_rank、ntile、cume_dist、 percent_rank) 在下推时会省略其窗口帧子句，因为 ClickHouse 不支持为这些函数指定窗口帧。

兼容性说明​

正则表达式​

当 pg_clickhouse.pushdown_regex 为 true (默认值) 时， pg_clickhouse 会将正则表达式下推为 ClickHouse 中的等价形式， 并尽力保证基本兼容性；但请注意两者之间的差异，以及 pg_clickhouse 对这些差异的处理方式。

  • PostgreSQL 支持 POSIX Regular Expressions，而 ClickHouse 支持 RE2 Regular Expressions。请注意两者在行为上的差异：当正则表达式由 ClickHouse 求值时 (例如在 WHERE 子句中) ，请按 RE2 语法编写；当它由 Postgres 求值时 (例如在 SELECT 子句中) ，请按 POSIX 语法编写。

  • pg_clickhouse 会通过将 Postgres 的 [Regex flags] 预加到 ClickHouse 正则表达式前面的 (?) 中，来下推这些标志。例如：

    regexp_like(val, '^VAL\d', 'i')

    会变为

    match(val, concat('(?i-s)', '^VAL\\d'))

    注意这里包含了 -s；这是为了禁用 ClickHouse 默认启用的 s， 从而使其行为与 Postgres 正则表达式保持一致。 如果 Postgres 函数调用中的标志包含 s，pg_clickhouse 就不会添加 -s。 不过，遗憾的是，这种行为会破坏 Postgres 24 及更早版本中某些正则表达式的兼容性。

  • 两者都支持、因此可在由 ClickHouse 求值时使用的标志只有：

    • i：不区分大小写
    • m：多行模式：
    • s：使 . 匹配 \n
    • p：部分换行敏感匹配 (处理方式与 s 相同)
    • t：紧凑语法 (默认值，会被 pg_clickhouse 移除)

    RE2 只支持这些标志；不要使用任何其他 Postgres flags

  • 传递给正则表达式函数的任何其他标志都会导致该函数无法下推。

  • 例外情况是 regexp_replace()，它还支持 g 标志。设置 g 后，pg_clickhouse 会使用 replaceRegexpAll() 而不是 replaceRegexpOne()，并在预加其他标志之前移除该标志。

  • Postgres regexp_replace() 的替换参数支持使用 \& 来引用整个匹配内容，而 ClickHouse 则使用 \0 表示整个匹配内容。 当该函数会下推到 ClickHouse 时，请务必使用 \0。

为避免任何歧义，可以考虑设置 pg_clickhouse.pushdown_regex 以阻止 Postgres 正则表达式下推到 ClickHouse，并使用 re2 extension；对于该扩展，pg_clickhouse 支持对与 ClickHouse 兼容的 RE2 正则表达式进行直接下推。

to_char()​

用于 timestamp 和 timestamp with time zone 的 PostgreSQL to_char() 只有在格式参数是非 NULL 的字符串常量，且其中每个 PostgreSQL 关键字在 ClickHouse 中都能找到按字节完全一致的对应项时，才会下推到 ClickHouse formatDateTime。如果格式是动态的 (不是 Const) ，或包含任何不受支持的 关键字或修饰符，则该调用会回退为在 PostgreSQL 中本地求值——绝不会在仅做 部分转换的情况下尝试下推，因此输出会保持与 PG 兼容。

用于 numeric、interval 及其他非时间戳类型的双参数 to_char() 形式 永远不会下推；ClickHouse formatDateTime 只能格式化日期时间值。

已转换的关键字​

PostgreSQLClickHouse含义
YYYY, yyyy%Y4 位年份
YY, yy%y2 位年份
MM, mm%m补零的月份 (01–12)
DD, dd%d补零的日期 (01–31)
DDD, ddd%j补零的一年中的第几天 (001–366)
HH24, hh24%H补零的 24 小时制小时 (00–23)
HH, hh, HH12, hh12%I补零的 12 小时制小时 (01–12)
MI, mi%i补零的分钟 (00–59)
SS, ss%S补零的秒 (00–59)
Q, q%Q季度 (1–4)
Mon%b月份简称，例如 Oct
Dy%a星期简称，例如 Mon
AM, PM%p上下午标记，始终为大写

引用文本和字面量​

用 "..." 包裹的文本会原样传递，其中任何字面的 % 都会写成 %%，以转义 ClickHouse 的说明符前缀。引号外的 \" 也会作为字面的 " 原样传递。在 "..." 内部，反斜杠 仅用于转义 "；其他反斜杠序列都会被视为字面文本。

作者​

David E. Wheeler

版权所有 (c) 2025-2026，ClickHouse