> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-detect-table-modification.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.3 及以上版本。
## 快速开始
试用 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"。**必填。** * `compression`：binary driver 的 native 协议压缩，可选值为 "none"、"lz4" 或 "zstd"。默认为 "lz4"。http 驱动会忽略此选项。 * `dbname`：连接时使用的 ClickHouse 数据库。默认为 "default"。 * `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 * `min_tls_version`：对使用 TLS 的连接协商的最低 TLS 协议版本。 可选值为 `TLSv1`、`TLSv1.1`、`TLSv1.2` 或 `TLSv1.3`。默认值 为 TLS 库自身的最低版本。适用于两种驱动程序。 * `secure`：控制连接是否使用 TLS。可选值为： * `auto` (默认) ：当 `host` 是 ClickHouse Cloud 主机或 `port` 是安全端口时使用 TLS；否则使用 plaintext。 * `on` (或 `true`/`yes`/`1`) ：始终使用 TLS。默认 `port` 为 8443 ("http") 或 9440 ("binary") 。 * `off` (或 `false`/`no`/`0`) ：从不使用 TLS。默认 `port` 为 8123 ("http") 或 9000 ("binary") 。
### 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(sign)' ); ``` 支持的表选项如下： * `database`：远程 数据库 的名称。默认使用为 外部服务器 定义的 数据库。 * `table_name`：远程表的名称。默认使用为 foreign table 指定的名称。 * `engine`：ClickHouse 表使用的\[表引擎]。对于 `CollapsingMergeTree()` 和 `AggregatingMergeTree()`，pg\_clickhouse 会自动将参数应用于在该表上执行的函数表达式。 对于每一列，请使用与远程 ClickHouse 数据 类型相对应的[数据类型](#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 行。
### 分区表
PostgreSQL \[分区表]可以同时包含本地分区和由 ClickHouse 支持的外部分区。常见的布局是将较早的数据迁移到 ClickHouse，而较新的数据保留在 PostgreSQL 中： ```sql theme={null} CREATE TABLE events (id int, ts date, val int, amt float8) PARTITION BY RANGE (ts); -- 2023 data lives on ClickHouse CREATE FOREIGN TABLE events_2023 PARTITION OF events FOR VALUES FROM ('2023-01-01') TO ('2024-01-01') SERVER ch_svr OPTIONS (table_name 'events'); -- 2024 data stays local CREATE TABLE events_2024 PARTITION OF events FOR VALUES FROM ('2024-01-01') TO ('2025-01-01'); ``` 有关如何将数据从本地分区迁移到外部分区的示例，请参阅 [offload-partition.sql]。 跨本地分区和外部分区的聚合需要启用 [partitionwise aggregation]，而 PostgreSQL 默认将其禁用： ```pgsql theme={null} SET enable_partitionwise_aggregate = on; ``` 启用 `enable_partitionwise_aggregate` 后，PostgreSQL 会在 `Append` 下方计算*部分聚合*，再由上方的*最终聚合*将这些部分结果合并为最终结果。pg\_clickhouse 会将外部分区的部分聚合下推至 ClickHouse： ```pgsql theme={null} try=# EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*), sum(val), min(ts), max(ts) FROM events; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate Output: count(*), sum(events.val), min(events.ts), max(events.ts) -> Append -> Foreign Scan Output: (PARTIAL count(*)), (PARTIAL sum(events.val)), (PARTIAL min(events.ts)), (PARTIAL max(events.ts)) Relations: Aggregate on (events_2023 events) Remote SQL: SELECT count(*), sum(val), min(ts), max(ts) FROM "default".events -> Partial Aggregate Output: PARTIAL count(*), PARTIAL sum(events_1.val), PARTIAL min(events_1.ts), PARTIAL max(events_1.ts) -> Seq Scan on public.events_2024 events_1 Output: events_1.val, events_1.ts ```
#### 部分聚合何时可以下推
PostgreSQL 将部分聚合表示为*转换状态*，并由最终化步骤在各分区间合并。只有当 pg\_clickhouse 能将分区的部分聚合表示为 ClickHouse 值时，才能将其下推： * **可分解聚合函数**的转换状态本身就是最终值，因此可以直接下推：`count`、`sum`、`min`、`max`、`bool_and`/`every`、`bool_or`、`bit_and`、`bit_or` 和 `bit_xor`。 * **作用于整数的 `avg`** 会将其 `{count, sum}` 状态作为数组下推。 * **作用于浮点数的 `avg`、`var_pop`、`var_samp`、`stddev_pop` 和 `stddev_samp`** 会将其 `{N, sum, squared deviations 的总和}` 状态作为数组下推。 `FILTER (WHERE …)` 也可以与这些聚合函数一起下推。
#### 何时回退
转换状态为 PostgreSQL 不透明 `internal` 类型的聚合没有可移植的表示形式，因此外部分区会转而拉取其行，并在本地进行聚合。这包括所有基于 `numeric` 的聚合，以及 `avg(bigint)` 和 `avg(interval)`。`DISTINCT`、有序集聚合和可变参数聚合也会回退。
### 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, transform_null_in 0 ``` 将其设为空字符串即可恢复为 ClickHouse server 的设置 — 但请注意，下推的正确性取决于其中某些默认值： OUTER JOIN 的 `join_use_nulls` 以及 `IN` 家族的 `transform_null_in` (请参阅 [IN 和 NULL 语义](#in-and-null-semantics)) 。 ```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 | | 任何列也可读取为 `text`、`varchar` 或其他 String 类型。该值 会采用上述 PostgreSQL 类型，然后通过该类型的输出 函数进行呈现。超过 bigint 最大值的 UInt64 值仍会报错，因此请使用 ClickHouse `toString()` 函数呈现这些值。 更多说明和细节如下。
### BYTEA
ClickHouse 不提供与 PostgreSQL [BYTEA] 类型等效的类型，但允许将任意字节存储在 [String] 类型中。一般情况下，ClickHouse 字符串应映射到 PostgreSQL 的 [TEXT] 类型；若需使用二进制数据，则应映射到 [BYTEA]。示例： ```sql theme={null} -- Create ClickHouse table with String columns. CALL clickhouse_perform('ch_srv', $$ 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` 查询将输出： ```pgsql theme={null} c1 | c2 | c3 ----+------------------------------------------------------------+------------------------------------ 1 | \x1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | \xae3b28cde02542f81acce8783245430d 2 | \x5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | \x23e7c6cacb8383f878ad093b0027d72b 3 | \x53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | \x7e969132fc656148b97b6a2ee8bc83c1 4 | \x4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | \x8ef30f44c65480d12b650ab6b2b04245 (4 rows) ``` 请注意，如果 ClickHouse 列中存在空字节，使用 [TEXT] 列的外部表将无法输出正确的值： ```sql theme={null} -- 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; ``` 输出结果： ```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} -- 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; ``` 文本列将是正确的： ```pgsql theme={null} c1 | encode | encode ----+----------------------------------------------------------+---------------------------------- 1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d 2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b0027d72b 3 | 53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | 7e969132fc656148b97b6a2ee8bc83c1 4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245 (4 rows) ``` 但如果按 [BYTEA] 读取，则不会： ```pgsql theme={null} # SELECT * FROM bytes; c1 | c2 | c3 ----+------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------ 1 | \x5c783162663766306363383231643331313738363136613535613865306335323637373733353339376364646536663431353361396664336437 | \x5c786165336232386364653032353432663831616363653837383332343534333064 2 | \x5c783566366539653132636438353932373132653633383031366634623161326537333233306565343064623439386330663062316463383431 | \x5c783233653763366361636238333833663837386164303933623030323764373262 3 | \x5c783533616332633166613833633866363436303366653935363864383833333331303037643632383164653333306134623565373238663965 | \x5c783765393639313332666336353631343862393762366132656538626338336331 4 | \x5c783465336332653463623735343261343531373361386461633933396464633462633735323032653334326562633736396230663564613266 | \x5c783865663330663434633635343830643132623635306162366232623034323435 (4 rows) ``` 一般来说，[TEXT] 列只用于编码字符串，[BYTEA] 列只用于二进制数据， 并且绝不要在两者之间切换。
## 函数与 operator 参考
### 函数
这些函数提供了查询 ClickHouse 数据库的接口。
#### `clickhouse_raw_query`
已弃用：`clickhouse_raw_query()` 会发出弃用 Warning，并将在下一个 release 中 移除。请使用 [`clickhouse_query`](#clickhouse_query) 读取行，并使用 [`clickhouse_perform`](#clickhouse_perform) 执行 不返回任何内容的语句。两者都会复用已配置 server 的 driver、credentials、 database 和 连接缓存，而非使用临时 connection string。 ```sql theme={null} SELECT clickhouse_raw_query( 'CREATE TABLE t1 (x String) ENGINE = Memory', 'host=localhost port=8123' ); ``` 连接到 ClickHouse 服务，执行单个 查询，然后断开连接。可选的第二个参数用于指定连接 字符串，默认为 `host=localhost port=8123`。支持的连接 参数如下： * `driver`：要使用的连接驱动程序，可以是 "http" 或 "binary"；默认为 "http" * `host`：要连接到的主机；必填。 * `port`：要连接的端口。对于 "http" 驱动程序，默认为 `8123`；对于 "binary" 驱动程序，默认为 `9000`。当 `host` 是 ClickHouse Cloud 主机时， 则分别切换为 `8443` 或 `9440` * `dbname`：要连接到的数据库名称。 * `username`：连接时使用的用户名；默认为 `default` * `password`：用于身份验证的密码；默认无密码 两种驱动程序都返回制表符分隔的行 (NULL 值为 `\N`) ，但各值的 表示形式不同："http" 驱动程序会原样返回 ClickHouse 自有的 TSV 格式，而 "binary" 驱动程序会将每个值传递给其 PostgreSQL 输出函数。 默认情况下，没有任何角色具有对此函数的 `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) ```
#### `clickhouse_server_version`
```sql theme={null} SELECT clickhouse_server_version('taxi_srv'); ``` 报告指定 外部服务器 的 ClickHouse 服务器版本，格式为 `major.minor.patch`。如有必要，将使用该服务器的选项和当前用户的 user mapping 进行连接： ```sql theme={null} clickhouse_server_version --------------------------- 25.8.1 (1 row) ``` 从原生协议连接的握手过程中读取版本信息，或通过 HTTP 执行单个 `SELECT version()` 查询获取版本信息，并在连接的整个生命周期内缓存该信息。
#### `clickhouse_query`
```sql theme={null} SELECT * FROM clickhouse_query( 'server', 'SELECT id, name, salary FROM remote_table WHERE salary > 50000' ) AS ch(id int, name text, salary numeric); ``` 针对已配置的外部服务器执行查询，并将返回的 行作为关系返回，同时将每个 ClickHouse 结果列映射为列定义列表中 指定的 PostgreSQL 类型。它会复用该服务器的 `driver`、 凭据、数据库和连接缓存。 第一个参数是通过 [CREATE SERVER] 创建的服务器名称。必须提供 列定义列表 (`AS name(col type, ...)`) ：PostgreSQL 在拉取行之前需要知道 结果形态，且该列表必须与查询返回的列相匹配。值会以与外部表列相同的方式 从 ClickHouse 转换为声明的类型。不返回结果的语句 (如 DDL) 无需声明任何内容；请改用 [`clickhouse_perform`](#clickhouse_perform) 执行它们。 默认情况下，任何角色都没有 `EXECUTE` 权限；请向角色 `GRANT` 权限，以允许其使用 该函数。 ```sql theme={null} GRANT EXECUTE ON FUNCTION clickhouse_query(text, text) TO ch_admin; ```
#### `clickhouse_perform`
```sql theme={null} CALL clickhouse_perform( 'server', 'CREATE TABLE remote_table (id Int32) ENGINE = MergeTree ORDER BY id' ); ``` 对已配置的 外部服务器 执行一条语句，并丢弃 所有结果。适用于不返回行的语句，例如 DDL；此时 [`clickhouse_query`](#clickhouse_query) 没有可声明的结果形态。它以与 `clickhouse_query` 相同的方式 解析 server，并复用其 `driver`、credentials、database 和 连接缓存。 作为存储过程，必须通过 [CALL] 而非 `SELECT` 调用，并且不返回 行。默认情况下，没有任何角色拥有 `EXECUTE` 权限；向角色授予 `GRANT` 权限即可允许其 使用该存储过程。 ```sql theme={null} GRANT EXECUTE ON PROCEDURE clickhouse_perform(text, text) TO ch_admin; ```
### 下推函数
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)，配合 [nullIf](reference/functions/regular-functions/functions-for-nulls#nullif) 将 `0` 转换为 `NULL`；如果存在用于指定搜索起始索引的第三个参数，则使用 [arraySlice](reference/functions/regular-functions/array-functions#arraySlice)。 请注意，`nan` 当前[不会匹配](https://github.com/ClickHouse/ClickHouse/issues/113169) * `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) * `cardinality`：[length](reference/functions/regular-functions/array-functions#length) * `array_length(array, 1)`：`nullIf(length(array), 0)` * `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`: [trimLeft](/zh/reference/functions/regular-functions/string-functions#trimLeft) * `rtrim`: [trimRight](/zh/reference/functions/regular-functions/string-functions#trimRight) * `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_match`: [extractGroups](/zh/reference/functions/regular-functions/string-search-functions#extractGroups) 如果正则表达式包含带括号的子表达式；否则为 [extractAll](/zh/reference/functions/regular-functions/string-search-functions#extractAll) 再使用 [arraySlice](/zh/reference/functions/regular-functions/array-functions#arraySlice) 切片。 * `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) * 当 `fmt` 为字符串常量 (不区分大小写) 时的 `encode(bytea, fmt)`： * `encode(bytea, 'hex')`：[hex](/zh/reference/functions/regular-functions/encoding-functions#hex) 再套用 [lower](/zh/reference/functions/regular-functions/string-functions#lower)， 因为 PostgreSQL 会输出小写 hex。 * `encode(bytea, 'base64')`：[base64Encode](/zh/reference/functions/regular-functions/encoding-functions#base64encode) 再套用 [replaceRegexpAll](/zh/reference/functions/regular-functions/string-replace-functions#replaceRegexpAll)， 以复现 PostgreSQL's MIME (RFC 2045) 每 76 个字符换行的规则。 * `encode(bytea, 'base64url')` (PostgreSQL 19+)：[base64URLEncode](/zh/reference/functions/regular-functions/encoding-functions#base64urlencode)， 与 PostgreSQL's 不带 padding 的 RFC 4648 URL 字母表一致。 * `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)`: [toDateTime64](/zh/reference/functions/regular-functions/type-conversion-functions#todatetime64) * `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)
### IN 和 NULL 语义
ClickHouse 对 `IN` 采用二值逻辑：当探测值未找到 匹配项时，即使涉及 NULL，也会返回 `0`，而 PostgreSQL 则会计算为 NULL。为保留 PostgreSQL 语义，pg\_clickhouse 会无条件下推针对常量列表或数组的 `IN` 系列操作 (`IN`、`NOT IN`、`= ANY`、`= ALL`、 `<> ANY`、`<> ALL`) ：若能够证明探测值和数组元素均不可能为 NULL，则使用原生或开销较低的形式； 否则使用带保护条件的 `CASE` 形式，在运行时检查 NULL 值， 从而在所有上下文中准确计算 PostgreSQL 的三值结果 (TRUE、FALSE、NULL) ， 包括 `SELECT` 列表或 `GROUP BY` 等值位置。 针对可空列的 `NOT IN (SELECT ...)` 过滤器也会下推， 并在反解析时加入补偿性保护条件，以保持 PostgreSQL 的行为：包含 NULL 的集合会排除 所有行，而 NULL 探测值仅会在集合为空时通过。当 `NOT NULL` 声明 证明某个保护条件并非必需时，该条件会被省略。与上述数组形式不同，此保护条件仅 适用于普通过滤条件 (或位于 `NOT` 之下) ；我们仍不会下推 值位置中的 `IN (SELECT ...)`，也不会下推已分组或聚合的子查询 主体。将列声明为 `NOT NULL` 可使系统采用开销更低的无保护形式，从而最大化 下推；对于非 `Nullable` 的 ClickHouse 列，[IMPORT FOREIGN SCHEMA] 会自动执行此操作。 该推断基于非 NULL 常量、`NOT NULL` 列，以及对它们执行的基本算术运算 (`+`、`-`、`*`、一元 `-`) 。 这些规则假定 ClickHouse 的默认设置为 `transform_null_in = 0`。pg\_clickhouse 会通过 [`pg_clickhouse.session_settings`](#pg_clickhousesession_settings) 参数的默认值， 在每个查询中设置该值， 以防止 ClickHouse server profile 对其进行静默更改。设置 `transform_null_in = 1` 会破坏所有已下推 `IN` 的语义。
### 自定义函数
这些由 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： * `@~` → [match](/zh/reference/functions/regular-functions/string-search-functions#match) * `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。 * [any\_value](/zh/reference/functions/aggregate-functions/any) * [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) * [corr](/zh/reference/functions/aggregate-functions/corr) * [covarpop](/zh/reference/functions/aggregate-functions/covarPop) * [covarsamp](/zh/reference/functions/aggregate-functions/covarSamp) * [min](/zh/reference/functions/aggregate-functions/min) * [max](/zh/reference/functions/aggregate-functions/max) * [stddev\_pop](/zh/reference/functions/aggregate-functions/stddevPop) * [stddev\_samp / stddev](/zh/reference/functions/aggregate-functions/stddevSamp) * [string\_agg](/zh/reference/functions/aggregate-functions/groupConcat) * [sum](/zh/reference/functions/aggregate-functions/sum) * [var\_op](/zh/reference/functions/aggregate-functions/varPop) * [var\_samp /variance](/zh/reference/functions/aggregate-functions/varSamp)
### 自定义聚合函数
这些由 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) * `percentile_cont(double[])`: [quantiles](https://clickhouse.com/docs/reference/functions/aggregate-functions/quantiles) * `percentile_disc(double)`: [quantileExactLow](https://clickhouse.com/docs/reference/functions/aggregate-functions/quantileExactLow) * `percentile_disc(double[])`: [quantilesExactLow](https://clickhouse.com/docs/reference/functions/aggregate-functions/quantilesExactLow)
### 自定义有序集合聚合函数
这些由 pg\_clickhouse 创建的自定义[有序集合聚合函数]为部分 ClickHouse \[参数化聚合 函数]提供外部查询下推。若其中任何函数无法下推，将引发异常。 * `quantile(double)`: [quantile](/zh/reference/functions/aggregate-functions/quantile) * `quantileExact(double)`: [quantileExact](/zh/reference/functions/aggregate-functions/quantileExact)
### 自定义有序集合聚合函数
这些由 pg\_clickhouse 创建的自定义[有序集合聚合函数]可为特定的 ClickHouse [参数化聚合函数]提供外部查询下推。如果其中任何函数无法下推，则会引发异常。 * [quantile](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantile) * [quantileExact](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantileexact)
### 下推窗口函数
以下 PostgreSQL \[窗口函数]可通过包含 `OVER (PARTITION BY ... ORDER BY ...)` 子句的查询下推到 ClickHouse；适用时还包括窗口帧规范。 * [row\_number](https://clickhouse.com/docs/reference/functions/window-functions/row_number) * [rank](https://clickhouse.com/docs/reference/functions/window-functions/rank) * [dense\_rank](https://clickhouse.com/docs/reference/functions/window-functions/dense_rank) * [ntile](https://clickhouse.com/docs/reference/functions/window-functions/ntile) * [cume\_dist](https://clickhouse.com/docs/reference/functions/window-functions/cume_dist) * [percent\_rank](https://clickhouse.com/docs/reference/functions/window-functions/percent_rank) * [lead](https://clickhouse.com/docs/reference/functions/window-functions/lead) * [lag](https://clickhouse.com/docs/reference/functions/window-functions/lag) * [first\_value](https://clickhouse.com/docs/reference/functions/window-functions/first_value) * [last\_value](https://clickhouse.com/docs/reference/functions/window-functions/last_value) * [nth\_value](https://clickhouse.com/docs/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 正则表达式]，而 ClickHouse 支持 [RE2 正则表达式][RE2]。请注意两者的行为差异：当正则表达式由 ClickHouse 求值时 (例如，在 `WHERE` 子句中) ，请使用 RE2；当它由 Postgres 求值时 (例如，在 `SELECT` 子句中) ，请使用 POSIX。 * pg\_clickhouse 通过将 \[Postgres 标志] 置于 ClickHouse 正则表达式前面并放入 `(?)` 中，来实现下推。例如： ```sql theme={null} regexp_like(val, '^VAL\d', 'i') ``` 变成 ```sql theme={null} match(val, concat('(?i)', '^VAL\\d')) ``` * 两者唯一同时支持、因此可在由 ClickHouse 求值时使用的标志有： | 标志 | 对应 | 说明 | | --- | ----- | ------------------------------- | | `i` | `i` | 不区分大小写匹配 | | `m` | `m-s` | `^` 和 `$` 除了匹配文本开头/结尾外，也匹配行首/行尾 | | `n` | `m-s` | `m` 的 Postgres 别名 | | `p` | `-s` | 不允许 `.` 和 `[^x]` 匹配 `\n` | | `s` | `s` | 允许 `.` 和 `[^x]` 匹配 `\n` | | `t` | | 紧凑语法，会被忽略 | | `w` | `m` | 部分换行敏感匹配的反向形式 | RE2 仅支持这些标志；请勿使用任何其他 \[Postgres 标志]。 * 下表概述了各种标志 (以及不加标志的情况，此时 与 `s` 相同) 在匹配换行符和行尾时的影响。请注意，在 Postgres 中，`m` 和 `p` 会阻止否定字符类 (`[^xyz]`) 匹配 换行符，而 ClickHouse 中的对应标志则不会。除此之外， ClickHouse 的行为与 Postgres 相同： | 应用于 `a\nb` 的模式 | Postgres | ClickHouse | 一致？ | | -------------- | :------: | :--------: | :-: | | `a.b` | true | true | ✔︎ | | `a[^x]b` | true | true | ✔︎ | | `a$` | false | false | ✔︎ | | **`s` 标志** | | | | | `(?s)a.b` | true | true | ✔︎ | | `(?s)a[^x]b` | true | true | ✔︎ | | `(?s)a$` | false | false | ✔︎ | | **`m` 标志** | | | | | `(?m)a.b` | false | false | ✔︎ | | `(?m)a[^x]b` | true | false | ✘ | | `(?m)a$` | true | true | ✔︎ | | **`p` 标志** | | | | | `(?p)a.b` | false | false | ✔︎ | | `(?p)a[^x]b` | true | false | ✘ | | `(?p)a$` | false | false | ✔︎ | | **`w` 标志** | | | | | `(?w)a.b` | true | true | ✔ | | `(?w)a[^x]b` | true | true | ✔ | | `(?w)a$` | true | true | ✔ | * 传递给正则表达式函数的任何其他标志都会阻止 该函数被下推。 * 例外是 `regexp_replace()`，它还支持 `g` 标志。当 设置 `g` 时，pg\_clickhouse 会使用 `replaceRegexpAll()` 而不是 `replaceRegexpOne()`，并会先移除该标志，再添加其他标志。 * Postgres 的 `regexp_replace()` 的 replacement 参数支持使用 `\&` 来 引用整个匹配；而在 ClickHouse 中，则使用 `\0` 表示整个 匹配。该函数下推到 ClickHouse 时，务必使用 `\0`。 * 当没有匹配项时，Postgres `regexp_match` 会返回 `NULL`，而 它下推执行的表达式则会返回空数组。请使用 `COALESCE()` 返回空数组而不是 `NULL`，以便以兼容的方式比较返回值。 例如： ```sql theme={null} SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}'); ``` 为避免任何歧义，建议设置 [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" [CALL]: https://www.postgresql.org/docs/current/sql-call.html "PostgreSQL 文档：CALL" [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/index "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" [partitioned table]: https://www.postgresql.org/docs/current/ddl-partitioning.html "PostgreSQL 文档：表分区" [partitionwise aggregation]: https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-ENABLE-PARTITIONWISE-AGGREGATE "PostgreSQL 文档：enable_partitionwise_aggregate" [offload-partition.sql]: https://github.com/ClickHouse/pg_clickhouse/blob/main/doc/offload-partition.sql [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" [共享库预加载]: https://www.postgresql.org/docs/current/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-PRELOAD "PostgreSQL 文档：共享库预加载" [有序集合聚合函数]: https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE [参数化聚合函数]: /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/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP "PostgreSQL 文档：POSIX 正则表达式" [Postgres flags]: https://www.postgresql.org/docs/current/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"