Documentation de référence pour pg_clickhouse

Description

pg_clickhouse est une extension PostgreSQL qui permet d’exécuter des requêtes à distance sur des bases de données ClickHouse, et inclut un foreign data wrapper. Elle est compatible avec PostgreSQL 13 et versions ultérieures, ainsi qu’avec ClickHouse 23.3 et versions ultérieures.

Prise en main

Le moyen le plus simple de tester pg_clickhouse est d’utiliser l’Docker image, qui contient l’image Docker PostgreSQL standard avec les extensions pg_clickhouse et re2 :

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

Consultez le tutoriel pour commencer à importer des tables ClickHouse et à déporter l’exécution des requêtes.

Utilisation

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;

Politique de versionnage

pg_clickhouse se conforme au [versionnement sémantique] pour ses versions publiques.

La version majeure est incrémentée en cas de modification de l’API
La version mineure est incrémentée en cas de modifications SQL rétrocompatibles
La version de correctif est incrémentée pour les modifications portant uniquement sur le binaire

Une fois installée, PostgreSQL distingue deux variantes de version :

La version de la bibliothèque (définie par PG_MODULE_MAGIC sur PostgreSQL 18 et versions ultérieures) inclut la version sémantique complète, visible dans la sortie de la fonction pgch_version() ou de la fonction Postgres pg_get_loaded_modules().
La version de l’extension (définie dans le fichier de contrôle) inclut uniquement les versions majeure et mineure, visibles dans la table pg_catalog.pg_extension, la sortie de la fonction pg_available_extension_versions() et \dx pg_clickhouse.

En pratique, cela signifie qu’une version qui incrémente la version de correctif, par ex. de v0.1.0 à v0.1.1, bénéficie à toutes les bases de données qui ont chargé v0.1 et n’ont pas besoin d’exécuter ALTER EXTENSION pour profiter de la mise à niveau. En revanche, une version qui incrémente la version mineure ou majeure sera accompagnée de scripts de mise à niveau SQL, et toutes les bases de données existantes qui contiennent l’extension doivent exécuter ALTER EXTENSION pg_clickhouse UPDATE pour bénéficier de la mise à niveau.

Référence SQL DDL

Les expressions SQL DDL suivantes utilisent pg_clickhouse.

CREATE EXTENSION

Utilisez CREATE EXTENSION pour ajouter pg_clickhouse à une base de données :

CREATE EXTENSION pg_clickhouse;

Utilisez WITH SCHEMA pour l’installer dans un schéma spécifique (recommandé) :

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

Utilisez ALTER EXTENSION pour modifier l’extension pg_clickhouse. Exemples :

Après l’installation d’une nouvelle version de pg_clickhouse, utilisez la clause UPDATE :
ALTER EXTENSION pg_clickhouse UPDATE;
Utilisez SET SCHEMA pour déplacer l’extension vers un nouveau schéma :
CREATE SCHEMA ch; ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION

Utilisez DROP EXTENSION pour supprimer pg_clickhouse d’une base de données :

DROP EXTENSION pg_clickhouse;

Cette commande échoue si des objets dépendent de pg_clickhouse. Utilisez la clause CASCADE pour les supprimer également :

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

Utilisez CREATE SERVER pour créer un serveur distant connecté à un serveur ClickHouse. Exemple :

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

Les options prises en charge sont :

driver : Le pilote de connexion ClickHouse à utiliser, soit “binary”, soit “http”. Obligatoire.
compression : Compression du protocole natif pour le pilote “binary”, parmi “none”, “lz4” ou “zstd”. La valeur par défaut est “lz4”. Ignoré par le pilote “http”.
dbname : La base de données ClickHouse à utiliser lors de la connexion. La valeur par défaut est “default”.
host : Le nom d’hôte du serveur ClickHouse. La valeur par défaut est “localhost” ;
port : Le port auquel se connecter sur le serveur ClickHouse. Les valeurs par défaut sont les suivantes :
- 9440 si driver vaut “binary” et que host est un hôte ClickHouse Cloud
- 9004 si driver vaut “binary” et que host n’est pas un hôte ClickHouse Cloud
- 8443 si driver vaut “http” et que host est un hôte ClickHouse Cloud
- 8123 si driver vaut “http” et que host n’est pas un hôte ClickHouse Cloud
min_tls_version : Version minimale du protocole TLS à négocier sur les connexions qui utilisent TLS. L’une de TLSv1, TLSv1.1, TLSv1.2 ou TLSv1.3. La valeur par défaut est la version minimale propre à la bibliothèque TLS. S’applique aux deux pilotes.
secure : Contrôle l’utilisation de TLS pour la connexion. L’une des valeurs suivantes :
- auto (par défaut) : utilise TLS lorsque host est un hôte ClickHouse Cloud ou que port est un port sécurisé ; en clair sinon.
- on (ou true/yes/1) : utilise toujours TLS. La valeur par défaut de port est 8443 (“http”) ou 9440 (“binary”).
- off (ou false/no/0) : n’utilise jamais TLS. La valeur par défaut de port est 8123 (“http”) ou 9000 (“binary”).

ALTER SERVER

Utilisez ALTER SERVER pour modifier un serveur distant. Exemple :

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

Les options sont les mêmes que pour CREATE SERVER.

DROP SERVER

Utilisez DROP SERVER pour supprimer un serveur distant :

DROP SERVER taxi_srv;

Cette commande échoue si d’autres objets dépendent du serveur. Utilisez CASCADE pour supprimer également ces objets dépendants :

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

Utilisez CREATE USER MAPPING pour associer un utilisateur PostgreSQL à un utilisateur ClickHouse. Par exemple, pour associer l’utilisateur PostgreSQL actuel à l’utilisateur ClickHouse distant lors de la connexion au serveur distant taxi_srv :

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

Les options prises en charge sont :

user : Le nom de l’utilisateur ClickHouse. La valeur par défaut est “default”.
password : Le mot de passe de l’utilisateur ClickHouse.

ALTER USER MAPPING

Utilisez ALTER USER MAPPING pour modifier la définition d’un mappage d’utilisateur :

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

Les options sont les mêmes que celles de CREATE USER MAPPING.

DROP USER MAPPING

Utilisez DROP USER MAPPING pour supprimer un mappage d’utilisateur :

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

Utilisez IMPORT FOREIGN SCHEMA pour importer toutes les tables définies dans une base de données ClickHouse en tant que tables étrangères dans un schéma PostgreSQL :

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

Utilisez LIMIT TO pour restreindre l’importation à certaines tables :

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

Utilisez EXCEPT pour exclure des tables :

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

pg_clickhouse récupère la liste de toutes les tables de la base de données ClickHouse spécifiée (« demo » dans les exemples ci-dessus), récupère la définition des colonnes de chacune d’elles et exécute des commandes CREATE FOREIGN TABLE pour créer les tables étrangères. Les colonnes sont définies à l’aide des types de données pris en charge et, lorsqu’elles sont détectables, des options prises en charge par CREATE FOREIGN TABLE.

Préservation de la casse des identifiants importésIMPORT FOREIGN SCHEMA exécute quote_identifier() sur les noms de table et de colonne qu’il importe, ce qui entoure de guillemets doubles les identifiants contenant des majuscules ou des espaces. Ces noms de table et de colonne doivent donc être entourés de guillemets doubles dans les requêtes PostgreSQL. Les noms entièrement en minuscules et sans espace n’ont pas besoin d’être mis entre guillemets.Par exemple, étant donnée cette table ClickHouse :

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

IMPORT FOREIGN SCHEMA crée cette table étrangère :

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

Les requêtes doivent donc utiliser les guillemets de manière appropriée, par exemple :

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

Pour créer des objets avec des noms différents ou entièrement en minuscules (et donc insensibles à la casse), utilisez CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE

Utilisez CREATE FOREIGN TABLE pour créer une table étrangère permettant d’interroger les données d’une base de données ClickHouse :

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

Les options de table prises en charge sont :

database : Le nom de la base de données distante. Par défaut, il s’agit de la base de données définie pour le serveur distant.
table_name : Le nom de la table distante. Par défaut, il s’agit du nom spécifié pour la table distante.
engine : Le [moteur de table] utilisé par la table ClickHouse. Pour CollapsingMergeTree() et AggregatingMergeTree(), pg_clickhouse applique automatiquement les paramètres aux expressions de fonction exécutées sur la table.

Utilisez le type de données adapté au type de données ClickHouse distant de chaque colonne. Les options de colonne prises en charge sont :

column_name : Le nom de la colonne côté ClickHouse, utilisé de préférence au nom d’attribut PostgreSQL lors de la régénération des requêtes et des insertions. Utile pour faire correspondre des noms de colonnes PostgreSQL non quotés en minuscules à des colonnes ClickHouse sensibles à la casse, par exemple :
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 : Le nom de la fonction d’agrégation appliquée à une colonne de [type AggregateFunction]. Faites correspondre le type de données au type ClickHouse passé à la fonction et spécifiez le nom de la fonction d’agrégation via l’option de colonne appropriée ; pg_clickhouse ajoutera automatiquement Merge à la fonction d’agrégation qui évalue la colonne.
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 : Le nom de la fonction d’agrégation appliquée à une colonne de [type SimpleAggregateFunction]. Faites correspondre le type de données au type ClickHouse passé à la fonction et spécifiez le nom de la fonction d’agrégation via l’option de colonne appropriée.

ALTER FOREIGN TABLE

Utilisez ALTER FOREIGN TABLE pour modifier la définition d’une table étrangère :

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

Les options de table et de colonne prises en charge sont identiques à celles de CREATE FOREIGN TABLE.

DROP FOREIGN TABLE

Utilisez DROP FOREIGN TABLE pour supprimer une table étrangère :

DROP FOREIGN TABLE acts;

Cette commande échoue s’il existe des objets dépendant de la table étrangère. Utilisez la clause CASCADE pour les supprimer également :

DROP FOREIGN TABLE acts CASCADE;

Référence SQL DML

Les expressions SQL DML ci-dessous peuvent utiliser pg_clickhouse. Les exemples s’appuient sur ces tables 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

La commande EXPLAIN fonctionne comme prévu, mais l’option VERBOSE provoque l’affichage de la requête 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)

Cette requête est exécutée dans ClickHouse via un nœud de plan “Foreign Scan”, le SQL exécuté à distance.

SELECT

Utilisez l’instruction SELECT pour exécuter des requêtes sur les tables pg_clickhouse, comme sur n’importe quelle autre table :

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 s’efforce de déléguer autant que possible l’exécution des requêtes à ClickHouse, y compris les fonctions d’agrégation. Utilisez EXPLAIN pour déterminer dans quelle mesure le pushdown s’applique. Pour la requête ci-dessus, par exemple, toute l’exécution est déléguée à 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 délègue également les JOIN entre des tables provenant du même serveur distant :

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)

Une jointure avec une table locale générera des requêtes moins efficaces sans un ajustement fin. Dans cet exemple, nous créons une copie locale de la table nodes et effectuons la jointure avec celle-ci plutôt qu’avec la table distante :

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

Dans ce cas, nous pouvons confier une plus grande part de l’agrégation à ClickHouse en regroupant par node_id plutôt que par la colonne locale, puis en effectuant la jointure avec la table de correspondance plus tard :

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

Le nœud “Foreign Scan” délègue désormais l’agrégation par node_id, réduisant le nombre de lignes devant être rapatriées dans Postgres de 1000 (la totalité d’entre elles) à seulement 8, une pour chaque nœud.

Tables partitionnées

Une [table partitionnée] PostgreSQL peut combiner des partitions locales et des partitions étrangères stockées dans ClickHouse. Une configuration courante consiste à déporter les données plus anciennes vers ClickHouse, tandis que les données récentes restent dans 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_svr OPTIONS (table_name 'events');

