pg_clickhouse Reference Documentation

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/tools/data-integration/pg_clickhouse/reference.md)

## Description[​](#description "Direct link to Description")

pg\_clickhouse is a PostgreSQL extension that enables remote query execution
on ClickHouse databases, including a [foreign data wrapper](https://www.postgresql.org/docs/current/fdwhandler.html "PostgreSQL Docs: Writing a Foreign Data Wrapper"). It supports
PostgreSQL 13 and higher and ClickHouse 23 and higher.

## Getting Started[​](#getting-started "Direct link to Getting Started")

The simplest way to try pg\_clickhouse is the [Docker image](https://github.com/ClickHouse/pg_clickhouse/pkgs/container/pg_clickhouse "Latest version on Docker Hub"), which contains
the standard PostgreSQL Docker image with the pg\_clickhouse 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
```

See the [tutorial](/docs/tutorial) to get started importing ClickHouse tables and
pushing down queries.

## Usage[​](#usage "Direct link to 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[​](#versioning-policy "Direct link to Versioning Policy")

pg\_clickhouse adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html "Semantic Versioning 2.0.0") 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
  `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[​](#ddl-sql-reference "Direct link to DDL SQL Reference")

The following SQL [DDL](https://en.wikipedia.org/wiki/Data_definition_language "Wikipedia: Data definition language") expressions use pg\_clickhouse.

### CREATE EXTENSION[​](#create-extension "Direct link to CREATE EXTENSION")

Use [CREATE EXTENSION](https://www.postgresql.org/docs/current/sql-createextension.html "PostgreSQL Docs: 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[​](#alter-extension "Direct link to ALTER EXTENSION")

Use [ALTER EXTENSION](https://www.postgresql.org/docs/current/sql-alterextension.html "PostgreSQL Docs: 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[​](#drop-extension "Direct link to DROP EXTENSION")

Use [DROP EXTENSION](https://www.postgresql.org/docs/current/sql-dropextension.html "PostgreSQL Docs: 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[​](#create-server "Direct link to CREATE SERVER")

Use [CREATE SERVER](https://www.postgresql.org/docs/current/sql-createserver.html "PostgreSQL Docs: 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.**
- `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

### ALTER SERVER[​](#alter-server "Direct link to ALTER SERVER")

Use [ALTER SERVER](https://www.postgresql.org/docs/current/sql-alterserver.html "PostgreSQL Docs: 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](#create-server).

### DROP SERVER[​](#drop-server "Direct link to DROP SERVER")

Use [DROP SERVER](https://www.postgresql.org/docs/current/sql-dropserver.html "PostgreSQL Docs: 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[​](#create-user-mapping "Direct link to CREATE USER MAPPING")

Use [CREATE USER MAPPING](https://www.postgresql.org/docs/current/sql-createusermapping.html "PostgreSQL Docs: 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 The supported options are:

- `user`: The name of the ClickHouse user. Defaults to "default".
- `password`: The password of the ClickHouse user.

### ALTER USER MAPPING[​](#alter-user-mapping "Direct link to ALTER USER MAPPING")

Use [ALTER USER MAPPING](https://www.postgresql.org/docs/current/sql-alterusermapping.html "PostgreSQL Docs: 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](#create-user-mapping).

### DROP USER MAPPING[​](#drop-user-mapping "Direct link to DROP USER MAPPING")

Use [DROP USER MAPPING](https://www.postgresql.org/docs/current/sql-dropusermapping.html "PostgreSQL Docs: DROP USER MAPPING") to remove a user mapping:

```
DROP USER MAPPING FOR CURRENT_USER SERVER taxi_srv;
```

### IMPORT FOREIGN SCHEMA[​](#import-foreign-schema "Direct link to IMPORT FOREIGN SCHEMA")

Use [IMPORT FOREIGN SCHEMA](https://www.postgresql.org/docs/current/sql-importforeignschema.html "PostgreSQL Docs: 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: