Documentación de referencia de pg_clickhouse

Documentación de referencia completa 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 14 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 en 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”).
• encoding_check: Define cómo tratar los caracteres no válidos según la [codificación de la base de datos] al convertir valores String y JSON de ClickHouse. Uno de:
  • fail (predeterminado): genera un error
  • remove elimina los bytes no válidos
  • replace: con la codificación UTF-8, sustituye los bytes no válidos por el carácter de reemplazo de Unicode (�); equivale a remove en otras codificaciones
  • truncate trunca el texto en el primer byte no válido

​

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 en 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 base de datos en 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.

Algunos detalles que conviene tener en cuenta:

• Las columnas importadas conservan el modificador de tipo del tipo de ClickHouse, incluidos los que se encuentran dentro de Arrays. Es decir, Array(Decimal(12,6)) se importa como numeric(12,6)[].

• Las columnas Nullable se importan sin NOT NULL; en cambio, Nullable dentro de un Array no tiene ese efecto, porque los arrays de PostgreSQL siempre admiten elementos NULL.

• Las columnas AggregateFunction y SimpleAggregateFunction se importan sin NOT NULL, independientemente de la declaración en ClickHouse; consulte Columnas de estado and NOT NULL.

• Las columnas Tuple se importan como text[]. Las columnas Map y las columnas Nested creadas con flatten_nested=0 se importan como text[][]. Cada conversión emite un NOTICE. El pseudotipo record de PostgreSQL no puede definir una columna de tabla, por lo que cada tupla se convierte en un array de valores, cada mapa en un array bidimensional de pares clave-valor y cada valor Nested en un array bidimensional de filas. Para leer los campos como registros, cambie el tipo de la columna a un array de un tipo compuesto equivalente; consulte Manual Type Mappings para ver un ejemplo.

• Las columnas DateTime64 y Time64 con una precisión superior a 6 (microsegundos) también generan un NOTICE, ya que PostgreSQL limita la precisión a timestamp(6) y time(6).

• Los tipos de columna sin equivalente en PostgreSQL, incluido el tipo heredado Object('json'), anterior a JSON de ClickHouse, generan un error.

Conservación de mayúsculas y minúsculas en los identificadores importados

IMPORT 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 Int64,
    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 en 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. Declare la columna como nullable para evitar los problemas descritos en Columnas de estado y NOT NULL.

  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.

​

Columnas de estado y NOT NULL

Mantenga las columnas AggregateFunction y SimpleAggregateFunction como Nullable. PostgreSQL 19 reemplaza count(column) por count(*) en las columnas de estado NOT NULL, lo que cuenta filas en lugar de fusionar estados.

Considere una tabla de ClickHouse que almacena un estado count por usuario, cada uno generado a partir de 1000 eventos:

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

Con NOT NULL, PostgreSQL cuenta los estados, lo que da como resultado un recuento no válido:

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

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

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

Sin NOT NULL, pg_clickhouse usa countMerge() para fusionar los estados, con lo que se obtiene el recuento correcto de 1000:

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

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

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

Use ALTER FOREIGN TABLE para eliminar la restricción de una tabla foránea existente:

ALTER FOREIGN TABLE hits ALTER COLUMN hits DROP NOT NULL;

NOT NULL solo afecta a PostgreSQL. Por eso, IMPORT FOREIGN SCHEMA importa las columnas de estado como columnas Nullable.

​

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_srv OPTIONS (table_name 'events');

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

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)

pg_clickhouse hace 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}:

                                                                                                         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

Esta tabla presenta la correspondencia preferida entre los tipos de datos de ClickHouse y PostgreSQL. IMPORT FOREIGN SCHEMA utiliza estas correspondencias y deriva a partir de ellas los modificadores de tipo y las dimensiones de array adecuados. Use CREATE FOREIGN TABLE para declarar tipos de PostgreSQL alternativos.

La columna de destinos de lectura adicionales enumera las conversiones que admite el controlador binary, además de las conversiones explícitas de PostgreSQL. Las celdas vacías no impiden esas conversiones. Los valores deben caber en los tipos de destino. Estos destinos se refieren a las lecturas; las escrituras siguen reglas de conversión propias.

