documentação de referência do pg_clickhouse

Descrição

pg_clickhouse é uma extensão do PostgreSQL que permite executar consultas remotamente em bancos de dados do ClickHouse, incluindo um foreign data wrapper. É compatível com o PostgreSQL 13 ou superior e o ClickHouse 23.3 ou superior.

Primeiros passos

A forma mais simples de testar o pg_clickhouse é usar a [imagem Docker], que contém a imagem Docker padrão do PostgreSQL com as extensões pg_clickhouse e [re2][extensão 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

Consulte o tutorial para começar a importar tabelas do ClickHouse e delegar consultas ao ClickHouse.

Uso

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;

Política de versionamento

pg_clickhouse segue o [Versionamento Semântico] em suas versões públicas.

A versão principal é incrementada para mudanças na API
A versão secundária é incrementada para mudanças de SQL compatíveis com versões anteriores
A versão de correção é incrementada para mudanças apenas no binário

Depois de instalado, o PostgreSQL acompanha duas variações da versão:

A versão da biblioteca (definida por PG_MODULE_MAGIC no PostgreSQL 18 e superiores) inclui a versão semântica completa, visível na saída da função pgch_version() ou da função pg_get_loaded_modules() do Postgres.
A versão da extensão (definida no arquivo de controle) inclui apenas as versões principal e secundária, visíveis na tabela pg_catalog.pg_extension, na saída da função pg_available_extension_versions() e em \dx pg_clickhouse.

Na prática, isso significa que uma versão que incrementa a versão de correção, por exemplo, de v0.1.0 para v0.1.1, beneficia todos os bancos de dados que carregaram v0.1 e não precisam executar ALTER EXTENSION para aproveitar a atualização. Já uma versão que incrementa a versão secundária ou principal virá acompanhada de scripts de atualização SQL, e todos os bancos de dados existentes que contêm a extensão deverão executar ALTER EXTENSION pg_clickhouse UPDATE para aproveitar a atualização.

Referência de SQL DDL

As expressões SQL DDL a seguir usam o pg_clickhouse.

CREATE EXTENSION

Use CREATE EXTENSION para adicionar a extensão pg_clickhouse a um banco de dados:

CREATE EXTENSION pg_clickhouse;

Use WITH SCHEMA para instalá-la em um esquema específico (recomendado):

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

Use ALTER EXTENSION para modificar a extensão pg_clickhouse. Exemplos:

Depois de instalar uma nova versão do pg_clickhouse, use a cláusula UPDATE:
ALTER EXTENSION pg_clickhouse UPDATE;

Use SET SCHEMA para mover a extensão para um novo esquema:

CREATE SCHEMA ch;
ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION

Use DROP EXTENSION para remover pg_clickhouse de um banco de dados:

DROP EXTENSION pg_clickhouse;

Este comando falha se houver objetos que dependam de pg_clickhouse. Use a cláusula CASCADE para removê-los também:

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

Use CREATE SERVER para criar um servidor externo que se conecta a um servidor ClickHouse. Exemplo:

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

As opções compatíveis são:

driver: O driver de conexão do ClickHouse a ser usado: “binary” ou “http”. Obrigatório.
compression: Compressão do protocolo nativo para o driver “binary”, uma entre “none”, “lz4” ou “zstd”. O padrão é “lz4”. Ignorada pelo driver “http”.
dbname: O banco de dados do ClickHouse a ser usado na conexão. O padrão é “default”.
host: O nome do host do servidor ClickHouse. O padrão é “localhost”;
port: A porta à qual se conectar no servidor ClickHouse. Os padrões são os seguintes:
- 9440 se driver for “binary” e host for um host do ClickHouse Cloud
- 9004 se driver for “binary” e host não for um host do ClickHouse Cloud
- 8443 se driver for “http” e host for um host do ClickHouse Cloud
- 8123 se driver for “http” e host não for um host do ClickHouse Cloud
min_tls_version: Versão mínima do protocolo TLS a ser negociada em conexões que usam TLS. Uma entre TLSv1, TLSv1.1, TLSv1.2 ou TLSv1.3. O padrão é o mínimo da própria biblioteca TLS. Aplica-se a ambos os drivers.
secure: Controla o uso de TLS na conexão. Uma entre:
- auto (padrão): usa TLS quando host é um host do ClickHouse Cloud ou port é uma porta segura; caso contrário, plaintext.
- on (ou true/yes/1): sempre usa TLS. O padrão de port é 8443 (“http”) ou 9440 (“binary”).
- off (ou false/no/0): nunca usa TLS. O padrão de port é 8123 (“http”) ou 9000 (“binary”).

ALTER SERVER

Use ALTER SERVER para modificar um servidor externo. Exemplo:

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

As opções são as mesmas do CREATE SERVER.

DROP SERVER

Use o DROP SERVER para remover um servidor externo:

DROP SERVER taxi_srv;

Esse comando falha se houver quaisquer outros objetos que dependam do servidor. Use CASCADE para também remover essas dependências:

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

Use CREATE USER MAPPING para mapear um usuário do PostgreSQL a um usuário do ClickHouse. Por exemplo, para mapear o usuário atual do PostgreSQL ao usuário remoto do ClickHouse ao se conectar ao servidor externo taxi_srv:

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

As opções compatíveis são:

user: O nome do usuário do ClickHouse. O valor padrão é “default”.
password: A senha do usuário do ClickHouse.

ALTER USER MAPPING

Use ALTER USER MAPPING para alterar a definição de um mapeamento de usuário:

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

As opções são as mesmas de CREATE USER MAPPING.

DROP USER MAPPING

Use o comando DROP USER MAPPING para remover um mapeamento de usuário:

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

Use IMPORT FOREIGN SCHEMA para importar todas as tabelas definidas em um banco de dados do ClickHouse como tabelas externas em um esquema do PostgreSQL:

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

Use LIMIT TO para restringir a importação a tabelas específicas:

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

Use EXCEPT para excluir tabelas:

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

pg_clickhouse recuperará uma lista de todas as tabelas no banco de dados ClickHouse especificado (“demo” nos exemplos acima), recuperará as definições das colunas de cada uma e executará comandos CREATE FOREIGN TABLE para criar as tabelas externas. As colunas serão definidas usando os tipos de dados suportados e, quando isso for detectável, as opções suportadas por CREATE FOREIGN TABLE.

Preservação de maiúsculas e minúsculas em identificadores importadosIMPORT FOREIGN SCHEMA executa quote_identifier() nos nomes de tabelas e colunas que importa, o que coloca aspas duplas em identificadores com caracteres maiúsculos ou espaços em branco. Portanto, esses nomes de tabelas e colunas devem ser colocados entre aspas duplas em consultas do PostgreSQL. Nomes com apenas letras minúsculas e sem espaços em branco não precisam ser colocados entre aspas.Por exemplo, dada esta tabela do ClickHouse:

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

IMPORT FOREIGN SCHEMA cria esta tabela externa:

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

Portanto, as consultas devem usar aspas de forma adequada, por exemplo,

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

Para criar objetos com nomes diferentes ou totalmente em minúsculas (e, portanto, sem distinção entre maiúsculas e minúsculas), use CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE

Use CREATE FOREIGN TABLE para criar uma tabela externa capaz de consultar dados de um banco de dados do 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)'
);

As opções de tabela compatíveis são:

database: O nome do banco de dados remoto. O padrão é o banco de dados definido para o servidor externo.
table_name: O nome da tabela remota. O padrão é o nome especificado para a foreign table.
engine: O [engine da tabela] usado pela tabela ClickHouse. Para CollapsingMergeTree() e AggregatingMergeTree(), o pg_clickhouse aplica automaticamente os parâmetros às expressões de função executadas na tabela.

Use o tipo de dado apropriado para o tipo de dado remoto do ClickHouse de cada coluna. As opções de coluna compatíveis são:

column_name: O nome da coluna no lado do ClickHouse, usado em vez do nome do atributo do PostgreSQL ao reconstruir consultas e inserções. Útil para mapear nomes de colunas do PostgreSQL em minúsculas e sem aspas para colunas do ClickHouse sensíveis a maiúsculas e minúsculas, por exemplo:
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: O nome da função de agregação aplicada a uma coluna do [tipo AggregateFunction]. Mapeie o tipo de dado para o tipo do ClickHouse passado à função e especifique o nome da função de agregação por meio da opção de coluna apropriada; o pg_clickhouse acrescentará automaticamente Merge à função de agregação usada para avaliar a coluna.
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: O nome da função de agregação aplicada a uma coluna do [tipo SimpleAggregateFunction]. Mapeie o tipo de dado para o tipo do ClickHouse passado à função e especifique o nome da função de agregação por meio da opção de coluna apropriada.

ALTER FOREIGN TABLE

Use ALTER FOREIGN TABLE para alterar a definição de uma tabela externa:

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

As opções de tabela e coluna suportadas são as mesmas do CREATE FOREIGN TABLE.

DROP FOREIGN TABLE

Use DROP FOREIGN TABLE para remover uma tabela externa:

DROP FOREIGN TABLE acts;

Este comando falha se houver objetos que dependam da tabela externa. Use a cláusula CASCADE para removê-los também:

DROP FOREIGN TABLE acts CASCADE;

Referência de SQL DML

As expressões DML em SQL abaixo podem usar pg_clickhouse. Os exemplos dependem destas tabelas do 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

O comando EXPLAIN funciona como esperado, mas a opção VERBOSE aciona a emissão da consulta “Remote SQL” do ClickHouse:

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)

Esta consulta é repassada ao ClickHouse por meio de um nó de plano “Foreign Scan”, o SQL remoto.

SELECT

Use a instrução SELECT para executar consultas em tabelas pg_clickhouse, assim como em qualquer outra tabela:

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 procura transferir a execução da consulta para o ClickHouse o máximo possível, incluindo funções de agregação. Use EXPLAIN para determinar o nível de pushdown. Para a consulta acima, por exemplo, toda a execução é transferida para o 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 também faz o pushdown de junções para tabelas do mesmo servidor remoto:

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)

Fazer join com uma tabela local gerará consultas menos eficientes sem um ajuste criterioso. Neste exemplo, fazemos uma cópia local da tabela nodes e fazemos join com ela em vez de usar a tabela remota:

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

Nesse caso, podemos delegar uma parte maior da agregação ao ClickHouse, agrupando por node_id em vez da coluna local e, depois, fazer join com a tabela de lookup:

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

