pg_clickhouse リファレンス

pg_clickhouse の完全なリファレンス

​

説明

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

​

はじめに

pg_clickhouse を試す最も簡単な方法は Docker image を使うことです。これには、 pg_clickhouse と [re2][re2 extension] 拡張機能を含む標準の 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” です。
• 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”) です。
• encoding_check: ClickHouse の String 値および JSON 値を変換する際に、 database encoding で無効な文字をどう扱うかを指定します。次のいずれかです。
  • fail (デフォルト): エラーを発生させます
  • remove: 無効なバイトを削除します
  • replace: UTF-8 エンコーディングでは、無効なバイトを Unicode の 置換文字 (�) に置き換えます。その他のエンコーディングでは remove と同じ動作です
  • truncate: 最初の無効なバイトの位置でテキストを切り詰めます

​

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 でサポートされているオプションも使用されます。

留意すべき点がいくつかあります。

• インポートされたカラムは、Array 内のものも含め、ClickHouse の型の型修飾子を 保持します。たとえば、Array(Decimal(12,6)) は numeric(12,6)[] としてインポートされます。

• Nullable カラムは NOT NULL なしでインポートされます。ただし、Array 内の Nullable はこの限りではありません。PostgreSQL の配列は常に NULL 要素を許容するためです。

• AggregateFunction および SimpleAggregateFunction カラムは、ClickHouse での宣言にかかわらず NOT NULL なしでインポートされます。詳しくは State Columns and NOT NULL を参照してください。

• Tuple カラムは text[] としてインポートされます。Map カラム、および flatten_nested=0 で作成された Nested カラムは text[][] としてインポートされます。いずれの変換でも NOTICE が出力されます。PostgreSQL の record 疑似型ではテーブルのカラムを定義できないため、 各タプルは値の配列に、各マップはキー・バリューのペアの 2 次元配列に、各 Nested 値は行の 2 次元配列になります。フィールドをレコードとして読み取るには、カラムを 対応する複合型の配列に変更してください。例については Manual Type Mappings を参照してください。

• 精度が 6 (マイクロ秒) を超える DateTime64 および Time64 カラムでも NOTICE が出力されます。PostgreSQL では精度の上限が timestamp(6) および time(6) であるためです。

• ClickHouse の JSON より前から存在するレガシーな Object('json') 型をはじめ、 PostgreSQL に対応する型が存在しないカラム型はエラーになります。

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

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

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

CREATE OR REPLACE TABLE test
(
    id Int64,
    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(sign)'
);

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

• database: リモートデータベースの名前です。デフォルトでは、外部サーバーに 定義されているデータベースが使用されます。
• 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 を付加します。State Columns and NOT NULL で説明されている問題を避けるため、カラムは Nullable として宣言してください。

  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 の型にマッピングし、適切なカラムオプションで 集約関数名を指定してください。

​

State Columnsと NOT NULL

AggregateFunction および SimpleAggregateFunction のカラムは Nullable のままにしてください。 PostgreSQL 19 では、NOT NULL のステート カラムに対する count(column) が count(*) に置き換えられるため、ステートがマージされず、行数がカウントされてしまいます。

例として、ユーザーごとに 1 つの count ステートを保持し、各ステートが 1,000 件のイベントから 構築されている ClickHouse テーブルを考えます。

CREATE TABLE hits (
    user_id UInt64,
    hits    AggregateFunction(count)
) ENGINE = AggregatingMergeTree() ORDER BY user_id;

NOT NULL を指定すると、PostgreSQL はステートの数をカウントしてしまうため、正しくないカウント結果になります。

CREATE FOREIGN TABLE hits (
    user_id bigint NOT NULL,
    hits    bigint NOT NULL OPTIONS(AggregateFunction 'count')
) SERVER clickhouse_srv;

try=# EXPLAIN (VERBOSE, COSTS OFF)
       SELECT user_id, count(hits) FROM hits GROUP BY user_id;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Foreign Scan
   Output: user_id, (count(*))
   Relations: Aggregate on (hits)
   Remote SQL: SELECT user_id, count(*) FROM "default".hits GROUP BY user_id
(4 rows)

try=# SELECT user_id, count(hits) FROM hits GROUP BY user_id;
 user_id | count
---------+-------
       1 |     1
(1 row)

NOT NULL を指定しない場合、pg_clickhouse は countMerge() を使用してステートをマージするため、 正しいカウント 1000 が得られます：

CREATE FOREIGN TABLE hits (
    user_id bigint NOT NULL,
    hits    bigint OPTIONS(AggregateFunction 'count')
) SERVER clickhouse_srv;

try=# EXPLAIN (VERBOSE, COSTS OFF)
       SELECT user_id, count(hits) FROM hits GROUP BY user_id;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Foreign Scan
   Output: user_id, (count(hits))
   Relations: Aggregate on (hits)
   Remote SQL: SELECT user_id, countMerge(hits) FROM "default".hits GROUP BY user_id
(4 rows)

try=# SELECT user_id, count(hits) FROM hits GROUP BY user_id;
 user_id | count
---------+-------
       1 |  1000
(1 row)

既存の外部テーブルから制約を削除するには、ALTER FOREIGN TABLE を使用します。

ALTER FOREIGN TABLE hits ALTER COLUMN hits DROP NOT NULL;

NOT NULL は PostgreSQL にのみ影響します。そのため、IMPORT FOREIGN SCHEMA では、ステート カラムは Nullable としてインポートされます。

​

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 行まで削減されます。

​

パーティション化テーブル

PostgreSQL の[パーティション化テーブル]では、ローカルパーティションと ClickHouse をバックエンドとする外部パーティションを混在させることができます。一般的な構成では、古いデータを ClickHouse にオフロードし、最新のデータは PostgreSQL に保持します。

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_srv 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を参照してください。

ローカルパーティションと外部パーティションの両方にまたがる集計には、PostgreSQL でデフォルトでは無効になっている[パーティション単位の 集約]が必要です。

SET enable_partitionwise_aggregate = on;

enable_partitionwise_aggregate を有効にすると、PostgreSQL は Append の下で部分集約を実行し、その後、上位の最終集約でそれらを結合して結果を生成します。pg_clickhouse は外部パーティションの部分集約を ClickHouse にプッシュダウンします。

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, sum of squared deviations} の状態を配列としてプッシュダウンします。

FILTER (WHERE …) も、これらの集約関数とともにプッシュダウンされます。

​

フォールバックする場合

遷移状態が PostgreSQL の不透明な internal 型である集約関数には、 ポータブルな表現がないため、外部パーティションは行を取得してローカルで集約します。 これには numeric に対するすべての集約関数のほか、avg(bigint) と avg(interval) が含まれます。 DISTINCT、順序付き集合集約関数、可変長引数集約関数もフォールバックします。

​

PREPARE, EXECUTE, DEALLOCATE

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

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

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

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

通常どおり、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, transform_null_in 0

ClickHouse サーバー の設定に戻すには、これを空文字列に設定します — ただし、プッシュダウンの正しさは、これらのデフォルト設定の一部に依存します: 外部結合には join_use_nulls、IN ファミリーには transform_null_in (IN と NULL のセマンティクスを参照) 。

SET pg_clickhouse.session_settings = '';

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

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

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

​

データ型

この表は、ClickHouse のデータ型と PostgreSQL のデータ型の推奨対応関係を示しています。 IMPORT FOREIGN SCHEMA はこの対応関係に基づいて、 適切な型修飾子と配列の次元を導出します。別の PostgreSQL 型を宣言する場合は、CREATE FOREIGN TABLE を使用してください。

「追加の読み取り先」列には、PostgreSQL の明示的なキャストに加えて binary ドライバーがサポートする変換を記載しています。 セルが空の場合でも、これらのキャストは使用できます。値は変換先の型に 収まる必要があります。これらの変換先は読み取り時のものであり、書き込みには別の 変換ルールが適用されます。

ClickHouse	PostgreSQL (デフォルト)	追加の読み取り対象	注記
Array(T)	T[]		ネストの深さごとに PG の配列型が 1 つ対応
BFloat16	real
Bool	boolean
Date	date
Date32	date
DateTime	timestamp with time zone	time
DateTime64(P)	timestamp(P) with time zone	time	P が 6 を超える場合は 6 に制限
Decimal(P,S)	numeric(P,S)	xid8, oid8
Decimal32(S)	numeric(9,S)	xid8, oid8
Decimal64(S)	numeric(18,S)	xid8, oid8
Decimal128(S)	numeric(38,S)	xid8, oid8
Decimal256(S)	numeric(76,S)	xid8, oid8
Enum8	text	bytea、入力互換の型	ラベルとしてデコード。ラベルが一致する PG の enum も対象
Enum16	text	bytea、入力互換の型	ラベルとしてデコード。ラベルが一致する PG の enum も対象
FixedString(N)	text	bytea、入力互換の型	末尾の NUL が保持されるのは bytea のみ
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int8	smallint	boolean	0 は false、0 以外は true
Int16	smallint	boolean	0 は false、0 以外は true
Int32	integer
Int64	bigint
Int128	numeric(39,0)	xid8, oid8
Int256	numeric(77,0)	xid8, oid8
IntervalDay	interval	smallint, integer, bigint	整数型には単位数が格納される
IntervalHour	interval	smallint, integer, bigint	整数型には単位数が格納される
IntervalMicrosecond	interval	smallint, integer, bigint	整数型には単位数が格納される
IntervalMillisecond	interval	smallint, integer, bigint	整数型には単位数が格納される
IntervalMinute	interval	smallint, integer, bigint	整数型には単位数が格納される
IntervalMonth	interval	smallint, integer, bigint	整数値は単位数として扱われる
IntervalNanosecond	interval	smallint, integer, bigint	整数値ではナノ秒が保持され、interval ではマイクロ秒に切り捨てられる
IntervalQuarter	interval	smallint, integer, bigint	整数値は単位数として扱われる
IntervalSecond	interval	smallint, integer, bigint	整数値は単位数として扱われる
IntervalWeek	interval	smallint, integer, bigint	整数値は単位数として扱われる
IntervalYear	interval	smallint, integer, bigint	整数値は単位数として扱われる
JSON	jsonb	json, text, bytea; 入力互換の型	jsonb ではドキュメントが正規化される
LineString	path	lseg	lseg には 2 点が必要
LowCardinality(T)	T
Map(K,V)	text[][]	composite[], T[][], text	ペアごとに text 要素の行が 1 つ
MultiLineString	path[]
MultiPolygon	polygon[][]
Nested(…)	text[][]	composite[], T[][], text	ネストされた行ごとに text 要素の行が 1 つ
Nullable(T)	T		カラムは NOT NULL 制約なしでインポートされる
Point	point
Polygon	polygon[]
Ring	polygon	lseg	lseg には 2 点が必要
SimpleAggregateFunction(f,T)	T		値は T として格納される
String	text	bytea; 入力互換の型	bytea では raw bytes が保持される
Time	time without time zone
Time64(P)	time(P) without time zone		P が 6 を超える場合は 6 に制限される
Tuple(…)	text[]	composite, T[], text; box, circle, line	各フィールドが text 要素になる
UInt8	smallint	boolean	0 は false、0 以外は true
UInt16	integer
UInt32	bigint
UInt64	numeric(20,0)	xid8, oid8
UInt128	numeric(39,0)	xid8, oid8
UInt256	numeric(78,0)	xid8, oid8
UUID	uuid

どのカラムも、text、varchar、またはその他の文字列型として読み取ることもできます。値は まず上記の PostgreSQL 型となり、その後、その型の出力関数を通じて レンダリングされます。テキスト型として読み取られた ClickHouse の文字列は データベースのエンコーディングに照らして検証されるため、PostgreSQL が読み取れないバイトが含まれているとエラーになります。 String、FixedString、Enum、または JSON のカラムを BYTEA として宣言すると、 ClickHouse が書き込んだとおりのバイト列として読み取れます。

入力互換の型は、PostgreSQL の入力関数を使用して文字列を読み取ることができます。 複合型では、フィールドが一致し、かつその順序も一致している必要があります。タプルを 配列として読み取るには、各フィールドが配列の要素型に変換可能である必要があり、 配列であるフィールドを含めることはできません。

配列として読み取る場合、Map はキー・バリューのペアごとに 1 行、Nested は ネストされたレコードごとに 1 行となります。タプルを box として読み取るには 2 つの点を、 circle として読み取るには点と半径を、line として読み取るには 3 つの係数を指定します。

追加の注記と詳細を以下に示します。

​

型変換

binary ドライバーでは、代替のスカラー型には可能な場合 PostgreSQL の明示的な キャストが使用され、配列要素は宣言された 要素型に変換されます。サポートされていない変換や範囲外の値はエラーになります。

どちらのドライバーでも、ClickHouse の Interval 型を smallint、integer、 または bigint にマッピングすると、単位数として読み書きできます。例えば、 IntervalDay の値 3 は整数 3 にマッピングされます。 IntervalNanosecond を bigint にマッピングするとナノ秒の精度が保持されますが、 interval にマッピングするとマイクロ秒単位に切り捨てられます。

​

BYTEA

ClickHouse は PostgreSQL の BYTEA 型に相当する型を提供していませんが、[String] 型に任意のバイト列を格納できます。通常、ClickHouse の文字列は PostgreSQL の [TEXT] にマッピングしますが、バイナリデータを扱う場合は BYTEA にマッピングしてください。例：

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

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

[TEXT] カラムを使用する外部テーブルでは、通常これらの値をテキストとして読み取ることはできません。pg_clickhouse が ClickHouse のバイト列をデータベースのエンコーディングに基づいて検証するためです：

-- 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;

出力結果:

ERROR:  invalid byte sequence for encoding "UTF8": 0xf7 0xf0 0xcc 0x82

ダイジェストは任意のバイト列で構成されており、それが有効なテキストになることはまれです。また、PostgreSQL は nul を予約しているため、nul を含む ClickHouse の文字列を [TEXT] カラムで読み取ることはできません。

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

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

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

テキストカラムには正しい値が格納されます：

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

しかし、BYTEAとして読み取ることはできません:

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

FixedString(N) カラムは、短い値を NUL バイトでパディングします。テキストカラムでは この末尾のパディングが削除されますが、BYTEA カラムではすべてのバイトが保持されます。

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

​

複合型

​

Array

ClickHouse の [Array] は PostgreSQL の配列に直接対応し、セマンティクスも同等です。主な違いは、Postgres の多次元配列では、配列式の次元がすべて一致していなければならない点です。[[1], [2,3]] のように次元が異なる ClickHouse の配列を読み取ろうとすると、エラーが発生します。

配列のインデックスアクセスは、多次元のインデックスアクセスも含め、必要に応じてプッシュダウンされます。例:

SELECT * FROM things where tags[1] = 'foo';
SELECT * FROM things where pairs[1][2] = 'bar';

Array のスライスも [arraySlice] 関数によってプッシュダウンされます。ただし、多次元スライスへのアクセスにはまだ対応していません：

SELECT id, vals FROM t1 WHERE vals[1:2] = ARRAY[10,20];
SELECT id, vals FROM t1 WHERE vals[1:2][2] = 20; -- currently fails

​

Map

PostgreSQL には、ClickHouse の [Map] 型に対応する型がありません。 そのため pg_clickhouse は [Map] カラムを text[][] にマッピングし、各キー・バリュー ペアをそれぞれ個別の配列として表現します。IMPORT FOREIGN SCHEMA でも このマッピングが使用されます。また、Map 値を配列として INSERT することもできます。例:

-- Create ClickHouse table with Map column.
CALL clickhouse_perform('ch_srv', $$
    CREATE TABLE maps (
        c1 Int32,
        c2 Map(String, Int64)
    ) ENGINE = MergeTree ORDER BY (c1);
$$);

-- Import text[][] column, then insert two pairs.
IMPORT FOREIGN SCHEMA ch LIMIT TO (maps) FROM SERVER ch_srv INTO public;
INSERT INTO maps VALUES (1, ARRAY[['a', '1'], ['b', '2']]);

try=# SELECT * FROM maps;
 c1 |      c2
----+---------------
  1 | {{a,1},{b,2}}
(1 row)

ネストされた各配列には、テキスト値をちょうど 2 つ含める必要があります。対応する ClickHouse のキー型または値型と互換性のない値を指定すると、エラーが発生します。

Map を挿入するには binary ドライバーが必要です。このドライバーは ClickHouse サーバーからカラム型を取得します。一方、http ドライバーはカラム型を取得しないため、適切な値をフォーマットして挿入するために必要な情報を持ちません。

​

Tuple

同様に、ClickHouse の [Tuple] カラムは text[] にマッピングされ、バイナリドライバー経由での INSERT に対応しています。IMPORT FOREIGN SCHEMA は、このようなマッピングを行う際に NOTICE を出力します。

​

Nested

デフォルトでは、ClickHouse は Nested カラムをフィールドごとに 1 つの Array カラムへ分割します (flatten_nested=1)。IMPORT FOREIGN SCHEMA は、これらのカラムを ClickHouse の system.columns カタログから読み取り、それぞれ個別の PostgreSQL 配列 カラムとして作成します。その際、items.a や items.b のようなドット区切りの名前はそのまま保持されます。たとえば、 次の外部テーブルがあるとします。

-- Create ClickHouse table with flattened Nested column.
CALL clickhouse_perform('ch_srv', $$
    CREATE TABLE ch.nests (
        c1 Int32,
        c2 Nested(id Int64, name String)
    ) ENGINE = MergeTree ORDER BY (c1)
$$);

-- Import text[][] column, then insert two pairs.
IMPORT FOREIGN SCHEMA ch LIMIT TO (nests) FROM SERVER ch_srv INTO public;

インポートされたスキーマには、2 つではなく 3 つのカラムがあります：

                   Foreign table "public.nests"
 Column  |   Type   | Collation | Nullable | Default | FDW options
---------+----------+-----------+----------+---------+-------------
 c1      | integer  |           | not null |         |
 c2.id   | bigint[] |           | not null |         |
 c2.name | text[]   |           | not null |         |

ネストされたカラムをクエリするには、次のようにカラム名をダブルクォートで囲みます。

SELECT ci, "c2.id" FROM nests;

また、配列の添字構文をはじめとする通常の配列操作を使って、WHERE 句で値をフィルタすることもできます。

SELECT * FROM nests
 WHERE "c2.id[2]" = '2' OR '12' = ANY("c2.id");

flatten_nested=0 で作成された [Nested] カラムは、ネストされた行ごとに 1 つの要素を持つ配列にマッピングされます。各配列要素には、対応する行の値が格納されます。

-- Create ClickHouse table with unflattened Nested column.
CALL clickhouse_perform('ch_srv', $$
    CREATE TABLE nests (
        c1 Int32,
        c2 Nested(id Int64, name String)
    ) ENGINE = MergeTree ORDER BY (c1) SETTINGS flatten_nested=0
$$);

-- Import text[][] column, then insert two pairs.
IMPORT FOREIGN SCHEMA ch LIMIT TO (nests) FROM SERVER ch_srv INTO public;

これで、最終的なスキーマは次のようになります。

                  Foreign table "public.nests"
 Column |  Type   | Collation | Nullable | Default | FDW options
--------+---------+-----------+----------+---------+-------------
 c1     | integer |           | not null |         |
 c2     | text[]  |           | not null |         |

ネストされたレコードは、ClickHouse のカラムで定義された各型に従って整形したテキスト値を、二次元配列として 構成します。この例の c2 Nested(id Int64, name String) の場合は、次のようになります。

INSERT INTO nests
VALUES (1, ARRAY[['42', 'Arthur'], ['99', 'Barbara']]);

ただし、Nested 型を介した text[][] カラムのプッシュダウンは失敗します。

SELECT * FROM nests WHERE c2[1][1] = '42`
ERROR:  pg_clickhouse: DB::Exception: First argument for function 'arrayElement' must be array, got 'Tuple(serial UInt32, order_id String)' instead

これは、pg_clickhouse が、ClickHouse の Array カラムに対応する text 配列カラムと Nested カラムに対応するものとを区別できず、ClickHouse の Nested 構文に書き換えられないためです。

ただし、対応する複合型の配列を使ってこれらの項目をマッピングすることもできます (詳細は 手動による型マッピング を参照) 。この場合、複合型のフィールド名が Nested のフィールド名と一致していれば、プッシュダウンが機能します。

SELECT * FROM nests WHERE c2[1].id = 42;

​

手動による型マッピング

IMPORT FOREIGN SCHEMA は汎用的な PostgreSQL 型を使用します。たとえば、 [Enum]、[Tuple]、Map、およびフラット化されていない [Nested] (flatten_nested = 0) の各カラムを持つ、次のような ClickHouse テーブルがあるとします。

CALL clickhouse_perform('ch_srv', $$
    CREATE TABLE events (
      id     UInt32,
      status Enum8('new' = 1, 'done' = 2),
      point  Tuple(Int32, Int32),
      labels Map(String, Int64),
      items  Nested(id Int32, name String)
    ) ORDER BY id SETTINGS flatten_nested = 0
$$);

CALL clickhouse_perform('ch_srv', $$
    INSERT INTO events
    VALUES(1, 'new', tuple(3, 4), {'k1': 5, 'k2': 6}, [tuple(100, 'xx')])
$$);

構造を保持したり値を制限したりするには、PostgreSQL の複合型または列挙型を定義し、 外部テーブルを手動で作成します。

CREATE TYPE event_status AS ENUM ('new', 'done');
CREATE TYPE event_point AS (x integer, y integer);
CREATE TYPE event_label AS (key text, value bigint);
CREATE TYPE event_item AS (id integer, name text);

CREATE FOREIGN TABLE events (
    id     bigint,
    status event_status,
    point  event_point,
    labels event_label[],
    items  event_item[]
) SERVER clickhouse_srv;

プッシュダウン時に正しい名前が指定されるよう、enum のラベルと複合型のフィールド名は、 ClickHouse の enum ラベルおよび Nested や Tuple のフィールド名と必ず一致させてください。

複合型のフィールドの順序と型は、ClickHouse での宣言に合わせてください。[Map] のキーと 値は、それぞれ 1 番目と 2 番目のフィールド (この例では key と value) になります。 Nested と Map は複合型の配列になり、Tuple は 1 つの複合型の値になります。

SELECT * FROM events ORDER BY id;
 id | status | point |       labels        |          items
----+--------+-------+---------------------+-------------------------
  1 | new    | (3,4) | {"(k1,5)","(k2,6)"} | {"(100,xx)","(101,yy)"}
(1 row)

もちろん、複合型のフィールド名は、次のように SELECT リストの中で使用することもできます:

SELECT (point).x,       (point).y,
       (labels[1]).key, (labels[1]).value,
       (items[1]).id,   (items[1]).name
  FROM events ORDER BY id;
 x | y | key | value | id  | name
---+---+-----+-------+-----+------
 3 | 4 | k1  |     5 | 100 | xx

また、WHERE 句内の [Nested] 型についても、フィールド名が 同一であれば同様に動作します:

SELECT * FROM events WHERE items[1].name = 'xx';

このような複合型を使用した INSERT は、現時点ではサポートされていません。

​

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

​

関数

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

​

clickhouse_server_version

SELECT clickhouse_server_version('taxi_srv');

指定したforeign serverのClickHouse server バージョンをmajor.minor.patch形式で返します。必要に応じて、server’s optionsおよびcurrent user’s user mappingを使用して接続します。

 clickhouse_server_version
---------------------------
 25.8.1
(1 row)

ネイティブプロトコル接続のハンドシェイク、または HTTP 経由で実行する単一の SELECT version() クエリからバージョンを取得し、接続の存続期間中キャッシュします。

​

clickhouse_query

SELECT * FROM clickhouse_query(
    'server',
    'SELECT id, name, salary FROM remote_table WHERE salary > 50000'
) AS ch(id int, name text, salary numeric);

構成済みのforeign serverに対してクエリを実行し、その 行をrelationとして返します。ClickHouseの各結果カラムは、カラム定義リストで 指定したPostgreSQL型にマッピングされます。serverのdriver、 credentials、database、およびconnection cacheが再利用されます。

最初のargumentには、CREATE SERVERで作成したserverの名前を指定します。 カラム定義リスト (AS name(col type, ...)) が必要です。PostgreSQLでは行を取得する前に 結果のshapeを把握する必要があり、クエリが返すカラムと一致している必要があります。値は、 外部テーブルのカラムと同様に、ClickHouseから宣言された型に変換されます。DDLなど、 結果を返さないステートメントでは宣言するものがありません。そのようなステートメントは 代わりにclickhouse_performで実行してください。

デフォルトでは、どのロールにもEXECUTEアクセス権はありません。関数の使用を許可するには、 ロールにGRANTしてください。

GRANT EXECUTE ON FUNCTION clickhouse_query(text, text) TO ch_admin;

​

clickhouse_perform

CALL clickhouse_perform(
    'server',
    'CREATE TABLE remote_table (id Int32) ENGINE = MergeTree ORDER BY id'
);

設定済みの foreign server に対してステートメントを実行し、結果を 破棄します。DDL のように行を返さず、clickhouse_query で 結果の shape を宣言できないステートメントに使用します。server は clickhouse_query と同じ方法で解決され、その driver、credentials、database、および connection cache が再利用されます。

プロシージャとして、SELECT ではなく CALL で呼び出す必要があり、行は返しません。 デフォルトでは、どのロールにも EXECUTE アクセス権は付与されていません。プロシージャの使用を許可するには、ロールに GRANT します。

GRANT EXECUTE ON PROCEDURE clickhouse_perform(text, text) TO ch_admin;

​

プッシュダウン関数

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 に nullIf を使用して 0 を NULL に変換し、検索開始インデックスを指定する第 3 引数がある場合は arraySlice を使用します。なお、現在 nan は一致しません
• array_cat: arrayConcat
• array_append: arrayPushBack
• array_prepend: arrayPushFront
• array_remove: arrayRemove
• cardinality: ClickHouse 26.9 以降では arrayFlattenedLength、 それ以前のバージョンでは length (外側の配列の要素のみをカウントします)
• array_length(array, 1): nullIf(length(array), 0)
• 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: trimLeft
• rtrim: trimRight
• 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
• sha224、sha256、sha384、sha512: 対応する ClickHouse の SHA 関数
• fmt が文字列定数 (大文字と小文字を区別しない) の場合の encode(bytea, fmt):
  • encode(bytea, 'hex'): hex を lower でラップします。 PostgreSQL は小文字の hex を出力するためです。
  • encode(bytea, 'base64'): base64Encode を replaceRegexpAll でラップし、 PostgreSQL の MIME (RFC 2045) 形式の 76 文字ごとの改行を再現します。
  • encode(bytea, 'base64url') (PostgreSQL 19 以降) : base64URLEncode。 これは、padding なしの PostgreSQL の RFC 4648 URL アルファベットに一致します。
• json_extract_path_text: サブカラムの構文
• json_extract_path: toJSONString + サブカラムの構文
• jsonb_extract_path_text: サブカラム構文
• jsonb_extract_path: toJSONString + サブカラムの構文
• bit_count(bytea): bitCount
• to_timestamp(float8): toDateTime64
• 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

​

IN と NULL のセマンティクス

ClickHouse は IN を二値論理で評価します。プローブで一致が見つからない場合、NULL が含まれていても 0 を返しますが、PostgreSQL では NULL になります。PostgreSQL のセマンティクスを維持するため、pg_clickhouse は定数リストまたは配列に対する IN ファミリー (IN、NOT IN、= ANY、= ALL、<> ANY、<> ALL) を無条件にプッシュダウンします。プローブと配列要素のいずれも NULL になり得ないことを証明できる場合はネイティブ形式または低コストな形式を使用し、それ以外の場合は実行時に NULL 値を確認するガード付き CASE 形式を使用します。これにより、SELECT リストや GROUP BY などの値の位置を含むあらゆるコンテキストで、PostgreSQL と完全に同じ三値の結果 (TRUE、FALSE、NULL) を計算します。

