pg_clickhouse リファレンス

pg_clickhouse の完全なリファレンス

​

説明

pg_clickhouse は、foreign data wrapper を含む、ClickHouse データベースに対する リモートクエリ実行を可能にする PostgreSQL 拡張機能です。PostgreSQL 13 以降と ClickHouse 23 以降をサポートしています。

​

はじめに

pg_clickhouse を試す最も簡単な方法は Docker image を使うことです。これには、 pg_clickhouse と re2 拡張機能を含む標準の PostgreSQL Docker イメージが含まれています:

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

ClickHouseテーブルのインポートやクエリのプッシュダウンを始めるには、チュートリアルを参照してください。

​

使い方

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

​

バージョニングポリシー

pg_clickhouse は、公開リリースにおいて Semantic Versioning に従います。

• API の変更時にはメジャーバージョンが増加します
• 後方互換性のある SQL の変更時にはマイナーバージョンが増加します
• binary のみの変更時にはパッチバージョンが増加します

インストールされると、PostgreSQL は 2 種類のバージョンを管理します。

• ライブラリバージョン (PostgreSQL 18 以降では PG_MODULE_MAGIC によって定義) には完全なセマンティックバージョンが含まれており、 pgch_version() 関数の出力または Postgres の pg_get_loaded_modules() 関数で確認できます。
• 拡張機能バージョン (control file で定義) にはメジャー バージョンとマイナーバージョンのみが含まれており、pg_catalog.pg_extension テーブル、 pg_available_extension_versions() 関数の出力、および \dx pg_clickhouse で確認できます。

実際には、これはたとえば v0.1.0 から v0.1.1 へのようにパッチバージョンが増加する release では、v0.1 を読み込んでいるすべての database がその恩恵を受けられ、アップグレードを反映するために ALTER EXTENSION を実行する必要がないことを意味します。

一方、マイナーバージョンまたはメジャーバージョンが増加する release には SQL アップグレードスクリプトが付属し、拡張機能を含む既存のすべての database では、 アップグレードを反映するために ALTER EXTENSION pg_clickhouse UPDATE を実行する必要があります。

​

DDL SQL リファレンス

以下の SQL DDL 文では、pg_clickhouse を使用します。

​

CREATE EXTENSION

CREATE EXTENSION を使用して、データベースに pg_clickhouse を追加します。

CREATE EXTENSION pg_clickhouse;

特定のスキーマにインストールするには、WITH SCHEMA を使用します (推奨) :

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

​

ALTER EXTENSION

pg_clickhouse を変更するには、ALTER EXTENSION を使用します。例:

• pg_clickhouse の新しい release をインストールした後は、UPDATE 句を使用します。

  ALTER EXTENSION pg_clickhouse UPDATE;
  

• 拡張機能を新しいスキーマに移動するには、SET SCHEMA を使用します。

  CREATE SCHEMA ch;
  ALTER EXTENSION pg_clickhouse SET SCHEMA ch;
  

​

DROP EXTENSION

データベースから pg_clickhouse を削除するには、DROP EXTENSION を使用します。

DROP EXTENSION pg_clickhouse;

pg_clickhouse に依存するオブジェクトがある場合、このコマンドは失敗します。これらも削除するには、 CASCADE 句を使用してください:

DROP EXTENSION pg_clickhouse CASCADE;

​

CREATE SERVER

ClickHouseサーバーに接続する外部サーバーを作成するには、CREATE SERVER を使用します。例:

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

サポートされるオプションは次のとおりです。

• driver: 使用する ClickHouse 接続ドライバーです。“binary” または “http” のいずれかを指定します。必須です。
• compression: バイナリドライバー用のネイティブプロトコル圧縮です。“none”、 “lz4”、“zstd” のいずれかです。デフォルトは “lz4” です。“http” ドライバーでは 無視されます。
• dbname: 接続時に使用する ClickHouse データベースです。デフォルトは “default” です。
• fetch_size: HTTP streaming の、おおよそのバッチサイズ (バイト単位) です。バッチは 行の境界で分割されます。デフォルトは 50000000 (50 MB) です。0 を指定すると ストリーミングが無効になり、レスポンス全体がバッファリングされます。外部テーブルはこの 値を上書きできます。
• host: ClickHouse サーバーのホスト名です。デフォルトは “localhost” です。
• port: 接続先の ClickHouse サーバーのポートです。デフォルトは 次のとおりです。
  • driver が “binary” で、host が ClickHouse Cloud ホストの場合は 9440
  • driver が “binary” で、host が ClickHouse Cloud ホストではない場合は 9004
  • driver が “http” で、host が ClickHouse Cloud ホストの場合は 8443
  • driver が “http” で、host が ClickHouse Cloud ホストではない場合は 8123