O nó “Foreign Scan” agora faz o pushdown da agregação por node_id, reduzindo o número de linhas que precisam ser trazidas de volta para o Postgres de 1000 (todas elas) para apenas 8, uma para cada nó.

Tabelas particionadas

Uma [tabela particionada] do PostgreSQL pode combinar partições locais com partições externas armazenadas no ClickHouse. Uma configuração comum transfere dados mais antigos para o ClickHouse, enquanto os dados mais recentes permanecem no 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');

Para ver um exemplo de como mover dados de partições locais para partições externas, consulte offload-partition.sql. Agregações que abrangem partições locais e externas exigem a [agregação por partição], que o PostgreSQL desativa por padrão:

SET enable_partitionwise_aggregate = on;

Com enable_partitionwise_aggregate ativado, o PostgreSQL calcula um agregado parcial abaixo de Append, e um agregado de finalização acima combina esses parciais no resultado. O pg_clickhouse envia o parcial da partição estrangeira ao 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

Quando agregações parciais são enviadas ao destino

O PostgreSQL representa uma agregação parcial como um estado de transição que a etapa de finalização combina entre as partições. O pg_clickhouse pode enviar a agregação parcial de uma partição ao destino somente quando puder expressá-la como um valor do ClickHouse:

Agregações decomponíveis cujo estado de transição já é o valor final são enviadas diretamente ao destino: count, sum, min, max, bool_and/every, bool_or, bit_and, bit_or e bit_xor.
avg sobre inteiros envia seu estado {count, sum} como um array.
avg, var_pop, var_samp, stddev_pop e stddev_samp sobre ponto flutuante enviam seu estado {N, sum, sum of squared deviations} como um array.

FILTER (WHERE …) é enviado ao destino com essas funções de agregação.

Quando usam uma alternativa

Agregações cujo estado de transição é o tipo opaco internal do PostgreSQL não têm representação portátil; por isso, a partição estrangeira busca suas linhas e as agrega localmente. Isso abrange tudo o que usa numeric, além de avg(bigint) e avg(interval). Agregações DISTINCT, de conjunto ordenado e variádicas também usam uma alternativa.

PREPARE, EXECUTE, DEALLOCATE

A partir da v0.1.2, o pg_clickhouse oferece suporte a consultas parametrizadas, criadas principalmente com o comando 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

Use EXECUTE normalmente para executar uma instrução preparada:

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 linhas)

A execução parametrizada impede que o http driver converta corretamente os fusos horários de DateTime em versões do ClickHouse anteriores à 25.8, quando o [bug subjacente] foi [corrigido]. Observe que, às vezes, o PostgreSQL usa um plano de consulta parametrizado mesmo sem usar PREPARE. Para consultas que exijam conversão precisa de fuso horário, e quando a atualização para a versão 25.8 ou posterior não for uma opção, use o driver binário.

O pg_clickhouse faz o pushdown das agregações, como de costume, como pode ser visto na saída detalhada de 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)

Observe que ele enviou os valores de data completos, e não os placeholders de parâmetro. Isso se aplica às cinco primeiras requisições, como descrito nas notas do PostgreSQL [sobre PREPARE]. Na sexta execução, ele envia os [parâmetros de consulta] no estilo {param:type} do ClickHouse: 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)

Use DEALLOCATE para liberar uma instrução preparada:

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT

Use o comando INSERT para inserir valores em uma tabela remota do 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

Use o comando COPY para inserir um lote de linhas em uma tabela remota do 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

⚠️ Limitações da API de Batch pg_clickhouse ainda não implementou suporte à API de inserção em lote do FDW do PostgreSQL. Portanto, COPY atualmente usa instruções INSERT para inserir registros. Isso será aprimorado em uma versão futura.

LOAD

Use LOAD para carregar a biblioteca compartilhada pg_clickhouse:

try=# LOAD 'pg_clickhouse';
LOAD

Normalmente, n’ao é necessário usar LOAD, pois o Postgres carregará pg_clickhouse automaticamente na primeira vez em que qualquer um dos seus recursos (funções, foreign tables etc.) for usado. A única situação em que pode ser útil usar LOAD pg_clickhouse é para SET os parâmetros do pg_clickhouse antes de executar consultas que dependam deles.

SET

Use SET para definir os parâmetros personalizados de configuração do pg_clickhouse.

`pg_clickhouse.session_settings`

O parâmetro pg_clickhouse.session_settings configura as [configurações do ClickHouse] a serem aplicadas às consultas subsequentes. Exemplo:

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

O padrão é

join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0

Defina como uma string vazia para voltar às configurações do servidor ClickHouse — mas observe que a execução correta do pushdown depende de alguns desses valores padrão: join_use_nulls para junções externas e transform_null_in para a família IN (consulte IN e semântica de NULL).

SET pg_clickhouse.session_settings = '';

A sintaxe é uma lista de pares chave/valor delimitada por vírgulas, separados por um ou mais espaços. As chaves devem corresponder às [configurações do ClickHouse]. Escape os espaços, as vírgulas e as barras invertidas nos valores com uma barra invertida:

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

Ou use valores entre aspas simples para não precisar escapar espaços e vírgulas; considere usar dollar quoting para evitar a necessidade de usar aspas duplas:

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

Se a legibilidade for importante e você precisar definir muitas configurações, use várias linhas, por exemplo:

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

Algumas configurações serão ignoradas nos casos em que possam interferir na operação do próprio pg_clickhouse. Entre elas estão:

date_time_output_format: o driver HTTP exige que seja “iso”
format_tsv_null_representation: o driver HTTP exige o valor padrão
output_format_tsv_crlf_end_of_line o driver HTTP exige o valor padrão

Fora isso, o pg_clickhouse não valida as configurações, apenas as repassa ao ClickHouse em cada consulta. Assim, ele oferece suporte a todas as configurações de cada versão do ClickHouse. Observe que o pg_clickhouse deve ser carregado antes de definir pg_clickhouse.session_settings; use [pré-carregamento de biblioteca compartilhada] ou simplesmente use um dos objetos da extensão para garantir que ele seja carregado.

`pg_clickhouse.pushdown_regex`

O parâmetro pg_clickhouse.pushdown_regex controla se o pg_clickhouse faz pushdown de funções e operadores de expressão regular. Isso ocorre por padrão; defina esse parâmetro como false para impedir esse pushdown:

SET pg_clickhouse.pushdown_regex = 'false';

Consulte Expressões regulares para obter mais detalhes.

ALTER ROLE

Use o comando SET de ALTER ROLE’s para pré-carregar o pg_clickhouse e/ou SET seus parâmetros para roles específicos:

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

Use o comando RESET de ALTER ROLE para redefinir o pré-carregamento do pg_clickhouse e/ou os parâmetros:

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

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

Pré-carregamento

Se toda ou quase toda conexão com o Postgres precisar usar o pg_clickhouse, considere usar o [pré-carregamento de biblioteca compartilhada] para carregá-lo automaticamente:

`session_preload_libraries`

Carrega a biblioteca compartilhada a cada nova conexão com o PostgreSQL:

session_preload_libraries = pg_clickhouse

Útil para aproveitar as atualizações sem reiniciar o servidor: basta se reconectar. Também pode ser configurado para usuários ou roles específicos por meio de ALTER ROLE.

`shared_preload_libraries`

Carrega a biblioteca compartilhada no processo principal do PostgreSQL durante a inicialização:

shared_preload_libraries = pg_clickhouse

Útil para economizar memória e reduzir a sobrecarga em cada sessão, mas exige que o cluster seja reiniciado quando a biblioteca for atualizada.

Tipos de dados

O pg_clickhouse mapeia os seguintes tipos de dados do ClickHouse para tipos de dados do PostgreSQL. IMPORT FOREIGN SCHEMA usa o primeiro tipo do PostgreSQL para a coluna ao importar colunas; tipos adicionais podem ser usados em instruções CREATE FOREIGN TABLE:

ClickHouse	PostgreSQL	Observações
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	Erro para valores > máximo de BIGINT
UInt8	smallint
UUID	uuid

Qualquer coluna também pode ser lida como text, varchar ou outro tipo de string. O valor assume o tipo PostgreSQL acima e é então renderizado pela função de saída desse tipo. Valores UInt64 acima do máximo de bigint ainda geram erro; portanto, renderize-os com a função toString() do ClickHouse. Notas e detalhes adicionais vêm a seguir.

BYTEA

O ClickHouse não oferece um equivalente ao tipo BYTEA do PostgreSQL, mas permite que quaisquer bytes sejam armazenados no tipo String. Em geral, strings do ClickHouse devem ser mapeadas para o tipo TEXT do PostgreSQL, mas ao trabalhar com dados binários, mapeie-as para BYTEA. Exemplo:

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

Essa consulta SELECT final produzirá:

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

Observe que, se houver bytes nulos nas colunas do ClickHouse, uma tabela estrangeira que utilize colunas TEXT não exibirá os valores corretos:

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

Saída:

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

Observe que as linhas dois e três contêm valores truncados. Isso ocorre porque o PostgreSQL depende de strings terminadas em nul e não oferece suporte a nuls em suas strings. A tentativa de inserir valores binários em colunas TEXT será bem-sucedida e funcionará conforme esperado:

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

As colunas de texto estarão corretas:

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

Mas lê-los como BYTEA não funcionará:

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

Em geral, use colunas TEXT apenas para strings codificadas e colunas BYTEA apenas para dados binários, e nunca alterne entre elas.

Referência de funções e operadores

Funções

Essas funções fornecem a interface para executar consultas em um banco de dados ClickHouse.

`clickhouse_raw_query`

Obsoleta: clickhouse_raw_query() emite um aviso de descontinuação e será removida no próximo lançamento. Use clickhouse_query para ler linhas e clickhouse_perform para executar instruções que não retornam nenhum valor. Ambas reutilizam o driver, as credenciais, o banco de dados e o cache de conexão de um servidor configurado, em vez de uma string de conexão ad hoc.

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

Conecte-se a um serviço ClickHouse, execute uma única consulta e desconecte-se. O segundo argumento opcional especifica uma string de conexão cujo padrão é host=localhost port=8123. Os parâmetros de conexão compatíveis são:

driver: O driver de conexão a usar, “http” ou “binary”; o padrão é “http”
host: O host ao qual se conectar; obrigatório.
port: A porta à qual se conectar. O padrão é 8123 para o driver “http” ou 9000 para o driver “binary”, mudando para 8443 ou 9440, respectivamente, quando host é um host do ClickHouse Cloud
dbname: O nome do banco de dados ao qual se conectar.
username: O nome de usuário com o qual se conectar; o padrão é default
password: A senha usada para autenticação; o padrão é não usar senha

Ambos os drivers retornam linhas separadas por tabulação (nulos como \N), mas suas representações de valores diferem: o driver “http” retorna literalmente a formatação TSV do próprio ClickHouse, enquanto o driver “binary” passa cada valor pela função de saída do PostgreSQL. Por padrão, nenhuma role tem acesso EXECUTE a esta função; considere GRANT conceder acesso somente a roles que realmente precisem executar consultas ad hoc no ClickHouse, por exemplo, uma role administrativa dedicada do ClickHouse: Útil para consultas que não retornam registros, mas as consultas que retornam valores serão retornadas como um único valor de texto:

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

Informe a versão do servidor ClickHouse, no formato major.minor.patch, para o servidor estrangeiro especificado, conectando-se, se necessário, usando as opções do servidor e o user mapping do usuário atual:

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

Lê a versão do handshake da conexão do protocolo nativo ou, via HTTP, por meio de uma única consulta SELECT version(), e a armazena em cache durante toda a vida da conexão.

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

Execute uma consulta em um servidor estrangeiro já configurado e retorne suas linhas como uma relação, mapeando cada coluna de resultado do ClickHouse para o tipo PostgreSQL especificado na lista de definição de colunas. A função reutiliza o driver do servidor, as credenciais, o banco de dados e o cache de conexão. O primeiro argumento é o nome de um servidor criado com CREATE SERVER. Uma lista de definição de colunas (AS name(col type, ...)) é obrigatória: o PostgreSQL precisa conhecer a estrutura do resultado antes de buscar as linhas, e ela deve corresponder às colunas retornadas pela consulta. Os valores são convertidos do ClickHouse para os tipos declarados da mesma forma que seriam em uma coluna de tabela estrangeira. Instruções que não retornam resultados, como DDL, não têm nada a declarar; execute-as com clickhouse_perform. Nenhuma role tem acesso EXECUTE por padrão; conceda GRANT a uma role para permitir o uso da função.

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

Execute uma instrução em um servidor estrangeiro já configurado e descarte qualquer resultado. Use-a para instruções que não retornam linhas, como DDL, para as quais clickhouse_query não tem formato de resultado a declarar. Ela resolve o servidor da mesma forma que clickhouse_query, reutilizando o driver, as credenciais, o banco de dados e o cache de conexão. Por ser um procedimento, deve ser invocada com CALL, e não com SELECT, e não retorna linhas. Nenhuma role tem acesso a EXECUTE por padrão; conceda GRANT a uma role para permitir o uso do procedimento.

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

Funções com pushdown

pg_clickhouse faz pushdown de um subconjunto das funções nativas do PostgreSQL usadas em condicionais (cláusulas HAVING e WHERE). Esse subconjunto corresponde aos equivalentes no ClickHouse, da seguinte forma:

abs: abs
factorial: factorial
mod (int2/int4/int8/numeric): módulo
pow & power (float8/numeric): pow
round: round
sin, cos, tan, atan, atan2, sinh, cosh, tanh, asinh, degrees, radians, pi: funções matemáticas do ClickHouse com o mesmo nome. asin, acos, atanh, acosh não são delegadas: o PG gera erro quando a entrada está fora do intervalo, enquanto o CH retorna 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): os mesmos mapeamentos de date_part
date(timestamp) & date(timestamptz): toDate (reapresentado como alias do CH date)
array_position: indexOf com nullIf para converter 0 em NULL e arraySlice quando houver um terceiro argumento para o índice inicial da busca; observe que nan atualmente não encontra correspondência
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: comprimento
array_to_string: arrayStringConcat
string_to_array: splitByString
split_part: splitByString + acesso por índice em array
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 se a expressão regular contiver subexpressões entre parênteses; caso contrário, extractAll fatiado com arraySlice.
regexp_replace: replaceRegexpOne ou replaceRegexpOne quando a flag g estiver presente
regexp_split_to_array: splitByRegexp
md5: MD5
encode(bytea, fmt) quando fmt é uma string constant (sem distinção entre maiúsculas e minúsculas):
- encode(bytea, 'hex'): hex envolvido por lower, pois o PostgreSQL gera valores hexadecimais em minúsculas.
- encode(bytea, 'base64'): base64Encode envolvido por replaceRegexpAll para reproduzir a quebra de linha MIME (RFC 2045) do PostgreSQL a cada 76 caracteres.
- encode(bytea, 'base64url') (PostgreSQL 19+): base64URLEncode, que corresponde ao alfabeto de URL da RFC 4648 do PostgreSQL, sem padding.
json_extract_path_text: sintaxe de subcoluna
json_extract_path: toJSONString + sintaxe de subcolunas
jsonb_extract_path_text: sintaxe de subcoluna
jsonb_extract_path: toJSONString + sintaxe de subcolunas
bit_count(bytea): bitCount
to_timestamp(float8): toDateTime64
to_char(timestamp[tz], fmt): formatDateTime quando fmt é uma string constant cujas palavras-chave têm todas um equivalente fiel no ClickHouse. Consulte to_char(), em Notas de compatibilidade, para ver as palavras-chave compatíveis. Caso contrário, a função é executada localmente no PostgreSQL.
statement_timestamp, transaction_timestamp, & clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))
CURRENT_DATE: now e 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: Passado como valor de uma função do PostgreSQL.
CURRENT_SCHEMA: Passado como valor de uma função do PostgreSQL.
CURRENT_CATALOG: Passado como valor retornado pela função do PostgreSQL.
CURRENT_USER: Passado como valor da função do PostgreSQL.
USER: Passado como valor por uma função do PostgreSQL.
CURRENT_ROLE: Passado como valor de uma função do PostgreSQL.
SESSION_USER: Passado como valor de uma função do PostgreSQL.

