pg_clickhouse Reference Documentation

```
SELECT clickhouse_raw_query(
    'SELECT schema_name, schema_owner from information_schema.schemata',
    'host=localhost port=8123'
);
```

```
      clickhouse_raw_query
---------------------------------
 INFORMATION_SCHEMA      default+
 default default                +
 git     default                +
 information_schema      default+
 system  default                +

(1 row)
```

### Pushdown Functions[​](#pushdown-functions "Direct link to Pushdown Functions")

All PostgreSQL builtin functions used in conditionals (`HAVING` and `WHERE`
clauses) to query ClickHouse foreign tables automatically push down to
ClickHouse with the same names and signatures. However, some have different
names or signatures and must be mapped to their equivalents. `pg_clickhouse`
maps the following functions:

- `date_part`:
  - `date_part('day')`: [toDayOfMonth](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toDayOfMonth)
  - `date_part('doy')`: [toDayOfYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toDayOfYear)
  - `date_part('dow')`: [toDayOfWeek](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toDayOfWeek)
  - `date_part('year')`: [toYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toYear)
  - `date_part('month')`: [toMonth](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toMonth)
  - `date_part('hour')`: [toHour](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toHour)
  - `date_part('minute')`: [toMinute](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toMinute)
  - `date_part('second')`: [toSecond](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toSecond)
  - `date_part('quarter')`: [toQuarter](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toQuarter)
  - `date_part('isoyear')`: [toISOYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toISOYear)
  - `date_part('week')`: [toISOYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toISOWeek)
  - `date_part('epoch')`: [toISOYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toUnixTimestamp)
- `date_trunc`:
  - `date_trunc('week')`: [toMonday](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toMonday)
  - `date_trunc('second')`: [toStartOfSecond](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfSecond)
  - `date_trunc('minute')`: [toStartOfMinute](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfMinute)
  - `date_trunc('hour')`: [toStartOfHour](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfHour)
  - `date_trunc('day')`: [toStartOfDay](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfDay)
  - `date_trunc('month')`: [toStartOfMonth](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfMonth)
  - `date_trunc('quarter')`: [toStartOfQuarter](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfQuarter)
  - `date_trunc('year')`: [toStartOfYear](https://clickhouse.com/docs/sql-reference/functions/date-time-functions#toStartOfYear)
- `array_position`: [indexOf](https://clickhouse.com/docs/sql-reference/functions/array-functions#indexOf)
- `btrim`: [trimBoth](https://clickhouse.com/docs/sql-reference/functions/string-functions#trimboth)
- `strpos`: [position](https://clickhouse.com/docs/sql-reference/functions/string-search-functions#position)
- `regexp_like`: [match](https://clickhouse.com/docs/sql-reference/functions/string-search-functions#match)
- `md5`: [MD5](https://clickhouse.com/docs/sql-reference/functions/hash-functions#MD5)

### Custom Functions[​](#custom-functions "Direct link to 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](https://clickhouse.com/docs/sql-reference/functions/ext-dict-functions#dictget-dictgetordefault-dictgetornull)

### Pushdown Casts[​](#pushdown-casts "Direct link to 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](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#touint8)
- [toUInt16](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#touint16)
- [toUInt32](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#touint32)
- [toUInt64](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#touint64)
- [toUInt128](https://clickhouse.com/docs/sql-reference/functions/type-conversion-functions#touint128)

### Pushdown Aggregates[​](#pushdown-aggregates "Direct link to Pushdown Aggregates")

These PostgreSQL aggregate functions pushdown to ClickHouse.

- [array\_agg](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/grouparray)
- [avg](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/avg)
- [count](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/count)
- [min](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/min)
- [max](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/max)

### Custom Aggregates[​](#custom-aggregates "Direct link to 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](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmax)
- [argMin](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/argmin)
- [uniq](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniq)
- [uniqCombined](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqcombined)
- [uniqCombined64](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqcombined64)
- [uniqExact](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqexact)
- [uniqHLL12](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqhll12)
- [uniqTheta](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/uniqthetasketch)
- [quantile](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantile)
- [quantileExact](https://clickhouse.com/docs/sql-reference/aggregate-functions/reference/quantileexact)

### Pushdown Ordered Set Aggregates[​](#pushdown-ordered-set-aggregates "Direct link to Pushdown Ordered Set Aggregates")

These [ordered-set aggregate functions](https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE) map to ClickHouse [Parametric
aggregate functions](https://clickhouse.com/docs/sql-reference/aggregate-functions/parametric-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.