-- 2024 data stays local
CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Pour savoir comment déplacer des données de partitions locales vers des partitions étrangères, consultez l’exemple offload-partition.sql. Les agrégats couvrant à la fois des partitions locales et étrangères nécessitent l’[agrégation par partition], que PostgreSQL désactive par défaut :

SET enable_partitionwise_aggregate = on;

Lorsque enable_partitionwise_aggregate est activé, PostgreSQL calcule un agrégat partiel sous Append, puis un agrégat de finalisation au-dessus, qui combine ces résultats partiels. pg_clickhouse délègue l’agrégat partiel de la partition étrangère à 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

Quand les agrégats partiels sont délégués

PostgreSQL représente un agrégat partiel par un état de transition que l’étape de finalisation combine entre les partitions. pg_clickhouse ne peut déléguer l’agrégat partiel d’une partition que s’il peut l’exprimer sous la forme d’une valeur ClickHouse :

Les agrégats décomposables dont l’état de transition correspond déjà à la valeur finale sont délégués directement : count, sum, min, max, bool_and/every, bool_or, bit_and, bit_or et bit_xor.
avg sur des integer délègue son état {count, sum} sous forme de tableau.
avg, var_pop, var_samp, stddev_pop et stddev_samp sur des nombres à virgule flottante délèguent leur état {N, sum, sum of squared deviations} sous forme de tableau.

FILTER (WHERE …) est délégué avec ces fonctions d’agrégation.

Lorsqu’ils basculent vers une solution de repli

Les agrégats dont l’état de transition correspond au type opaque internal de PostgreSQL ne disposent d’aucune représentation portable ; la partition étrangère récupère donc leurs lignes et les agrège localement. Cela concerne tout ce qui utilise numeric, ainsi que avg(bigint) et avg(interval). Les agrégats DISTINCT, à ensemble ordonné et variadiques basculent également vers une solution de repli.

PREPARE, EXECUTE, DEALLOCATE

À partir de la version v0.1.2, pg_clickhouse prend en charge les requêtes paramétrées, principalement créées par la commande 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

Utilisez EXECUTE comme d’habitude pour exécuter une requête préparée :

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)

L’exécution paramétrée empêche le pilote « http » de convertir correctement les fuseaux horaires des valeurs DateTime sur les versions de ClickHouse antérieures à 25.8, version dans laquelle le [bogue sous-jacent] a été [corrigé]. Notez que PostgreSQL peut parfois utiliser un plan de requête paramétré même sans PREPARE. Pour toute requête nécessitant une conversion précise des fuseaux horaires, si une mise à niveau vers la version 25.8 ou ultérieure n’est pas possible, utilisez plutôt le pilote « binary ».

pg_clickhouse pousse les agrégations, comme d’habitude, comme on peut le voir dans la sortie EXPLAIN en mode verbose :

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)

Notez qu’il a envoyé les valeurs de date complètes, et non les marqueurs de paramètres. Ceci vaut pour les cinq premières requêtes, comme décrit dans les [notes sur PREPARE] de PostgreSQL. Lors de la sixième exécution, il envoie des [paramètres de requête] ClickHouse au format {param:type} : parameters:

                                                                                                         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)

Utilisez DEALLOCATE pour libérer une instruction préparée :

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT

Utilisez la commande INSERT pour insérer des valeurs dans une table ClickHouse distante :

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

Utilisez la commande COPY pour insérer un lot de lignes dans une table ClickHouse distante :

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

⚠️ Limitations de l’API Batch pg_clickhouse n’a pas encore implémenté la prise en charge de l’API d’insertion par lot du FDW de PostgreSQL. Par conséquent, COPY utilise actuellement des instructions INSERT pour insérer les enregistrements. Cela sera amélioré dans une prochaine version.

LOAD

Utilisez LOAD pour charger la bibliothèque partagée pg_clickhouse :

try=# LOAD 'pg_clickhouse';
LOAD

Il n’est généralement pas nécessaire d’utiliser LOAD, car Postgres chargera automatiquement pg_clickhouse la première fois que l’une de ses fonctionnalités (fonctions, tables étrangères, etc.) est utilisée. Le seul cas où il peut être utile de LOAD pg_clickhouse est pour SET les paramètres de pg_clickhouse avant d’exécuter des requêtes qui en dépendent.

SET

Utilisez SET pour définir les paramètres de configuration personnalisés de pg_clickhouse.

`pg_clickhouse.session_settings`

Le paramètre pg_clickhouse.session_settings configure les [paramètre ClickHouse] à appliquer aux requêtes suivantes. Exemple :

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

Par défaut, la valeur est

join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0

Définissez-la sur une chaîne vide pour revenir aux paramètres du serveur ClickHouse — mais notez que la validité du pushdown dépend de certaines de ces valeurs par défaut : join_use_nulls pour les jointures externes et transform_null_in pour la famille IN (voir IN et sémantique NULL).

SET pg_clickhouse.session_settings = '';

La syntaxe est une liste de paires clé/valeur délimitées par des virgules et séparées par un ou plusieurs espaces. Les clés doivent correspondre aux [paramètre ClickHouse]. Faites précéder les espaces, les virgules et les barres obliques inverses dans les valeurs d’une barre oblique inverse :

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

Ou utilisez des valeurs entre guillemets simples pour éviter d’avoir à échapper les espaces et les virgules ; envisagez d’utiliser le dollar quoting pour éviter d’avoir à utiliser des guillemets doubles :

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

Si la lisibilité est importante pour vous et que vous devez définir de nombreux paramètres, utilisez plusieurs lignes, par exemple :

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

Certains paramètres seront ignorés lorsqu’ils risqueraient d’interférer avec le fonctionnement de pg_clickhouse lui-même. Il s’agit des paramètres suivants :

date_time_output_format : le pilote « http » exige qu’il soit défini sur “iso”
format_tsv_null_representation : le pilote « http » exige la valeur par défaut
output_format_tsv_crlf_end_of_line le pilote « http » exige la valeur par défaut

Sinon, pg_clickhouse ne valide pas les paramètres, mais les transmet à ClickHouse pour chaque requête. Il prend donc en charge tous les paramètres de chaque version de ClickHouse. Notez que pg_clickhouse doit être chargé avant de définir pg_clickhouse.session_settings ; utilisez soit le [préchargement de bibliothèque partagée], soit simplement l’un des objets de l’extension pour garantir son chargement.

`pg_clickhouse.pushdown_regex`

Le paramètre pg_clickhouse.pushdown_regex contrôle si pg_clickhouse déporte les fonctions et les opérateurs d’expressions régulières. C’est le comportement par défaut ; définissez ce paramètre sur false pour éviter qu’ils ne soient déportés :

SET pg_clickhouse.pushdown_regex = 'false';

Voir Expressions régulières pour plus de détails.

ALTER ROLE

Utilisez la commande SET d’ALTER ROLE pour précharger pg_clickhouse et/ou SET ses paramètres pour certains rôles :

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

Utilisez la commande RESET de ALTER ROLE pour réinitialiser le préchargement de pg_clickhouse et/ou les paramètres :

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

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

Préchargement

Si presque toutes les connexions Postgres doivent utiliser pg_clickhouse, envisagez d’utiliser le [préchargement de bibliothèque partagée] pour le charger automatiquement :

`session_preload_libraries`

Charge la bibliothèque partagée à chaque nouvelle connexion à PostgreSQL :

session_preload_libraries = pg_clickhouse

Utile pour profiter des mises à jour sans redémarrer le serveur : il suffit de se reconnecter. Peut aussi être défini pour des utilisateurs ou des rôles spécifiques via ALTER ROLE.

`shared_preload_libraries`

Charge la bibliothèque partagée dans le processus parent de PostgreSQL au démarrage :

shared_preload_libraries = pg_clickhouse

Utile pour économiser de la mémoire et réduire la surcharge de chargement à chaque session, mais nécessite le redémarrage du cluster lors de la mise à jour de la bibliothèque.

Types de données

pg_clickhouse fait correspondre les types de données ClickHouse suivants aux types de données PostgreSQL. IMPORT FOREIGN SCHEMA utilise le premier type de la colonne PostgreSQL lors de l’importation des colonnes ; des types supplémentaires peuvent être utilisés dans les instructions CREATE FOREIGN TABLE :

ClickHouse	PostgreSQL	Notes
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	Erreur pour les valeurs > max BIGINT
UInt8	smallint
UUID	uuid

Toute colonne peut également être lue en text, varchar ou dans un autre type de chaîne. La valeur est d’abord convertie vers le type PostgreSQL indiqué ci-dessus, puis rendue via la fonction de sortie de ce type. Les valeurs UInt64 supérieures au maximum de bigint génèrent toujours une erreur ; utilisez donc la fonction ClickHouse toString() pour les rendre. Des notes et informations complémentaires suivent.

BYTEA

ClickHouse ne fournit pas l’équivalent du type PostgreSQL BYTEA, mais permet de stocker n’importe quels octets dans le type String. En général, les chaînes ClickHouse doivent être associées au type PostgreSQL TEXT, mais pour les données binaires, utilisez BYTEA. Exemple :

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

Cette dernière requête SELECT produira :

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

Notez que si les colonnes ClickHouse contiennent des octets nuls, une table externe utilisant des colonnes TEXT ne produira pas les valeurs attendues :

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

Affichera :

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

Notez que les lignes deux et trois contiennent des valeurs tronquées. En effet, PostgreSQL repose sur des chaînes terminées par un caractère nul et ne prend pas en charge les caractères nuls dans ses chaînes. L’insertion de valeurs binaires dans des colonnes TEXT réussira et fonctionnera comme prévu :

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

Les colonnes de texte seront correctement interprétées :

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

Mais pas si vous les lisez comme BYTEA :

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

En règle générale, utilisez les colonnes TEXT uniquement pour les chaînes encodées et les colonnes BYTEA uniquement pour les données binaires, sans jamais passer de l’une à l’autre.

Référence des fonctions et des opérateurs

Fonctions

Ces fonctions constituent l’interface permettant d’interroger une base de données ClickHouse.

`clickhouse_raw_query`

Obsolète : clickhouse_raw_query() émet un avertissement de dépréciation et sera supprimée dans la prochaine version. Utilisez clickhouse_query pour lire des lignes et clickhouse_perform pour exécuter des instructions qui ne renvoient rien. Les deux réutilisent le pilote, les informations d’identification, la base de données et le cache de connexions d’un serveur configuré au lieu d’une chaîne de connexion ad hoc.

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

Se connecte à un service ClickHouse, exécute une seule query, puis se déconnecte. Le deuxième argument facultatif indique une chaîne de connexion dont la valeur par défaut est host=localhost port=8123. Les paramètres de connexion pris en charge sont :

driver : le pilote de connexion à utiliser, soit “http”, soit “binary” ; la valeur par défaut est “http”
host : l’hôte auquel se connecter ; obligatoire.
port : le port auquel se connecter ; la valeur par défaut est 8123 pour le pilote “http” ou 9000 pour le pilote “binary”, et passe respectivement à 8443 ou 9440 lorsque host est un hôte ClickHouse Cloud
dbname : le nom de la database à laquelle se connecter.
username : le username à utiliser pour la connexion ; la valeur par défaut est default
password : le password à utiliser pour l’authentification ; par défaut, aucun mot de passe n’est utilisé

Les deux pilotes renvoient des lignes séparées par des tabulations (les valeurs nulles sous la forme \N), mais leurs représentations de chaque valeur diffèrent : le pilote “http” renvoie le formatage TSV propre à ClickHouse tel quel, tandis que le pilote “binary” transmet chaque valeur à sa fonction de sortie PostgreSQL. Par défaut, aucun rôle ne dispose de l’autorisation EXECUTE à cette fonction ; envisagez d’accorder, via GRANT, cet accès uniquement aux rôles qui doivent légitimement exécuter des queries ClickHouse ad hoc, par exemple un admin role ClickHouse dédié : Utile pour les queries qui ne renvoient aucun enregistrement, mais celles qui renvoient des values sont retournées sous la forme d’une seule valeur textuelle :

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)

`clickhouse_server_version`

SELECT clickhouse_server_version('taxi_srv');

Renvoie la version du serveur ClickHouse, au format major.minor.patch, pour le serveur externe spécifié, en s’y connectant si nécessaire à l’aide des options du serveur et du user mapping de l’utilisateur courant :

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

Lit la version lors du handshake de la connexion via le protocole natif ou, via HTTP, à l’aide d’une unique requête SELECT version(), puis la met en cache pendant toute la durée de vie de la connexion.

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

Exécute une requête sur un serveur externe déjà configuré et renvoie ses lignes sous forme de relation, en associant chaque colonne de résultat ClickHouse au type PostgreSQL indiqué dans la liste de définition des colonnes. Elle réutilise le driver du serveur, ses informations d’identification, sa base de données et le cache de connexion. Le premier argument est le nom d’un serveur créé avec CREATE SERVER. Une liste de définition des colonnes (AS name(col type, ...)) est obligatoire : PostgreSQL doit connaître la structure du résultat avant de récupérer les lignes, et elle doit correspondre aux colonnes renvoyées par la requête. Les valeurs sont converties de ClickHouse vers les types déclarés, comme le seraient celles d’une colonne de table externe. Les instructions qui ne renvoient aucun résultat, telles que les DDL, n’ont rien à déclarer ; exécutez-les plutôt avec clickhouse_perform. Aucun rôle ne dispose par défaut de l’autorisation EXECUTE ; accordez GRANT à un rôle pour lui permettre d’utiliser la fonction.

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

Exécute une instruction sur un serveur externe déjà configuré et ignore tout résultat. Utilisez-la pour les instructions qui ne renvoient aucune ligne, telles que les DDL, lorsque clickhouse_query n’a aucune forme de résultat à déclarer. Elle résout le serveur de la même manière que clickhouse_query, en réutilisant son driver, ses informations d’identification, sa base de données et le cache de connexions. En tant que procédure, elle doit être appelée avec CALL, et non avec SELECT, et ne renvoie aucune ligne. Par défaut, aucun rôle ne dispose de l’autorisation EXECUTE ; utilisez GRANT pour autoriser un rôle à utiliser la procédure.

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

Fonctions de pushdown

pg_clickhouse applique le pushdown à un sous-ensemble des fonctions intégrées de PostgreSQL utilisées dans les expressions conditionnelles (clauses HAVING et WHERE). Ce sous-ensemble correspond aux équivalents ClickHouse suivants :

abs : abs
factorial : factorial
mod (int2/int4/int8/numeric) : modulo
pow & power (float8/numeric) : pow
round : round
sin, cos, tan, atan, atan2, sinh, cosh, tanh, asinh, degrees, radians, pi : fonctions mathématiques ClickHouse portant le même nom. asin, acos, atanh, acosh ne sont pas poussées vers le bas : PG renvoie une erreur sur une entrée hors intervalle, là où CH renvoie 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): mêmes correspondances que date_part
date(timestamp) & date(timestamptz): toDate (restitué sous forme d’alias CH date)
array_position: indexOf avec nullIf pour convertir 0 en NULL et arraySlice lorsqu’un troisième argument indique l’index de début de la recherche ; notez que nan ne correspond actuellement pas
array_cat: arrayConcat
array_append: arrayPushBack
array_prepend: arrayPushFront
array_remove: arrayRemove
cardinality: length
array_length(array, 1): nullIf(length(array), 0)
array_length & cardinality: length
array_to_string: arrayStringConcat
string_to_array: splitByString
split_part: splitByString + indexation de tableau
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 si l’expression régulière contient des sous-expressions entre parenthèses ; sinon extractAll puis découpé avec arraySlice.
regexp_replace : replaceRegexpOne ou replaceRegexpOne lorsque l’indicateur g est présent
regexp_split_to_array : splitByRegexp
md5 : MD5
encode(bytea, fmt) lorsque fmt est une constante de chaîne (sans distinction entre majuscules et minuscules) :
- encode(bytea, 'hex') : hex enveloppé dans lower, car PostgreSQL produit des valeurs hexadécimales en minuscules.
- encode(bytea, 'base64') : base64Encode enveloppé dans replaceRegexpAll afin de reproduire les sauts de ligne MIME (RFC 2045) de PostgreSQL toutes les 76 caractères.
- encode(bytea, 'base64url') (PostgreSQL 19+) : base64URLEncode, qui utilise l’alphabet d’URL RFC 4648 de PostgreSQL sans remplissage.
json_extract_path_text : syntaxe des sous-colonnes
json_extract_path : toJSONString + syntaxe des sous-colonnes
jsonb_extract_path_text : syntaxe des sous-colonnes
jsonb_extract_path : toJSONString + syntaxe des sous-colonnes
bit_count(bytea) : bitCount
to_timestamp(float8) : toDateTime64
to_char(timestamp[tz], fmt) : formatDateTime lorsque fmt est une constante de chaîne dont chaque mot-clé possède un équivalent fidèle dans ClickHouse. Voir to_char() dans les Notes de compatibilité pour les mots-clés pris en charge. Sinon, la fonction est évaluée localement dans PostgreSQL.
statement_timestamp, transaction_timestamp, & clock_timestamp : nowInBlock64 (nowInBlock64(9, $session_timezone))
CURRENT_DATE : now et 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 : Transmise comme valeur par la fonction PostgreSQL.
CURRENT_SCHEMA : Transmise comme valeur par la fonction PostgreSQL.
CURRENT_CATALOG : Transmise comme valeur par la fonction PostgreSQL.
CURRENT_USER : Transmise comme valeur par la fonction PostgreSQL.
USER : transmis comme valeur par la fonction PostgreSQL.
CURRENT_ROLE : transmis comme valeur par la fonction PostgreSQL.
SESSION_USER : transmis comme valeur par la fonction PostgreSQL.