ClickHouse	PostgreSQL predeterminado	Destinos de lectura adicionales	Notas
Array(T)	T[]		Un tipo de array de PG por cada nivel de profundidad
BFloat16	real
Bool	boolean
Date	date
Date32	date
DateTime	timestamp with time zone	time
DateTime64(P)	timestamp(P) with time zone	time	Los valores de P superiores a 6 se limitan a 6
Decimal(P,S)	numeric(P,S)	xid8, oid8
Decimal32(S)	numeric(9,S)	xid8, oid8
Decimal64(S)	numeric(18,S)	xid8, oid8
Decimal128(S)	numeric(38,S)	xid8, oid8
Decimal256(S)	numeric(76,S)	xid8, oid8
Enum8	text	bytea; tipos compatibles con la entrada	Decodifica la etiqueta; se admiten enums de PG con etiquetas coincidentes
Enum16	text	bytea; tipos compatibles con la entrada	Decodifica la etiqueta; se admiten enums de PG con etiquetas coincidentes
FixedString(N)	text	bytea; tipos compatibles con la entrada	Solo bytea conserva los NUL finales
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int8	smallint	boolean	Cero equivale a false; cualquier valor distinto de cero, a true
Int16	smallint	boolean	Cero equivale a false; cualquier valor distinto de cero, a true
Int32	integer
Int64	bigint
Int128	numeric(39,0)	xid8, oid8
Int256	numeric(77,0)	xid8, oid8
IntervalDay	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalHour	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalMicrosecond	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalMillisecond	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalMinute	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalMonth	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalNanosecond	interval	smallint, integer, bigint	Los enteros conservan los ns; interval trunca a us
IntervalQuarter	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalSecond	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalWeek	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
IntervalYear	interval	smallint, integer, bigint	Los enteros reciben el recuento de unidades
JSON	jsonb	json, text, bytea; tipos compatibles con la entrada	jsonb normaliza el documento
LineString	path	lseg	lseg requiere dos puntos
LowCardinality(T)	T
Map(K,V)	text[][]	composite[], T[][], text	Una fila de elementos de texto por cada par
MultiLineString	path[]
MultiPolygon	polygon[][]
Nested(…)	text[][]	composite[], T[][], text	Una fila de elementos de texto por cada fila anidada
Nullable(T)	T		La columna se importa sin NOT NULL
Point	point
Polygon	polygon[]
Ring	polygon	lseg	lseg requiere dos puntos
SimpleAggregateFunction(f,T)	T		Almacena los valores como T
String	text	bytea; tipos compatibles con la entrada	bytea conserva los bytes sin procesar
Time	time without time zone
Time64(P)	time(P) without time zone		Los valores de P superiores a 6 se limitan a 6
Tuple(…)	text[]	composite, T[], text; box, circle, line	Los campos se convierten en elementos de texto
UInt8	smallint	boolean	El cero es false; cualquier valor distinto de cero es true
UInt16	integer
UInt32	bigint
UInt64	numeric(20,0)	xid8, oid8
UInt128	numeric(39,0)	xid8, oid8
UInt256	numeric(78,0)	xid8, oid8
UUID	uuid

Cualquier columna también se puede leer como text, varchar u otro tipo de cadena. El valor adopta el tipo de PostgreSQL indicado arriba y luego se representa mediante la función de salida de ese tipo. Una cadena de ClickHouse leída como un tipo de texto se valida con la codificación de la base de datos, por lo que los bytes que PostgreSQL no puede leer generan un error. Declare una columna String, FixedString, Enum o JSON como BYTEA para leer sus bytes tal como ClickHouse los escribió.

Los tipos compatibles con la entrada pueden leer cadenas mediante su función de entrada de PostgreSQL. Los tipos compuestos deben tener los mismos campos en el mismo orden. Para leer una tupla como un array, cada campo debe poder convertirse al tipo de elemento del array, y ningún campo puede ser, a su vez, un array.

Cuando se leen como arrays, Map usa una fila por cada par clave-valor y Nested usa una fila por cada registro anidado. Para leer una tupla como box, proporcione dos puntos; para circle, un punto y un radio; y para line, tres coeficientes.

A continuación se incluyen notas y detalles adicionales.

​

Coerción de tipos

Con el controlador binary, los tipos escalares alternativos usan las conversiones explícitas de PostgreSQL cuando están disponibles, y los elementos de array se convierten al tipo de elemento declarado. Las conversiones no compatibles y los valores fuera de rango generan errores.

Con cualquiera de los dos controladores, asigne los tipos Interval de ClickHouse a smallint, integer o bigint para leer y escribir el recuento de sus unidades. Por ejemplo, un valor IntervalDay de 3 corresponde al integer 3. Si se asigna IntervalNanosecond a bigint, se conservan los nanosegundos, mientras que si se asigna a interval, el valor se trunca a microsegundos.

​

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)

Por lo general, una tabla externa que use columnas [TEXT] no puede leer estos valores como texto, porque pg_clickhouse valida los bytes de ClickHouse con respecto a la codificación de la base de datos:

-- 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á:

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

Un digest contiene bytes arbitrarios, que rara vez forman texto válido. Además, PostgreSQL reserva el carácter nul, por lo que una columna [TEXT] nunca puede leer una cadena de ClickHouse que lo contenga.

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 mostrarán los valores correctos:

 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)

Una columna FixedString(N) rellena los valores cortos con bytes nulos. Una columna de texto descarta ese relleno final, mientras que una columna BYTEA conserva todos los bytes.

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.

​

Tipos compuestos

​

Array

Los [Array] de ClickHouse se corresponden directamente con los arrays de PostgreSQL, con una semántica equivalente. La principal diferencia es que, en Postgres, los arrays multidimensionales deben tener expresiones de array con dimensiones coincidentes. Si se intenta leer un array de ClickHouse con dimensiones distintas, como [[1], [2,3]], se produce un error.

El acceso por índice a los arrays hace pushdown cuando corresponde, incluido el acceso por índice multidimensional. Ejemplos:

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

Los segmentos de Array también se delegan mediante la función [arraySlice], aunque el acceso a segmentos multidimensionales aún no es compatible:

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

​

Map

PostgreSQL no ofrece ningún tipo equivalente al tipo [Map] de ClickHouse. Por ello, pg_clickhouse asigna las columnas [Map] a text[][], de modo que cada par clave-valor se representa como un array independiente. IMPORT FOREIGN SCHEMA utiliza esta correspondencia. Asimismo, es posible usar INSERT para insertar mapas como arrays. Por ejemplo:

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

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

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

Cada array anidado debe contener exactamente dos valores de texto. Los valores incompatibles con los tipos de clave o de valor correspondientes de ClickHouse provocan un error.

Para insertar un Map se requiere el controlador binary, que obtiene los tipos de columna del servidor de ClickHouse. El controlador http no lo hace, por lo que carece de la información necesaria para formatear e insertar el valor adecuado.

​

Tuple

Del mismo modo, las columnas [Tuple] de ClickHouse se asignan a text[] y admiten operaciones INSERT mediante el controlador binario. IMPORT FOREIGN SCHEMA emite un NOTICE cuando establece dicha correspondencia.

​

Nested

De forma predeterminada, ClickHouse divide una columna Nested en una columna Array por cada campo (flatten_nested=1). IMPORT FOREIGN SCHEMA lee estas columnas del catálogo system.columns de ClickHouse y crea columnas de array independientes en PostgreSQL, conservando los nombres con puntos, como items.a e items.b. Por ejemplo, dada esta tabla externa:

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

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

Su esquema importado tiene tres columnas en lugar de dos:

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

Consulte las columnas anidadas escribiendo los nombres de las columnas entre comillas dobles, por ejemplo:

SELECT ci, "c2.id" FROM nests;

Y filtre valores en una cláusula WHERE con las funcionalidades habituales de los arrays, incluida la sintaxis de subíndices de array:

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

Una columna [Nested] creada con flatten_nested=0 se corresponde con un array con un elemento por cada fila anidada. Cada elemento del array contiene los valores de esa fila:

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

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

Ahora, el esquema resultante es:

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

Componga los registros anidados en un array bidimensional con valores de texto formateados según cada tipo definido por la columna de ClickHouse. Para c2 Nested(id Int64, name String) de este ejemplo, quedaría así:

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

Sin embargo, el pushdown de columnas text[][] sobre tipos Nested falla:

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

Esto se debe a que pg_clickhouse no puede distinguir entre una columna de array de texto definida sobre una columna de array de ClickHouse y otra definida sobre una columna Nested, por lo que no puede reescribirla con la sintaxis Nested de ClickHouse.

No obstante, un array de un tipo compuesto equivalente también puede mapear estos elementos (consulte Correspondencias manuales de tipos para más detalles). En ese caso, el pushdown funciona siempre que los nombres de los campos del tipo compuesto sean idénticos a los nombres de los campos Nested:

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

​

Correspondencias manuales de tipos