Operadores de pushdown

Fatiamento de Array (arr[L:U]): arraySlice
@> (array contém): hasAll
<@ (array contido em): hasAll
&& (sobreposição de arrays): hasAny
~ (correspondência com regexp): match
!~ (sem correspondência com regexp): match
~* (regexp sem distinção entre maiúsculas e minúsculas, sem correspondência): match
!~* (regexp sem distinção entre maiúsculas e minúsculas, sem correspondência): match
->> (extrai elemento de JSON/JSONB como texto): sintaxe de subcoluna
-> (extrai JSON/JSONB): toJSONString + sintaxe de subcoluna

Semântica de IN e NULL

O ClickHouse avalia IN com lógica de dois valores: quando a busca não encontra correspondência, retorna 0, mesmo que haja um NULL envolvido, enquanto o PostgreSQL retorna NULL. Para preservar a semântica do PostgreSQL, o pg_clickhouse faz pushdown incondicionalmente da família IN sobre uma lista ou array constante (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL): usa a forma nativa ou mais econômica quando consegue provar que nem o valor pesquisado nem um elemento do array podem ser NULL, ou, caso contrário, uma forma CASE com proteções, que verifica valores NULL em tempo de execução e calcula a resposta exata de três valores do PostgreSQL (TRUE, FALSE, NULL) em todos os contextos, inclusive em posições de valor, como em uma lista SELECT ou em GROUP BY. Um filtro NOT IN (SELECT ...) sobre colunas Nullable também recebe pushdown, sendo reconvertido com proteções compensatórias que preservam o comportamento do PostgreSQL: um conjunto que contém um NULL desqualifica todas as linhas, e um valor pesquisado NULL passa apenas contra um conjunto vazio. Cada proteção é omitida quando uma declaração NOT NULL prova que ela é desnecessária. Ao contrário das formas de array acima, essa proteção se aplica somente em uma condição de filtro simples (ou sob NOT); ainda não fazemos pushdown de IN (SELECT ...) (em uma posição de valor), nem de corpos de subconsulta agrupados ou agregados. Declarar colunas como NOT NULL maximiza o pushdown, pois permite enviar a forma mais econômica sem proteções; IMPORT FOREIGN SCHEMA faz isso automaticamente para colunas do ClickHouse que não são Nullable. A prova considera constantes não NULL, colunas NOT NULL e aritmética básica (+, -, *, - unário) sobre elas. Essas regras pressupõem o valor padrão do ClickHouse transform_null_in = 0, que o pg_clickhouse define em cada consulta por meio do valor padrão do parâmetro pg_clickhouse.session_settings, impedindo que um perfil de servidor do ClickHouse o altere silenciosamente. Definir transform_null_in = 1 quebra a semântica de todos os IN com pushdown.

Funções personalizadas

Estas funções personalizadas criadas por pg_clickhouse permitem o pushdown de consultas externas para determinadas funções do ClickHouse que não têm equivalentes no PostgreSQL. Se alguma dessas funções não puder ser processada via pushdown, será gerada uma exceção.

dictGet

Pushdown de extensões

O pg_clickhouse reconhece funções de algumas extensões principais e de terceiros, fazendo o pushdown delas para seus equivalentes no ClickHouse.

re2

Todos os operadores e funções da [extensão re2] são convertidos 1:1 para o 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

Uma função intarray pode fazer pushdown para o ClickHouse:

idx → indexOf

fuzzystrmatch

Duas funções fuzzystrmatch podem ser executadas no ClickHouse:

soundex: soundex
levenshtein (2 argumentos): editDistanceUTF8

Casts com pushdown

O pg_clickhouse faz pushdown de casts como CAST(x AS bigint) para tipos de dados compatíveis. Para tipos incompatíveis, o pushdown falha; se x, neste exemplo, for um UInt64 do ClickHouse, o ClickHouse se recusará a converter o valor. Para fazer pushdown de casts para tipos de dados incompatíveis, o pg_clickhouse fornece as funções a seguir. Elas geram uma exceção no PostgreSQL se não forem executadas com pushdown.

Funções de agregação com pushdown

Estas funções de agregação do PostgreSQL oferecem suporte a pushdown para o ClickHouse.

Agregações personalizadas

Estas funções de agregação personalizadas criadas por pg_clickhouse fornecem pushdown de consultas externas para algumas funções de agregação do ClickHouse sem equivalentes no PostgreSQL. Se alguma dessas funções não puder ter pushdown, será gerada uma exceção.

Agregações ordered-set com pushdown

Estas [funções de agregação de conjunto ordenado] são mapeadas para funções de agregação paramétricas do ClickHouse, passando o argumento direto como parâmetro e as expressões de ORDER BY como argumentos. Por exemplo, esta consulta PostgreSQL:

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

Corresponde à seguinte consulta do ClickHouse:

SELECT quantile(0.25)(a) FROM t1;

Observe que os sufixos não padrão DESC e NULLS FIRST de ORDER BY não têm suporte e gerarão um erro.

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

Agregações ordered-set personalizadas

Estas [funções de agregação de conjunto ordenado] personalizadas, criadas pelo pg_clickhouse, oferecem pushdown de consultas externas para determinadas funções de agregação paramétricas do ClickHouse. Se não for possível aplicar pushdown a alguma dessas funções, será gerada uma exceção.

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

Agregados personalizados de conjunto ordenado

Estas [funções de agregação de conjunto ordenado] personalizadas, criadas pelo pg_clickhouse, permitem o pushdown de consultas externas para funções de [agregação paramétricas] selecionadas do ClickHouse. Se não for possível aplicar pushdown a alguma dessas funções, será gerada uma exceção.

Funções de janela com pushdown

Estas [funções de janela] do PostgreSQL são executadas no ClickHouse com cláusulas OVER (PARTITION BY ... ORDER BY ...), incluindo especificações de frame quando aplicável.

row_number
rank
dense_rank
ntile
cume_dist
percent_rank
lead
lag
first_value
last_value
nth_value
min / max (com a cláusula OVER)

As funções de ranking (row_number, rank, dense_rank, ntile, cume_dist, percent_rank) omitem a cláusula de frame durante o pushdown porque o ClickHouse rejeita especificações de frame nessas funções.

Notas de compatibilidade

Expressões regulares

Embora o pg_clickhouse faça pushdown de expressões regulares para equivalentes no ClickHouse quando pg_clickhouse.pushdown_regex é true (o padrão), e se esforce para garantir um nível básico de compatibilidade, esteja ciente das diferenças entre os dois e de como o pg_clickhouse as trata.

O PostgreSQL oferece suporte a [Expressões Regulares POSIX], enquanto o ClickHouse oferece suporte a Expressões Regulares RE2. Fique atento às diferenças de comportamento: use RE2 quando a expressão regular for avaliada pelo ClickHouse (por exemplo, em uma cláusula WHERE) e POSIX quando ela for avaliada pelo Postgres (por exemplo, em uma cláusula SELECT).
pg_clickhouse aplica as [flags do Postgres] prefixando-as à expressão regular do ClickHouse dentro de (?). Por exemplo:
regexp_like(val, '^VAL\d', 'i')
Passa a ser
match(val, concat('(?i)', '^VAL\\d'))

As únicas flags compatíveis com ambos e, portanto, que podem ser usadas quando interpretadas pelo ClickHouse são:

Flag	Como	Observações
`i`	`i`	correspondência sem diferenciar maiúsculas de minúsculas
`m`	`m-s`	`^` e `$` correspondem ao início/fim da linha, além do início/fim do texto
`n`	`m-s`	alias do Postgres para `m`
`p`	`-s`	não permitir que `.` e `[^x]` correspondam a `\n`
`s`	`s`	permitir que `.` e `[^x]` correspondam a `\n`
`t`		sintaxe estrita, ignorado
`w`	`m`	correspondência parcial inversa sensível a quebras de linha

O RE2 oferece suporte apenas a estas flags; não use nenhuma outra [flags do Postgres].

Esta tabela resume os efeitos das várias flags (e da ausência de flag, que é o mesmo que s) na correspondência de quebras de linha e terminações de linha. Observe que, no Postgres, m e p impedem que classes de caracteres negadas ([^xyz]) correspondam a uma quebra de linha, enquanto os equivalentes no ClickHouse não. Fora isso, os comportamentos são os mesmos no ClickHouse e no Postgres:

Padrão aplicado a `a\nb`	Postgres	ClickHouse	Igual?
`a.b`	true	true	✔︎
`a[^x]b`	true	true	✔︎
`a$`	false	false	✔︎
`s` Flag
`(?s)a.b`	true	true	✔︎
`(?s)a[^x]b`	true	true	✔︎
`(?s)a$`	false	false	✔︎
`m` Flag
`(?m)a.b`	false	false	✔︎
`(?m)a[^x]b`	true	false	✘
`(?m)a$`	true	true	✔︎
`p` Flag
`(?p)a.b`	false	false	✔︎
`(?p)a[^x]b`	true	false	✘
`(?p)a$`	false	false	✔︎
`w` Flag
`(?w)a.b`	true	true	✔
`(?w)a[^x]b`	true	true	✔
`(?w)a$`	true	true	✔

Quaisquer outras flags passadas para funções de expressão regular impedem o pushdown da função.
A exceção é regexp_replace(), que também aceita a flag g. Quando g está definida, pg_clickhouse usa replaceRegexpAll() em vez de replaceRegexpOne() e remove a flag antes de adicionar outras flags no início.
O argumento de substituição de regexp_replace() no Postgres aceita \& para se referir à correspondência inteira, enquanto no ClickHouse \0 representa a correspondência inteira. Certifique-se de usar \0 quando a função fizer pushdown para o ClickHouse.
Postgres regexp_match retorna NULL quando não há correspondências, enquanto as expressões delegadas retornam um array vazio. Use COALESCE() para retornar um array vazio em vez de NULL e comparar os valores de retorno de forma compatível. Por exemplo:
SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');