• min_tls_version: TLS を使用する接続でネゴシエートする最小 TLS プロトコルバージョンです。 TLSv1、TLSv1.1、TLSv1.2、TLSv1.3 のいずれかです。デフォルトは TLS ライブラリ自体の最小バージョンです。両方のドライバーに適用されます。
• secure: 接続で使用する TLS を制御します。次のいずれかです。
  • auto (デフォルト): host が ClickHouse Cloud ホストであるか、 port がセキュアポートの場合は TLS を使用し、それ以外では平文を使用します。
  • on (または true/yes/1): 常に TLS を使用します。デフォルトの port は 8443 (“http”) または 9440 (“binary”) です。
  • off (または false/no/0): TLS を使用しません。デフォルトの port は 8123 (“http”) または 9000 (“binary”) です.

​

ALTER SERVER

ALTER SERVER は外部サーバーを変更するために使用します。例:

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

オプションは、CREATE SERVER と同じです。

​

DROP SERVER

外部サーバー を削除するには、DROP SERVER を使用します。

DROP SERVER taxi_srv;

このコマンドは、他のオブジェクトがそのサーバーに依存している場合、失敗します。CASCADE を使用すると、 それらの依存オブジェクトも削除できます:

DROP SERVER taxi_srv CASCADE;

​

CREATE USER MAPPING

CREATE USER MAPPING を使用すると、PostgreSQL ユーザーを ClickHouse ユーザーにマッピングできます。たとえば、taxi_srv 外部サーバー を使って接続する際に、現在の PostgreSQL ユーザーをリモートの ClickHouse ユーザーにマッピングするには、次のようにします。

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

サポートされているオプションは以下のとおりです。

• user: ClickHouseユーザー名です。デフォルトは “default” です。
• password: ClickHouseユーザーのパスワードです。

​

ALTER USER MAPPING

ALTER USER MAPPING を使用して、ユーザーマッピングの定義を変更できます。

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

CREATE USER MAPPING と同じオプションを使用できます。

​

DROP USER MAPPING

ユーザーマッピングを削除するには、DROP USER MAPPING を使用します。

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

​

IMPORT FOREIGN SCHEMA

IMPORT FOREIGN SCHEMA を使用すると、ClickHouse データベースで定義されているすべてのテーブルを、外部テーブルとして PostgreSQL のスキーマにインポートできます。

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

LIMIT TO を使用して、インポート対象を特定のテーブルのみに限定します:

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

EXCEPT を使用してテーブルを除外します:

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

pg_clickhouse は、指定された ClickHouse データベース (上記の例では “demo”) 内のすべてのテーブルの一覧を取得し、各テーブルの カラム定義を取得したうえで、外部テーブルを作成するための CREATE FOREIGN TABLE コマンドを実行します。カラムは サポートされているデータ 型 を使用して定義され、検出可能な場合は CREATE FOREIGN TABLE でサポートされているオプションも使用されます。

インポートされた識別子の大文字・小文字の保持

IMPORT FOREIGN SCHEMA は、インポートするテーブル名とカラム 名に対して quote_identifier() を実行します。これにより、大文字を含む識別子 または空白を含む識別子は二重引用符で囲まれます。そのため、そのようなテーブル名とカラム名は PostgreSQL クエリ内でも二重引用符で囲む必要があります。すべて小文字で空白文字を含まない名前は、 引用符で囲む必要はありません。

たとえば、次の ClickHouse テーブルがあるとします。

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

IMPORT FOREIGN SCHEMA は、次の外部テーブルを作成します。

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

したがって、クエリでは適切に引用する必要があります。たとえば、

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

異なる名前のオブジェクトや、すべて小文字の (つまり 大文字・小文字を区別しない) 名前でオブジェクトを作成するには、CREATE FOREIGN TABLE を使用します。

​

CREATE FOREIGN TABLE

ClickHouseデータベースのデータをクエリできる外部テーブルを作成するには、CREATE FOREIGN TABLE を使用します。

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

サポートされているテーブルオプションは次のとおりです。

• database: リモートデータベースの名前です。デフォルトでは、外部サーバーに 定義されているデータベースが使用されます。
• fetch_size: HTTP streaming のおおよそのバッチサイズ (バイト単位) です。サーバーレベルの fetch_size を上書きします。デフォルトは 50000000 (50 MB) です。0 を指定すると streaming が無効になり、レスポンス全体がバッファされます。
• table_name: リモートテーブルの名前です。デフォルトでは、外部テーブルに 指定された名前が使用されます。
• engine: ClickHouse テーブルで使用される [テーブルエンジン] です。 CollapsingMergeTree() および AggregatingMergeTree() では、pg_clickhouse が テーブル上で実行される関数式に対して自動的にパラメーターを適用します。

各カラムには、リモートの ClickHouse の データ型 に対応するものを使用してください。サポートされているカラムオプションは次のとおりです。

• column_name: ClickHouse 側のカラム名です。クエリや INSERT のデパース時には、PostgreSQL の属性名よりもこちらが優先して使用されます。 これは、引用符なしの小文字の PostgreSQL カラム名を 大文字と小文字を区別する ClickHouse カラムにマッピングする場合に便利です。例:

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

• AggregateFunction: [AggregateFunction 型] カラムに適用される 集約関数の名前です。データ型を、その関数に渡される ClickHouse の型にマッピングし、適切なカラムオプションで 集約関数名を指定すると、pg_clickhouse が自動的に カラムを評価する集約関数に Merge を付加します。

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

• SimpleAggregateFunction: [SimpleAggregateFunction 型] カラムに適用される 集約関数の名前です。データ型を、その関数に渡される ClickHouse の型にマッピングし、適切なカラムオプションで 集約関数名を指定してください。

​

ALTER FOREIGN TABLE

外部テーブルの定義を変更するには、ALTER FOREIGN TABLE を使用します。

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

サポートされているテーブルおよびカラムのオプションは、CREATE FOREIGN TABLE と同じです。

​

DROP FOREIGN TABLE

外部テーブルを削除するには、DROP FOREIGN TABLEを使用します。

DROP FOREIGN TABLE acts;

このコマンドは、外部テーブルに依存するオブジェクトがある場合、失敗します。 それらも削除するには、CASCADE 句を使用します:

DROP FOREIGN TABLE acts CASCADE;

​

DML SQL リファレンス

以下の SQL DML 式では、pg_clickhouse を使用することがあります。例は、 以下の ClickHouse テーブルを前提としています:

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

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

​

EXPLAIN

EXPLAIN コマンドは想定どおりに動作しますが、VERBOSE オプションを指定すると ClickHouse の “Remote SQL” クエリが出力されます:

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

このクエリでは、“Foreign Scan” プランノードを介して、リモート SQL が ClickHouse にプッシュダウンされます。

​

SELECT

SELECTステートメントを使うと、他のテーブルと同様に pg_clickhouse テーブルに対してクエリを実行できます。

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

pg_clickhouse は、集約関数を含め、可能な限りクエリ実行を ClickHouse にプッシュダウンします。EXPLAIN を使用して、 どこまでプッシュダウンされているかを確認してください。たとえば、上記のクエリでは、実行はすべて ClickHouse にプッシュダウンされます

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

pg_clickhouse は、同じリモートサーバー上のテーブルに対する JOIN もプッシュダウンします。

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

ローカルテーブルとの JOIN は、注意深くチューニングしないと、 非効率なクエリを生成します。この例では、 nodes テーブルのローカルコピーを作成し、リモートテーブルの代わりに それと JOIN します。

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

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

この場合、ローカルのカラムではなく node_id でグループ化することで、 集約処理のより多くを ClickHouse 側に任せられ、その後で ルックアップテーブルと join できます。

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

