> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > pg_clickhouse 的完整参考文档 # pg_clickhouse 参考文档
## 描述
pg\_clickhouse 是一个 PostgreSQL 扩展，可在 ClickHouse 数据库上远程执行查询， 其中包括一个\[外部数据包装器]。它支持 PostgreSQL 13 及以上版本，以及 ClickHouse 23 及以上版本。
## 快速开始
试用 pg\_clickhouse 最简单的方式是使用 \[Docker 镜像]，其中包含 标准的 PostgreSQL Docker 镜像，并集成了 pg\_clickhouse 和 \[re2]\[re2 扩展] 扩展： ```sh theme={null} 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 ``` 请参阅[教程](/zh/products/managed-postgres/extensions/pg_clickhouse/tutorial)，开始了解如何导入 ClickHouse 表并下推查询。
## 用法
```sql theme={null} 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 扩展添加到数据库中： ```sql theme={null} CREATE EXTENSION pg_clickhouse; ``` 使用 `WITH SCHEMA` 将其安装到指定的 schema 中 (推荐) ： ```sql theme={null} CREATE SCHEMA ch; CREATE EXTENSION pg_clickhouse WITH SCHEMA ch; ```
### ALTER EXTENSION
使用 [ALTER EXTENSION] 修改 pg\_clickhouse。示例： * 安装 pg\_clickhouse 的新 release 后，使用 `UPDATE` 子句： ```sql theme={null} ALTER EXTENSION pg_clickhouse UPDATE; ``` * 使用 `SET SCHEMA` 将该扩展移动到新的 schema： ```sql theme={null} CREATE SCHEMA ch; ALTER EXTENSION pg_clickhouse SET SCHEMA ch; ```
### DROP EXTENSION
使用 [DROP EXTENSION] 从数据库中删除 pg\_clickhouse： ```sql theme={null} DROP EXTENSION pg_clickhouse; ``` 如果存在任何依赖于 pg\_clickhouse 的对象，此命令会失败。请使用 `CASCADE` 子句将它们一并删除： ```sql theme={null} DROP EXTENSION pg_clickhouse CASCADE; ```
### CREATE SERVER
使用 [CREATE SERVER] 创建连接到 ClickHouse 服务器的外部服务器。示例： ```sql theme={null} 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 streaming 的近似批次大小 (以字节为单位) 。批次 按行边界拆分。默认为 `50000000` (50 MB) 。`0` 会禁用 streaming 并缓冲整个响应。foreign table 可以覆盖此 值。 * `host`：ClickHouse server 的主机名。默认为 "localhost"； * `port`：连接到 ClickHouse server 时使用的端口。默认值 如下： * 如果 `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] 修改 外部服务器。示例： ```sql theme={null} ALTER SERVER taxi_srv OPTIONS (SET driver 'http'); ``` 这些选项与 [CREATE SERVER](#create-server) 中的选项相同。
### DROP SERVER
使用 [DROP SERVER] 删除 外部服务器： ```sql theme={null} DROP SERVER taxi_srv; ``` 如果有任何其他对象依赖于该服务器，此命令将失败。使用 `CASCADE` 还会 一并删除这些依赖对象： ```sql theme={null} DROP SERVER taxi_srv CASCADE; ```
### CREATE USER MAPPING
使用 [CREATE USER MAPPING] 将 PostgreSQL 用户映射为 ClickHouse 用户。例如，使用 `taxi_srv` 外部服务器 连接时，可将当前 PostgreSQL 用户映射为远程 ClickHouse 用户： ```sql theme={null} CREATE USER MAPPING FOR CURRENT_USER SERVER taxi_srv OPTIONS (user 'demo'); ``` 支持的选项如下： * `user`：ClickHouse 用户名。默认为 "default"。 * `password`：ClickHouse 用户的密码。
### ALTER USER MAPPING
使用 [ALTER USER MAPPING] 修改用户映射的定义： ```sql theme={null} ALTER USER MAPPING FOR CURRENT_USER SERVER taxi_srv OPTIONS (SET user 'default'); ``` 这些选项与 [CREATE USER MAPPING](#create-user-mapping) 中的选项相同。
### DROP USER MAPPING
使用 [DROP USER MAPPING] 移除用户映射： ```sql theme={null} DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv; ```
### IMPORT FOREIGN SCHEMA
使用 [IMPORT FOREIGN SCHEMA] 可将 ClickHouse 数据库中定义的所有表作为外部表导入到 PostgreSQL schema 中： ```sql theme={null} CREATE SCHEMA taxi; IMPORT FOREIGN SCHEMA demo FROM SERVER taxi_srv INTO taxi; ``` 使用 `LIMIT TO` 将导入限制到特定表： ```sql theme={null} IMPORT FOREIGN SCHEMA demo LIMIT TO (trips) FROM SERVER taxi_srv INTO taxi; ``` 使用 `EXCEPT` 排除这些表： ```sql theme={null} IMPORT FOREIGN SCHEMA demo EXCEPT (users) FROM SERVER taxi_srv INTO taxi; ``` pg\_clickhouse 将获取指定 ClickHouse 数据库 (上述示例中的 "demo") 中所有表的列表，获取每个表的列定义， 并执行 [CREATE FOREIGN TABLE](#create-foreign-table) 命令来创建 外部表。列将使用[支持的数据 类型](#data-types)进行定义，并在可检测的情况下使用 [CREATE FOREIGN TABLE](#create-foreign-table) 支持的选项。 **导入标识符的大小写保留** `IMPORT FOREIGN SCHEMA` 会对导入的表名和列名运行 `quote_identifier()`， 为包含大写字母或空格的标识符加上双引号。 因此，这类表名和列名在 PostgreSQL 查询中必须用双引号括起来。 如果名称全部为小写且不包含空格字符，则无需加引号。 例如，给定以下 ClickHouse 表： ```sql theme={null} CREATE OR REPLACE TABLE test ( id UInt64, Name TEXT, updatedAt DateTime DEFAULT now() ) ENGINE = MergeTree ORDER BY id; ``` `IMPORT FOREIGN SCHEMA` 会创建这个外部表： ```sql theme={null} CREATE TABLE test ( id BIGINT NOT NULL, "Name" TEXT NOT NULL, "updatedAt" TIMESTAMPTZ NOT NULL ); ``` 因此，查询中也必须正确加引号，例如： ```sql theme={null} SELECT id, "Name", "updatedAt" FROM test; ``` 若要创建名称不同或全部为小写 (因此 不区分大小写) 的对象，请使用 [CREATE FOREIGN TABLE](#create-foreign-table)。
### CREATE FOREIGN TABLE
使用 [CREATE FOREIGN TABLE] 创建可查询 ClickHouse 数据库中数据的外部表： ```sql theme={null} 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`：远程 database 的名称。默认使用为 外部服务器 定义的 database。 * `fetch_size`：HTTP streaming 的近似批次大小 (以字节为单位) 。会覆盖 server 级别的 `fetch_size`。默认值为 `50000000` (50 MB) 。`0` 会禁用 流式传输并缓冲完整响应。 * `table_name`：远程表的名称。默认使用为 foreign table 指定的名称。 * `engine`：ClickHouse 表使用的\[表引擎]。对于 `CollapsingMergeTree()` 和 `AggregatingMergeTree()`，pg\_clickhouse 会自动将参数应用于在该表上执行的函数表达式。 对于每一列，请使用与远程 ClickHouse 数据 类型相对应的[data type](#data-types)。支持的列选项如下： * `column_name`：ClickHouse 端的列名，在反解析查询和 插入语句时，会优先使用该名称而不是 PostgreSQL attribute 名称。 这对于将未加引号的小写 PostgreSQL 列名映射到 区分大小写的 ClickHouse 列很有用，例如： ```sql theme={null} 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`。 ```sql theme={null} 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] 修改外部表的定义： ```sql theme={null} ALTER TABLE table ALTER COLUMN b OPTIONS (SET AggregateFunction 'count'); ``` 支持的表和列选项与 [CREATE FOREIGN TABLE] 中的相同。
### DROP FOREIGN TABLE
使用 [DROP FOREIGN TABLE] 删除外部表： ```sql theme={null} DROP FOREIGN TABLE acts; ``` 如果有任何对象依赖于该外部表，此命令将失败。 使用 `CASCADE` 子句也会将它们一并删除： ```sql theme={null} DROP FOREIGN TABLE acts CASCADE; ```
## DML SQL 参考
下面的 SQL [DML] 表达式可能会用到 pg\_clickhouse。示例基于 以下 ClickHouse 表： ```sql theme={null} 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" 查询： ```pgsql theme={null} 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 表上执行查询，与在任何其他表上执行查询无异： ```pgsql theme={null} 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](#explain) 可确定 下推的范围。例如，对于上述查询，所有执行都会下推到 ClickHouse ```pgsql theme={null} 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 下推： ```pgsql theme={null} 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` 表的一个本地副本，并与它连接，而不是连接远程表： ```pgsql theme={null} 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： ```sql theme={null} 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 行，即每个节点 1 行。
### PREPARE, EXECUTE, DEALLOCATE
自 v0.1.2 起，pg\_clickhouse 支持参数化查询，主要通过 [PREPARE] 命令创建： ```pgsql theme={null} 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] 执行预处理语句： ```pgsql theme={null} 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 行) ``` 参数化执行会导致 [http 驱动](#create-server) 在 25.8 之前的 ClickHouse 版本中 无法正确转换 DateTime 时区，因为该\[底层缺陷]当时尚未\[修复]。请注意，PostgreSQL 有时即使不使用 `PREPARE`，也会采用参数化查询计划。对于任何需要精确时区转换的查询， 如果无法升级到 25.8 或更高版本，请改用 [binary driver](#create-server)。 与往常一样，pg\_clickhouse 会下推聚合操作，如 [EXPLAIN](#explain) 的详细输出所示： ```pgsql theme={null} 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}` 风格的\[查询参数]： 参数： ```pgsql theme={null} 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] 解除分配预处理语句： ```pgsql theme={null} try=# DEALLOCATE avg_durations_between_dates; DEALLOCATE ```
### INSERT
使用 [INSERT] 命令向远程 ClickHouse 表中插入值： ```pgsql theme={null} 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 表： ```pgsql theme={null} 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](#insert) 语句 > 插入记录。后续版本将对此进行改进。
### LOAD
使用 [LOAD] 加载 pg\_clickhouse 共享库： ```pgsql theme={null} try=# LOAD 'pg_clickhouse'; LOAD ``` 通常不需要使用 [LOAD]，因为 Postgres 会在首次使用 pg\_clickhouse 的任一功能 (函数、外部表等) 时自动加载它。 而 [LOAD] pg\_clickhouse 可能有用的一种情况是：在执行依赖这些参数的查询之前，先通过 [SET](#set) 设置 pg\_clickhouse 参数。
### SET
使用 [SET] 来设置 pg\_clickhouse 的自定义配置参数。
#### `pg_clickhouse.session_settings`
`pg_clickhouse.session_settings` 参数用于配置在后续查询中要设置的 \[ClickHouse 设置]。示例： ```sql theme={null} SET pg_clickhouse.session_settings = 'join_use_nulls 1, final 1'; ``` 默认值为 `join_use_nulls 1, group_by_use_nulls 1, final 1`。将其设为空 字符串即可恢复为 ClickHouse server 的设置。 ```sql theme={null} SET pg_clickhouse.session_settings = ''; ``` 其语法为由逗号分隔的键/值对列表，各项之间以一个或 多个空格分隔。键必须与 \[ClickHouse 设置] 对应。在值中，空格、 逗号和反斜杠都要用反斜杠转义： ```sql theme={null} SET pg_clickhouse.session_settings = 'join_algorithm grace_hash\,hash'; ``` 或者使用单引号包裹值，以避免转义空格和逗号；也可以考虑 使用\[美元引号表示法]，这样就无需再使用双引号： ```sql theme={null} SET pg_clickhouse.session_settings = $$join_algorithm 'grace_hash,hash'$$; ``` 如果你更注重可读性，并且需要设置很多项，可以分成多 行，例如： ```sql theme={null} 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 可阻止下推： ```sql theme={null} SET pg_clickhouse.pushdown_regex = 'false'; ``` 详见[正则表达式](#regular-expressions)。
### ALTER ROLE
使用 [ALTER ROLE] 的 `SET` 命令，可为特定角色[预加载](#preloading) pg\_clickhouse， 和/或 [SET](#set) 其参数： ```pgsql theme={null} 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]'s `RESET` 命令重置 pg\_clickhouse 的预加载 和/或相关参数： ```pgsql theme={null} 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 的新连接时加载共享库： ```ini theme={null} session_preload_libraries = pg_clickhouse ``` 有助于在不重启服务器的情况下应用更新：只需 重新连接即可。也可以通过 [ALTER ROLE](#alter-role) 为特定用户或角色设置。
### `shared_preload_libraries`
在启动时将共享库加载到 PostgreSQL 主进程中： ```ini theme={null} shared_preload_libraries = pg_clickhouse ``` 有助于减少每个会话的内存占用和加载开销，但在库更新时需要 重启集群。
## 数据类型
pg\_clickhouse 将以下 ClickHouse 数据类型映射为 PostgreSQL 数据 类型。[IMPORT FOREIGN SCHEMA](#import-foreign-schema) 在导入列时会使用 PostgreSQL 列中的第一种类型；其他类型可在 [CREATE FOREIGN TABLE](#create-foreign-table) 语句中使用： | ClickHouse | PostgreSQL | 说明 | | ---------- | ---------------- | ------------------ | | Bool | boolean | | | Date | date | | | Date32 | date | | | DateTime | timestamptz | | | Decimal | numeric | | | Float32 | real | | | Float64 | double precision | | | IPv4 | inet | | | IPv6 | inet | | | Int16 | smallint | | | Int32 | integer | | | Int64 | bigint | | | Int8 | smallint | | | JSON | jsonb, json | | | String | text, bytea | | | UInt16 | integer | | | UInt32 | bigint | | | UInt64 | bigint | 值大于 BIGINT 最大值时会报错 | | UInt8 | smallint | | | UUID | uuid | | 更多说明和细节如下。
### BYTEA
ClickHouse 不提供与 PostgreSQL [BYTEA] 类型等效的类型，但允许将任意字节存储在 [String] 类型中。一般情况下，ClickHouse 字符串应映射到 PostgreSQL 的 [TEXT] 类型；若需使用二进制数据，则应映射到 [BYTEA]。示例： ```sql theme={null} -- 创建带有 String 列的 ClickHouse 表。 SELECT clickhouse_raw_query($$ CREATE TABLE bytes ( c1 Int8, c2 String, c3 String ) ENGINE = MergeTree ORDER BY (c1); $$); -- 创建带有 BYTEA 列的外部表。 CREATE FOREIGN TABLE bytes ( c1 int, c2 BYTEA, c3 BYTEA ) SERVER ch_srv OPTIONS( table_name 'bytes' ); -- 向外部表中插入二进制数据。 INSERT INTO bytes SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex') FROM generate_series(1, 4) n; -- 查看结果。 SELECT * FROM bytes; ``` 最终的 `SELECT` 查询将输出： ```pgsql theme={null} c1 | c2 | c3 ----+------------------------------------------------------------+------------------------------------ 1 | \x1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | \xae3b28cde02542f81acce8783245430d 2 | \x5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | \x23e7c6cacb8383f878ad093b0027d72b 3 | \x53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | \x7e969132fc656148b97b6a2ee8bc83c1 4 | \x4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | \x8ef30f44c65480d12b650ab6b2b04245 (4 行) ``` 请注意，如果 ClickHouse 列中存在空字节，使用 [TEXT] 列的外部表将无法输出正确的值： ```sql theme={null} -- 创建包含 TEXT 列的外部表。 CREATE FOREIGN TABLE texts ( c1 int, c2 TEXT, c3 TEXT ) SERVER ch_srv OPTIONS( table_name 'bytes' ); -- 将二进制数据编码为十六进制。 SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1; ``` 输出结果： ```pgsql theme={null} c1 | encode | encode ----+----------------------------------------------------------+---------------------------------- 1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d 2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b 3 | 53ac2c1fa83c8f64603fe9568d883331 | 7e969132fc656148b97b6a2ee8bc83c1 4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245 (4 rows) ``` 请注意，第二行和第三行包含被截断的值。这是因为 PostgreSQL 依赖以 nul 结尾的字符串，且不支持在字符串中 包含 nul 字符。 尝试向 [TEXT] 列插入二进制值将会成功，并按预期运行： ```sql theme={null} -- 通过文本列插入： TRUNCATE texts; INSERT INTO texts SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex') FROM generate_series(1, 4) n; -- 查看数据。 SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1; ``` 文本列将显示正确： ```pgsql theme={null} c1 | encode | encode ----+----------------------------------------------------------+---------------------------------- 1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d 2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b0027d72b 3 | 53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | 7e969132fc656148b97b6a2ee8bc83c1 4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245 (4 行) ``` 但如果按 [BYTEA] 读取，则不会： ```pgsql theme={null} # SELECT * FROM bytes; c1 | c2 | c3 ----+------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------ 1 | \x5c783162663766306363383231643331313738363136613535613865306335323637373733353339376364646536663431353361396664336437 | \x5c786165336232386364653032353432663831616363653837383332343534333064 2 | \x5c783566366539653132636438353932373132653633383031366634623161326537333233306565343064623439386330663062316463383431 | \x5c783233653763366361636238333833663837386164303933623030323764373262 3 | \x5c783533616332633166613833633866363436303366653935363864383833333331303037643632383164653333306134623565373238663965 | \x5c783765393639313332666336353631343862393762366132656538626338336331 4 | \x5c783465336332653463623735343261343531373361386461633933396464633462633735323032653334326562633736396230663564613266 | \x5c783865663330663434633635343830643132623635306162366232623034323435 (4 行) ``` 一般来说，[TEXT] 列只用于编码字符串，[BYTEA] 列只用于二进制数据， 并且绝不要在两者之间切换。
## 函数与 operator 参考
### 函数
这些函数提供了查询 ClickHouse 数据库的接口。
#### `clickhouse_raw_query`
```sql theme={null} SELECT clickhouse_raw_query( 'CREATE TABLE t1 (x String) ENGINE = Memory', 'host=localhost port=8123' ); ``` 通过其 HTTP interface 连接到 ClickHouse 服务，执行单个 查询，然后断开连接。可选的第二个参数用于指定连接 字符串，默认为 `host=localhost port=8123`。支持的连接 参数如下： * `host`：要连接到的主机；必填。 * `port`：要连接的 HTTP 端口；默认为 `8123`，除非 `host` 是 ClickHouse Cloud 主机，此时默认值为 `8443` * `dbname`：要连接到的数据库名称。 * `username`：连接时使用的用户名；默认为 `default` * `password`：用于身份验证的密码；默认无密码 默认情况下，没有任何角色具有对此函数的 `EXECUTE` 权限；请考虑仅向 确实需要执行临时 ClickHouse 查询的角色 [GRANT] 此权限， 例如专用的 ClickHouse 管理员角色： 适用于不返回记录的查询，但如果查询确实返回值， 则会作为单个文本值返回： ```sql theme={null} SELECT clickhouse_raw_query( 'SELECT schema_name, schema_owner from information_schema.schemata', 'host=localhost port=8123' ); ``` ```sql theme={null} clickhouse_raw_query --------------------------------- INFORMATION_SCHEMA default+ default default + git default + information_schema default+ system default + (1 row) ```
### 下推函数
`pg_clickhouse` 会将条件子句 (`HAVING` 和 `WHERE` 子句) 中使用的部分 PostgreSQL 内置函数下推到 ClickHouse 中执行。该子集与 ClickHouse 中的对应函数如下： * `abs`: [abs](/zh/reference/functions/regular-functions/arithmetic-functions#abs) * `factorial`: [factorial](/zh/reference/functions/regular-functions/math-functions#factorial) * `mod` (int2/int4/int8/numeric): [取模](/zh/reference/functions/regular-functions/arithmetic-functions#modulo) * `pow` & `power` (float8/numeric): [pow](/zh/reference/functions/regular-functions/math-functions#pow) * `round`: [round](/zh/reference/functions/regular-functions/rounding-functions#round) * `sin`, `cos`, `tan`, `atan`, `atan2`, `sinh`, `cosh`, `tanh`, `asinh`, `degrees`, `radians`, `pi`：[ClickHouse 数学函数](/zh/reference/functions/regular-functions/math-functions) 中的同名函数。`asin`、`acos`、`atanh`、`acosh` 不会下推：对于超出范围的输入，PG 会报错，而 CH 会返回 `NaN`。 * `date_part`: * `date_part('day')`: [toDayOfMonth](/zh/reference/functions/regular-functions/date-time-functions#toDayOfMonth) * `date_part('doy')`: [toDayOfYear](/zh/reference/functions/regular-functions/date-time-functions#toDayOfYear) * `date_part('dow')`: [toDayOfWeek](/zh/reference/functions/regular-functions/date-time-functions#toDayOfWeek) * `date_part('year')`: [toYear](/zh/reference/functions/regular-functions/date-time-functions#toYear) * `date_part('month')`: [toMonth](/zh/reference/functions/regular-functions/date-time-functions#toMonth) * `date_part('hour')`: [toHour](/zh/reference/functions/regular-functions/date-time-functions#toHour) * `date_part('minute')`: [toMinute](/zh/reference/functions/regular-functions/date-time-functions#toMinute) * `date_part('second')`: [toSecond](/zh/reference/functions/regular-functions/date-time-functions#toSecond) * `date_part('quarter')`: [toQuarter](/zh/reference/functions/regular-functions/date-time-functions#toQuarter) * `date_part('isoyear')`: [toISOYear](/zh/reference/functions/regular-functions/date-time-functions#toISOYear) * `date_part('week')`: [toISOYear](/zh/reference/functions/regular-functions/date-time-functions#toISOWeek) * `date_part('epoch')`: [toISOYear](/zh/reference/functions/regular-functions/date-time-functions#toUnixTimestamp) * `date_trunc`: * `date_trunc('week')`: [toMonday](/zh/reference/functions/regular-functions/date-time-functions#toMonday) * `date_trunc('second')`: [toStartOfSecond](/zh/reference/functions/regular-functions/date-time-functions#toStartOfSecond) * `date_trunc('minute')`: [toStartOfMinute](/zh/reference/functions/regular-functions/date-time-functions#toStartOfMinute) * `date_trunc('hour')`: [toStartOfHour](/zh/reference/functions/regular-functions/date-time-functions#toStartOfHour) * `date_trunc('day')`: [toStartOfDay](/zh/reference/functions/regular-functions/date-time-functions#toStartOfDay) * `date_trunc('month')`: [toStartOfMonth](/zh/reference/functions/regular-functions/date-time-functions#toStartOfMonth) * `date_trunc('quarter')`: [toStartOfQuarter](/zh/reference/functions/regular-functions/date-time-functions#toStartOfQuarter) * `date_trunc('year')`: [toStartOfYear](/zh/reference/functions/regular-functions/date-time-functions#toStartOfYear) * `extract(field FROM source)`：映射与 `date_part` 相同 * `date(timestamp)` & `date(timestamptz)`: [toDate](/zh/reference/functions/regular-functions/type-conversion-functions#toDate) (反解析后为 CH 别名 `date`) * `array_position`：[indexOf](/zh/reference/functions/regular-functions/array-functions#indexOf) * `array_cat`: [arrayConcat](/zh/reference/functions/regular-functions/array-functions#arrayConcat) * `array_append`: [arrayPushBack](/zh/reference/functions/regular-functions/array-functions#arrayPushBack) * `array_prepend`: [arrayPushFront](/zh/reference/functions/regular-functions/array-functions#arrayPushFront) * `array_remove`: [arrayRemove](/zh/reference/functions/regular-functions/array-functions#arrayRemove) * `array_length` & `cardinality`：[length](/zh/reference/functions/regular-functions/array-functions#length) * `array_to_string`: [arrayStringConcat](/zh/reference/functions/regular-functions/array-functions#arrayStringConcat) * `string_to_array`: [splitByString](/zh/reference/functions/regular-functions/splitting-merging-functions#splitByString) * `split_part`: [splitByString](/zh/reference/functions/regular-functions/splitting-merging-functions#splitByString) + 数组下标 * `trim_array`: [arrayResize](/zh/reference/functions/regular-functions/array-functions#arrayResize) * `array_fill`: [arrayWithConstant](/zh/reference/functions/regular-functions/array-functions#arrayWithConstant) * `array_reverse`: [arrayReverse](/zh/reference/functions/regular-functions/array-functions#arrayReverse) * `array_shuffle`：[arrayShuffle](/zh/reference/functions/regular-functions/array-functions#arrayShuffle) * `array_sample`: [arrayRandomSample](/zh/reference/functions/regular-functions/array-functions#arrayRandomSample) * `array_sort`: [arraySort](/zh/reference/functions/regular-functions/array-functions#arraySort) / [arrayReverseSort](/zh/reference/functions/regular-functions/array-functions#arrayReverseSort) * `btrim`: [trimBoth](/zh/reference/functions/regular-functions/string-functions#trimboth) * `ltrim`: [ltrim](/zh/reference/functions/regular-functions/string-functions#ltrim) * `rtrim`: [rtrim](/zh/reference/functions/regular-functions/string-functions#rtrim) * `concat_ws`: [concatWithSeparator](/zh/reference/functions/regular-functions/string-functions#concatwithseparator) * `lower(text)`: [lowerUTF8](/zh/reference/functions/regular-functions/string-functions#lowerutf8) * `upper(text)`: [upperUTF8](/zh/reference/functions/regular-functions/string-functions#upperutf8) * `substring(text, ...)` & `substr(text, ...)`: [substringUTF8](/zh/reference/functions/regular-functions/string-functions#substringutf8) * `substring(bytea, ...)` & `substr(bytea, ...)`： [子串](/zh/reference/functions/regular-functions/string-functions#substring) * `length(text)`: [lengthUTF8](/zh/reference/functions/regular-functions/string-functions#lengthutf8) * `length(bytea)` & `octet_length`：[length](/zh/reference/functions/regular-functions/array-functions#length) * `reverse(text)`: [reverseUTF8](/zh/reference/functions/regular-functions/string-functions#reverseutf8) * `reverse(bytea)`: [reverse](/zh/reference/functions/regular-functions/string-functions#reverse) * `strpos`：[positionUTF8](/zh/reference/functions/regular-functions/string-search-functions#positionutf8) * `regexp_like`: [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `regexp_replace`：[replaceRegexpOne](/zh/reference/functions/regular-functions/string-replace-functions#replaceRegexpOne) 或 [replaceRegexpOne](/zh/reference/functions/regular-functions/string-replace-functions#replaceRegexpAll)，存在 `g` 标志时 * `regexp_split_to_array`: [splitByRegexp](/zh/reference/functions/regular-functions/splitting-merging-functions#splitByRegexp) * `md5`: [MD5](/zh/reference/functions/regular-functions/hash-functions#MD5) * `json_extract_path_text`：[子列语法](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns) * `json_extract_path`: [toJSONString](/zh/reference/functions/regular-functions/json-functions#toJSONString) + [子列语法](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns) * `jsonb_extract_path_text`: [子列语法](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns) * `jsonb_extract_path`：[toJSONString](/zh/reference/functions/regular-functions/json-functions#toJSONString) + [子列语法](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns) * `bit_count(bytea)`: [bitCount](/zh/reference/functions/regular-functions/bit-functions#bitcount) * `to_timestamp(float8)`: [fromUnixTimestamp](/zh/reference/functions/regular-functions/date-time-functions#fromUnixTimestamp) * `to_char(timestamp[tz], fmt)`: [formatDateTime](/zh/reference/functions/regular-functions/date-time-functions#formatDateTime) 当 `fmt` 为字符串常量，且其中每个关键字在 ClickHouse 中都有完全对应的等价写法时。支持的关键字请参见兼容性说明中的 [to\_char()](#to_char)。 否则，该函数会在 PostgreSQL 中本地执行。 * `statement_timestamp`, `transaction_timestamp`, & `clock_timestamp`: [nowInBlock64](/zh/reference/functions/regular-functions/date-time-functions#nowInBlock64) (`nowInBlock64(9, $session_timezone)`) * `CURRENT_DATE`: [now](/zh/reference/functions/regular-functions/date-time-functions#now) 以及 [toDate](/zh/reference/functions/regular-functions/type-conversion-functions#toDate) (`toDate(now($session_timezone))`) * `now`、`CURRENT_TIMESTAMP` 和 `LOCALTIMESTAMP`： [now64](/zh/reference/functions/regular-functions/date-time-functions#now64) (`now64(9, $session_timezone)`) * `CURRENT_TIMESTAMP(n)` & `LOCALTIMESTAMP(n)`: [now64](/zh/reference/functions/regular-functions/date-time-functions#now64) (`now64(n, $session_timezone)`) * `CURRENT_DATABASE`：作为值由 PostgreSQL 函数传入。 * `CURRENT_SCHEMA`：以 PostgreSQL 函数返回值的形式传递。 * `CURRENT_CATALOG`：作为值由 PostgreSQL 函数传入。 * `CURRENT_USER`：作为值从 PostgreSQL 函数传入。 * `USER`：由 PostgreSQL 函数作为值传入。 * `CURRENT_ROLE`：作为值从 PostgreSQL 函数中传入。 * `SESSION_USER`：作为值从 PostgreSQL 函数传入。
### 下推操作符
* 数组切片 (`arr[L:U]`): [arraySlice](/zh/reference/functions/regular-functions/array-functions#arraySlice) * `@>` (数组包含) : [hasAll](/zh/reference/functions/regular-functions/array-functions#hasAll) * `<@` (数组被包含) : [hasAll](/zh/reference/functions/regular-functions/array-functions#hasAll) * `&&` (数组重叠) : [hasAny](/zh/reference/functions/regular-functions/array-functions#hasAny) * `~` (正则表达式匹配) : [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `!~` (正则表达式不匹配) : [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `~*` (不区分大小写的正则表达式不匹配) : [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `!~*` (不区分大小写的正则表达式不匹配) : [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `->>` (将 JSON/JSONB 元素提取为文本) : [sub-column syntax](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns) * `->` (提取 JSON/JSONB) : [toJSONString](/zh/reference/functions/regular-functions/json-functions#toJSONString) + [sub-column syntax](/zh/reference/data-types/newjson#reading-json-paths-as-sub-columns)
### 自定义函数
这些由 `pg_clickhouse` 创建的自定义函数，可为部分在 PostgreSQL 中没有对应实现的 ClickHouse 函数提供外部查询下推。如果其中任何函数无法下推，则会引发异常。 * [dictGet](/zh/reference/functions/regular-functions/ext-dict-functions#dictget-dictgetordefault-dictgetornull)
### 扩展下推
pg\_clickhouse 可识别部分核心扩展和第三方扩展中的函数，并将其下推为 ClickHouse 中对应的等效函数。
#### re2
所有 [re2 extension] 函数都可 1:1 下推到 ClickHouse： * `re2match` → [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `re2extract` → [extract](/zh/reference/functions/regular-functions/string-search-functions#extract) * `re2extractall` → [extractAll](/zh/reference/functions/regular-functions/string-search-functions#extractAll) * `re2regexpextract` → [regexpExtract](/zh/reference/functions/regular-functions/string-search-functions#regexpExtract) * `re2extractgroups` → [extractGroups](/zh/reference/functions/regular-functions/string-search-functions#extractGroups) * `re2replaceregexpone` → [replaceRegexpOne](/zh/reference/functions/regular-functions/string-replace-functions#replaceRegexpOne) * `re2replaceregexpall` → [replaceRegexpAll](/zh/reference/functions/regular-functions/string-replace-functions#replaceRegexpAll) * `re2countmatches` → [countMatches](/zh/reference/functions/regular-functions/string-search-functions#countMatches) * `re2countmatchescaseinsensitive` → [countMatchesCaseInsensitive](/zh/reference/functions/regular-functions/string-search-functions#countMatchesCaseInsensitive) * `re2multimatchany` → [multiMatchAny](/zh/reference/functions/regular-functions/string-search-functions#multiMatchAny) * `re2multimatchanyindex` → [multiMatchAnyIndex](/zh/reference/functions/regular-functions/string-search-functions#multiMatchAnyIndex) * `re2multimatchallindices` → [multiMatchAllIndices](/zh/reference/functions/regular-functions/string-search-functions#multiMatchAllIndices)
#### intarray
有一个 [intarray] 函数可下推至 ClickHouse： * `idx` → [indexOf](/zh/reference/functions/regular-functions/array-functions#indexOf)
#### fuzzystrmatch
两个 [fuzzystrmatch] 函数可下推至 ClickHouse： * `soundex`：[soundex](/zh/reference/functions/regular-functions/string-functions#soundex) * `levenshtein` (2 参数) ：[editDistanceUTF8](/zh/reference/functions/regular-functions/string-functions#editDistanceUTF8)
### 下推类型转换
对于兼容的 数据类型，pg\_clickhouse 会下推 `CAST(x AS bigint)` 这类类型转换。 对于不兼容的类型，下推会失败；例如，如果此示例中的 `x` 是 ClickHouse 的 `UInt64`， ClickHouse 会拒绝执行该类型转换。 为了将类型转换下推到不兼容的 数据类型，pg\_clickhouse 提供了 以下函数。如果这些函数没有被下推，就会在 PostgreSQL 中引发异常。 * [toUInt8](/zh/reference/functions/regular-functions/type-conversion-functions#touint8) * [toUInt16](/zh/reference/functions/regular-functions/type-conversion-functions#touint16) * [toUInt32](/zh/reference/functions/regular-functions/type-conversion-functions#touint32) * [toUInt64](/zh/reference/functions/regular-functions/type-conversion-functions#touint64) * [toUInt128](/zh/reference/functions/regular-functions/type-conversion-functions#touint128)
### 聚合函数下推
以下 PostgreSQL 聚合函数可下推到 ClickHouse。 * [array\_agg](/zh/reference/functions/aggregate-functions/groupArray) * [avg](/zh/reference/functions/aggregate-functions/avg) * [bit\_and](/zh/reference/functions/aggregate-functions/groupBitAnd) * [bit\_or](/zh/reference/functions/aggregate-functions/groupBitOr) * [bit\_xor](/zh/reference/functions/aggregate-functions/groupBitXor) * [bool\_and / every](/zh/reference/functions/aggregate-functions/groupBitAnd) * [bool\_or](/zh/reference/functions/aggregate-functions/groupBitOr) * [count](/zh/reference/functions/aggregate-functions/count) * [min](/zh/reference/functions/aggregate-functions/min) * [max](/zh/reference/functions/aggregate-functions/max) * [string\_agg](/zh/reference/functions/aggregate-functions/groupConcat) * [sum](/zh/reference/functions/aggregate-functions/sum)
### 自定义聚合函数
这些由 `pg_clickhouse` 创建的自定义聚合函数，可为部分在 PostgreSQL 中没有对应函数的 ClickHouse 聚合函数提供外部查询下推。如果这些函数中的任何一个无法下推， 就会引发异常。 * [argMax](/zh/reference/functions/aggregate-functions/argMax) * [argMin](/zh/reference/functions/aggregate-functions/argMin) * [uniq](/zh/reference/functions/aggregate-functions/uniq) * [uniqCombined](/zh/reference/functions/aggregate-functions/uniqCombined) * [uniqCombined64](/zh/reference/functions/aggregate-functions/uniqCombined64) * [uniqExact](/zh/reference/functions/aggregate-functions/uniqExact) * [uniqHLL12](/zh/reference/functions/aggregate-functions/uniqHLL12) * [uniqTheta](/zh/reference/functions/aggregate-functions/uniqthetasketch) * [quantile](/zh/reference/functions/aggregate-functions/quantile) * [quantileExact](/zh/reference/functions/aggregate-functions/quantileExact)
### 下推有序集合聚合函数
这些\[有序集合聚合函数]会映射为 ClickHouse 的\[参数化 聚合函数]：将其*直接参数*作为 parameter 传递，并将其 `ORDER BY` expression 作为 argument。例如，以下 PostgreSQL 查询： ```sql theme={null} SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1; ``` 对应的 ClickHouse 查询如下： ```sql theme={null} SELECT quantile(0.25)(a) FROM t1; ``` 请注意，不支持非默认的 `ORDER BY` 后缀 `DESC` 和 `NULLS FIRST`， 使用它们会报错。 * `percentile_cont(double)`: [quantile](/zh/reference/functions/aggregate-functions/quantile) * `quantile(double)`: [quantile](/zh/reference/functions/aggregate-functions/quantile) * `quantileExact(double)`: [quantileExact](/zh/reference/functions/aggregate-functions/quantileExact)
### 下推窗口函数
这些 PostgreSQL \[窗口函数] 可以通过 `OVER (PARTITION BY ... ORDER BY ...)` 子句下推到 ClickHouse；在适用情况下，也包括窗口帧规范。 * [row\_number](/zh/reference/functions/window-functions#row_number) * [rank](/zh/reference/functions/window-functions#rank) * [dense\_rank](/zh/reference/functions/window-functions#dense_rank) * [ntile](/zh/reference/functions/window-functions#ntile) * [cume\_dist](/zh/reference/functions/window-functions#cume_dist) * [percent\_rank](/zh/reference/functions/window-functions#percent_rank) * [lead](/zh/reference/functions/window-functions#lead) * [lag](/zh/reference/functions/window-functions#lag) * [first\_value](/zh/reference/functions/window-functions#first_value) * [last\_value](/zh/reference/functions/window-functions#last_value) * [nth\_value](/zh/reference/functions/window-functions#nth_value) * `min` / `max` (带 `OVER` 子句) 排名函数 (`row_number`、`rank`、`dense_rank`、`ntile`、`cume_dist`、 `percent_rank`) 在下推时会省略窗口帧子句，因为 ClickHouse 不接受这些函数的窗口帧规范。
## 兼容性说明
### 正则表达式
当 [pg\_clickhouse.pushdown\_regex](#pg_clickhousepushdown_regex) 为 true (默认值) 时， pg\_clickhouse 会将正则表达式下推为 ClickHouse 中的等效形式， 并尽力保证基本兼容性，但你仍需了解二者之间的差异，以及 pg\_clickhouse 如何处理这些差异。 * PostgreSQL 支持 [POSIX Regular Expressions]，而 ClickHouse 支持 [RE2 Regular Expressions][RE2]。请注意二者在行为上的差异：当正则表达式由 ClickHouse 计算时 (例如在 `WHERE` 子句中) ，请按 RE2 编写；当它由 Postgres 计算时 (例如在 `SELECT` 子句中) ，请按 POSIX 编写。 * pg\_clickhouse 会将 Postgres 的 \[Regex flags] 预置到 ClickHouse 正则表达式的 `(?)` 中，以实现下推。例如： ```sql theme={null} regexp_like(val, '^VAL\d', 'i') ``` 会变成 ```sql theme={null} 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()` 的 replacement 参数支持使用 `\&` 表示整个匹配结果，而 ClickHouse 则使用 `\0` 表示整个 匹配结果。函数下推到 ClickHouse 时，请务必使用 `\0`。 为避免任何歧义，建议设置 [pg\_clickhouse.pushdown\_regex](#pg_clickhousepushdown_regex) 以阻止 Postgres 正则表达式下推到 ClickHouse，并使用 [re2 extension]；对于该扩展，pg\_clickhouse 支持将 与 ClickHouse 兼容的 [RE2] 正则表达式进行[直接下推](#re2)。
### `to_char()`
PostgreSQL 中用于 `timestamp` 和 `timestamp with time zone` 的 [`to_char()`] 仅在 format argument 为非 NULL 的 string constant，且其中每个 PostgreSQL 关键字 在 ClickHouse 中都有一个逐字节完全一致的对应项时，才会下推到 ClickHouse [formatDateTime]。 如果 format 是动态的 (不是 `Const`) ，或者包含任何不支持的关键字或 modifier， 则该调用会回退到 PostgreSQL 本地求值——绝不会在部分翻译的情况下尝试下推， 因此输出会保持与 PG 兼容。 用于 `numeric`、`interval` 及其他非 timestamp 类型的双参数 `to_char()` 形式永远不会下推；ClickHouse [formatDateTime] 仅用于格式化日期时间值。
#### 已转换的关键字
| PostgreSQL | ClickHouse | 含义 | | -------------------------- | ---------- | -------------------- | | `YYYY`, `yyyy` | `%Y` | 4 位年份 | | `YY`, `yy` | `%y` | 2 位年份 | | `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](https://justatheory.com/) 版权所有 (c) 2025-2026，ClickHouse [foreign data wrapper]: https://www.postgresql.org/docs/current/fdwhandler.html "PostgreSQL 文档：编写外部数据包装器" [Docker image]: https://github.com/ClickHouse/pg_clickhouse/pkgs/container/pg_clickhouse "Docker Hub 上的最新版本" [ClickHouse]: https://clickhouse.com/clickhouse [Semantic Versioning]: https://semver.org/spec/v2.0.0.html "语义化版本控制 2.0.0" [`pg_get_loaded_modules()`]: https://pgpedia.info/g/pg_get_loaded_modules.html "pgPedia：pg_get_loaded_modules()" [DDL]: https://en.wikipedia.org/wiki/Data_definition_language "维基百科：数据定义语言" [CREATE EXTENSION]: https://www.postgresql.org/docs/current/sql-createextension.html "PostgreSQL 文档：CREATE EXTENSION" [ALTER EXTENSION]: https://www.postgresql.org/docs/current/sql-alterextension.html "PostgreSQL 文档：ALTER EXTENSION" [DROP EXTENSION]: https://www.postgresql.org/docs/current/sql-dropextension.html "PostgreSQL 文档：DROP EXTENSION" [CREATE SERVER]: https://www.postgresql.org/docs/current/sql-createserver.html "PostgreSQL 文档：CREATE SERVER" [ALTER SERVER]: https://www.postgresql.org/docs/current/sql-alterserver.html "PostgreSQL 文档：ALTER SERVER" [DROP SERVER]: https://www.postgresql.org/docs/current/sql-dropserver.html "PostgreSQL 文档：DROP SERVER" [CREATE USER MAPPING]: https://www.postgresql.org/docs/current/sql-createusermapping.html "PostgreSQL 文档：CREATE USER MAPPING" [ALTER USER MAPPING]: https://www.postgresql.org/docs/current/sql-alterusermapping.html "PostgreSQL 文档：ALTER USER MAPPING" [DROP USER MAPPING]: https://www.postgresql.org/docs/current/sql-dropusermapping.html "PostgreSQL 文档：DROP USER MAPPING" [IMPORT FOREIGN SCHEMA]: https://www.postgresql.org/docs/current/sql-importforeignschema.html "PostgreSQL 文档：IMPORT FOREIGN SCHEMA" [CREATE FOREIGN TABLE]: https://www.postgresql.org/docs/current/sql-createforeigntable.html "PostgreSQL 文档：CREATE FOREIGN TABLE" [table engine]: /reference/engines/table-engines "ClickHouse 文档：表引擎" [AggregateFunction Type]: /reference/data-types/aggregatefunction "ClickHouse 文档：AggregateFunction 类型" [SimpleAggregateFunction Type]: /reference/data-types/simpleaggregatefunction "ClickHouse 文档：SimpleAggregateFunction 类型" [ALTER FOREIGN TABLE]: https://www.postgresql.org/docs/current/sql-alterforeigntable.html "PostgreSQL 文档：ALTER FOREIGN TABLE" [DROP FOREIGN TABLE]: https://www.postgresql.org/docs/current/sql-dropforeigntable.html "PostgreSQL 文档：DROP FOREIGN TABLE" [DML]: https://en.wikipedia.org/wiki/Data_manipulation_language "维基百科：数据操纵语言" [EXPLAIN]: https://www.postgresql.org/docs/current/sql-explain.html "PostgreSQL 文档：EXPLAIN" [SELECT]: https://www.postgresql.org/docs/current/sql-select.html "PostgreSQL 文档：SELECT" [PREPARE]: https://www.postgresql.org/docs/current/sql-prepare.html "PostgreSQL 文档：PREPARE" [EXECUTE]: https://www.postgresql.org/docs/current/sql-execute.html "PostgreSQL 文档：EXECUTE" [DEALLOCATE]: https://www.postgresql.org/docs/current/sql-deallocate.html "PostgreSQL 文档：DEALLOCATE" [PREPARE]: https://www.postgresql.org/docs/current/sql-prepare.html "PostgreSQL 文档：PREPARE" [INSERT]: https://www.postgresql.org/docs/current/sql-insert.html "PostgreSQL 文档：INSERT" [COPY]: https://www.postgresql.org/docs/current/sql-copy.html "PostgreSQL 文档：COPY" [LOAD]: https://www.postgresql.org/docs/current/sql-load.html "PostgreSQL 文档：LOAD" [SET]: https://www.postgresql.org/docs/current/sql-set.html "PostgreSQL 文档：SET" [ALTER ROLE]: https://www.postgresql.org/docs/current/sql-alterrole.html "PostgreSQL 文档：ALTER ROLE" [shared library preloading]: https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-PRELOAD "PostgreSQL 文档：共享库预加载" [ordered-set aggregate functions]: https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE [Parametric aggregate functions]: /reference/functions/aggregate-functions/parametric-functions [ClickHouse settings]: /reference/settings/session-settings "ClickHouse 文档：会话设置" [dollar quoting]: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING "PostgreSQL 文档：美元引号字符串常量" [PREPARE notes]: https://www.postgresql.org/docs/current/sql-prepare.html#SQL-PREPARE-NOTES "PostgreSQL 文档：PREPARE 说明" [query parameters]: /guides/clickhouse/data-modelling/stored-procedures-and-prepared-statements#alternatives-to-prepared-statements-in-clickhouse "ClickHouse 文档：ClickHouse 中预处理语句的替代方案" [underlying bug]: https://github.com/ClickHouse/ClickHouse/issues/85847 "ClickHouse/ClickHouse#85847 某些 multipart 表单中的查询无法读取设置" [fixed]: https://github.com/ClickHouse/ClickHouse/pull/85570 "ClickHouse/ClickHouse#85570 修复带 multipart 的 HTTP" [BYTEA]: https://www.postgresql.org/docs/current/datatype-binary.html "PostgreSQL 文档：二进制数据类型" [GRANT]: https://www.postgresql.org/docs/current/sql-grant.html "PostgreSQL 文档：GRANT" [String]: /reference/data-types/string "ClickHouse 文档：String" [TEXT]: https://www.postgresql.org/docs/current/datatype-character.html "PostgreSQL 文档：字符类型" [window functions]: https://www.postgresql.org/docs/current/functions-window.html "PostgreSQL 文档：窗口函数" [POSIX Regular Expressions]: https://www.postgresql.org/docs/18/functions-matching.html#FUNCTIONS-POSIX-REGEXP "PostgreSQL 文档：POSIX 正则表达式" [Postgres flags]: https://www.postgresql.org/docs/18/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE "PostgreSQL 文档：ARE 内嵌选项字母" [RE2]: https://github.com/google/re2/wiki/Syntax "RE2 语法" [re2 extension]: https://github.com/ClickHouse/pg_re2 "pg_re2：使用 RE2 的 ClickHouse 兼容正则函数" [intarray]: https://www.postgresql.org/docs/current/intarray.html "PostgreSQL 文档：intarray" [fuzzystrmatch]: https://www.postgresql.org/docs/current/fuzzystrmatch.html "PostgreSQL 文档：fuzzystrmatch" [`to_char()`]: https://www.postgresql.org/docs/current/functions-formatting.html "PostgreSQL 文档：数据类型格式化函数" [formatDateTime]: /reference/functions/regular-functions/date-time-functions#formatDateTime "ClickHouse 文档：formatDateTime"