NULL 許容カラムに対する NOT IN (SELECT ...) フィルタもプッシュダウンされ、PostgreSQL の動作を維持するための補償ガードを付加して逆変換されます。NULL を含む集合はすべての行を除外し、NULL のプローブが通過するのは集合が空の場合のみです。NOT NULL 宣言によって不要であることが証明できる場合は、各ガードを省略します。上記の配列形式とは異なり、このガードは通常のフィルタ条件内 (または NOT 配下) でのみ適用されます。値の位置にある IN (SELECT ...) や、グループ化または集計されたサブクエリ本体は、引き続きプッシュダウンされません。カラムを NOT NULL と宣言すると、より低コストなガードなし形式を送信できるため、プッシュダウンを最大化できます。IMPORT FOREIGN SCHEMA は、Nullable ではない ClickHouse カラムに対してこれを自動的に行います。この証明では、非 NULL 定数、NOT NULL カラム、およびそれらに対する基本的な算術演算 (+、-、*、単項 -) を追跡します。

これらの規則は、ClickHouse のデフォルト設定である transform_null_in = 0 を前提としています。pg_clickhouse は pg_clickhouse.session_settings パラメータのデフォルト値を通じて、すべてのクエリでこれを設定します。これにより、ClickHouseサーバープロファイルがこの設定を暗黙的に変更することはありません。transform_null_in = 1 を設定すると、プッシュダウンされたすべての IN のセマンティクスが破綻します。

​

カスタム関数

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

• dictGet

​

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

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

​

re2

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

• @~ → match
• 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

​

pgcyrpto

• digest(text, text) および digest(bytea, text): アルゴリズムが定数で、md5、sha1、sha224、 sha256、sha384、sha512 のいずれか (大文字と小文字を区別せずに照合) である場合は、 対応する ClickHouse のハッシュ関数 にプッシュダウンされます。

​

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

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

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

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

​

集計のプッシュダウン

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

• any_value
• array_agg
• avg
• bit_and
• bit_or
• bit_xor
• bool_and / every
• bool_or
• count
• corr
• covarpop
• covarsamp
• min
• max
• stddev_pop
• stddev_samp / stddev
• string_agg
• sum
• var_op
• var_samp /variance

​

カスタム集約関数

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
• percentile_cont(double[]): quantiles
• percentile_disc(double): quantileExactLow
• percentile_disc(double[]): quantilesExactLow

​

カスタム ordered-set 集約関数

pg_clickhouse によって作成されたこれらのカスタムordered-set 集約関数は、一部の ClickHouse parametric 集約関数に対する 外部クエリのプッシュダウン を提供します。これらの関数のいずれかをプッシュダウンできない場合は、 例外 が発生します。

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

​

カスタム ordered-set 集約関数

pg_clickhouse が提供するこれらのカスタムordered-set 集約関数では、特定の ClickHouse parametric 集約関数に対して外部クエリのプッシュダウンが可能です。これらの関数のいずれかをプッシュダウンできない場合は、例外が発生します。

• quantile
• 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 正規表現][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] にプッシュダウンされます。フォーマットが動的な場合、または未サポートのキーワードや modifier を含む場合、この呼び出しは PostgreSQL でローカルに評価されます。pg_clickhouse が部分的な変換をプッシュダウンすることはないため、出力の互換性は保たれます。

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

[Array] https://clickhouse.com/docs/reference/data-types/array “ClickHouse ドキュメント: Array(T)” [Map]: https://clickhouse.com/docs/sql-reference/data-types/map “ClickHouse ドキュメント: Map” [Tuple]: https://clickhouse.com/docs/reference/data-types/tuple “ClickHouse ドキュメント: Tuple(T1, T2, …)” [Nested]: https://clickhouse.com/docs/sql-reference/data-types/nested-data-structures/nested “ClickHouse ドキュメント: Nested” [Enum]: https://clickhouse.com/docs/reference/data-types/enum “ClickHouse ドキュメント: Enum” [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”