この “Foreign Scan” ノードでは、node_id ごとの集約がプッシュダウンされるようになり、 Postgres に引き戻す必要のある行数が 1000 行 (全件) から 各ノード 1 行ずつのわずか 8 行まで削減されます。

​

PREPARE, EXECUTE, DEALLOCATE

v0.1.2以降、pg_clickhouse は主に PREPARE コマンドで作成される パラメータ付きクエリをサポートしています:

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

プリペアドステートメントを実行するには、通常どおり EXECUTE を使用します。

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

パラメータ化された実行では、[根本原因となっていたバグ] が [修正] される ClickHouse 25.8 より前のバージョンで、http ドライバー が DateTime のタイムゾーンを正しく変換できません。PostgreSQL では PREPARE を 使っていなくても、パラメータ化されたクエリプランが使用されることがある点に 注意してください。タイムゾーンの正確な変換が必要なクエリで、25.8 以降に アップグレードできない場合は、代わりに バイナリドライバー を 使用してください。

通常どおり、pg_clickhouse は集計をプッシュダウンします。これは EXPLAIN の詳細出力で確認できます：

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

完全な日付の値が送信されており、パラメータのプレースホルダーではないことに注意してください。 これは、PostgreSQL の [PREPARE に関する注意事項]で説明されているとおり、最初の 5 回のリクエストに当てはまります。6 回目の実行では、ClickHouse の {param:type} 形式の[クエリパラメータ]が送信されます。 パラメータ:

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

プリペアドステートメントを解放するには、DEALLOCATE を使用します:

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

​

INSERT

リモートのClickHouseテーブルに値を挿入するには、INSERT コマンドを使用します。

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

リモートの ClickHouse テーブルに複数の行をまとめて挿入するには、COPY コマンドを使用します。

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

⚠️ Batch API の制限

pg_clickhouse は、PostgreSQL FDW のバッチ insert API のサポートをまだ実装していません。そのため、現在 COPY はレコードを 挿入するために INSERT ステートメントを使用しています。これは今後のリリースで改善される予定です。

​

LOAD

pg_clickhouse 共有ライブラリを読み込むには、LOAD を使用します:

try=# LOAD 'pg_clickhouse';
LOAD

通常、LOAD を使う必要はありません。Postgres は、その機能 (関数、外部 テーブルなど) のいずれかが初めて使用されたときに、自動的に pg_clickhouse を読み込みます。

LOAD pg_clickhouse が役立つ可能性があるのは、SET で pg_clickhouse のパラメータを設定してから、それらに依存するクエリを 実行したい場合に限られます。

​

SET

SET を使用して、pg_clickhouse のカスタム設定パラメーターを設定します。

​

pg_clickhouse.session_settings

pg_clickhouse.session_settings パラメーターは、後続のクエリに適用する [ClickHouse 設定] を指定します。例:

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

デフォルトは join_use_nulls 1, group_by_use_nulls 1, final 1 です。ClickHouse server の設定に戻すには、これを 空文字列に設定します。

SET pg_clickhouse.session_settings = '';

構文は、1 つ以上のスペースで区切られたキー/値ペアのカンマ区切りリストです。キーは ClickHouse settings に対応している必要があります。値に含まれるスペース、カンマ、バックスラッシュは、バックスラッシュでエスケープします:

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

または、スペースやカンマをエスケープせずに済むよう、単一引用符で囲んだ値を使用します。二重引用符を使う必要がなくなるため、[ダラークォート] の使用も検討してください:

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

可読性を重視し、多くの設定を行う必要がある場合は、たとえば複数行で 記述します。

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

一部の設定は、pg_clickhouse 自体の動作に支障をきたす場合、無視されます。これには次のものが含まれます。

• date_time_output_format: http ドライバーではこれが “iso” である必要があります
• format_tsv_null_representation: http ドライバーではデフォルト値が必要です
• output_format_tsv_crlf_end_of_line http ドライバーではデフォルト値が必要です

それ以外については、pg_clickhouse は設定を検証せず、すべてのクエリごとに ClickHouse にそのまま渡します。したがって、各 ClickHouse バージョンですべての設定をサポートします。

