pg_clickhouse Reference Documentation

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

### ALTER ROLE[​](#alter-role "Direct link to ALTER ROLE")

Use [ALTER ROLE](https://www.postgresql.org/docs/current/sql-alterrole.html "PostgreSQL Docs: ALTER ROLE")'s `SET` command to [preload](#preloading) pg\_clickhouse
and/or [SET](#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](https://www.postgresql.org/docs/current/sql-alterrole.html "PostgreSQL Docs: 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[​](#preloading "Direct link to 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`[​](#session_preload_libraries "Direct link to 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](#alter-role).

### `shared_preload_libraries`[​](#shared_preload_libraries "Direct link to 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[​](#data-types "Direct link to Data Types")

pg\_clickhouse maps the following ClickHouse data types to PostgreSQL data
types. [IMPORT FOREIGN SCHEMA](#import-foreign-schema) use the first type in
the PostgreSQL column when importing columns; additional types may be used in
[CREATE FOREIGN TABLE](#create-foreign-table) statements: