Перейти к основному содержимому
Перейти к основному содержимому

Справочная документация по pg_clickhouse

Описание​

pg_clickhouse — это расширение PostgreSQL, позволяющее удалённо выполнять запросы к базам данных ClickHouse, включая реализацию [обёртки внешних данных (foreign data wrapper)]. Оно поддерживает PostgreSQL 13 и новее и ClickHouse 23 и новее.

Начало работы​

Самый простой способ попробовать pg_clickhouse — использовать [образ Docker], который содержит стандартный образ PostgreSQL с расширениями pg_clickhouse и 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

См. руководство, чтобы начать импортировать таблицы 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 следует [Семантическому версионированию] для своих публичных релизов.

  • Старшая версия увеличивается при изменениях API
  • Младшая версия увеличивается при обратно совместимых изменениях SQL
  • Патч-версия увеличивается при изменениях только на уровне бинарных файлов

После установки PostgreSQL отслеживает два варианта номера версии:

  • Версия библиотеки (определяется PG_MODULE_MAGIC в PostgreSQL 18 и выше) включает полную семантическую версию, видимую в выводе функции pgch_version() или функции Postgres pg_get_loaded_modules().
  • Версия расширения (определяется в control-файле) включает только старшую и младшую версии, видимую в таблице pg_catalog.pg_extension, в выводе функции pg_available_extension_versions() и в \dx pg_clickhouse.

На практике это означает, что релиз, который увеличивает патч-версию, например с v0.1.0 до v0.1.1, применяется ко всем базам данных, которые загрузили v0.1, и им не нужно выполнять ALTER EXTENSION, чтобы получить преимущества обновления.

Релиз, который увеличивает младшую или старшую версии, напротив, будет сопровождаться SQL-скриптами обновления, и все существующие базы данных, в которых установлено это расширение, должны выполнить ALTER EXTENSION pg_clickhouse UPDATE, чтобы получить преимущества обновления.

Справочник по SQL DDL​

В следующих 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​

Используйте ALTER EXTENSION, чтобы изменить расширение pg_clickhouse. Примеры:

  • После установки новой версии pg_clickhouse используйте оператор UPDATE:

    ALTER EXTENSION pg_clickhouse UPDATE;

  • Используйте SET SCHEMA, чтобы перенести расширение в другую схему:

    CREATE SCHEMA ch;
ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION​

Используйте DROP EXTENSION, чтобы удалить расширение pg_clickhouse из базы данных:

DROP EXTENSION pg_clickhouse;

Эта команда завершится с ошибкой, если существуют какие-либо объекты, зависящие от pg_clickhouse. Используйте предложение CASCADE, чтобы удалить и их:

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER​

Используйте CREATE SERVER, чтобы создать внешний сервер для подключения к серверу ClickHouse. Пример:

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

Поддерживаемые параметры:

  • driver: драйвер подключения к ClickHouse, который следует использовать — либо "binary", либо "http". Обязательный.
  • dbname: база данных ClickHouse, которая будет использоваться при подключении. По умолчанию "default".
  • fetch_size: приблизительный размер пакета в байтах для HTTP-streaming. Пакеты разделяются по границам строк. По умолчанию 50000000 (50 МБ). Значение 0 отключает streaming и буферизует полный ответ. Внешние таблицы могут переопределить это значение.
  • host: имя хоста сервера ClickHouse. По умолчанию "localhost".
  • port: порт для подключения к серверу ClickHouse. Значения по умолчанию:
    • 9440, если driver — "binary" и host является хостом ClickHouse Cloud
    • 9004, если driver — "binary" и host не является хостом ClickHouse Cloud
    • 8443, если driver — "http" и host является хостом ClickHouse Cloud
    • 8123, если driver — "http" и host не является хостом ClickHouse Cloud

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. Например, чтобы сопоставить текущего пользователя PostgreSQL с удалённым пользователем ClickHouse при подключении к внешнему серверу taxi_srv:

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.

Imported Identifier Case Preservation

IMPORT FOREIGN SCHEMA выполняет quote_identifier() для импортируемых имён таблиц и столбцов, что приводит к заключению в двойные кавычки идентификаторов с прописными буквами или пробелами. Такие имена таблиц и столбцов, соответственно, должны указываться в двойных кавычках в запросах PostgreSQL. Имена, состоящие только из строчных букв и не содержащие пробелов, не нужно заключать в кавычки.

Например, для следующей таблицы ClickHouse:

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

IMPORT FOREIGN SCHEMA создаёт следующую внешнюю таблицу:

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

Поэтому в запросах такие имена нужно правильно заключать в кавычки, например:

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

Чтобы создать объекты с другими именами или именами целиком в нижнем регистре (а значит, нечувствительными к регистру), используйте CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE​

Используйте CREATE FOREIGN TABLE, чтобы создать внешнюю таблицу, которая может выполнять запросы к данным из базы данных 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'
);

Поддерживаются следующие параметры таблицы:

  • database: Имя удалённой базы данных. По умолчанию используется база данных, заданная для внешнего сервера.
  • fetch_size: Примерный размер пакета в байтах для HTTP-стриминга. Переопределяет серверный параметр fetch_size. По умолчанию — 50000000 (50 MB). Значение 0 отключает стриминг и буферизует полный ответ.
  • table_name: Имя удалённой таблицы. По умолчанию используется имя, указанное для внешней таблицы.
  • engine: [движок таблицы], используемый таблицей ClickHouse. Для CollapsingMergeTree() и AggregatingMergeTree() pg_clickhouse автоматически применяет параметры к функциональным выражениям, выполняемым для таблицы.

Используйте тип данных, соответствующий удалённому типу данных ClickHouse для каждого столбца. Поддерживаются следующие параметры столбцов:

  • column_name: Имя столбца на стороне ClickHouse, которое используется вместо имени атрибута 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 Type. Сопоставьте тип данных с типом ClickHouse, передаваемым в функцию, и укажите имя агрегатной функции через соответствующий параметр столбца; pg_clickhouse автоматически добавит Merge к агрегатной функции, вычисляющей этот столбец.

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

  • SimpleAggregateFunction: Имя агрегатной функции, применяемой к столбцу типа SimpleAggregateFunction Type. Сопоставьте тип данных с типом ClickHouse, передаваемым в функцию, и укажите имя агрегатной функции через соответствующий параметр столбца.

ALTER FOREIGN TABLE​

Используйте команду ALTER FOREIGN TABLE, чтобы изменить определение внешней таблицы:

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

Поддерживаемые параметры таблиц и столбцов совпадают с параметрами для CREATE FOREIGN TABLE.

DROP FOREIGN TABLE​

Используйте оператор DROP FOREIGN TABLE для удаления внешней таблицы:

DROP FOREIGN TABLE acts;

Эта команда завершится с ошибкой, если существуют какие-либо объекты, зависящие от внешней таблицы. Используйте ключевое слово CASCADE, чтобы удалить и их:

DROP FOREIGN TABLE acts CASCADE;

Справочник по SQL DML​

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)

Этот запрос пробрасывается в ClickHouse в виде удалённого SQL через плановый узел "Foreign Scan".

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 и выполняем соединение с ней вместо удалённой таблицы:

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

В этом случае мы можем переложить больше работы по агрегации на ClickHouse, выполняя группировку по node_id вместо локального столбца, а затем выполнить 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 (всех) до всего лишь 8, по одной на каждый узел.

PREPARE, EXECUTE, DEALLOCATE​

Начиная с версии v0.1.2, pg_clickhouse поддерживает параметризованные запросы, как правило создаваемые командой PREPARE:

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

Используйте EXECUTE как обычно, чтобы выполнить подготовленный запрос:

try=# EXECUTE avg_durations_between_dates('2025-12-09', '2025-12-13');
    date    | average_duration
------------+------------------
 2025-12-09 |              190
 2025-12-10 |              194
 2025-12-11 |              197
 2025-12-12 |              190
 2025-12-13 |              195
(5 rows)
Примечание

Параметризованное выполнение не позволяет http-драйверу корректно преобразовывать часовые пояса для значений DateTime в версиях ClickHouse до 25.8, когда [основная ошибка] была [исправлена]. Обратите внимание, что PostgreSQL иногда использует параметризованный план запроса даже без PREPARE. Для любых запросов, которым требуется точное преобразование часовых поясов и когда обновление до 25.8 или более новой версии невозможно, вместо этого используйте бинарный драйвер.

pg_clickhouse, как и обычно, проталкивает агрегации на нижележащий уровень, что видно из подробного вывода EXPLAIN:

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

Обратите внимание, что отправлены полные значения дат, а не шаблоны параметров. Это справедливо для первых пяти запросов, как описано в PostgreSQL PREPARE notes. При шестом выполнении он отправляет в 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​

Используйте команду INSERT, чтобы вставлять значения в удалённую таблицу ClickHouse:

try=# INSERT INTO nodes(node_id, name, region, arch, os)
VALUES (9,  'Augustin Gamarra', 'us-west-2', 'amd64', 'Linux')
     , (10, 'Cerisier', 'us-east-2', 'amd64', 'Linux')
     , (11, 'Dewalt', 'use-central-1', 'arm64', 'macOS')
;
INSERT 0 3

COPY​

Используйте команду COPY, чтобы вставить пакет строк в удалённую таблицу ClickHouse:

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 Batch Insert API. Поэтому COPY в настоящее время использует команды INSERT для вставки записей. Это будет улучшено в одном из следующих релизов.

LOAD​

Используйте LOAD, чтобы загрузить общую библиотеку pg_clickhouse:

try=# LOAD 'pg_clickhouse';
LOAD

Обычно нет необходимости использовать LOAD, так как Postgres автоматически загружает pg_clickhouse при первом использовании любой из его возможностей (функции, внешние таблицы и т. д.).

Единственный случай, когда может быть полезно выполнить LOAD для pg_clickhouse, — это задать с помощью SET параметры pg_clickhouse перед выполнением зависящих от них запросов.

SET​

Используйте SET, чтобы задать пользовательские параметры конфигурации pg_clickhouse.

pg_clickhouse.session_settings​

Этот параметр настраивает [параметры ClickHouse], которые будут применены к последующим запросам. Пример:

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

По умолчанию — join_use_nulls 1, group_by_use_nulls 1, final 1. Установите пустую строку, чтобы перейти к использованию настроек сервера ClickHouse.

SET pg_clickhouse.session_settings = '';

Синтаксис: список пар ключ/значение, разделённых запятыми; ключ и значение в паре разделяются одним или несколькими пробелами. Ключи должны соответствовать [настройкам ClickHouse]. В значениях экранируйте пробелы, запятые и обратные косые черты с помощью обратной косой черты:

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

Или используйте значения в одинарных кавычках, чтобы избежать экранирования пробелов и запятых; рассмотрите возможность использования dollar quoting, чтобы избежать необходимости двойного заключения в кавычки:

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 должен быть загружен до задания pg_clickhouse.session_settings; либо используйте [предзагрузку общей библиотеки], либо просто используйте один из объектов расширения, чтобы гарантировать его загрузку.

pg_clickhouse.pushdown_regex​

Параметр pg_clickhouse.pushdown_regex управляет тем, передаёт ли pg_clickhouse обработку функций и операторов регулярных выражений на нижележащий уровень. По умолчанию это включено; установите для этого параметра значение false, чтобы отключить такую передачу:

SET pg_clickhouse.pushdown_regex = 'false';