pg_clickhouse.session_settings を設定する前に、 pg_clickhouse を読み込んでおく必要がある点に注意してください。[共有ライブラリのプリロード] を使用するか、 または拡張機能内のいずれかのオブジェクトを使って、確実に読み込まれるようにしてください。

​

pg_clickhouse.pushdown_regex

pg_clickhouse.pushdown_regex パラメータは、pg_clickhouse が 正規表現関数および演算子をプッシュダウンするかどうかを制御します。既定では プッシュダウンが有効です。プッシュダウンを無効にするには、このパラメータを false に設定します:

SET pg_clickhouse.pushdown_regex = 'false';

詳細については、正規表現を参照してください。

​

ALTER ROLE

ALTER ROLE’s SET コマンドを使用すると、pg_clickhouse をプリロードしたり、 特定のロールに対してそのパラメータをSETしたりできます。

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

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

ALTER ROLE の RESET コマンドを使用して、pg_clickhouse のプリロード設定 やパラメータをリセットします:

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

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

​

プリロード

すべて、またはほぼすべてのPostgres接続で pg_clickhouse を使用する必要がある場合は、 これを自動的に読み込むために、[共有ライブラリのプリロード]の使用を検討してください:

​

session_preload_libraries

PostgreSQL への新しい接続のたびに共有ライブラリを読み込みます:

session_preload_libraries = pg_clickhouse

サーバーを再起動せずに更新を反映するのに便利で、再接続するだけで済みます。ALTER ROLE を使って、特定のユーザーやロールに対して設定することもできます。

​

shared_preload_libraries

起動時に、共有ライブラリを PostgreSQL の親プロセスに読み込みます:

shared_preload_libraries = pg_clickhouse

各セッションごとのメモリ使用量と読み込み時のオーバーヘッドを抑えられますが、ライブラリの更新時には クラスターの再起動が必要です。

​

データ型

pg_clickhouse は、以下の ClickHouse データ型を PostgreSQL のデータ型にマッピングします。IMPORT FOREIGN SCHEMA では、カラムのインポート時に PostgreSQL カラム型として先頭の型が使用されます。追加の型は、CREATE FOREIGN TABLE ステートメントで使用できます。

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 にマッピングしてください。例：

-- 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クエリの出力は以下のとおりです：

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

ClickHouseのカラムにnulバイトが含まれている場合、TEXTカラムを使用する外部テーブルは正しい値を出力しないことに注意してください：

