pg_clickhouse reference documentation

Complete reference documentation for pg_clickhouse

Description

pg_clickhouse is a PostgreSQL extension that enables remote query execution on ClickHouse databases, including a foreign data wrapper. It supports PostgreSQL 14 and higher and ClickHouse 23.3 and higher.

Getting started

The simplest way to try pg_clickhouse is the Docker image, which contains the standard PostgreSQL Docker image with the pg_clickhouse and [re2][re2 extension] extensions:

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

See the tutorial to get started importing ClickHouse tables and pushing down queries.

Usage

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;

Versioning policy

pg_clickhouse adheres to Semantic Versioning for its public releases.

• The major version increments for API changes
• The minor version increments for backward compatible SQL changes
• The patch version increments for binary-only changes

Once installed, PostgreSQL tracks two variations of the version:

• The library version (defined by PG_MODULE_MAGIC on PostgreSQL 18 and higher) includes the full semantic version, visible in the output of the pgch_version() function or the Postgres pg_get_loaded_modules() function.
• The extension version (defined in the control file) includes only the major and minor versions, visible in the pg_catalog.pg_extension table, the output of the pg_available_extension_versions() function, and \dx pg_clickhouse.

In practice this means that a release that increments the patch version, e.g. from v0.1.0 to v0.1.1, benefits all databases that have loaded v0.1 and don’t need to run ALTER EXTENSION to benefit from the upgrade.

A release that increments the minor or major versions, on the other hand, will be accompanied by SQL upgrade scripts, and all existing database that contain the extension must run ALTER EXTENSION pg_clickhouse UPDATE to benefit from the upgrade.

DDL SQL reference

The following SQL DDL expressions use pg_clickhouse.

CREATE EXTENSION

Use CREATE EXTENSION to add pg_clickhouse to a database:

CREATE EXTENSION pg_clickhouse;

Use WITH SCHEMA to install it into a specific schema (recommended):

CREATE SCHEMA ch;
CREATE EXTENSION pg_clickhouse WITH SCHEMA ch;

ALTER EXTENSION

Use ALTER EXTENSION to change pg_clickhouse. Examples:

• After installing a new release of pg_clickhouse, use the UPDATE clause:

  ALTER EXTENSION pg_clickhouse UPDATE;
  

• Use SET SCHEMA to move the extension to a new schema:

  CREATE SCHEMA ch;
  ALTER EXTENSION pg_clickhouse SET SCHEMA ch;
  

DROP EXTENSION

Use DROP EXTENSION to remove pg_clickhouse from a database:

DROP EXTENSION pg_clickhouse;

This command fails if there are any objects that depend on pg_clickhouse. Use the CASCADE clause to drop them, too:

DROP EXTENSION pg_clickhouse CASCADE;

CREATE SERVER

Use CREATE SERVER to create a foreign server that connects to a ClickHouse server. Example:

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

The supported options are:

• driver: The ClickHouse connection driver to use, either “binary” or “http”. Required.
• compression: Native-protocol compression for the “binary” driver, one of “none”, “lz4”, or “zstd”. Defaults to “lz4”. Ignored by the “http” driver.
• dbname: The ClickHouse database to use upon connecting. Defaults to “default”.
• host: The host name of the ClickHouse server. Defaults to “localhost”;
• port: The port to connect to on the ClickHouse server. Defaults as follows:
  • 9440 if driver is “binary” and host is a ClickHouse Cloud host
  • 9004 if driver is “binary” and host isn’t a ClickHouse Cloud host
  • 8443 if driver is “http” and host is a ClickHouse Cloud host
  • 8123 if driver is “http” and host isn’t a ClickHouse Cloud host
• min_tls_version: Minimum TLS protocol version to negotiate on connections that use TLS. One of TLSv1, TLSv1.1, TLSv1.2, or TLSv1.3. Defaults to the TLS library’s own minimum. Applies to both drivers.
• secure: Controls TLS for the connection. One of:
  • auto (default): use TLS when host is a ClickHouse Cloud host or port is a secure port; plaintext otherwise.
  • on (or true/yes/1): always use TLS. Defaults port to 8443 (“http”) or 9440 (“binary”).
  • off (or false/no/0): never use TLS. Defaults port to 8123 (“http”) or 9000 (“binary”).
• encoding_check: Defines how to to handle invalid characters under the database encoding when converting ClickHouse String and JSON values. One of:
  • fail (default): raise an error
  • remove removes invalid bytes
  • replace: under the UTF-8 encoding, replaces invalid bytes with the Unicode replacement character (�); same as remove for other encodings
  • truncate truncates the text at the first invalid byte

ALTER SERVER

Use ALTER SERVER to change a foreign server. Example:

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

The options are the same as for CREATE SERVER.

DROP SERVER

Use DROP SERVER to remove a foreign server:

DROP SERVER taxi_srv;

This command fails if any other objects depend on the server. Use CASCADE to also drop those dependencies:

DROP SERVER taxi_srv CASCADE;

CREATE USER MAPPING

Use CREATE USER MAPPING to map a PostgreSQL user to a ClickHouse user. For example, to map the current PostgreSQL user to the remote ClickHouse user when connecting with the taxi_srv foreign server:

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

The supported options are:

• user: The name of the ClickHouse user. Defaults to “default”.
• password: The password of the ClickHouse user.

ALTER USER MAPPING

Use ALTER USER MAPPING to change the definition of a user mapping:

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

The options are the same as for CREATE USER MAPPING.

DROP USER MAPPING

Use DROP USER MAPPING to remove a user mapping:

DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;

IMPORT FOREIGN SCHEMA

Use IMPORT FOREIGN SCHEMA to import all the tables defines in a ClickHouse database as foreign tables into a PostgreSQL schema:

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

Use LIMIT TO to limit the import to specific tables:

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

Use EXCEPT to exclude tables:

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

pg_clickhouse will fetch a list of all the tables in the specified ClickHouse database (“demo” in the above examples), fetch column definitions for each, and execute CREATE FOREIGN TABLE commands to create the foreign tables. Columns will be defined using the supported data types and, were detectable, the options supported by CREATE FOREIGN TABLE.

Some details to keep in mind:

• Imported columns keep the type modifier of the ClickHouse type, including those within Arrays. In other words, Array(Decimal(12,6)) imports as numeric(12,6)[].

• Nullable columns import without NOT NULL; Nullable inside an Array does not, because PostgreSQL arrays always allow NULL elements.

• AggregateFunction and SimpleAggregateFunction columns import without NOT NULL, whatever the ClickHouse declaration, see State Columns and NOT NULL.

• Tuple columns import as text[]. Map and Nested columns columns created with flatten_nested=0 import as text[][]. Each conversion emits a NOTICE. PostgreSQL’s record pseudotype cannot define a table column, so each tuple becomes an array of values, each map becomes an two-dimensional array of key-value pairs, and each Nested value becomes an two-dimensional array of rows. To read fields as records, alter the column to an array of a matching composite type, see Manual Type Mappings for an example.

• DateTime64 and Time64 columns with precision greater than 6 (microseconds) also trigger a NOTICE, since PostgreSQL caps precision at timestamp(6) and time(6).

• Column types without a PostgreSQL counterpart, including the legacy Object('json') type that predates ClickHouse JSON, trigger an error.

Imported Identifier Case Preservation

IMPORT FOREIGN SCHEMA runs quote_identifier() on the table and column names it imports, which double-quotes identifiers with uppercase characters or blank spaces. Such table and column names thus must be double-quoted in PostgreSQL queries. Names with all lowercase and no blank space characters don’t need to be quoted.

For example, given this ClickHouse table:

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

IMPORT FOREIGN SCHEMA creates this foreign table:

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

Queries therefore must quote appropriately, e.g.,

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

To create objects with different names or all lowercase (and therefore case-insensitive) names, use CREATE FOREIGN TABLE.

CREATE FOREIGN TABLE

Use CREATE FOREIGN TABLE to create a foreign table that can query data from a ClickHouse database:

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

The supported table options are:

• database: The name of the remote database. Defaults to the database defined for the foreign server.
• table_name: The name of the remote table. Default to the name specified for the foreign table.
• engine: The table engine used by the ClickHouse table. For CollapsingMergeTree() and AggregatingMergeTree(), pg_clickhouse automatically applies the parameters to function expressions executed on the table.

Use the data type appropriate for the remote ClickHouse data type of each column. The supported column options are:

• column_name: The name of the column on the ClickHouse side, used in preference to the PostgreSQL attribute name when deparsing queries and inserts. Useful for mapping unquoted lowercase PostgreSQL column names to case-sensitive ClickHouse columns, e.g.,

  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: The name of the aggregate function applied to an AggregateFunction Type column. Map the data type to the ClickHouse type passed to the function and specify the name of the aggregate function via the appropriate column option and pg_clickhouse will automatically append Merge to an aggregate function evaluating the column. Declare the column nullable to avoid issues described in State Columns and 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: The name of the aggregate function applied to an SimpleAggregateFunction Type column. Map the data type to the ClickHouse type passed to the function and specify the name of the aggregate function via the appropriate column option.

State Columns and NOT NULL

Keep AggregateFunction and SimpleAggregateFunction columns nullable. PostgreSQL 19 changes count(column) to count(*) for NOT NULL state columns, which counts rows instead of merging states.

Take a ClickHouse table holding one count state per user, each state built from 1,000 events:

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

With NOT NULL, PostgreSQL counts states, which produces an invalid count:

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)

Without NOT NULL, pg_clickhouse uses countMerge() to merge states, yielding the proper 1000 count:

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 to drop the constraint from an existing foreign table:

ALTER FOREIGN TABLE hits ALTER COLUMN hits DROP NOT NULL;

NOT NULL only affects PostgreSQL. IMPORT FOREIGN SCHEMA therefore imports state columns as nullable.

ALTER FOREIGN TABLE

Use ALTER FOREIGN TABLE to change the definition of a foreign table:

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

The supported table and column options are the same as for CREATE FOREIGN TABLE.

DROP FOREIGN TABLE

Use DROP FOREIGN TABLE to remove a foreign table:

DROP FOREIGN TABLE acts;

This command fails if there are any objects that depend on the foreign table. Use the CASCADE clause to drop them, too:

DROP FOREIGN TABLE acts CASCADE;

DML SQL reference

The SQL DML expressions below may use pg_clickhouse. Examples depend on these ClickHouse tables:

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

The EXPLAIN command works as expected, but the VERBOSE option triggers the ClickHouse “Remote SQL” query to be emitted:

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)

This query pushes down to ClickHouse via a “Foreign Scan” plan node, the remote SQL.

SELECT

Use the SELECT statement to execute queries on pg_clickhouse tables just like any other tables:

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 works to push query execution down to ClickHouse as much as possible, including aggregate functions. Use EXPLAIN to determine the pushdown extent. For the above query, for example, all execution is pushed down to 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 also pushes down JOINs to tables that are from the same remote server:

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)

Joining with a local table will generate less efficient queries without careful tuning. In this example, we make a local copy of the nodes table and join to it instead of the remote table:

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

In this case, we can push more of the aggregation down to ClickHouse by grouping on node_id instead of the local column, and then join to the lookup table later:

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

The “Foreign Scan” node now pushes down aggregation by node_id, reducing the number of rows that must be pulled back into Postgres from 1000 (all of them) to just 8, one for each node.

Partitioned Tables

A PostgreSQL partitioned table can mix local partitions with foreign partitions backed by ClickHouse. A common layout offloads older data to ClickHouse while recent data stays in 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');

For example on how to move data from local to foreign partitions, see offload-partition.sql.

Aggregates spanning both local and foreign partitions need partitionwise aggregation, which PostgreSQL disables by default:

SET enable_partitionwise_aggregate = on;

With enable_partitionwise_aggregate enabled, PostgreSQL computes a partial aggregate below Append, then a finalize aggregate above combines those partials into result. pg_clickhouse pushes the foreign partition’s partial down to 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

When partial aggregates push down

PostgreSQL represents a partial aggregate as a transition state that the finalize step combines across partitions. pg_clickhouse can push a partition’s partial down only when it can express it as a ClickHouse value:

• Decomposable aggregates whose transition state is already the final value, push down directly: count, sum, min, max, bool_and/every, bool_or, bit_and, bit_or, and bit_xor.
• avg over integers pushes its {count, sum} state as an array.
• avg, var_pop, var_samp, stddev_pop, and stddev_samp over floating point push their {N, sum, sum of squared deviations} state as an array.

FILTER (WHERE …) pushes down with these aggregate functions.

When they fall back

Aggregates whose transition state is PostgreSQL’s opaque internal type have no portable representation, so the foreign partition instead fetches its rows and aggregates them locally. This covers anything over numeric, plus avg(bigint) and avg(interval). DISTINCT, ordered-set, and variadic aggregates also fall back.

PREPARE, EXECUTE, DEALLOCATE

As of v0.1.2, pg_clickhouse supports parameterized queries, mainly created by the PREPARE command:

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

Use EXECUTE as usual to execute a prepared statement:

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 pushes down the aggregations, as usual, as seen in the EXPLAIN verbose output:

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)

Note that it has sent the full date values, not the parameter placeholders. This holds for the first five requests, as described in the PostgreSQL PREPARE notes. On the sixth execution, it sends ClickHouse {param:type}-style query parameters:

                                                                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=1.00..5.10 rows=1000 width=36)
   Output: (date(start_at)), (round(avg(duration), 0))
   Relations: Aggregate on (logs)
   Remote SQL: SELECT date(start_at), round(avg(duration), 0) FROM "default".logs WHERE ((date(start_at) >= {p1:Date})) AND ((date(start_at) <= {p2:Date})) GROUP BY (date(start_at)) ORDER BY date(start_at) ASC NULLS LAST
(4 rows)

Use DEALLOCATE to deallocate a prepared statement:

try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE

INSERT

Use the INSERT command to insert values into a remote ClickHouse table:

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

COPY

Use the COPY command to insert a batch of rows into a remote ClickHouse table:

try=# COPY logs FROM stdin CSV;
4285871863,2025-12-05 11:13:58.360760,206,/widgets,POST,8,401
4020882978,2025-12-05 11:33:48.248450,199,/users/1321945,HEAD,3,200
3231273177,2025-12-05 12:20:42.158575,220,/search,GET,2,201
\.
>> COPY 3

⚠️ Batch API Limitations

pg_clickhouse hasn’t yet implemented support for the PostgreSQL FDW batch insert API. Thus COPY currently uses INSERT statements to insert records. This will be improved in a future release.

LOAD

Use LOAD to load the pg_clickhouse shared library:

try=# LOAD 'pg_clickhouse';
LOAD

It’s not normally necessary to use LOAD, as Postgres will automatically load pg_clickhouse the first time any of its features (functions, foreign tables, etc.) are used.

The one time it may be useful to LOAD pg_clickhouse is to SET pg_clickhouse parameters before executing queries that depend on them.

SET

Use SET to set the pg_clickhouse custom configuration parameters.

pg_clickhouse.session_settings

The pg_clickhouse.session_settings parameter configures ClickHouse settings to be set on subsequent queries. Example:

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

The default is

join_use_nulls 1, group_by_use_nulls 1, final 1, transform_null_in 0

Set it to an empty string to fall back on the ClickHouse server’s settings — but note that pushdown correctness depends on some of these defaults: join_use_nulls for outer joins and transform_null_in for the IN family (see IN and NULL Semantics).

SET pg_clickhouse.session_settings = '';

The syntax is a comma-delimited list of key/value pairs separated by one or more spaces. Keys must correspond to ClickHouse settings. Escape spaces, commas, and backslashes in values with a backslash:

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

Or use single quoted values to avoid escaping spaces and commas; consider using dollar quoting to avoid the need to double-quote:

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

If you care about legibility and need to set many settings, use multiple lines, for example:

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

Some settings will be ignored in cases where they would interfere with the operation of pg_clickhouse itself. These include:

• date_time_output_format: the http driver requires it to be “iso”
• format_tsv_null_representation: the http driver requires the default
• output_format_tsv_crlf_end_of_line the http driver requires the default

Otherwise, pg_clickhouse does not validate the settings, but passes them on to ClickHouse for every query. It thus supports all settings for each ClickHouse version.

Note that pg_clickhouse must be loaded before setting pg_clickhouse.session_settings; either use shared library preloading or simply use one of the objects in the extension to ensure it loads.

pg_clickhouse.pushdown_regex

The pg_clickhouse.pushdown_regex parameter controls whether pg_clickhouse pushes down regular expression functions and operators. It does so by default; set this parameter to false to prevent them from being pushed down:

SET pg_clickhouse.pushdown_regex = 'false';

See Regular Expressions for details.

ALTER ROLE

Use ALTER ROLE’s SET command to preload pg_clickhouse and/or SET its parameters for specific 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

Use the ALTER ROLE’s RESET command to reset pg_clickhouse preloading and/or parameters:

try=# ALTER ROLE CURRENT_USER RESET session_preload_libraries;
ALTER ROLE

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

Preloading

If every or nearly every Postgres connection needs to use pg_clickhouse, consider using shared library preloading to automatically load it:

session_preload_libraries

Loads the shared library for every new connection to PostgreSQL:

session_preload_libraries = pg_clickhouse

Useful to take advantage of updates without restarting the server: just reconnect. May also be set for specific users or roles via ALTER ROLE.

shared_preload_libraries

Loads the shared library into the PostgreSQL parent process at startup time:

shared_preload_libraries = pg_clickhouse

Useful to save memory and load overhead for every session, but requires the cluster to be restart when the library is updated.

Data Types

This table presents the preferred mapping of ClickHouse to PostgreSQL data types. IMPORT FOREIGN SCHEMA uses these mappings and derives the appropriate type modifiers and array dimensions. Use CREATE FOREIGN TABLE to declare alternate PostgreSQL types.

Additional read targets list conversions supported by binary driver beyond PostgreSQL explicit casts. Empty cells still allow those casts. Values must fit target types. These targets describe reads; writes follow separate conversion rules.

ClickHouse	Default PostgreSQL	Additional read targets	Notes
Array(T)	T[]		One PG array type per depth
BFloat16	real
Bool	boolean
Date	date
Date32	date
DateTime	timestamp with time zone	time
DateTime64(P)	timestamp(P) with time zone	time	P over 6 caps at 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; input-compatible types	Decodes label; PG enums with matching labels qualify
Enum16	text	bytea; input-compatible types	Decodes label; PG enums with matching labels qualify
FixedString(N)	text	bytea; input-compatible types	Only bytea keeps trailing NULs
Float32	real
Float64	double precision
IPv4	inet
IPv6	inet
Int8	smallint	boolean	Zero is false; nonzero is true
Int16	smallint	boolean	Zero is false; nonzero is true
Int32	integer
Int64	bigint
Int128	numeric(39,0)	xid8, oid8
Int256	numeric(77,0)	xid8, oid8
IntervalDay	interval	smallint, integer, bigint	Integers receive unit counts
IntervalHour	interval	smallint, integer, bigint	Integers receive unit counts
IntervalMicrosecond	interval	smallint, integer, bigint	Integers receive unit counts
IntervalMillisecond	interval	smallint, integer, bigint	Integers receive unit counts
IntervalMinute	interval	smallint, integer, bigint	Integers receive unit counts
IntervalMonth	interval	smallint, integer, bigint	Integers receive unit counts
IntervalNanosecond	interval	smallint, integer, bigint	Integers keep ns; interval truncates to us
IntervalQuarter	interval	smallint, integer, bigint	Integers receive unit counts
IntervalSecond	interval	smallint, integer, bigint	Integers receive unit counts
IntervalWeek	interval	smallint, integer, bigint	Integers receive unit counts
IntervalYear	interval	smallint, integer, bigint	Integers receive unit counts
JSON	jsonb	json, text, bytea; input-compatible types	jsonb normalizes document
LineString	path	lseg	lseg requires two points
LowCardinality(T)	T
Map(K,V)	text[][]	composite[], T[][], text	One row of text items per pair
MultiLineString	path[]
MultiPolygon	polygon[][]
Nested(…)	text[][]	composite[], T[][], text	One row of text items per nested row
Nullable(T)	T		Column imports without NOT NULL
Point	point
Polygon	polygon[]
Ring	polygon	lseg	lseg requires two points
SimpleAggregateFunction(f,T)	T		Stores values as T
String	text	bytea; input-compatible types	bytea keeps raw bytes
Time	time without time zone
Time64(P)	time(P) without time zone		P over 6 caps at 6
Tuple(…)	text[]	composite, T[], text; box, circle, line	Fields become text items
UInt8	smallint	boolean	Zero is false; nonzero is 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

Any column also reads into text, varchar, or another string type. The value takes the PostgreSQL type above, then renders through that type’s output function. A ClickHouse string read into a text type is validated against the database encoding, so bytes PostgreSQL cannot read raise an error. Declare a String, FixedString, Enum, or JSON column BYTEA to read its bytes as ClickHouse wrote them.

Input-compatible types can read strings using their PostgreSQL input function. Composite types must have matching fields in matching order. To read a tuple as an array, each field must convert to the array’s element type, and no field can itself be an array.

When read as arrays, Map uses one row per key-value pair and Nested uses one row per nested record. To read a tuple as box, provide two points; for circle, provide a point and radius; for line, provide three coefficients.

Additional notes and details follow.

Type Coercion

With the binary driver, alternate scalar types use PostgreSQL’s explicit casts where available, and array elements are converted to the declared element type. Unsupported conversions and out-of-range values raise errors.

With either driver, map ClickHouse Interval types to smallint, integer, or bigint to read and write counts of their units. For example, an IntervalDay value of 3 maps to the integer 3. Mapping IntervalNanosecond to bigint preserves nanoseconds, while mapping to interval truncates to microseconds.

BYTEA

ClickHouse does not provide the equivalent of the PostgreSQL BYTEA type, but allows any bytes to be stored in [String] type. In general ClickHouse strings should be mapped to the PostgreSQL [TEXT], but when using binary data, map it to BYTEA. Example:

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

That final SELECT query will output:

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

A foreign table using [TEXT] columns generally cannot read these values into text, because pg_clickhouse validates ClickHouse bytes against the database encoding:

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

Will output:

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

A digest holds arbitrary bytes, which seldom form valid text. PostgreSQL also reserves nul, so a [TEXT] column never reads a ClickHouse string holding one.

Attempting to insert binary values into [TEXT] columns will succeed and work as expected:

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

The text columns will be correct:

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

But reading them as BYTEA will not:

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

A FixedString(N) column pads short values with nul bytes. A text column drops that trailing padding, where a BYTEA column keeps every byte.

As a rule, only use [TEXT] columns for encoded strings and use BYTEA columns only for binary data, and never switch between them.

Composite Types

Array

ClickHouse [Array]s map directly to PostgreSQL arrays, with equivalent semantics. The main differences is that Postgres multidimensional arrays must have array expressions with matching dimensions. An attempt to read a ClickHouse array with different dimensions, such as [[1], [2,3]], triggers an error.

Array index access pushes down as appropriate, including multidimensional index access. Examples:

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

Array slices also push down using the [arraySlice] function, although multidimensional slice access is not yet supported:

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 provides no type corresponding to the ClickHouse [Map] type. pg_clickhouse therefore maps [Map] columns to text[][], with each key-value pair as its own array. IMPORT FOREIGN SCHEMA uses this mapping. One can INSERT maps as arrays, as well. An example:

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

Each nested array must contain exactly two text values. Values incompatible with corresponding ClickHouse key or value types trigger an error.

Inserting a Map requires the binary driver, which derives column types from the ClickHouse sever. The http driver does not, so lacks the information to format and insert the appropriate value.

Tuple

Similarly, a ClickHouse [Tuple] columns map to text[] and supports INSERTs via the binary driver. IMPORT FOREIGN SCHEMA emits a NOTICE when it makes such a mapping.

Nested

By default, ClickHouse splits a Nested column into one Array column per field (flatten_nested=1). IMPORT FOREIGN SCHEMA reads these from ClickHouse’s system.columns catalog and creates separate PostgreSQL array columns, preserving dotted names such as items.a and items.b. For example, given this foreign table:

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

It’s imported schema has three columns rather than two:

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

Query the nested columns by double-quoting the column names, e.g.,

SELECT ci, "c2.id" FROM nests;

And filter values in a WHERE clause using the usual array features, including array subscript syntax:

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

A [Nested] column created with flatten_nested=0 maps to an array with one item per nested row. Each array item contains that row’s values:

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

Now the resulting schema is:

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

Compose nested records in a two-dimensional array with text values formatted for each type defined by the ClickHouse Column. For c2 Nested(id Int64, name String) in this example, it would be:

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

Pushdown of text[][] columns over Nested types fails, however:

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

This is because pg_clickhouse cannot tell a text array column over a ClickHouse array column from one over a Nested column, so cannot rewrite it in ClickHouse’s Nested syntax.

However, an array of a matching composite type can also map these items (see Manual Type Mappings for details), in which case pushdown works as long as the composite field names are identical to the Nested field names:

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

Manual Type Mappings

IMPORT FOREIGN SCHEMA uses general-purpose PostgreSQL types. For example, given a ClickHouse table using [Enum], [Tuple], Map, and unflattened [Nested] (flatten_nested = 0) columns, such as:

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

To retain structure or constrain values, define PostgreSQL composite or enum types and create a foreign table manually:

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;

Always make the enum labels and composite type field names identical to the ClickHouse enum labels and Nested or Tuple field names to ensure that pushdown specifies the proper names.

Match composite field order and types to ClickHouse declarations. [Map] keys and values become first and second fields, respectively (key and value in this case). Nested and Map become arrays of composites, while Tuple becomes one composite value:

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)

Of course you can use the composite type field names, too, both in a SELECT list:

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

And, for [Nested] types in a WHERE clause --- as long as the field names are identical:

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

INSERT using such composites is not yet supported.

Function and operator reference

Functions

These functions provide the interface to query a ClickHouse database.

clickhouse_server_version

SELECT clickhouse_server_version('taxi_srv');

Report the ClickHouse server version, as major.minor.patch, for the named foreign server, connecting if necessary using the server’s options and the current user’s user mapping:

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

Reads the version from the native-protocol connection handshake, or over HTTP from a single SELECT version() query, and caches it for the life of the connection.

clickhouse_query

SELECT * FROM clickhouse_query(
    'server',
    'SELECT id, name, salary FROM remote_table WHERE salary > 50000'
) AS ch(id int, name text, salary numeric);

Execute a query against an already-configured foreign server and return its rows as a relation, mapping each ClickHouse result column to the PostgreSQL type named in the column definition list. It reuses the server’s driver, credentials, database, and the connection cache.

The first argument is the name of a server created with CREATE SERVER. A column definition list (AS name(col type, ...)) is required: PostgreSQL needs the result shape before fetching rows, and it must match the columns the query returns. Values are converted from ClickHouse to the declared types the same way a foreign table column would be. Statements that return no results, such as DDL, have nothing to declare; run them with clickhouse_perform instead.

No role has EXECUTE access by default; GRANT to a role to allow it to use the function.

GRANT EXECUTE ON FUNCTION clickhouse_query(text, text) TO ch_admin;

clickhouse_perform

CALL clickhouse_perform(
    'server',
    'CREATE TABLE remote_table (id Int32) ENGINE = MergeTree ORDER BY id'
);

Execute a statement against an already-configured foreign server and discard any result. Use it for statements that return no rows, such as DDL, where clickhouse_query has no result shape to declare. It resolves the server the same way clickhouse_query does, reusing its driver, credentials, database, and the connection cache.

As a procedure it must be invoked with CALL, not SELECT, and it returns no rows. No role has EXECUTE access by default; GRANT to a role to allow it to use the procedure.

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

Pushdown functions

pg_clickhouse pushes down a subset of the PostgreSQL builtin functions used in conditionals (HAVING and WHERE clauses). That subset maps to ClickHouse equivalents as follows:

• abs: abs
• factorial: factorial
• mod (int2/int4/int8/numeric): modulo
• pow & power (float8, not numeric): pow
• round: round
• sin, cos, tan, atan, atan2, sinh, cosh, tanh, asinh, degrees, radians, pi: ClickHouse math functions of the same name. asin, acos, atanh, acosh are not pushed down: PG raises on out-of-range input where CH returns 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): same mappings as date_part
• date(timestamp) & date(timestamptz): toDate (deparsed as CH alias date)
• array_position: indexOf with nullIf to convert 0 to NULL and arraySlice when there’s a third argument for the search starting index; note that nan currently does not match
• array_cat: arrayConcat
• array_append: arrayPushBack
• array_prepend: arrayPushFront
• array_remove: arrayRemove
• cardinality: arrayFlattenedLength on ClickHouse 26.9+, otherwise length, which counts only the outer array
• array_length(array, 1): nullIf(length(array), 0)
• array_length & cardinality: length
• array_to_string: arrayStringConcat
• string_to_array: splitByString
• split_part: splitByString + array subscript
• 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 if the regular expression contains parenthesized subexpressions; otherwise extractAll sliced with arraySlice.
• regexp_replace: replaceRegexpOne or replaceRegexpOne when the g flag is present
• regexp_split_to_array: splitByRegexp
• md5: MD5
• sha224, sha256, sha384, and sha512: Corresponding ClickHouse SHA functions
• encode(bytea, fmt) when fmt is a string constant (case-insensitive):
  • encode(bytea, 'hex'): hex wrapped in lower, since PostgreSQL emits lowercase hex.
  • encode(bytea, 'base64'): base64Encode wrapped in replaceRegexpAll to reproduce PostgreSQL’s MIME (RFC 2045) line break every 76 characters.
  • encode(bytea, 'base64url') (PostgreSQL 19+): base64URLEncode, which matches PostgreSQL’s RFC 4648 URL alphabet without padding.
• json_extract_path_text: sub-column syntax
• json_extract_path: toJSONString + sub-column syntax
• jsonb_extract_path_text: sub-column syntax
• jsonb_extract_path: toJSONString + sub-column syntax
• bit_count(bytea): bitCount
• to_timestamp(float8): toDateTime64
• to_char(timestamp[tz], fmt): formatDateTime when fmt is a string constant whose every keyword has a faithful ClickHouse equivalent. See to_char() under Compatibility Notes for the supported keywords. Otherwise the function evaluates locally in PostgreSQL.
• statement_timestamp, transaction_timestamp, & clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))
• CURRENT_DATE: now and 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: Passed as value from PostgreSQL function.
• CURRENT_SCHEMA: Passed as value from PostgreSQL function.
• CURRENT_CATALOG: Passed as value from PostgreSQL function.
• CURRENT_USER: Passed as value from PostgreSQL function.
• USER: Passed as value from PostgreSQL function.
• CURRENT_ROLE: Passed as value from PostgreSQL function.
• SESSION_USER: Passed as value from PostgreSQL function.

Pushdown operators

• Array slice (arr[L:U]): arraySlice
• @> (array contains): hasAll
• <@ (array contained by): hasAll
• && (array overlap): hasAny
• ~ (regexp match): match
• !~ (regexp not match): match
• ~* (case insensitive regexp no match): match
• !~* (case insensitive regexp not match): match
• ->> (JSON/JSONB extract element as text): sub-column syntax
• -> (JSON/JSONB extract): toJSONString + sub-column syntax

IN and NULL semantics

ClickHouse evaluates IN under two-valued logic: when the probe finds no match it returns 0 even if a NULL is involved, where PostgreSQL computes NULL. To preserve PostgreSQL semantics, pg_clickhouse pushes down the IN family over a constant list or array (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL) unconditionally: the native or cheap form where it can prove neither the probe nor an array element can be NULL, or a guarded CASE form otherwise that checks for NULL values at runtime instead, computing PostgreSQL’s exact three-valued answer (TRUE, FALSE, NULL) in every context, including value positions like a SELECT list or GROUP BY.

A NOT IN (SELECT ...) filter over nullable columns also pushes down, deparsed with compensating guards that keep PostgreSQL’s behavior: a set containing a NULL disqualifies every row, and a NULL probe passes only against an empty set. Each guard is omitted when a NOT NULL declaration proves it unnecessary. Unlike the array forms above, this guard only applies in a plain filter condition (or under NOT); we still do not push down IN (SELECT ...) (in a value position) nor grouped/aggregated subquery bodies. Declaring columns NOT NULL maximizes pushdown by letting the cheaper unguarded form ship instead; IMPORT FOREIGN SCHEMA does so automatically for non-Nullable ClickHouse columns. The proof follows non-NULL constants, NOT NULL columns, and basic arithmetic (+, -, *, unary -) over them.

These rules assume ClickHouse’s default transform_null_in = 0, which pg_clickhouse sets on every query through the default value of the pg_clickhouse.session_settings parameter so that a ClickHouse server profile cannot silently change it. Setting transform_null_in = 1 breaks the semantics of every pushed IN.

Custom functions

These custom functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse functions with no PostgreSQL equivalents. If any of these functions can’t be pushed down they will raise an exception.

• dictGet

Extension pushdown

pg_clickhouse recognizes functions from select core and third-party extensions, pushing them down to their ClickHouse equivalents.

re2

All [re2 extension] operators and functions push down 1:1 to 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

One [intarray] function pushes down to ClickHouse:

• idx → indexOf

fuzzystrmatch

Two [fuzzystrmatch] functions push down to ClickHouse:

• soundex: soundex
• levenshtein (2-arg): editDistanceUTF8

pgcyrpto

• digest(text, text) and digest(bytea, text): Corresponding ClickHouse hash function when the algorithm is a constant md5, sha1, sha224, sha256, sha384, or sha512 (matched case-insensitively).

Pushdown casts

pg_clickhouse pushes down casts such as CAST(x AS bigint) for compatible data types. For incompatible types the pushdown will fail; if x in this example is a ClickHouse UInt64, ClickHouse will refuse to cast the value.

In order to push down casts to incompatible data types, pg_clickhouse provides the following functions. They raise an exception in PostgreSQL if they’re not pushed down.

• toUInt8
• toUInt16
• toUInt32
• toUInt64
• toUInt128

Pushdown aggregates

These PostgreSQL aggregate functions pushdown to 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

Custom aggregates

These custom aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse aggregate functions with no PostgreSQL equivalents. If any of these functions can’t be pushed down they will raise an exception.

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

Pushdown ordered set aggregates

These ordered-set aggregate functions map to ClickHouse parametric aggregate functions by passing their direct argument as a parameter and their ORDER BY expressions as arguments. For example, this PostgreSQL query:

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

Maps to this ClickHouse query:

SELECT quantile(0.25)(a) FROM t1;

Note that the non-default ORDER BY suffixes DESC and NULLS FIRST aren’t supported and will raise an error.

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

Custom Ordered Set Aggregates

These custom ordered-set aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse parametric aggregate functions. If any of these functions cannot be pushed down they will raise an exception.

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

Custom Ordered Set Aggregates

These custom ordered-set aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse parametric aggregate functions. If any of these functions cannot be pushed down they will raise an exception.

• quantile
• quantileExact

Pushdown window functions

These PostgreSQL [window functions] push down to ClickHouse with OVER (PARTITION BY ... ORDER BY ...) clauses, including frame specifications where applicable.

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

Ranking functions (row_number, rank, dense_rank, ntile, cume_dist, percent_rank) omit their frame clause during pushdown because ClickHouse rejects frame specifications on these functions.

Compatibility notes

Regular expressions

While pg_clickhouse pushes down regular expressions to ClickHouse equivalents when pg_clickhouse.pushdown_regex is true (the default), and makes an effort to ensure a basic level of compatibility, be aware of the differences between the two and how pg_clickhouse handles them.

• PostgreSQL supports [POSIX Regular Expressions] while ClickHouse supports [RE2 Regular Expressions][RE2]. Beware of differences in behavior: write RE2 when the regular expression will be evaluated by ClickHouse (e.g., in a WHERE clause) and POSIX when it will be evaluated by Postgres (e.g., in a SELECT clause).

• pg_clickhouse pushes down the [Postgres flags] by prepending them to ClickHouse regular expression inside (?). For example:

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

  Becomes

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

• The only flags both support, and therefore can be used when evaluated by ClickHouse, are:

  Flag	As	Notes
  i	i	case-insensitive matching
  m	m-s	^ and $ match begin/end line in addition to begin/end text
  n	m-s	Postgres alias for m
  p	-s	don’t let . and [^x] match \n
  s	s	let . and [^x] match \n
  t		tight syntax, ignored
  w	m	inverse partial newline-sensitive matching

  RE2 supports only these flags; don’t use any other [Postgres flags].

• This table summarizes the affects of the various flags (and no flag, which is the same as s) when matching newlines and line endings. Note that in Postgres, m and p prevent negated character classes ([^xyz]) from matching a newline, while the ClickHouse equivalents do not. Otherwise, the behaviors are the same in ClickHouse as in Postgres:

  Pattern applied to a\nb	Postgres	ClickHouse	Same?
  a.b	true	true	✔︎
  a[^x]b	true	true	✔︎
  a$	false	false	✔︎
  s Flag
  (?s)a.b	true	true	✔︎
  (?s)a[^x]b	true	true	✔︎
  (?s)a$	false	false	✔︎
  m Flag
  (?m)a.b	false	false	✔︎
  (?m)a[^x]b	true	false	✘
  (?m)a$	true	true	✔︎
  p Flag
  (?p)a.b	false	false	✔︎
  (?p)a[^x]b	true	false	✘
  (?p)a$	false	false	✔︎
  w Flag
  (?w)a.b	true	true	✔
  (?w)a[^x]b	true	true	✔
  (?w)a$	true	true	✔

• Any other flags passed to regular expression functions will prevent pushdown of the function.

• The exception is regexp_replace(), which also supports the g flag. When g is set, pg_clickhouse uses replaceRegexpAll() instead of replaceRegexpOne() and removes the flag before prepending other flags.

• The replacement argument to Postgres regexp_replace() supports \& to refer to the entire match, while in ClickHouse supports \0 for the entire match. Be sure to use \0 when the function pushes down to ClickHouse.

• Postgres regexp_match returns NULL when there are no matches, while the expressions it pushes down to return an empty array. Use COALESCE() to return an empty array instead of NULL to compare return values compatibly. For example:

  SELECT * FROM events WHERE COALESCE(regexp_match(msg, '^ERR'), '{}');
  

To avoid all ambiguity, consider setting pg_clickhouse.pushdown_regex to prevent Postgres regular expression from pushing down to ClickHouse, and using the [re2 extension], for which pg_clickhouse supports direct pushdown of ClickHouse-compatible [RE2] regular expressions.

to_char()

PostgreSQL [to_char()] for timestamp and timestamp with time zone pushes down to ClickHouse [formatDateTime] only when the format argument is a non-NULL string constant whose every PostgreSQL keyword has a byte-for-byte identical ClickHouse equivalent. If the format is dynamic or contains any unsupported keyword or modifier, the call falls back to local evaluation in PostgreSQL. pg_clickhouse never pushes down a partial translation, so output remains compatible.

Two-argument to_char() forms over numeric, interval, and other non-timestamp types never push down; ClickHouse [formatDateTime] only formats date-time values.

Translated keywords

PostgreSQL	ClickHouse	Meaning
YYYY, yyyy	%Y	4-digit year
YY, yy	%y	2-digit year
MM, mm	%m	zero-padded month (01–12)
DD, dd	%d	zero-padded day of month (01–31)
DDD, ddd	%j	zero-padded day of year (001–366)
HH24, hh24	%H	zero-padded 24-hour (00–23)
HH, hh, HH12, hh12	%I	zero-padded 12-hour (01–12)
MI, mi	%i	zero-padded minute (00–59)
SS, ss	%S	zero-padded second (00–59)
Q, q	%Q	quarter (1–4)
Mon	%b	abbreviated month name, e.g., Oct
Dy	%a	abbreviated weekday name, e.g., Mon
AM, PM	%p	meridiem indicator, always uppercase

Quoted text and literals

Text wrapped in "..." passes through verbatim, with any literal % doubled to %% to escape ClickHouse’s specifier prefix. A \" outside quotes also passes through as a literal ". Inside "...", backslash only escapes "; other backslash sequences are treated as literal text.

Authors

David E. Wheeler

Copyright

Copyright (c) 2025-2026, ClickHouse

[Array] https://clickhouse.com/docs/reference/data-types/array “ClickHouse Docs: Array(T)” [Map]: https://clickhouse.com/docs/sql-reference/data-types/map “ClickHouse Docs: Map” [Tuple]: https://clickhouse.com/docs/reference/data-types/tuple “ClickHouse Docs: Tuple(T1, T2, …)” [Nested]: https://clickhouse.com/docs/sql-reference/data-types/nested-data-structures/nested “ClickHouse Docs: Nested” [Enum]: https://clickhouse.com/docs/reference/data-types/enum “ClickHouse Docs: Enum” [String]: /reference/data-types/string “ClickHouse Docs: String” [TEXT]: https://www.postgresql.org/docs/current/datatype-character.html “PostgreSQL Docs: Character Types” [window functions]: https://www.postgresql.org/docs/current/functions-window.html “PostgreSQL Docs: Window Functions” [POSIX Regular Expressions]: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP “PostgreSQL Docs: POSIX Regular Expressions” [Postgres flags]: https://www.postgresql.org/docs/current/functions-matching.html#POSIX-EMBEDDED-OPTIONS-TABLE “PostgreSQL Docs: ARE Embedded-Option Letters” [RE2]: https://github.com/google/re2/wiki/Syntax “RE2 Syntax” [re2 extension]: https://github.com/ClickHouse/pg_re2 “pg_re2: ClickHouse-compatible regex functions using RE2” [intarray]: https://www.postgresql.org/docs/current/intarray.html “PostgreSQL Docs: intarray” [fuzzystrmatch]: https://www.postgresql.org/docs/current/fuzzystrmatch.html “PostgreSQL Docs: fuzzystrmatch” [to_char()]: https://www.postgresql.org/docs/current/functions-formatting.html “PostgreSQL Docs: Data Type Formatting Functions” [formatDateTime]: /reference/functions/regular-functions/date-time-functions#formatDateTime “ClickHouse Docs: formatDateTime”