pg_clickhouse Reference Documentation

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:

```
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](#create-foreign-table) commands to create
the foreign tables. Columns will be defined using the [supported data
types](#data-types) and, were detectable, the options supported by [CREATE
FOREIGN TABLE](#create-foreign-table).

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 UInt64,
    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).

### CREATE FOREIGN TABLE[​](#create-foreign-table "Direct link to CREATE FOREIGN TABLE")

Use [CREATE FOREIGN TABLE](https://www.postgresql.org/docs/current/sql-createforeigntable.html "PostgreSQL Docs: CREATE FOREIGN TABLE") to create a foreign table that can query data from
a ClickHouse database:

```
CREATE FOREIGN TABLE uact (
    user_id    bigint NOT NULL,
    page_views int,
    duration   smallint,
    sign       smallint
) SERVER taxi_srv OPTIONS(
    table_name 'uact'
    engine 'CollapsingMergeTree'
);
```

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](https://clickhouse.com/docs/engines/table-engines "ClickHouse Docs: Table engines") 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](#data-types) appropriate for the remote ClickHouse data
type of each column. For [AggregateFunction Type](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction "ClickHouse Docs: AggregateFunction Type") and [SimpleAggregateFunction
Type](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction "ClickHouse Docs: SimpleAggregateFunction Type") columns, 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:

- `AggregateFunction`: The name of the aggregate function applied to an
  [AggregateFunction Type](https://clickhouse.com/docs/sql-reference/data-types/aggregatefunction "ClickHouse Docs: AggregateFunction Type") column
- `SimpleAggregateFunction`: The name of the aggregate function applied to
  an [SimpleAggregateFunction Type](https://clickhouse.com/docs/sql-reference/data-types/simpleaggregatefunction "ClickHouse Docs: SimpleAggregateFunction Type") column

Example:

(aggregatefunction 'sum')

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

For columns with the `AggregateFunction` function, pg\_clickhouse will
automatically append `Merge` to an aggregate function evaluating the column.

### ALTER FOREIGN TABLE[​](#alter-foreign-table "Direct link to ALTER FOREIGN TABLE")

Use [ALTER FOREIGN TABLE](https://www.postgresql.org/docs/current/sql-alterforeigntable.html "PostgreSQL Docs: 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](https://www.postgresql.org/docs/current/sql-createforeigntable.html "PostgreSQL Docs: CREATE FOREIGN TABLE").

### DROP FOREIGN TABLE[​](#drop-foreign-table "Direct link to DROP FOREIGN TABLE")

Use [DROP FOREIGN TABLE](https://www.postgresql.org/docs/current/sql-dropforeigntable.html "PostgreSQL Docs: DROP FOREIGN TABLE") to remove a foreign table:

```
DROP FOREIGN TABLE uact;
```

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 uact CASCADE;
```

## DML SQL Reference[​](#dml-sql-reference "Direct link to DML SQL Reference")

The SQL [DML](https://en.wikipedia.org/wiki/Data_manipulation_language "Wikipedia: Data manipulation language") 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[​](#explain "Direct link to EXPLAIN")

The [EXPLAIN](https://www.postgresql.org/docs/current/sql-explain.html "PostgreSQL Docs: 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[​](#select "Direct link to SELECT")

Use the [SELECT](https://www.postgresql.org/docs/current/sql-select.html "PostgreSQL Docs: 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/totam
(1 row)
```