-- TEXTカラムを持つ外部テーブルを作成する。
CREATE FOREIGN TABLE texts (
    c1 int,
    c2 TEXT,
    c3 TEXT
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- バイナリデータを16進数にエンコードする。
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;

出力結果:

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

2行目と3行目の値が切り捨てられていることに注意してください。これは、PostgreSQLがnul終端文字列に依存しており、文字列内のnulをサポートしていないためです。

バイナリ値を TEXT カラムに挿入しようとすると、成功し、期待どおりに動作します：

-- テキストカラム経由で挿入:
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;

テキストのカラムは正しく表示されます：

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

しかし、BYTEAとしては読み取れません:

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

原則として、エンコードされた文字列には TEXT カラムのみを、バイナリデータには BYTEA カラム のみを使用し、両者を混在させないでください。

​

関数と演算子のリファレンス

​

関数

これらの関数は、ClickHouse データベースにクエリを実行するためのインターフェイスです。

​

clickhouse_raw_query

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

ClickHouse service の HTTP インターフェイス経由で接続し、単一の クエリを実行してから切断します。省略可能な 2 番目の引数では connection string を指定します。デフォルトは host=localhost port=8123 です。サポートされる connection parameter は次のとおりです。

• host: 接続先の host。必須です。
• port: 接続先の HTTP port。デフォルトは 8123 ですが、host が ClickHouse Cloud host の場合は 8443 になります
• dbname: 接続先の database 名。
• username: 接続に使用する username。デフォルトは default です
• password: 認証に使用する password。デフォルトでは password はありません

デフォルトでは、どのロールにもこの関数に対する EXECUTE 権限はありません。GRANTで、 アドホックな ClickHouse クエリを正当に実行する必要があるロールにのみ アクセスを付与することを検討してください。たとえば、専用の ClickHouse 管理者ロールです。

結果を返さないクエリに便利ですが、値を返すクエリの場合は 単一のテキスト値として返されます。

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

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

(1 row)

​

プッシュダウン関数

pg_clickhouse は、条件式 (HAVING 句および WHERE 句) で使用される PostgreSQL の組み込み関数の一部をプッシュダウンします。対応する関数は、ClickHouse では以下のとおりです。

• abs: abs
• factorial: factorial
• mod (int2/int4/int8/numeric): 剰余
• pow & power (float8/numeric): pow
• round: round
• sin, cos, tan, atan, atan2, sinh, cosh, tanh, asinh, degrees, radians, pi: ClickHouse の数学関数 と同名です。asin, acos, atanh, acosh はプッシュダウンされません。PG では範囲外の入力でエラーになりますが、CH では NaN が返されます。
• date_part:
  • date_part('day'): toDayOfMonth
  • date_part('doy'): toDayOfYear
  • date_part('dow'): toDayOfWeek
  • date_part('year'): toYear
  • date_part('month'): toMonth
  • date_part('hour'): toHour
  • date_part('minute'): toMinute
  • date_part('second'): toSecond
  • date_part('quarter'): toQuarter
  • date_part('isoyear'): toISOYear
  • date_part('week'): toISOYear
  • date_part('epoch'): toISOYear
• date_trunc:
  • date_trunc('week'): toMonday
  • date_trunc('second'): toStartOfSecond
  • date_trunc('minute'): toStartOfMinute
  • date_trunc('hour'): toStartOfHour
  • date_trunc('day'): toStartOfDay
  • date_trunc('month'): toStartOfMonth
  • date_trunc('quarter'): toStartOfQuarter
  • date_trunc('year'): toStartOfYear
• extract(field FROM source): date_part と同じ対応
• date(timestamp) & date(timestamptz): toDate (CH alias date としてデパースされます)
• array_position: indexOf
• array_cat: arrayConcat
• array_append: arrayPushBack
• array_prepend: arrayPushFront
• array_remove: arrayRemove
• array_length & cardinality: length
• array_to_string: arrayStringConcat
• string_to_array: splitByString
• split_part: splitByString + 配列の添字指定
• trim_array: arrayResize
• array_fill: arrayWithConstant
• array_reverse: arrayReverse
• array_shuffle: arrayShuffle
• array_sample: arrayRandomSample
• array_sort: arraySort / arrayReverseSort
• btrim: trimBoth
• ltrim: ltrim
• rtrim: rtrim
• concat_ws: concatWithSeparator
• lower(text): lowerUTF8
• upper(text): upperUTF8
• substring(text, ...) & substr(text, ...): substringUTF8
• substring(bytea, ...) & substr(bytea, ...): substring
• length(text): lengthUTF8
• length(bytea) & octet_length: length
• reverse(text): reverseUTF8
• reverse(bytea): reverse
• strpos: positionUTF8
• regexp_like: match
• regexp_match: 正規表現に丸括弧で囲まれた部分式が含まれている場合は extractGroups、 そうでない場合は extractAll を arraySlice でスライスしたものです。
• regexp_replace: g フラグがある場合は replaceRegexpOne、または replaceRegexpOne
• regexp_split_to_array: splitByRegexp
• md5: MD5
• json_extract_path_text: サブカラムの構文
• json_extract_path: toJSONString + サブカラムの構文
• jsonb_extract_path_text: サブカラム構文
• jsonb_extract_path: toJSONString + サブカラムの構文
• bit_count(bytea): bitCount
• to_timestamp(float8): fromUnixTimestamp
• to_char(timestamp[tz], fmt): fmt が、含まれるすべてのキーワードに ClickHouse で忠実に対応するものがある文字列定数である場合は、formatDateTime です。対応しているキーワードについては、Compatibility Notes の to_char() を参照してください。それ以外の場合、この関数は PostgreSQL 側でローカルに評価されます。
• statement_timestamp, transaction_timestamp, & clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))
• CURRENT_DATE: now および toDate (toDate(now($session_timezone)))
• now, CURRENT_TIMESTAMP, & LOCALTIMESTAMP: now64 (now64(9, $session_timezone))
• CURRENT_TIMESTAMP(n) & LOCALTIMESTAMP(n): 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 関数から値として渡されるものです。

​

プッシュダウン演算子

• Array スライス (arr[L:U]): arraySlice
• @> (配列が含む) : hasAll
• <@ (配列に含まれる) : hasAll
• && (配列の重複) : hasAny
• ~ (正規表現に一致) : match
• !~ (正規表現に一致しない) : match
• ~* (大文字と小文字を区別しない正規表現に一致しない) : match
• !~* (大文字と小文字を区別しない正規表現に一致しない) : match
• ->> (JSON/JSONB の要素をテキストとして抽出) : sub-column syntax
• -> (JSON/JSONB を抽出) : toJSONString + sub-column syntax

​

カスタム関数

pg_clickhouse が作成するこれらのカスタム関数は、PostgreSQL に対応する機能がない一部の ClickHouse 関数について、外部クエリのプッシュダウンを可能にします。これらの関数のいずれかをプッシュダウンできない場合は、例外が発生します。

• dictGet

​

拡張機能のプッシュダウン

pg_clickhouse は、一部のコア拡張機能やサードパーティ拡張機能の関数を認識し、それらを ClickHouse の対応する関数にプッシュダウンします。

​

re2

すべての [re2 拡張機能] 関数は、ClickHouse に 1:1 でプッシュダウンされます。

• re2match → match
• re2extract → extract
• re2extractall → extractAll
• re2regexpextract → regexpExtract
• re2extractgroups → extractGroups
• re2replaceregexpone → replaceRegexpOne
• re2replaceregexpall → replaceRegexpAll
• re2countmatches → countMatches
• re2countmatchescaseinsensitive → countMatchesCaseInsensitive
• re2multimatchany → multiMatchAny
• re2multimatchanyindex → multiMatchAnyIndex
• re2multimatchallindices → multiMatchAllIndices

​

intarray

intarray 関数のうち、ClickHouse にプッシュダウンされるものは 1 つあります。

• idx → indexOf

​

fuzzystrmatch

2つの fuzzystrmatch 関数が ClickHouse にプッシュダウンされます。

• soundex: soundex
• levenshtein (2引数) : editDistanceUTF8

​

キャストのプッシュダウン

pg_clickhouse は、互換性のあるデータ型に対して CAST(x AS bigint) のようなキャストをプッシュダウンします。互換性のない型ではプッシュダウンに失敗します。たとえば、この例で x が ClickHouse の UInt64 である場合、ClickHouse はその値をキャストしません。

互換性のないデータ型へのキャストをプッシュダウンするために、pg_clickhouse は次の関数を提供しています。これらの関数は、プッシュダウンされなかった場合に PostgreSQL で例外を発生させます。

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

​

集計のプッシュダウン

以下の PostgreSQL 集約関数は ClickHouse にプッシュダウンされます。

• array_agg
• avg
• bit_and
• bit_or
• bit_xor
• bool_and / every
• bool_or
• count
• min
• max
• string_agg
• sum

​

カスタム集約関数

pg_clickhouse が作成するこれらのカスタム集約関数は、PostgreSQL に同等の機能がない一部の ClickHouse 集約関数について、外部クエリのプッシュダウンを提供します。これらの関数のいずれかをプッシュダウンできない場合は、例外をスローします。

• argMax
• argMin
• uniq
• uniqCombined
• uniqCombined64
• uniqExact
• uniqHLL12
• uniqTheta
• quantile
• quantileExact

​

ordered-set 集約関数 の プッシュダウン

これらのordered-set 集約関数は、それぞれの direct argument をパラメータとして、ORDER BY 式を引数として渡すことで、ClickHouse のParametric 集約関数に対応します。たとえば、次の PostgreSQL クエリです。

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

次のClickHouseクエリにマッピングされます：

SELECT quantile(0.25)(a) FROM t1;

デフォルト以外の ORDER BY 接尾辞である DESC と NULLS FIRST は サポートされておらず、error が発生します。

• percentile_cont(double): quantile
• quantile(double): quantile
• quantileExact(double): quantileExact

​

プッシュダウン可能なウィンドウ関数

以下の PostgreSQL の[ウィンドウ関数]は、該当する場合はフレーム指定を含む OVER (PARTITION BY ... ORDER BY ...) 句とともに ClickHouse にプッシュダウンされます。

• row_number
• rank
• dense_rank
• ntile
• cume_dist
• percent_rank
• lead
• lag
• first_value
• last_value
• nth_value
• min / max (OVER 句付き)

順位付け関数 (row_number、rank、dense_rank、ntile、cume_dist、 percent_rank) は、ClickHouse ではこれらの関数に対するフレーム指定が受け付けられないため、プッシュダウン時にはフレーム句が省略されます。

​

互換性に関する注意

​

正規表現

pg_clickhouse.pushdown_regex が true の場合 (デフォルト) 、pg_clickhouse は 正規表現を ClickHouse の同等表現に プッシュダウン し、基本的な互換性を確保するよう努めます。 ただし、両者の違いと、それらを pg_clickhouse がどのように扱うかは把握しておいてください。

• PostgreSQL は [POSIX 正規表現] をサポートし、ClickHouse は RE2 正規表現 をサポートしています。動作の違いに注意してください。正規表現が ClickHouse によって評価される場合 (例: WHERE 句内) は RE2 を、Postgres によって評価される場合 (例: SELECT 句内) は POSIX を使用してください。

• pg_clickhouse は、[Postgres フラグ]を ClickHouse の正規表現の (?) 内の先頭に追加することで、プッシュダウンします。たとえば:

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

  になります

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

• 両方でサポートされており、そのため ClickHouse で評価される際に使用できるフラグは、次のとおりです:

  フラグ	相当	注記
  i	i	大文字と小文字を区別しないマッチング
  m	m-s	^ と $ は、テキストの先頭/末尾に加えて行頭/行末にもマッチします
  n	m-s	Postgres での m の別名
  p	-s	. と [^x] が \n にマッチしないようにします
  s	s	. と [^x] が \n にマッチするようにします
  t		厳密構文、無視されます
  w	m	部分的な改行感知マッチングの逆

  RE2 がサポートしているのはこれらのフラグだけです。それ以外の [Postgres のフラグ] は使用しないでください。

• この表は、改行および行末のマッチングにおける各種フラグ (およびフラグなし。これは s と同じです) の効果を要約したものです。Postgres では、 m と p を指定すると否定文字クラス ([^xyz]) が 改行にマッチしなくなる点に注意してください。一方、ClickHouse の同等の機能ではそうなりません。それ以外の 動作は、ClickHouse でも Postgres と同じです:

  a\nb に適用した pattern	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 は replaceRegexpOne() ではなく replaceRegexpAll() を使用し、 他のフラグを先頭に追加する前に g フラグを削除します。

• Postgres の regexp_replace() の置換引数では、一致全体を参照するために \& を使用できますが、ClickHouse では一致全体に \0 を使用します。 関数が ClickHouse にプッシュダウンされる場合は、必ず \0 を使用してください。

• Postgres の regexp_match は一致がない場合に NULL を返しますが、 push down される式は空の配列を返します。戻り値を 互換性のある形で比較するには、COALESCE() を使って NULL ではなく空の配列を返すようにします。例えば:

  SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');
  

曖昧さを完全に避けるには、 Postgres の正規表現が ClickHouse に プッシュダウン されないよう pg_clickhouse.pushdown_regex を設定し、 pg_clickhouse が ClickHouse 互換の RE2 正規表現の direct プッシュダウン をサポートしている [re2 拡張機能] の使用を検討してください。

​

to_char()

timestamp および timestamp with time zone に対する PostgreSQL の to_char() は、フォーマット引数が非 NULL の文字列定数であり、なおかつ含まれる PostgreSQL のキーワードがすべて ClickHouse にバイト単位で完全一致する対応を持つ場合にのみ、ClickHouse の formatDateTime にプッシュダウンされます。フォーマットが動的な場合 (Const ではない場合) 、または未サポートのキーワードや modifier を含む場合、この呼び出しは PostgreSQL でローカルに評価されます。部分的な変換でプッシュダウンを試みることはないため、出力は PG 互換のまま保たれます。

numeric、interval、およびその他の timestamp 以外の型に対する 2 引数の 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

​

著作権

著作権 (c) 2025-2026, ClickHouse