Подробнее см. в разделе Регулярные выражения.

ALTER ROLE​

Используйте команду SET оператора ALTER ROLE для предварительной загрузки pg_clickhouse и/или настройки его параметров для определённых ролей:

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

Используйте команду RESET оператора ALTER ROLE, чтобы сбросить предзагрузку pg_clickhouse и/или его параметры:

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

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

Предварительная загрузка​

Если каждому или почти каждому подключению к Postgres нужно использовать pg_clickhouse, рассмотрите возможность использования [предварительной загрузки общих библиотек], чтобы он загружался автоматически:

session_preload_libraries​

Загружает разделяемую библиотеку для каждого нового соединения с PostgreSQL:

session_preload_libraries = pg_clickhouse

Полезно, чтобы применять обновления без перезапуска сервера: достаточно просто переподключиться. Этот параметр также можно задать для отдельных пользователей или ролей с помощью ALTER ROLE.

shared_preload_libraries​

Загружает общую библиотеку в родительский процесс PostgreSQL при запуске:

shared_preload_libraries = pg_clickhouse

Полезно для экономии памяти и снижения накладных расходов на загрузку в каждом сеансе, но при обновлении библиотеки требуется перезапуск кластера.

Типы данных​

pg_clickhouse сопоставляет следующие типы данных ClickHouse с типами данных PostgreSQL. IMPORT FOREIGN SCHEMA использует первый тип в определении столбца PostgreSQL при импорте столбцов; дополнительные типы могут указываться в командах CREATE FOREIGN TABLE:

ClickHousePostgreSQLПримечания
Boolboolean
Datedate
Date32date
DateTimetimestamptz
Decimalnumeric
Float32real
Float64double precision
IPv4inet
IPv6inet
Int16smallint
Int32integer
Int64bigint
Int8smallint
JSONjsonb, json
Stringtext, bytea
UInt16integer
UInt32bigint
UInt64bigintОшибка для значений > максимального значения BIGINT
UInt8smallint
UUIDuuid

Дополнительные замечания и подробности приведены ниже.

BYTEA​

ClickHouse не предоставляет эквивалента типа PostgreSQL BYTEA, однако позволяет хранить произвольные байты в типе String. В общем случае строки ClickHouse следует сопоставлять с типом PostgreSQL TEXT, но при работе с двоичными данными используйте тип BYTEA. Пример:

-- Create clickHouse table with String columns.
SELECT clickhouse_raw_query($$
    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)

Обратите внимание: если в столбцах ClickHouse присутствуют нулевые байты, внешняя таблица, использующая столбцы TEXT, не будет выводить правильные значения:

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

Вывод:

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

Обратите внимание, что вторая и третья строки содержат усечённые значения. Это связано с тем, что PostgreSQL использует строки, завершающиеся нулевым символом, и не поддерживает нулевые символы внутри строк.

Попытка вставить бинарные значения в столбцы 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)
Совет

Как правило, используйте столбцы TEXT только для закодированных строк, а столбцы BYTEA — только для двоичных данных и никогда не используйте их взаимозаменяемо.

Справочник функций и операторов​

Функции​

Эти функции предоставляют интерфейс для выполнения запросов к базе данных ClickHouse.

clickhouse_raw_query​

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

Подключается к серверу ClickHouse через его HTTP‑интерфейс, выполняет один запрос и отключается. Необязательный второй аргумент задаёт строку подключения, по умолчанию host=localhost port=8123. Поддерживаемые параметры подключения:

  • host: Хост, к которому выполняется подключение; обязательный параметр.
  • port: HTTP‑порт для подключения; по умолчанию 8123, если только host не является хостом ClickHouse Cloud, — в этом случае по умолчанию используется 8443
  • dbname: Имя базы данных, к которой выполняется подключение.
  • username: Имя пользователя, под которым выполняется подключение; по умолчанию default
  • password: Пароль, используемый для аутентификации; по умолчанию пароль не используется