IMPORT FOREIGN SCHEMA utiliza tipos genéricos de PostgreSQL. Por ejemplo, dada una tabla de ClickHouse con columnas [Enum], [Tuple], Map y [Nested] no aplanadas (flatten_nested = 0), como la siguiente:

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

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

Para conservar la estructura o restringir los valores, defina tipos compuestos o enum de PostgreSQL y cree manualmente una tabla externa:

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

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

Asegúrese siempre de que las etiquetas de enum y los nombres de los campos de los tipos compuestos sean idénticos a las etiquetas de enum de ClickHouse y a los nombres de los campos de Nested o Tuple, para garantizar que el pushdown utilice los nombres correctos.

Haga coincidir el orden y los tipos de los campos compuestos con las declaraciones de ClickHouse. Las claves y los valores de [Map] pasan a ser el primer y el segundo campo, respectivamente (key y value en este caso). Nested y Map se convierten en arrays de tipos compuestos, mientras que Tuple se convierte en un único valor compuesto:

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

Por supuesto, también puede usar los nombres de los campos del tipo compuesto, tanto en una lista SELECT:

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

Y, para los tipos [Nested] en una cláusula WHERE, siempre que los nombres de los campos sean idénticos:

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

Aún no se admite INSERT con este tipo de tipos compuestos.

​

Referencia de funciones y operadores

​

Funciones

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

​

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 estructura del 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, no 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: arrayFlattenedLength en ClickHouse 26.9+; en caso contrario, length, que solo cuenta el array exterior
• 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
• sha224, sha256, sha384 y sha512: Las funciones SHA correspondientes de ClickHouse
• 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

​

pgcyrpto

• digest(text, text) y digest(bytea, text): la función hash de ClickHouse correspondiente cuando el algoritmo es una constante md5, sha1, sha224, sha256, sha384 o sha512 (sin distinguir entre mayúsculas y minúsculas).

​

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.

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

​

Funciones de agregación con pushdown

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

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

​

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.

• argMax
• argMin
• uniq
• uniqCombined
• uniqCombined64
• uniqExact
• uniqHLL12
• uniqTheta
• quantile
• quantileExact

​

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.

• quantile
• quantileExact

​

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][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 o contiene alguna palabra clave o modificador no admitido, la llamada vuelve a evaluarse localmente en PostgreSQL. pg_clickhouse nunca hace pushdown de una traducción parcial, por lo que la salida sigue siendo compatible.

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

​

Derechos de autor

Derechos de autor (c) 2025-2026, ClickHouse

[Array] https://clickhouse.com/docs/reference/data-types/array “Documentación de ClickHouse: Array(T)” [Map]: https://clickhouse.com/docs/sql-reference/data-types/map “Documentación de ClickHouse: Map” [Tuple]: https://clickhouse.com/docs/reference/data-types/tuple “Documentación de ClickHouse: Tuple(T1, T2, …)” [Nested]: https://clickhouse.com/docs/sql-reference/data-types/nested-data-structures/nested “Documentación de ClickHouse: Nested” [Enum]: https://clickhouse.com/docs/reference/data-types/enum “Documentación de ClickHouse: Enum” [String]: /reference/data-types/string “Documentación de ClickHouse: String” [TEXT]: https://www.postgresql.org/docs/current/datatype-character.html “Documentación de PostgreSQL: Tipos de caracteres” [window functions]: https://www.postgresql.org/docs/current/functions-window.html “Documentación de PostgreSQL: Funciones de ventana” [POSIX Regular Expressions]: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP “Documentación de PostgreSQL: Expresiones regulares POSIX” [Postgres flags]: https://www.postgresql.org/docs/current/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE “Documentación de PostgreSQL: Letras de opciones integradas de ARE” [RE2]: https://github.com/google/re2/wiki/Syntax “Sintaxis de RE2” [extensión re2]: https://github.com/ClickHouse/pg_re2 “pg_re2: funciones de expresiones regulares compatibles con ClickHouse que usan RE2” [intarray]: https://www.postgresql.org/docs/current/intarray.html “Documentación de PostgreSQL: intarray” [fuzzystrmatch]: https://www.postgresql.org/docs/current/fuzzystrmatch.html “Documentación de PostgreSQL: fuzzystrmatch” [to_char()]: https://www.postgresql.org/docs/current/functions-formatting.html “Documentación de PostgreSQL: Funciones de formato de tipos de datos” [formatDateTime]: /reference/functions/regular-functions/date-time-functions#formatDateTime “Documentación de ClickHouse: formatDateTime”