Opérateurs de pushdown

Tranche d’Array (arr[L:U]) : arraySlice
@> (le tableau contient) : hasAll
<@ (tableau contenu dans) : hasAll
&& (chevauchement de tableaux) : hasAny
~ (correspondance regexp) : match
!~ (absence de correspondance regexp) : match
~* (absence de correspondance regexp insensible à la casse) : match
!~* (absence de correspondance regexp insensible à la casse) : match
->> (extraction d’un élément JSON/JSONB sous forme de texte) : syntaxe des sous-colonnes
-> (extraction JSON/JSONB) : toJSONString + syntaxe des sous-colonnes

Sémantique de IN et NULL

ClickHouse évalue IN selon une logique à deux valeurs : lorsqu’aucune correspondance n’est trouvée, il renvoie 0 même si un NULL est impliqué, tandis que PostgreSQL renvoie NULL. Pour préserver la sémantique de PostgreSQL, pg_clickhouse pousse systématiquement vers ClickHouse la famille IN appliquée à une liste ou un tableau constant (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL) : sous sa forme native ou peu coûteuse lorsqu’il peut prouver que ni la valeur testée ni un élément du tableau ne peuvent être NULL, ou sinon sous une forme CASE protégée qui vérifie les valeurs NULL à l’exécution et calcule la réponse exacte à trois valeurs de PostgreSQL (TRUE, FALSE, NULL) dans tous les contextes, y compris dans des positions de valeur telles qu’une liste SELECT ou GROUP BY. Un filtre NOT IN (SELECT ...) sur des colonnes Nullable est également poussé vers ClickHouse, puis déparsé avec des protections compensatoires qui préservent le comportement de PostgreSQL : un ensemble contenant un NULL exclut chaque ligne, et une valeur testée NULL ne passe que face à un ensemble vide. Chaque protection est omise lorsqu’une déclaration NOT NULL prouve qu’elle est inutile. Contrairement aux formes tableau ci-dessus, cette protection ne s’applique que dans une simple condition de filtre (ou sous NOT) ; nous ne poussons toujours pas IN (SELECT ...) vers ClickHouse lorsqu’il est utilisé dans une position de valeur, ni les corps de sous-requêtes groupés ou agrégés. Déclarer les colonnes NOT NULL maximise le pushdown en permettant d’envoyer à la place la forme non protégée, moins coûteuse ; IMPORT FOREIGN SCHEMA le fait automatiquement pour les colonnes ClickHouse non-Nullable. La preuve s’appuie sur les constantes non-NULL, les colonnes NOT NULL et l’arithmétique de base (+, -, *, unary -) appliquée à celles-ci. Ces règles supposent que transform_null_in = 0 conserve sa valeur par défaut dans ClickHouse, que pg_clickhouse définit pour chaque requête via la valeur par défaut du paramètre pg_clickhouse.session_settings, afin qu’un profil de serveur ClickHouse ne puisse pas la modifier silencieusement. Définir transform_null_in = 1 rompt la sémantique de chaque IN poussé vers ClickHouse.

Fonctions personnalisées

Ces fonctions personnalisées créées par pg_clickhouse assurent le pushdown des requêtes externes pour certaines fonctions ClickHouse n’ayant pas d’équivalent dans PostgreSQL. Si l’une de ces fonctions ne peut pas être poussée down, elle déclenchera une exception.

dictGet

Pushdown des extensions

pg_clickhouse reconnaît les fonctions de certaines extensions de base et tierces, et effectue leur pushdown vers leurs équivalents dans ClickHouse.

re2

Tous les opérateurs et fonctions de l’[extension re2] font l’objet d’un pushdown 1:1 à ClickHouse :

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

Une fonction intarray fait l’objet d’un pushdown vers ClickHouse :

idx → indexOf

fuzzystrmatch

Deux fonctions fuzzystrmatch sont déléguées à ClickHouse :

soundex : soundex
levenshtein (à 2 arguments) : editDistanceUTF8

Conversions de type avec pushdown

pg_clickhouse prend en charge le pushdown de conversions de type telles que CAST(x AS bigint) pour les types de données compatibles. Pour les types incompatibles, le pushdown échoue ; si x dans cet exemple est un UInt64 de ClickHouse, ClickHouse refusera de convertir la valeur. Pour effectuer le pushdown de conversions vers des types de données incompatibles, pg_clickhouse fournit les fonctions suivantes. Elles lèvent une exception dans PostgreSQL si elles ne sont pas pushed down.

Agrégats en pushdown

Ces fonctions d’agrégation PostgreSQL sont déléguées à ClickHouse via le pushdown.

Agrégats personnalisés

Ces fonctions d’agrégation personnalisées créées par pg_clickhouse assurent le pushdown des requêtes externes pour certaines fonctions d’agrégation ClickHouse sans équivalent dans PostgreSQL. Si l’une de ces fonctions ne peut pas faire l’objet d’un pushdown, une exception sera levée.

Agrégats d’ensemble ordonné en pushdown

Ces [fonctions d’agrégation d’ensemble ordonné] sont associées aux [fonctions d’agrégation paramétriques] de ClickHouse en passant leur argument direct comme paramètre et leurs expressions ORDER BY comme arguments. Par exemple, cette requête PostgreSQL :

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

Correspond à la requête ClickHouse suivante :

SELECT quantile(0.25)(a) FROM t1;

Notez que les suffixes DESC et NULLS FIRST non définis par défaut pour ORDER BY ne sont pas pris en charge et génèrent une erreur.

percentile_cont(double) : quantile
percentile_cont(double[]) : quantiles
percentile_disc(double) : quantileExactLow
percentile_disc(double[]) : quantilesExactLow

Agrégats d’ensemble ordonné personnalisés

Ces [fonctions d’agrégation d’ensemble ordonné] personnalisées créées par pg_clickhouse permettent le pushdown de requêtes externes pour sélectionner des [fonctions d’agrégation paramétriques] de ClickHouse. Si l’une de ces fonctions ne peut pas être exécutée en pushdown, elle génère une exception.

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

Agrégats d’ensembles ordonnés personnalisés

Ces [fonctions d’agrégation d’ensembles ordonnés] personnalisées, créées par pg_clickhouse, permettent le pushdown de requête externe pour sélectionner des [fonctions d’agrégation paramétriques] ClickHouse. Si l’une de ces fonctions ne peut pas être déléguée, elle génère une exception.

Fonctions de fenêtre prises en charge par le pushdown

Ces [fonctions de fenêtre] PostgreSQL sont déléguées à ClickHouse avec des clauses OVER (PARTITION BY ... ORDER BY ...), y compris des spécifications de cadre le cas échéant.

row_number
rank
dense_rank
ntile
cume_dist
percent_rank
lead
lag
first_value
last_value
nth_value
min / max (avec une clause OVER)

Les fonctions de classement (row_number, rank, dense_rank, ntile, cume_dist, percent_rank) omettent leur clause de cadre lors du pushdown, car ClickHouse rejette les spécifications de cadre pour ces fonctions.

Notes de compatibilité

Expressions régulières

Bien que pg_clickhouse délègue les expressions régulières à leurs équivalents ClickHouse lorsque pg_clickhouse.pushdown_regex vaut true (par défaut), et s’efforce d’assurer un minimum de compatibilité, gardez à l’esprit les différences entre les deux et la manière dont pg_clickhouse les gère.

PostgreSQL prend en charge les [expressions régulières POSIX], tandis que ClickHouse prend en charge les expressions régulières RE2. Tenez compte des différences de comportement : utilisez RE2 lorsque l’expression régulière est évaluée par ClickHouse (par exemple, dans une clause WHERE) et POSIX lorsqu’elle est évaluée par Postgres (par exemple, dans une clause SELECT).
pg_clickhouse répercute les [options Postgres] en les ajoutant en préfixe à l’expression régulière ClickHouse dans (?). Par exemple :
regexp_like(val, '^VAL\d', 'i')
Devient
match(val, concat('(?i)', '^VAL\\d'))

Les seules options prises en charge par les deux, et qui peuvent donc être utilisées lorsqu’elles sont évaluées par ClickHouse, sont :

Flag	Équiv.	Remarques
`i`	`i`	correspondance insensible à la casse
`m`	`m-s`	`^` et `$` correspondent au début/à la fin d’une ligne, en plus du début/de la fin du texte
`n`	`m-s`	alias Postgres de `m`
`p`	`-s`	empêche `.` et `[^x]` de correspondre à `\n`
`s`	`s`	autorise `.` et `[^x]` à correspondre à `\n`
`t`		syntaxe stricte, sans effet
`w`	`m`	correspondance partielle inverse sensible aux retours à la ligne

RE2 ne prend en charge que ces options ; n’utilisez pas d’autres [options Postgres].

Ce tableau résume les effets des différents modificateurs (et de l’absence de modificateur, qui revient au même que s) pour la correspondance des sauts de ligne et des fins de ligne. Notez que dans Postgres, m et p empêchent les classes de caractères négatives ([^xyz]) de correspondre à un saut de ligne, alors que les équivalents de ClickHouse ne le font pas. Sinon, les comportements sont les mêmes dans ClickHouse que dans Postgres :

Motif appliqué à `a\nb`	Postgres	ClickHouse	Identique ?
`a.b`	true	true	✔︎
`a[^x]b`	true	true	✔︎
`a$`	false	false	✔︎
Modificateur `s`
`(?s)a.b`	true	true	✔︎
`(?s)a[^x]b`	true	true	✔︎
`(?s)a$`	false	false	✔︎
Modificateur `m`
`(?m)a.b`	false	false	✔︎
`(?m)a[^x]b`	true	false	✘
`(?m)a$`	true	true	✔︎
Modificateur `p`
`(?p)a.b`	false	false	✔︎
`(?p)a[^x]b`	true	false	✘
`(?p)a$`	false	false	✔︎
Modificateur `w`
`(?w)a.b`	true	true	✔
`(?w)a[^x]b`	true	true	✔
`(?w)a$`	true	true	✔

Tout autre flag transmis aux fonctions d’expression régulière empêchera le pushdown de la fonction.
L’exception est regexp_replace(), qui prend aussi en charge le flag g. Lorsque g est activé, pg_clickhouse utilise replaceRegexpAll() au lieu de replaceRegexpOne() et supprime le flag avant d’ajouter les autres flags en préfixe.
L’argument de remplacement de regexp_replace() dans Postgres prend en charge \& pour désigner la correspondance complète, tandis que ClickHouse utilise \0 pour la correspondance complète. Veillez à utiliser \0 lorsque la fonction fait l’objet d’un pushdown vers ClickHouse.
Postgres regexp_match renvoie NULL lorsqu’il n’y a aucune correspondance, tandis que les expressions dont l’exécution est déléguée renvoient un tableau vide. Utilisez COALESCE() pour renvoyer un tableau vide au lieu de NULL afin de comparer les valeurs de retour de manière compatible. Par exemple :
SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');

Pour lever toute ambiguïté, envisagez de définir pg_clickhouse.pushdown_regex pour empêcher le pushdown des expressions régulières Postgres vers ClickHouse, et d’utiliser l’ [extension re2], pour laquelle pg_clickhouse prend en charge le pushdown direct des expressions régulières RE2 compatibles avec ClickHouse.

`to_char()`

Le to_char() de PostgreSQL pour timestamp et timestamp with time zone n’est délégué à ClickHouse formatDateTime que lorsque l’argument de format est une constante de chaîne non-NULL dont chaque mot-clé PostgreSQL a un équivalent ClickHouse strictement identique, octet pour octet. Si le format est dynamique (et non un Const), ou s’il contient un mot-clé ou un modificateur non pris en charge, l’appel retombe sur une évaluation locale dans PostgreSQL — aucun pushdown n’est jamais tenté avec une traduction partielle, de sorte que le résultat reste compatible avec PG. Les formes à deux arguments de to_char() appliquées à numeric, interval et à d’autres types autres que timestamp ne font jamais l’objet d’un pushdown ; ClickHouse formatDateTime ne formate que les valeurs de date et d’heure.

Mots-clés traduits

PostgreSQL	ClickHouse	Signification
`YYYY`, `yyyy`	`%Y`	année à 4 chiffres
`YY`, `yy`	`%y`	année à 2 chiffres
`MM`, `mm`	`%m`	mois à 2 chiffres, complété par un zéro à gauche (01–12)
`DD`, `dd`	`%d`	jour du mois à 2 chiffres, complété par un zéro à gauche (01–31)
`DDD`, `ddd`	`%j`	jour de l’année à 3 chiffres, complété par un zéro à gauche (001–366)
`HH24`, `hh24`	`%H`	heure sur 24 heures, complétée par un zéro à gauche (00–23)
`HH`, `hh`, `HH12`, `hh12`	`%I`	heure sur 12 heures, complétée par un zéro à gauche (01–12)
`MI`, `mi`	`%i`	minute à 2 chiffres, complétée par un zéro à gauche (00–59)
`SS`, `ss`	`%S`	seconde à 2 chiffres, complétée par un zéro à gauche (00–59)
`Q`, `q`	`%Q`	trimestre (1–4)
`Mon`	`%b`	nom du mois abrégé, par ex. `Oct`
`Dy`	`%a`	nom du jour de la semaine abrégé, par ex. `Mon`
`AM`, `PM`	`%p`	indicateur AM/PM, toujours en majuscules

Texte entre guillemets et littéraux

Le texte placé entre "..." est transmis tel quel, avec chaque % littéral doublé en %% pour échapper au préfixe de spécificateur de ClickHouse. Un \" en dehors des guillemets est également transmis comme un ". À l’intérieur de "...", l’antislash n’échappe que " ; les autres séquences avec antislash sont traitées comme du texte littéral.

Auteurs

David E. Wheeler

​Description

​Prise en main

​Utilisation

​Politique de versionnage

​Référence SQL DDL

​CREATE EXTENSION

​ALTER EXTENSION

​DROP EXTENSION

​CREATE SERVER

​ALTER SERVER

​DROP SERVER

​CREATE USER MAPPING

​ALTER USER MAPPING

​DROP USER MAPPING

​IMPORT FOREIGN SCHEMA

​CREATE FOREIGN TABLE

​ALTER FOREIGN TABLE

​DROP FOREIGN TABLE

​Référence SQL DML

​EXPLAIN

​SELECT

​Tables partitionnées

​Quand les agrégats partiels sont délégués

​Lorsqu’ils basculent vers une solution de repli

​PREPARE, EXECUTE, DEALLOCATE

​INSERT

​COPY

​LOAD

​SET

​pg_clickhouse.session_settings

​pg_clickhouse.pushdown_regex

​ALTER ROLE

​Préchargement

​session_preload_libraries

​shared_preload_libraries

​Types de données

​BYTEA

​Référence des fonctions et des opérateurs

​Fonctions

​clickhouse_raw_query

​clickhouse_server_version

​clickhouse_query

​clickhouse_perform

​Fonctions de pushdown

​Opérateurs de pushdown

​Sémantique de IN et NULL

​Fonctions personnalisées

​Pushdown des extensions

​re2

​intarray

​fuzzystrmatch

​Conversions de type avec pushdown

​Agrégats en pushdown

​Agrégats personnalisés

​Agrégats d’ensemble ordonné en pushdown

​Agrégats d’ensemble ordonné personnalisés

​Agrégats d’ensembles ordonnés personnalisés

​Fonctions de fenêtre prises en charge par le pushdown

​Notes de compatibilité

​Expressions régulières

​to_char()

​Mots-clés traduits

​Texte entre guillemets et littéraux

​Auteurs

​Droits d’auteur

Description

Prise en main

Utilisation

Politique de versionnage

Référence SQL DDL

CREATE EXTENSION

ALTER EXTENSION

DROP EXTENSION

CREATE SERVER

ALTER SERVER

DROP SERVER

CREATE USER MAPPING

ALTER USER MAPPING

DROP USER MAPPING

IMPORT FOREIGN SCHEMA

CREATE FOREIGN TABLE

ALTER FOREIGN TABLE

DROP FOREIGN TABLE

Référence SQL DML

EXPLAIN

SELECT

Tables partitionnées

Quand les agrégats partiels sont délégués

Lorsqu’ils basculent vers une solution de repli

PREPARE, EXECUTE, DEALLOCATE

INSERT

COPY

LOAD

SET

`pg_clickhouse.session_settings`

`pg_clickhouse.pushdown_regex`

ALTER ROLE

Préchargement

`session_preload_libraries`

`shared_preload_libraries`

Types de données

BYTEA

Référence des fonctions et des opérateurs

Fonctions

`clickhouse_raw_query`

`clickhouse_server_version`

`clickhouse_query`

`clickhouse_perform`

Fonctions de pushdown

Opérateurs de pushdown

Sémantique de IN et NULL

Fonctions personnalisées

Pushdown des extensions

re2

intarray

fuzzystrmatch

Conversions de type avec pushdown

Agrégats en pushdown

Agrégats personnalisés

Agrégats d’ensemble ordonné en pushdown

Agrégats d’ensemble ordonné personnalisés

Agrégats d’ensembles ordonnés personnalisés

Fonctions de fenêtre prises en charge par le pushdown

Notes de compatibilité

Expressions régulières

`to_char()`

Mots-clés traduits

Texte entre guillemets et littéraux

Auteurs

Droits d’auteur