Para evitar qualquer ambiguidade, considere definir pg_clickhouse.pushdown_regex para impedir que expressões regulares do Postgres façam pushdown para o ClickHouse e usar a [extensão re2], para a qual o pg_clickhouse oferece suporte a pushdown direto de expressões regulares RE2 compatíveis com o ClickHouse.

`to_char()`

O to_char() do PostgreSQL para timestamp e timestamp with time zone só é enviado ao ClickHouse formatDateTime quando o argumento de formato é uma constante de string não NULL em que cada palavra-chave do PostgreSQL tem um equivalente idêntico byte a byte no ClickHouse. Se o formato for dinâmico (não for uma Const) ou contiver qualquer palavra-chave ou modificador sem suporte, a chamada recorre à avaliação local no PostgreSQL — o pushdown nunca é tentado com uma tradução parcial, para que a saída permaneça compatível com o PostgreSQL. As variantes de to_char() com dois argumentos aplicadas a numeric, interval e outros tipos que não sejam timestamp nunca usam pushdown; o formatDateTime do ClickHouse apenas formata valores de data e hora.

Palavras-chave traduzidas

PostgreSQL	ClickHouse	Significado
`YYYY`, `yyyy`	`%Y`	ano com 4 dígitos
`YY`, `yy`	`%y`	ano com 2 dígitos
`MM`, `mm`	`%m`	mês com zero à esquerda (01–12)
`DD`, `dd`	`%d`	dia do mês com zero à esquerda (01–31)
`DDD`, `ddd`	`%j`	dia do ano com zero à esquerda (001–366)
`HH24`, `hh24`	`%H`	hora no formato de 24 horas com zero à esquerda (00–23)
`HH`, `hh`, `HH12`, `hh12`	`%I`	hora no formato de 12 horas com zero à esquerda (01–12)
`MI`, `mi`	`%i`	minuto com zero à esquerda (00–59)
`SS`, `ss`	`%S`	segundo com zero à esquerda (00–59)
`Q`, `q`	`%Q`	trimestre (1–4)
`Mon`	`%b`	nome abreviado do mês, por exemplo, `Oct`
`Dy`	`%a`	nome abreviado do dia da semana, por exemplo, `Mon`
`AM`, `PM`	`%p`	indicador de meridiano, sempre em maiúsculas

Texto entre aspas e literais

O texto entre "..." é passado literalmente, com qualquer % literal duplicado como %% para escapar o prefixo de especificador do ClickHouse. Um \" fora das aspas também é passado como um literal ". Dentro de "...", a barra invertida escapa apenas "; outras sequências com barra invertida são tratadas como texto literal.

Autores

David E. Wheeler

​Descrição

​Primeiros passos

​Uso

​Política de versionamento

​Referência de 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

​Referência de SQL DML

​EXPLAIN

​SELECT

​Tabelas particionadas

​Quando agregações parciais são enviadas ao destino

​Quando usam uma alternativa

​PREPARE, EXECUTE, DEALLOCATE

​INSERT

​COPY

​LOAD

​SET

​pg_clickhouse.session_settings

​pg_clickhouse.pushdown_regex

​ALTER ROLE

​Pré-carregamento

​session_preload_libraries

​shared_preload_libraries

​Tipos de dados

​BYTEA

​Referência de funções e operadores

​Funções

​clickhouse_raw_query

​clickhouse_server_version

​clickhouse_query

​clickhouse_perform

​Funções com pushdown

​Operadores de pushdown

​Semântica de IN e NULL

​Funções personalizadas

​Pushdown de extensões

​re2

​intarray

​fuzzystrmatch

​Casts com pushdown

​Funções de agregação com pushdown

​Agregações personalizadas

​Agregações ordered-set com pushdown

​Agregações ordered-set personalizadas

​Agregados personalizados de conjunto ordenado

​Funções de janela com pushdown

​Notas de compatibilidade

​Expressões regulares

​to_char()

​Palavras-chave traduzidas

​Texto entre aspas e literais

​Autores

​Direitos autorais

Descrição

Primeiros passos

Uso

Política de versionamento

Referência de 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

Referência de SQL DML

EXPLAIN

SELECT

Tabelas particionadas

Quando agregações parciais são enviadas ao destino

Quando usam uma alternativa

PREPARE, EXECUTE, DEALLOCATE

INSERT

COPY

LOAD

SET

`pg_clickhouse.session_settings`

`pg_clickhouse.pushdown_regex`

ALTER ROLE

Pré-carregamento

`session_preload_libraries`

`shared_preload_libraries`

Tipos de dados

BYTEA

Referência de funções e operadores

Funções

`clickhouse_raw_query`

`clickhouse_server_version`

`clickhouse_query`

`clickhouse_perform`

Funções com pushdown

Operadores de pushdown

Semântica de IN e NULL

Funções personalizadas

Pushdown de extensões

re2

intarray

fuzzystrmatch

Casts com pushdown

Funções de agregação com pushdown

Agregações personalizadas

Agregações ordered-set com pushdown

Agregações ordered-set personalizadas

Agregados personalizados de conjunto ordenado

Funções de janela com pushdown

Notas de compatibilidade

Expressões regulares

`to_char()`

Palavras-chave traduzidas

Texto entre aspas e literais

Autores

Direitos autorais