По умолчанию ни одна роль не имеет права EXECUTE для этой функции; доступ с помощью GRANT следует предоставлять только тем ролям, которым действительно необходимо выполнять специальные запросы ClickHouse, например выделенной административной роли ClickHouse:

Полезно для запросов, которые не возвращают записей; результаты запросов, которые возвращают значения, возвращаются в виде одного текстового значения:

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

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

(1 row)

Функции передачи​

pg_clickhouse передаёт на выполнение в ClickHouse подмножество встроенных функций PostgreSQL, используемых в условных выражениях (предложения HAVING и WHERE). Это подмножество сопоставляется с эквивалентами ClickHouse следующим образом:

Операторы передачи​

  • Срез массива (arr[L:U]): arraySlice
  • @> (массив содержит): hasAll
  • <@ (массив содержится в): hasAll
  • && (массивы пересекаются): hasAny
  • ~ (совпадение с регулярным выражением): match
  • !~ (нет совпадения с регулярным выражением): match
  • ~* (регистронезависимое совпадение с регулярным выражением): match
  • !~* (регистронезависимое несовпадение с регулярным выражением): match
  • ->> (извлечение элемента JSON/JSONB как текста): sub-column syntax
  • -> (извлечение из JSON/JSONB): toJSONString + sub-column syntax

Пользовательские функции​

Эти пользовательские функции, созданные pg_clickhouse, обеспечивают передачу удалённых запросов для отдельных функций ClickHouse, не имеющих эквивалентов в PostgreSQL. Если какую-либо из этих функций нельзя передать, будет возбуждено исключение.

Передача расширений​

pg_clickhouse распознаёт функции из некоторых встроенных и сторонних расширений и передаёт их эквивалентам в ClickHouse.

re2​

Все функции [расширения re2] напрямую передаются в ClickHouse один к одному:

intarray​

Одна функция intarray передается на выполнение в ClickHouse:

fuzzystrmatch​

Две функции из fuzzystrmatch передаются на выполнение в ClickHouse:

Передача приведений типов​

pg_clickhouse проталкивает приведения типов, такие как CAST(x AS bigint), для совместимых типов данных. Для несовместимых типов операция передачи завершится ошибкой; если x в этом примере — ClickHouse UInt64, ClickHouse откажется выполнять такое приведение.

Для того чтобы выполнять приведения с передачей к несовместимым типам данных, pg_clickhouse предоставляет следующие функции. Они вызывают исключение в PostgreSQL, если приведение не было протолкнуто в ClickHouse.

Агрегаты с передачей​

Эти агрегатные функции PostgreSQL выполняются в ClickHouse посредством передачи.

Пользовательские агрегаты​

Эти пользовательские агрегатные функции, созданные pg_clickhouse, обеспечивают передачу удалённых запросов (foreign query pushdown) для отдельных агрегатных функций ClickHouse, не имеющих эквивалентов в PostgreSQL. Если какую-либо из этих функций невозможно передать, будет сгенерировано исключение.

Передача Ordered Set Aggregates​

Эти ordered-set aggregate functions сопоставляются с Parametric aggregate functions в ClickHouse: их прямой аргумент передается как параметр, а выражения ORDER BY — как аргументы. Например, этот запрос 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 не поддерживаются и приведут к ошибке.

Передача для оконных функций​

Эти [оконные функции] PostgreSQL передаются на сторону ClickHouse с предложением OVER (PARTITION BY ... ORDER BY ...), включая спецификации фрейма там, где это применимо.

При передаче в ClickHouse у функций ранжирования (row_number, rank, dense_rank, ntile, cume_dist, percent_rank) предложение frame опускается, поскольку ClickHouse не поддерживает спецификации фрейма для этих функций.

Примечания по совместимости​

Регулярные выражения​

Хотя pg_clickhouse передаёт регулярные выражения в эквиваленты ClickHouse, когда pg_clickhouse.pushdown_regex равно true (по умолчанию), и старается обеспечить базовый уровень совместимости, учитывайте различия между ними и то, как pg_clickhouse их обрабатывает.

  • PostgreSQL поддерживает [регулярные выражения POSIX], а ClickHouse — регулярные выражения RE2. Учитывайте различия в поведении: используйте RE2, когда регулярное выражение будет вычисляться в ClickHouse (например, в предложении WHERE), и POSIX, когда оно будет вычисляться в Postgres (например, в предложении SELECT).

  • pg_clickhouse передаёт [флаги регулярных выражений] Postgres, добавляя их в начало регулярного выражения ClickHouse внутри (?). Например:

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

    Превращается в

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

    Обратите внимание на добавление -s; это приводит поведение в соответствие с регулярными выражениями Postgres за счёт отключения s, который в ClickHouse включён по умолчанию. pg_clickhouse не добавляет -s, если флаги в вызове функции Postgres включают s. К сожалению, из-за этого нарушается совместимость некоторых регулярных выражений в Postgres 24 и более ранних версиях.

  • Единственные флаги, которые поддерживаются обеими системами и поэтому могут использоваться при вычислении в ClickHouse:

    • i: без учёта регистра
    • m: многострочный режим:
    • s: позволяет . соответствовать \n
    • p: частичное сопоставление с учётом новой строки (обрабатывается так же, как s)
    • t: строгий синтаксис (по умолчанию, удаляется pg_clickhouse)

    RE2 поддерживает только эти флаги; не используйте никакие другие [флаги Postgres]

  • Любые другие флаги, переданные в функции регулярных выражений, приведут к тому, что функция не будет передана в ClickHouse.

  • Исключение — regexp_replace(), которая также поддерживает флаг g. Когда установлен g, pg_clickhouse использует replaceRegexpAll() вместо replaceRegexpOne() и удаляет этот флаг перед добавлением остальных флагов в начало.

  • Аргумент замены в Postgres regexp_replace() поддерживает \& для ссылки на всё совпадение, тогда как в ClickHouse для всего совпадения используется \0. Обязательно используйте \0, когда функция передаётся в ClickHouse.

Чтобы полностью избежать неоднозначности, рассмотрите возможность настройки pg_clickhouse.pushdown_regex, чтобы предотвратить передачу регулярных выражений Postgres в ClickHouse, и используйте [расширение re2], для которого pg_clickhouse поддерживает прямую передачу регулярных выражений RE2, совместимых с ClickHouse.

to_char()​

PostgreSQL to_char() для timestamp и timestamp with time zone передаётся в ClickHouse formatDateTime только в том случае, если аргумент формата является строковой константой не-NULL и для каждого ключевого слова PostgreSQL существует побайтно идентичный эквивалент в ClickHouse. Если формат задаётся динамически (не Const) или содержит неподдерживаемое ключевое слово либо модификатор, вызов обрабатывается локально в PostgreSQL — частичный pushdown никогда не используется, поэтому результат остаётся совместимым с PG.

Варианты to_char() с двумя аргументами для numeric, interval и других типов, не относящихся к временным меткам, никогда не передаются; ClickHouse formatDateTime форматирует только значения даты и времени.

Переведённые ключевые слова​

PostgreSQLClickHouseЗначение
YYYY, yyyy%Y4-значный год
YY, yy%y2-значный год
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индикатор времени AM/PM, всегда в верхнем регистре

Текст в кавычках и литералы​

Текст в "..." передаётся как есть; при этом любой литеральный % удваивается до %%, чтобы экранировать префикс спецификатора в ClickHouse. \" вне кавычек также передаётся как литеральный ". Внутри "..." обратная косая черта экранирует только "; остальные последовательности с обратной косой чертой трактуются как литеральный текст.

Автор​

David E. Wheeler

Copyright (c) 2025-2026, ClickHouse