Documentación de referencia de pg_clickhouse

Descripción

pg_clickhouse es una extensión de PostgreSQL que permite ejecutar consultas de forma remota en bases de datos de ClickHouse, e incluye un foreign data wrapper. Es compatible con PostgreSQL 13 y versiones posteriores, y con ClickHouse 23.3 y versiones posteriores.

Primeros pasos

La forma más sencilla de probar pg_clickhouse es la [imagen de Docker], que contiene la imagen estándar de PostgreSQL para Docker con las extensiones pg_clickhouse y [re2][re2 extension]:

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

Consulta el tutorial para empezar a importar tablas de ClickHouse y delegar la ejecución de consultas.

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 versionado

pg_clickhouse sigue Semantic Versioning para sus versiones públicas.

La versión principal se incrementa con cambios en la API
La versión secundaria se incrementa con cambios de SQL compatibles con versiones anteriores
La versión de parche se incrementa con cambios solo en el binario

Una vez instalado, PostgreSQL lleva un seguimiento de dos variantes de la versión:

La versión de la biblioteca (definida por PG_MODULE_MAGIC en PostgreSQL 18 y posteriores) incluye la versión semántica completa, visible en la salida de la función pgch_version() o de la función de Postgres pg_get_loaded_modules().
La versión de la extensión (definida en el archivo de control) incluye solo las versiones principal y secundaria, visibles en la tabla pg_catalog.pg_extension, la salida de la función pg_available_extension_versions() y \dx pg_clickhouse.

En la práctica, esto significa que una versión que incrementa la versión de parche, por ejemplo, de v0.1.0 a v0.1.1, beneficia a todas las bases de datos que han cargado v0.1 y no necesitan ejecutar ALTER EXTENSION para aprovechar la actualización. Por otro lado, una versión que incrementa las versiones secundaria o principal irá acompañada de scripts de actualización de SQL, y todas las bases de datos existentes que contengan la extensión deben ejecutar ALTER EXTENSION pg_clickhouse UPDATE para aprovechar la actualización.

Referencia de SQL DDL

Las siguientes expresiones DDL de SQL usan pg_clickhouse.

CREATE EXTENSION

Usa CREATE EXTENSION para añadir pg_clickhouse a una base de datos:

CREATE EXTENSION pg_clickhouse;

Usa WITH SCHEMA para instalarlo en un esquema concreto (recomendado):

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

Use ALTER EXTENSION para modificar pg_clickhouse. Ejemplos:

Después de instalar una nueva versión de pg_clickhouse, use la cláusula UPDATE:
ALTER EXTENSION pg_clickhouse UPDATE;

Use SET SCHEMA para mover la extensión a un nuevo esquema:

CREATE SCHEMA ch;
ALTER EXTENSION pg_clickhouse SET SCHEMA ch;

DROP EXTENSION

Utiliza DROP EXTENSION para eliminar pg_clickhouse de una base de datos:

DROP EXTENSION pg_clickhouse;

Este comando falla si hay algún objeto que dependa de pg_clickhouse. Use la cláusula CASCADE para eliminarlos también:

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

Utilice CREATE SERVER para crear un servidor foráneo que se conecta a un servidor de ClickHouse. Ejemplo:

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

Las opciones admitidas son:

driver: El driver de conexión de ClickHouse que se va a usar, ya sea “binary” o “http”. Obligatorio.
compression: Compresión del protocolo nativo para el driver “binary”, una de “none”, “lz4” o “zstd”. El valor predeterminado es “lz4”. El driver “http” la ignora.
dbname: La base de datos de ClickHouse que se usará al conectarse. El valor predeterminado es “default”.
host: El nombre de host del servidor de ClickHouse. El valor predeterminado es “localhost”;
port: El puerto del servidor de ClickHouse al que conectarse. Los valores predeterminados son los siguientes:
- 9440 si driver es “binary” y host es un host de ClickHouse Cloud
- 9004 si driver es “binary” y host no es un host de ClickHouse Cloud
- 8443 si driver es “http” y host es un host de ClickHouse Cloud
- 8123 si driver es “http” y host no es un host de ClickHouse Cloud
min_tls_version: Versión mínima del protocolo TLS que se negociará en las conexiones que usan TLS. Una de TLSv1, TLSv1.1, TLSv1.2 o TLSv1.3. El valor predeterminado es el mínimo propio de la biblioteca TLS. Se aplica a ambos drivers.
secure: Controla TLS para la conexión. Uno de:
- auto (predeterminado): usa TLS cuando host es un host de ClickHouse Cloud o port es un puerto seguro; texto no cifrado en caso contrario.
- on (o true/yes/1): usa siempre TLS. port toma por defecto el valor 8443 (“http”) o 9440 (“binary”).
- off (o false/no/0): no usa nunca TLS. port toma por defecto el valor 8123 (“http”) o 9000 (“binary”).

ALTER SERVER

Use ALTER SERVER para modificar un servidor foráneo. Ejemplo:

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

Las opciones son las mismas que en CREATE SERVER.

DROP SERVER

Use DROP SERVER para eliminar un servidor foráneo:

DROP SERVER taxi_srv;

Este comando falla si algún otro objeto depende del servidor. Use CASCADE para eliminar también esas dependencias:

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

Utilice CREATE USER MAPPING para asignar un usuario de PostgreSQL a un usuario de ClickHouse. Por ejemplo, para asignar el usuario actual de PostgreSQL al usuario remoto de ClickHouse al conectarse al servidor foráneo taxi_srv:

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

Las opciones admitidas son:

user: El nombre del usuario de ClickHouse. El valor predeterminado es “default”.
password: La contraseña del usuario de ClickHouse.

ALTER USER MAPPING

Use ALTER USER MAPPING para cambiar la definición de una asignación de usuario:

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

Las opciones son las mismas que en CREATE USER MAPPING.

DROP USER MAPPING

Utilice DROP USER MAPPING para eliminar una asignación de usuario:

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

Utilice IMPORT FOREIGN SCHEMA para importar todas las tablas definidas en una base de datos de ClickHouse como tablas foráneas en un esquema de PostgreSQL:

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

Use LIMIT TO para limitar la importación a tablas específicas:

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

Usa EXCEPT para excluir tablas:

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

pg_clickhouse obtendrá una lista de todas las tablas de la database de ClickHouse especificada (“demo” en los ejemplos anteriores), recuperará las definiciones de las columnas de cada una y ejecutará comandos CREATE FOREIGN TABLE para crear las tablas foráneas. Las columnas se definirán usando los tipos de datos compatibles y, cuando sea posible detectarlas, las opciones compatibles con CREATE FOREIGN TABLE.

Conservación de mayúsculas y minúsculas en los identificadores importadosIMPORT FOREIGN SCHEMA ejecuta quote_identifier() sobre los nombres de tablas y columnas que importa, lo que pone entre comillas dobles los identificadores con caracteres en mayúsculas o espacios en blanco. Por lo tanto, esos nombres de tablas y columnas deben ir entre comillas dobles en las consultas de PostgreSQL. Los nombres formados solo por minúsculas y sin espacios en blanco no necesitan comillas.Por ejemplo, dada esta tabla de ClickHouse:

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

IMPORT FOREIGN SCHEMA crea esta tabla foránea:

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

Por lo tanto, las consultas deben entrecomillar los identificadores según corresponda; por ejemplo:

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

Para crear objetos con nombres diferentes o completamente en minúsculas (y, por tanto, sin distinguir entre mayúsculas y minúsculas), use CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE

Utilice CREATE FOREIGN TABLE para crear una tabla foránea que permita consultar datos de una base de datos de 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)'
);

Las opciones de tabla admitidas son:

database: El nombre de la base de datos remota. De forma predeterminada, usa la base de datos definida para el servidor foráneo.
table_name: El nombre de la tabla remota. De forma predeterminada, usa el nombre especificado para la tabla foránea.
engine: El [motor de tabla] usado por la tabla de ClickHouse. Para CollapsingMergeTree() y AggregatingMergeTree(), pg_clickhouse aplica automáticamente los parámetros a las expresiones de función ejecutadas en la tabla.

Use el tipo de dato apropiado para el tipo de dato remoto de ClickHouse de cada columna. Las opciones de columna admitidas son:

column_name: El nombre de la columna en el lado de ClickHouse, usado con preferencia al nombre del atributo de PostgreSQL al reconstruir consultas e inserciones. Es útil para mapear nombres de columna de PostgreSQL en minúsculas y sin comillas a columnas de ClickHouse sensibles a mayúsculas y minúsculas; por ejemplo,
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: El nombre de la función de agregación aplicada a una columna de [tipo AggregateFunction]. Mapee el tipo de dato al tipo de ClickHouse pasado a la función y especifique el nombre de la función de agregación mediante la opción de columna adecuada; pg_clickhouse añadirá automáticamente Merge a la función de agregación que evalúe la columna.
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: El nombre de la función de agregación aplicada a una columna de [tipo SimpleAggregateFunction]. Mapee el tipo de dato al tipo de ClickHouse pasado a la función y especifique el nombre de la función de agregación mediante la opción de columna adecuada.

ALTER FOREIGN TABLE

Use ALTER FOREIGN TABLE para cambiar la definición de una tabla foránea:

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

Las opciones de tabla y columna admitidas son las mismas que para CREATE FOREIGN TABLE.

DROP FOREIGN TABLE

Utilice DROP FOREIGN TABLE para eliminar una tabla foránea:

DROP FOREIGN TABLE acts;

Este comando falla si hay algún objeto que depende de la tabla foránea. Use la cláusula CASCADE para eliminarlos también:

DROP FOREIGN TABLE acts CASCADE;

Referencia de SQL DML

Las expresiones SQL DML que aparecen a continuación pueden usar pg_clickhouse. Los ejemplos dependen de estas tablas de 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

El comando EXPLAIN funciona como es de esperar, pero la opción VERBOSE provoca que se emita la consulta de 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)

Esta consulta se envía a ClickHouse a través de un nodo del plan “Foreign Scan”, el SQL remoto.

SELECT

Use la instrucción SELECT para ejecutar consultas en las tablas pg_clickhouse igual que en cualquier otra tabla:

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 busca trasladar la ejecución de la consulta a ClickHouse tanto como sea posible, incluidas las funciones de agregación. Use EXPLAIN para determinar hasta qué punto se aplica el pushdown. Para la consulta anterior, por ejemplo, toda la ejecución se traslada a 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 también delega los JOINs a tablas que provienen del mismo 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)

Hacer un JOIN con una tabla local generará consultas menos eficientes si no se ajusta cuidadosamente. En este ejemplo, hacemos una copia local de la tabla nodes y hacemos un JOIN con ella en lugar de con la tabla 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

En este caso, podemos delegar una mayor parte de la agregación a ClickHouse agrupando por node_id en lugar de por la columna local, y luego hacer el join con la tabla de búsqueda más adelante:

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

El nodo “Foreign Scan” ahora traslada la agregación por node_id, lo que reduce el número de filas que deben volver a Postgres de 1000 (todas ellas) a solo 8, una por cada nodo.

Tablas particionadas

Una [tabla particionada] de PostgreSQL puede combinar particiones locales con particiones externas respaldadas por ClickHouse. Una configuración habitual traslada los datos más antiguos a ClickHouse, mientras que los datos recientes permanecen en 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 un ejemplo de cómo mover datos de particiones locales a externas, consulte offload-partition.sql. Las agregaciones que abarcan particiones locales y externas requieren la [agregación por particiones], que PostgreSQL desactiva de forma predeterminada:

SET enable_partitionwise_aggregate = on;

Con enable_partitionwise_aggregate habilitado, PostgreSQL calcula una agregación parcial debajo de Append y, a continuación, una agregación de finalización encima combina esos resultados parciales en el resultado final. pg_clickhouse envía la agregación parcial de la partición externa a 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

Cuándo se hace pushdown de agregaciones parciales

PostgreSQL representa una agregación parcial como un estado de transición que el paso de finalización combina entre particiones. pg_clickhouse puede hacer pushdown de la agregación parcial de una partición solo cuando puede expresarla como un valor de ClickHouse:

Agregaciones descomponibles cuyo estado de transición ya es el valor final, se envían directamente mediante pushdown: count, sum, min, max, bool_and/every, bool_or, bit_and, bit_or y bit_xor.
avg sobre enteros envía mediante pushdown su estado {count, sum} como un array.
avg, var_pop, var_samp, stddev_pop y stddev_samp sobre números de punto flotante envían mediante pushdown su estado {N, sum, suma de desviaciones al cuadrado} como un array.

FILTER (WHERE …) se envía mediante pushdown con estas funciones de agregación.

Cuándo recurren a una alternativa

Las agregaciones cuyo estado de transición es el tipo opaco internal de PostgreSQL no tienen una representación portable, por lo que la partición externa obtiene sus filas y las agrega localmente. Esto incluye cualquier agregación sobre numeric, además de avg(bigint) y avg(interval). Las agregaciones DISTINCT, de conjuntos ordenados y variádicas también recurren a una alternativa.

PREPARE, EXECUTE, DEALLOCATE

A partir de la versión v0.1.2, pg_clickhouse admite consultas parametrizadas, que se crean principalmente con el 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

Utilice EXECUTE como siempre para ejecutar una sentencia 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 rows)

La ejecución parametrizada impide que el http driver convierta correctamente las zonas horarias de DateTime en versiones de ClickHouse anteriores a la 25.8, antes de que se [corrigiera] el [error subyacente]. Ten en cuenta que, en ocasiones, PostgreSQL usará un plan de consulta parametrizado incluso sin usar PREPARE. Para cualquier consulta que requiera una conversión precisa de la zona horaria, y si actualizar a la 25.8 o a una versión posterior no es una opción, usa en su lugar el binary driver.

pg_clickhouse aplica el pushdown de las agregaciones, como es habitual, tal como se ve en la salida detallada 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)

Ten en cuenta que ha enviado los valores de fecha completos, no los marcadores de posición de los parámetros. Esto aplica a las primeras cinco solicitudes, como se describe en las [notas sobre PREPARE de PostgreSQL]. En la sexta ejecución, envía ClickHouse [parámetros de consulta] con el estilo {param:type}: parámetros:

                                                                                                         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 una sentencia preparada:

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT

Utilice el comando INSERT para insertar valores en una tabla remota de 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

Utiliza el comando COPY para insertar un lote de filas en una tabla remota de 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

⚠️ Limitaciones de la API de inserción por lotes pg_clickhouse aún no admite la API de inserción por lotes del FDW de PostgreSQL. Por lo tanto, COPY utiliza actualmente sentencias INSERT para insertar registros. Esto se mejorará en una futura versión.

LOAD

Utilice LOAD para cargar la biblioteca compartida pg_clickhouse:

try=# LOAD 'pg_clickhouse';
LOAD

No suele ser necesario usar LOAD, ya que Postgres cargará automáticamente pg_clickhouse la primera vez que se utilice cualquiera de sus funciones (funciones, tablas foráneas, etc.). La única vez que puede resultar útil LOAD pg_clickhouse es para SET los parámetros de pg_clickhouse antes de ejecutar consultas que dependan de ellos.

SET

Use SET para establecer los parámetros de configuración personalizados de pg_clickhouse.

`pg_clickhouse.session_settings`

El parámetro pg_clickhouse.session_settings configura la [configuración de ClickHouse] que se establecerá en las consultas posteriores. Ejemplo:

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

El valor predeterminado es

join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0

Establézcalo en una cadena vacía para usar la configuración del servidor de ClickHouse, pero tenga en cuenta que la corrección del pushdown depende de algunos de estos valores predeterminados: join_use_nulls para las combinaciones externas y transform_null_in para la familia IN (consulte IN y semántica de NULL).

SET pg_clickhouse.session_settings = '';

La sintaxis es una lista de pares clave/valor delimitados por comas y separados por uno o más espacios. Las claves deben corresponder a la [configuración de ClickHouse]. Escape los espacios, las comas y las barras invertidas en los valores con una barra invertida:

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

O bien, use valores entre comillas simples para evitar tener que escapar espacios y comas; considere usar dollar quoting para evitar tener que usar comillas dobles:

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

Si te preocupa la legibilidad y necesitas establecer muchos ajustes, usa varias líneas, por ejemplo:

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

Algunas configuraciones se ignorarán en los casos en que interfieran con el funcionamiento de pg_clickhouse. Estas incluyen:

date_time_output_format: el driver HTTP requiere que sea “iso”
format_tsv_null_representation: el driver HTTP requiere el valor predeterminado
output_format_tsv_crlf_end_of_line el driver HTTP requiere el valor predeterminado

Por lo demás, pg_clickhouse no valida las configuraciones, sino que las pasa a ClickHouse en cada consulta. Por lo tanto, admite todas las configuraciones de cada versión de ClickHouse. Tenga en cuenta que pg_clickhouse debe cargarse antes de establecer pg_clickhouse.session_settings; use precarga de bibliotecas compartidas o simplemente use uno de los objetos de la extensión para asegurarse de que se cargue.

`pg_clickhouse.pushdown_regex`

El parámetro pg_clickhouse.pushdown_regex controla si pg_clickhouse realiza pushdown de las funciones y los operadores de expresiones regulares. Lo hace de forma predeterminada; establezca este parámetro en false para evitarlo:

SET pg_clickhouse.pushdown_regex = 'false';

Consulte Expresiones regulares para más detalles.

ALTER ROLE

Use el comando SET de ALTER ROLE para precargar pg_clickhouse y/o SET sus parámetros para determinados roles:

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

Utilice el comando RESET de ALTER ROLE para restablecer la precarga de pg_clickhouse y/o los 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

Precarga

Si todas o casi todas las conexiones a Postgres necesitan usar pg_clickhouse, considere usar la precarga de bibliotecas compartidas para que se cargue automáticamente:

`session_preload_libraries`

Carga la biblioteca compartida en cada nueva conexión a PostgreSQL:

session_preload_libraries = pg_clickhouse

Resulta útil para aprovechar las actualizaciones sin reiniciar el servidor: basta con volver a conectarse. También puede establecerse para usuarios o roles específicos mediante ALTER ROLE.

`shared_preload_libraries`

Carga la biblioteca compartida en el proceso principal de PostgreSQL durante el arranque:

shared_preload_libraries = pg_clickhouse

Útil para ahorrar memoria y reducir la sobrecarga en cada sesión, pero requiere que se reinicie el clúster cuando se actualiza la biblioteca.

Tipos de datos

pg_clickhouse mapea los siguientes tipos de datos de ClickHouse a tipos de datos de PostgreSQL. IMPORT FOREIGN SCHEMA usa el primer tipo de la columna de PostgreSQL al importar columnas; también pueden usarse tipos adicionales en las sentencias CREATE FOREIGN TABLE:

ClickHouse	PostgreSQL	Notas
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	Da error con valores > BIGINT max
UInt8	smallint
UUID	uuid

Cualquier columna también se puede leer como text, varchar u otro tipo de cadena. El valor adopta el tipo de PostgreSQL anterior y luego se representa mediante la función de salida de ese tipo. Los valores UInt64 superiores al máximo de bigint siguen dando error, así que represente esos valores con la función toString() de ClickHouse. A continuación se incluyen notas y detalles adicionales.

BYTEA

ClickHouse no ofrece un equivalente al tipo BYTEA de PostgreSQL, pero permite almacenar cualquier secuencia de bytes en el tipo String. En general, las cadenas de ClickHouse deben mapearse al tipo TEXT de PostgreSQL; sin embargo, cuando se trabaja con datos binarios, deben mapearse al tipo BYTEA. Ejemplo:

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

Esa consulta SELECT final generará:

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

Tenga en cuenta que si hay bytes nulos en las columnas de ClickHouse, una tabla externa que use columnas TEXT no mostrará los valores correctos:

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

Generará:

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

Tenga en cuenta que las filas dos y tres contienen valores truncados. Esto se debe a que PostgreSQL utiliza cadenas terminadas en nul y no admite nuls en sus cadenas. Intentar insertar valores binarios en columnas TEXT funcionará correctamente y producirá el resultado 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;

Las columnas de texto serán correctas:

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

Pero leerlos como BYTEA no funcionará:

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

Por regla general, use las columnas TEXT solo para cadenas codificadas y las columnas BYTEA solo para datos binarios, y nunca alterne entre unas y otras.

Referencia de funciones y operadores

Funciones

Estas funciones proporcionan la interfaz para realizar consultas a una base de datos de ClickHouse.

`clickhouse_raw_query`

Obsoleto: clickhouse_raw_query() emite una advertencia de obsolescencia y se eliminará en la próxima versión. Use clickhouse_query para leer filas y clickhouse_perform para ejecutar sentencias que no devuelven resultados. Ambas reutilizan el controlador, las credenciales, la base de datos y la caché de conexiones de un servidor configurado en lugar de una cadena de conexión ad hoc.

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

Conéctese a un servicio de ClickHouse, ejecute una sola consulta y desconéctese. El segundo argumento opcional especifica una cadena de conexión cuyo valor predeterminado es host=localhost port=8123. Los parámetros de conexión admitidos son:

driver: El driver de conexión que se usará, ya sea “http” o “binary”; el valor predeterminado es “http”
host: El host al que conectarse; obligatorio.
port: El puerto al que conectarse. El valor predeterminado es 8123 para el driver “http” o 9000 para el driver “binary”, cambiando a 8443 o 9440 respectivamente cuando host es un host de ClickHouse Cloud
dbname: El nombre de la base de datos a la que conectarse.
username: El nombre de usuario con el que conectarse; el valor predeterminado es default
password: La contraseña que se usará para autenticarse; de forma predeterminada no hay contraseña

Ambos drivers devuelven filas separadas por tabulaciones (los NULL como \N), pero sus representaciones de los valores difieren: el driver “http” devuelve literalmente el formato TSV propio de ClickHouse, mientras que el driver “binary” procesa cada valor mediante su función de salida de PostgreSQL. De forma predeterminada, ningún rol tiene acceso EXECUTE a esta función; considere usar GRANT para otorgar acceso solo a los roles que realmente necesiten ejecutar consultas ad hoc de ClickHouse, por ejemplo, un rol de administrador de ClickHouse dedicado: Útil para consultas que no devuelven registros, pero las consultas que sí devuelven valores se devolverán como un ú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');

Indica la versión del servidor ClickHouse, como major.minor.patch, del servidor externo indicado; si es necesario, se conecta mediante las opciones del servidor y la asignación de usuario del usuario actual:

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

Obtiene la versión durante el handshake de la conexión mediante el protocolo nativo o, a través de HTTP, mediante una única consulta SELECT version(), y la almacena en caché durante la vida de la conexión.

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

Ejecute una consulta en un servidor externo ya configurado y devuelva sus filas como una relación, asignando cada columna de resultados de ClickHouse al tipo de PostgreSQL indicado en la lista de definición de columnas. Reutiliza el driver del servidor, las credenciales, la base de datos y la caché de conexiones. El primer argumento es el nombre de un servidor creado con CREATE SERVER. Se requiere una lista de definición de columnas (AS name(col type, ...)): PostgreSQL necesita conocer la estructura del resultado antes de obtener las filas, y debe coincidir con las columnas que devuelve la consulta. Los valores se convierten de ClickHouse a los tipos declarados del mismo modo que los de una columna de tabla externa. Las sentencias que no devuelven resultados, como las DDL, no tienen nada que declarar; ejecútelas con clickhouse_perform. Ningún rol tiene acceso EXECUTE de forma predeterminada; use GRANT para conceder a un rol permiso para usar la función.

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

Ejecute una sentencia en un servidor externo ya configurado y descarte cualquier resultado. Úselo para sentencias que no devuelven filas, como DDL, cuando clickhouse_query no tiene una forma de resultado que declarar. Resuelve el servidor del mismo modo que clickhouse_query y reutiliza su driver, credenciales, base de datos y caché de conexión. Como procedimiento, debe invocarse con CALL, no con SELECT, y no devuelve filas. Ningún rol tiene acceso EXECUTE de forma predeterminada; conceda GRANT a un rol para permitirle usar el procedimiento.

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

Funciones pushdown

pg_clickhouse aplica pushdown a un subconjunto de las funciones integradas de PostgreSQL que se usan en condicionales (cláusulas HAVING y WHERE). Ese subconjunto se corresponde con los equivalentes en ClickHouse de la siguiente manera:

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: funciones matemáticas de ClickHouse con el mismo nombre. asin, acos, atanh, acosh no se delegan: PG produce un error con entradas fuera de rango, mientras que CH devuelve 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): mismas equivalencias que date_part
date(timestamp) & date(timestamptz): toDate (se muestra como alias de CH date)
array_position: indexOf con nullIf para convertir 0 en NULL y arraySlice cuando hay un tercer argumento para el índice inicial de búsqueda; ten en cuenta que actualmente nan no coincide
array_cat: arrayConcat
array_append: arrayPushBack
array_prepend: arrayPushFront
array_remove: arrayRemove
cardinality: length
array_length(array, 1): nullIf(length(array), 0)
array_length & cardinality: length
array_to_string: arrayStringConcat
string_to_array: splitByString
split_part: splitByString + subíndice del 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 si la expresión regular contiene subexpresiones entre paréntesis; en caso contrario, extractAll recortado con arraySlice.
regexp_replace: replaceRegexpOne o replaceRegexpOne cuando está presente la marca g
regexp_split_to_array: splitByRegexp
md5: MD5
encode(bytea, fmt) cuando fmt es una constante de cadena (sin distinción entre mayúsculas y minúsculas):
- encode(bytea, 'hex'): hex envuelto en lower, ya que PostgreSQL genera valores hexadecimales en minúsculas.
- encode(bytea, 'base64'): base64Encode envuelto en replaceRegexpAll para reproducir los saltos de línea MIME (RFC 2045) de PostgreSQL cada 76 caracteres.
- encode(bytea, 'base64url') (PostgreSQL 19+): base64URLEncode, que coincide con el alfabeto de URL de RFC 4648 de PostgreSQL sin padding.
json_extract_path_text: sintaxis de subcolumnas
json_extract_path: toJSONString + sintaxis de subcolumnas
jsonb_extract_path_text: sintaxis de subcolumnas
jsonb_extract_path: toJSONString + sintaxis de subcolumnas
bit_count(bytea): bitCount
to_timestamp(float8): toDateTime64
to_char(timestamp[tz], fmt): formatDateTime cuando fmt es una constante de cadena cuyas palabras clave tienen cada una un equivalente fiel en ClickHouse. Consulta to_char() en las Notas de compatibilidad para ver las palabras clave admitidas. En caso contrario, la función se evalúa localmente en PostgreSQL.
statement_timestamp, transaction_timestamp, & clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))
CURRENT_DATE: now y 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: Se pasa como valor desde una función de PostgreSQL.
CURRENT_SCHEMA: Se pasa como valor de la función de PostgreSQL.
CURRENT_CATALOG: Se pasa como valor desde una función de PostgreSQL.
CURRENT_USER: Se pasa como valor desde una función de PostgreSQL.
USER: Se pasa como valor desde una función de PostgreSQL.
CURRENT_ROLE: Se pasa como valor desde la función de PostgreSQL.
SESSION_USER: Se pasa como valor de la función de PostgreSQL.

Operadores pushdown

Segmento de Array (arr[L:U]): arraySlice
@> (el array contiene): hasAll
<@ (array contenido en): hasAll
&& (solapamiento de arrays): hasAny
~ (coincidencia de regexp): match
!~ (sin coincidencia de regexp): match
~* (regexp sin distinguir mayúsculas de minúsculas, sin coincidencia): match
!~* (regexp sin distinguir mayúsculas de minúsculas, sin coincidencia): match
->> (extraer elemento de JSON/JSONB como texto): sintaxis de subcolumnas
-> (extracción de JSON/JSONB): toJSONString + sintaxis de subcolumnas

Semántica de IN y NULL

ClickHouse evalúa IN con lógica de dos valores: cuando la comprobación no encuentra ninguna coincidencia, devuelve 0 incluso si interviene un NULL, mientras que PostgreSQL calcula NULL. Para preservar la semántica de PostgreSQL, pg_clickhouse hace pushdown de la familia IN sobre una lista o un array constantes (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL) de forma incondicional: usa la forma nativa o de bajo coste cuando puede demostrar que ni la comprobación ni un elemento del array pueden ser NULL, o bien una forma CASE con protección en caso contrario, que comprueba los valores NULL en tiempo de ejecución y calcula la respuesta exacta de tres valores de PostgreSQL (TRUE, FALSE, NULL) en cualquier contexto, incluidas posiciones de valor como una lista SELECT o GROUP BY. Un filtro NOT IN (SELECT ...) sobre columnas que admiten NULL también hace pushdown, y se desanaliza con protecciones compensatorias que preservan el comportamiento de PostgreSQL: un conjunto que contiene un NULL descalifica todas las filas, y una comprobación NULL solo pasa frente a un conjunto vacío. Cada protección se omite cuando una declaración NOT NULL demuestra que no es necesaria. A diferencia de las formas de array anteriores, esta protección solo se aplica en una condición de filtro simple (o bajo NOT); seguimos sin hacer pushdown de IN (SELECT ...) (en una posición de valor) ni de cuerpos de subconsultas agrupados o agregados. Declarar las columnas como NOT NULL maximiza el pushdown, ya que permite enviar en su lugar la forma sin protección, más económica; IMPORT FOREIGN SCHEMA lo hace automáticamente para las columnas de ClickHouse que no son Nullable. La comprobación sigue las constantes no NULL, las columnas NOT NULL y la aritmética básica (+, -, *, - unario) aplicada a ellas. Estas reglas presuponen el valor predeterminado de ClickHouse transform_null_in = 0, que pg_clickhouse establece en cada consulta mediante el valor predeterminado del parámetro pg_clickhouse.session_settings, para que un perfil del servidor ClickHouse no pueda modificarlo silenciosamente. Establecer transform_null_in = 1 rompe la semántica de todos los IN a los que se les hace pushdown.

Funciones personalizadas

Estas funciones personalizadas creadas por pg_clickhouse permiten el pushdown de consultas externas para ciertas funciones de ClickHouse que no tienen equivalentes en PostgreSQL. Si alguna de estas funciones no puede enviarse mediante pushdown, lanzará una excepción.

dictGet

Pushdown de extensiones

pg_clickhouse reconoce funciones de ciertas extensiones principales y de terceros y las envía a sus equivalentes en ClickHouse.

re2

Todos los operadores y funciones de la extensión re2 hacen pushdown 1:1 a 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

Una función de intarray hace pushdown a ClickHouse:

idx → indexOf

fuzzystrmatch

Dos funciones de fuzzystrmatch hacen pushdown a ClickHouse:

soundex: soundex
levenshtein (2-arg): editDistanceUTF8

Conversiones de tipos con pushdown

pg_clickhouse aplica pushdown a conversiones de tipos como CAST(x AS bigint) para tipos de datos compatibles. En el caso de tipos incompatibles, el pushdown fallará; si x en este ejemplo es un UInt64 de ClickHouse, ClickHouse se negará a convertir el valor. Para aplicar pushdown a conversiones de tipos a tipos de datos incompatibles, pg_clickhouse proporciona las siguientes funciones. Generan una excepción en PostgreSQL si no se aplican con pushdown.

Funciones de agregación con pushdown

Estas funciones de agregación de PostgreSQL admiten pushdown en ClickHouse.

Agregados personalizados

Estas funciones de agregación personalizadas creadas por pg_clickhouse permiten el pushdown de consultas externas para determinadas funciones de agregación de ClickHouse que no tienen equivalentes en PostgreSQL. Si alguna de estas funciones no puede aplicarse mediante pushdown, generará una excepción.

Pushdown de funciones de agregación de conjuntos ordenados

Estas funciones de agregación de conjuntos ordenados se corresponden con las [funciones de agregación paramétricas] de ClickHouse al pasar su argumento directo como parámetro y sus expresiones ORDER BY como argumentos. Por ejemplo, esta consulta de PostgreSQL:

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

Corresponde a esta consulta de ClickHouse:

SELECT quantile(0.25)(a) FROM t1;

Ten en cuenta que los sufijos no predeterminados de ORDER BY, DESC y NULLS FIRST no son compatibles y provocarán un error.

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

Funciones de agregación de conjuntos ordenados personalizadas

Estas funciones de agregación de conjuntos ordenados personalizadas creadas por pg_clickhouse permiten hacer pushdown de consultas externas para determinadas [funciones de agregación paramétricas] de ClickHouse. Si no se puede hacer pushdown de alguna de estas funciones, se generará una excepción.

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

Funciones de agregación de conjuntos ordenados personalizadas

Estas funciones de agregación de conjuntos ordenados personalizadas creadas por pg_clickhouse permiten el pushdown de consultas externas para determinadas funciones de [agregación paramétricas] de ClickHouse. Si no se puede hacer pushdown de alguna de estas funciones, se generará una excepción.

Funciones de ventana con pushdown

Estas [funciones de ventana] de PostgreSQL se ejecutan mediante pushdown en ClickHouse con cláusulas OVER (PARTITION BY ... ORDER BY ...), incluidas las especificaciones de marco cuando corresponde.

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

Las funciones de clasificación (row_number, rank, dense_rank, ntile, cume_dist, percent_rank) omiten la cláusula de marco durante el pushdown porque ClickHouse rechaza las especificaciones de marco para estas funciones.

Notas de compatibilidad

Expresiones regulares

Aunque pg_clickhouse aplica pushdown de las expresiones regulares a equivalentes de ClickHouse cuando pg_clickhouse.pushdown_regex es true (el valor predeterminado), y se esfuerza por garantizar un nivel básico de compatibilidad, tenga en cuenta las diferencias entre ambos y cómo las gestiona pg_clickhouse.

PostgreSQL admite [expresiones regulares POSIX], mientras que ClickHouse admite expresiones regulares RE2. Tenga cuidado con las diferencias de comportamiento: use RE2 cuando la expresión regular vaya a ser evaluada por ClickHouse (p. ej., en una cláusula WHERE) y POSIX cuando vaya a ser evaluada por Postgres (p. ej., en una cláusula SELECT).
pg_clickhouse hace pushdown de los [indicadores de Postgres] al anteponerlos a la expresión regular de ClickHouse dentro de (?). Por ejemplo:
regexp_like(val, '^VAL\d', 'i')
Pasa a ser
match(val, concat('(?i)', '^VAL\\d'))

Los únicos indicadores que admiten ambos y que, por lo tanto, pueden usarse al ser evaluados por ClickHouse son:

Indicador	Como	Notas
`i`	`i`	coincidencia sin distinguir entre mayúsculas y minúsculas
`m`	`m-s`	`^` y `$` coinciden con el inicio/final de línea, además del inicio/final del texto
`n`	`m-s`	alias de Postgres de `m`
`p`	`-s`	no permitir que `.` y `[^x]` coincidan con `\n`
`s`	`s`	permitir que `.` y `[^x]` coincidan con `\n`
`t`		sintaxis estricta; se ignora
`w`	`m`	coincidencia parcial inversa sensible a los saltos de línea

RE2 solo admite estos modificadores; no uses otros [modificadores de Postgres].

Esta tabla resume los efectos de los distintos modificadores (y de no usar ninguno, que equivale a s) al hacer coincidir saltos de línea y finales de línea. Tenga en cuenta que en Postgres, m y p impiden que las clases de caracteres negadas ([^xyz]) coincidan con un salto de línea, mientras que los equivalentes de ClickHouse no. Por lo demás, el comportamiento es el mismo en ClickHouse que en Postgres:

Patrón aplicado a `a\nb`	Postgres	ClickHouse	¿Coincide?
`a.b`	true	true	✔︎
`a[^x]b`	true	true	✔︎
`a$`	false	false	✔︎
Indicador `s`
`(?s)a.b`	true	true	✔︎
`(?s)a[^x]b`	true	true	✔︎
`(?s)a$`	false	false	✔︎
Indicador `m`
`(?m)a.b`	false	false	✔︎
`(?m)a[^x]b`	true	false	✘
`(?m)a$`	true	true	✔︎
Indicador `p`
`(?p)a.b`	false	false	✔︎
`(?p)a[^x]b`	true	false	✘
`(?p)a$`	false	false	✔︎
Indicador `w`
`(?w)a.b`	true	true	✔
`(?w)a[^x]b`	true	true	✔
`(?w)a$`	true	true	✔

Cualquier otro indicador pasado a las funciones de expresiones regulares impedirá el pushdown de la función.
La excepción es regexp_replace(), que también admite el indicador g. Cuando g está activado, pg_clickhouse usa replaceRegexpAll() en lugar de replaceRegexpOne() y elimina el indicador antes de anteponer otros indicadores.
El argumento de reemplazo de regexp_replace() de Postgres admite \& para referirse a la coincidencia completa, mientras que en ClickHouse se usa \0 para la coincidencia completa. Asegúrate de usar \0 cuando la función haga pushdown a ClickHouse.
Postgres regexp_match devuelve NULL cuando no hay coincidencias, mientras que las expresiones a las que se les hace pushdown devuelven un arreglo vacío. Use COALESCE() para devolver un arreglo vacío en lugar de NULL y así poder comparar los valores devueltos de forma compatible. Por ejemplo:
SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');

Para evitar cualquier ambigüedad, considere configurar pg_clickhouse.pushdown_regex para impedir que las expresiones regulares de Postgres se envíen mediante pushdown a ClickHouse, y usar la extensión re2, para la cual pg_clickhouse admite direct pushdown de expresiones regulares RE2 compatibles con ClickHouse.

`to_char()`

PostgreSQL to_char() para timestamp y timestamp with time zone solo hace pushdown a ClickHouse formatDateTime cuando el argumento de formato es una constante de cadena no NULL en la que todas las palabras clave de PostgreSQL tienen un equivalente idéntico byte por byte en ClickHouse. Si el formato es dinámico (no es una Const), o contiene alguna palabra clave o modificador no admitido, la llamada vuelve a evaluarse localmente en PostgreSQL — nunca se intenta el pushdown con una traducción parcial, por lo que la salida sigue siendo compatible con PG. Las variantes de to_char() de dos argumentos sobre numeric, interval y otros tipos que no son de marca de tiempo nunca hacen pushdown; ClickHouse formatDateTime solo da formato a valores de fecha y hora.

Palabras clave traducidas

PostgreSQL	ClickHouse	Significado
`YYYY`, `yyyy`	`%Y`	año de 4 dígitos
`YY`, `yy`	`%y`	año de 2 dígitos
`MM`, `mm`	`%m`	mes con relleno de ceros (01–12)
`DD`, `dd`	`%d`	día del mes con relleno de ceros (01–31)
`DDD`, `ddd`	`%j`	día del año con relleno de ceros (001–366)
`HH24`, `hh24`	`%H`	hora de 24 horas con relleno de ceros (00–23)
`HH`, `hh`, `HH12`, `hh12`	`%I`	hora de 12 horas con relleno de ceros (01–12)
`MI`, `mi`	`%i`	minuto con relleno de ceros (00–59)
`SS`, `ss`	`%S`	segundo con relleno de ceros (00–59)
`Q`, `q`	`%Q`	trimestre (1–4)
`Mon`	`%b`	nombre abreviado del mes, p. ej., `Oct`
`Dy`	`%a`	nombre abreviado del día de la semana, p. ej., `Mon`
`AM`, `PM`	`%p`	indicador AM/PM, siempre en mayúsculas

Texto y literales entre comillas

El texto entrecomillado con "..." se pasa literalmente, y cualquier % literal se duplica como %% para escapar el prefijo de especificador de ClickHouse. Un \" fuera de las comillas también se pasa como un " literal. Dentro de "...", la barra invertida solo escapa "; las demás secuencias con barra invertida se tratan como texto literal.

Autores

David E. Wheeler

​Descripción

​Primeros pasos

​Uso

​Política de versionado

​Referencia 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

​Referencia de SQL DML

​EXPLAIN

​SELECT

​Tablas particionadas

​Cuándo se hace pushdown de agregaciones parciales

​Cuándo recurren a una alternativa

​PREPARE, EXECUTE, DEALLOCATE

​INSERT

​COPY

​LOAD

​SET

​pg_clickhouse.session_settings

​pg_clickhouse.pushdown_regex

​ALTER ROLE

​Precarga

​session_preload_libraries

​shared_preload_libraries

​Tipos de datos

​BYTEA

​Referencia de funciones y operadores

​Funciones

​clickhouse_raw_query

​clickhouse_server_version

​clickhouse_query

​clickhouse_perform

​Funciones pushdown

​Operadores pushdown

​Semántica de IN y NULL

​Funciones personalizadas

​Pushdown de extensiones

​re2

​intarray

​fuzzystrmatch

​Conversiones de tipos con pushdown

​Funciones de agregación con pushdown

​Agregados personalizados

​Pushdown de funciones de agregación de conjuntos ordenados

​Funciones de agregación de conjuntos ordenados personalizadas

​Funciones de agregación de conjuntos ordenados personalizadas

​Funciones de ventana con pushdown

​Notas de compatibilidad

​Expresiones regulares

​to_char()

​Palabras clave traducidas

​Texto y literales entre comillas

​Autores

​Derechos de autor

Descripción

Primeros pasos

Uso

Política de versionado

Referencia 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

Referencia de SQL DML

EXPLAIN

SELECT

Tablas particionadas

Cuándo se hace pushdown de agregaciones parciales

Cuándo recurren a una alternativa

PREPARE, EXECUTE, DEALLOCATE

INSERT

COPY

LOAD

SET

`pg_clickhouse.session_settings`

`pg_clickhouse.pushdown_regex`

ALTER ROLE

Precarga

`session_preload_libraries`

`shared_preload_libraries`

Tipos de datos

BYTEA

Referencia de funciones y operadores

Funciones

`clickhouse_raw_query`

`clickhouse_server_version`

`clickhouse_query`

`clickhouse_perform`

Funciones pushdown

Operadores pushdown

Semántica de IN y NULL

Funciones personalizadas

Pushdown de extensiones

re2

intarray

fuzzystrmatch

Conversiones de tipos con pushdown

Funciones de agregación con pushdown

Agregados personalizados

Pushdown de funciones de agregación de conjuntos ordenados

Funciones de agregación de conjuntos ordenados personalizadas

Funciones de agregación de conjuntos ordenados personalizadas

Funciones de ventana con pushdown

Notas de compatibilidad

Expresiones regulares

`to_char()`

Palabras clave traducidas

Texto y literales entre comillas

Autores

Derechos de autor