pg_clickhouse Reference Documentation

```
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](https://www.postgresql.org/docs/current/sql-execute.html "PostgreSQL Docs: 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)
```

Note

Parameterized execution prevents the [http driver](#create-server) from
properly converting DateTime time zones on ClickHouse versions prior to 25.8,
when the [underlying bug](https://github.com/ClickHouse/ClickHouse/issues/85847 "ClickHouse/ClickHouse#85847 Some queries in a multipart forms don't read settings") was [fixed](https://github.com/ClickHouse/ClickHouse/pull/85570 "ClickHouse/ClickHouse#85570 fix HTTP with multipart"). Note that sometimes PostgreSQL will use
a parameterized query plan even without using `PREPARE`. For any queries on
that require accurate time zone conversion, and where upgrading to 25.8 or
later is not an option, use the [binary driver](#create-server), instead.

pg\_clickhouse pushes down the aggregations, as usual, as seen in the
[EXPLAIN](#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](https://www.postgresql.org/docs/current/sql-prepare.html#SQL-PREPARE-NOTES "PostgreSQL Docs: PREPARE notes"). On the sixth execution, it sends ClickHouse
`{param:type}`-style [query parameters](https://clickhouse.com/docs/guides/developer/stored-procedures-and-prepared-statements#alternatives-to-prepared-statements-in-clickhouse "ClickHouse Docs: Alternatives to prepared statements in ClickHouse"):
parameters:

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

Use [DEALLOCATE](https://www.postgresql.org/docs/current/sql-deallocate.html "PostgreSQL Docs: DEALLOCATE") to deallocate a prepared statement:

```
try=# DEALLOCATE avg_durations_between_dates;
DEALLOCATE
```

### INSERT[​](#insert "Direct link to INSERT")

Use the [INSERT](https://www.postgresql.org/docs/current/sql-insert.html "PostgreSQL Docs: 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[​](#copy "Direct link to COPY")

Use the [COPY](https://www.postgresql.org/docs/current/sql-copy.html "PostgreSQL Docs: 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](https://www.postgresql.org/docs/current/sql-copy.html "PostgreSQL Docs: COPY") currently uses [INSERT](#insert) statements to
> insert records. This will be improved in a future release.

### LOAD[​](#load "Direct link to LOAD")

Use [LOAD](https://www.postgresql.org/docs/current/sql-load.html "PostgreSQL Docs: LOAD") to load the pg\_clickhouse shared library:

```
try=# LOAD 'pg_clickhouse';
LOAD
```

It's not normally necessary to use [LOAD](https://www.postgresql.org/docs/current/sql-load.html "PostgreSQL Docs: LOAD"), as Postgres will automatically load
pg\_clickhouse the first time any of of its features (functions, foreign
tables, etc.) are used.

The one time it may be useful to [LOAD](https://www.postgresql.org/docs/current/sql-load.html "PostgreSQL Docs: LOAD") pg\_clickhouse is to [SET](#set)
pg\_clickhouse parameters before executing queries that depend on them.

### SET[​](#set "Direct link to SET")

Use [SET](https://www.postgresql.org/docs/current/sql-set.html "PostgreSQL Docs: SET") to set the the `pg_clickhouse.session_settings` runtime parameter.
This parameter configures [ClickHouse settings](https://clickhouse.com/docs/operations/settings/settings "ClickHouse Docs: Session 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`. Set it to an empty string to fall back on
the ClickHouse server's settings.

```
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](https://clickhouse.com/docs/operations/settings/settings "ClickHouse Docs: Session 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](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING "PostgreSQL Docs: Dollar-Quoted String Constants") 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