pg_clickhouse Reference Documentation

Additional notes and details follow.

### BYTEA[​](#bytea "Direct link to BYTEA")

ClickHouse does not provide the equivalent of the PostgreSQL [BYTEA](https://www.postgresql.org/docs/current/datatype-binary.html "PostgreSQL Docs: Binary Data Types") type, but
allows any bytes to be stored in [String](https://clickhouse.com/docs/sql-reference/data-types/string "ClickHouse Docs: String") type. In general ClickHouse strings
should be mapped to the PostgreSQL [TEXT](https://www.postgresql.org/docs/current/datatype-character.html "PostgreSQL Docs: Character Types"), but when using binary data, map it
to [BYTEA](https://www.postgresql.org/docs/current/datatype-binary.html "PostgreSQL Docs: Binary Data Types"). Example:

```
-- Create clickHouse table with String columns.
SELECT clickhouse_raw_query($$
    CREATE TABLE bytes (
        c1 Int8, c2 String, c3 String
    ) ENGINE = MergeTree ORDER BY (c1);
$$);

-- Create foreign table with BYTEA columns.
CREATE FOREIGN TABLE bytes (
    c1 int,
    c2 BYTEA,
    c3 BYTEA
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Insert binary data into the foreign table.
INSERT INTO bytes
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the results.
SELECT * FROM bytes;
```

That final `SELECT` query will output:

```
 c1 |                             c2                             |                 c3
----+------------------------------------------------------------+------------------------------------
  1 | \x1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | \xae3b28cde02542f81acce8783245430d
  2 | \x5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | \x23e7c6cacb8383f878ad093b0027d72b
  3 | \x53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | \x7e969132fc656148b97b6a2ee8bc83c1
  4 | \x4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | \x8ef30f44c65480d12b650ab6b2b04245
(4 rows)
```

Note that if there are any nul bytes in the ClickHouse columns, a foreign
table using [TEXT](https://www.postgresql.org/docs/current/datatype-character.html "PostgreSQL Docs: Character Types") columns will not output the proper values:

```
-- Create foreign table with TEXT columns.
CREATE FOREIGN TABLE texts (
    c1 int,
    c2 TEXT,
    c3 TEXT
) SERVER ch_srv OPTIONS( table_name 'bytes' );

-- Encode binary data as hex.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;
```

Will output:

```
 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b
  3 | 53ac2c1fa83c8f64603fe9568d883331                         | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)
```

Note that rows two and three contain truncated values. This is because
PostgreSQL relies on nul-terminated strings and does not support nuls in its
strings.

Attempting to insert binary values into [TEXT](https://www.postgresql.org/docs/current/datatype-character.html "PostgreSQL Docs: Character Types") columns will succeed and work
as expected:

```
-- Insert via text columns:
TRUNCATE texts;
INSERT INTO texts
SELECT n, sha224(bytea('val'||n)), decode(md5('int'||n), 'hex')
  FROM generate_series(1, 4) n;

-- View the data.
SELECT c1, encode(c2::bytea, 'hex'), encode(c3::bytea, 'hex') FROM texts ORDER BY c1;
```

The text columns will be correct:

```
 c1 |                          encode                          |              encode
----+----------------------------------------------------------+----------------------------------
  1 | 1bf7f0cc821d31178616a55a8e0c52677735397cdde6f4153a9fd3d7 | ae3b28cde02542f81acce8783245430d
  2 | 5f6e9e12cd8592712e638016f4b1a2e73230ee40db498c0f0b1dc841 | 23e7c6cacb8383f878ad093b0027d72b
  3 | 53ac2c1fa83c8f64603fe9568d883331007d6281de330a4b5e728f9e | 7e969132fc656148b97b6a2ee8bc83c1
  4 | 4e3c2e4cb7542a45173a8dac939ddc4bc75202e342ebc769b0f5da2f | 8ef30f44c65480d12b650ab6b2b04245
(4 rows)
```

But reading them as [BYTEA](https://www.postgresql.org/docs/current/datatype-binary.html "PostgreSQL Docs: Binary Data Types") will not:

```
# SELECT * FROM bytes;
 c1 |                                                           c2                                                           |                                   c3
----+------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------
  1 | \x5c783162663766306363383231643331313738363136613535613865306335323637373733353339376364646536663431353361396664336437 | \x5c786165336232386364653032353432663831616363653837383332343534333064
  2 | \x5c783566366539653132636438353932373132653633383031366634623161326537333233306565343064623439386330663062316463383431 | \x5c783233653763366361636238333833663837386164303933623030323764373262
  3 | \x5c783533616332633166613833633866363436303366653935363864383833333331303037643632383164653333306134623565373238663965 | \x5c783765393639313332666336353631343862393762366132656538626338336331
  4 | \x5c783465336332653463623735343261343531373361386461633933396464633462633735323032653334326562633736396230663564613266 | \x5c783865663330663434633635343830643132623635306162366232623034323435
(4 rows)
```

Tip

As a rule, only use [TEXT](https://www.postgresql.org/docs/current/datatype-character.html "PostgreSQL Docs: Character Types") columns for encoded strings and use [BYTEA](https://www.postgresql.org/docs/current/datatype-binary.html "PostgreSQL Docs: Binary Data Types") columns
only for binary data, and never switch between them.

## Function and Operator Reference[​](#function-and-operator-reference "Direct link to Function and Operator Reference")

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

These functions provide the interface to query a ClickHouse database.

#### `clickhouse_raw_query`[​](#clickhouse_raw_query "Direct link to clickhouse_raw_query")

```
SELECT clickhouse_raw_query(
    'CREATE TABLE t1 (x String) ENGINE = Memory',
    'host=localhost port=8123'
);
```

Connect to a ClickHouse service via its HTTP interface, execute a single
query, and disconnect. The optional second argument specifies a connection
string that defaults to `host=localhost port=8123`. The supported connection
parameters are:

- `host`: The host to connect to; required.
- `port`: The HTTP port to connect to; defaults to `8123` unless `host` is a
  ClickHouse Cloud host, in which case it defaults to `8443`
- `dbname`: The name of the database to connect to.
- `username`: The username to connect as; defaults to `default`
- `password`: The password to use to authenticate; defaults to no password

Useful for queries that return no records, but queries that do return values
will be returned as a single text value:

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