pg_clickhouse & chdb updates: encoding, nesting, and types

\->Scroll to top

<-Back

Blog
/
Product

Copy pageCopied!More actions

View as Markdown Open this page in Markdown
Open in ChatGPT Ask questions about this page
Open in Claude Ask questions about this page
Open in v0 Ask questions about this page

pg\_clickhouse & chdb updates: encoding, nesting, and types

image 512x512 1

David Wheeler

Sep 30, 2026 · 11 minutes read

Out now on GitHub and PGXN, pg\_clickhouse v0.11.0 and the chdb extensionv0.1.2 continue our dogged focus on cross-database compatibility. A slew of these enhancements derive from our header-only C libraries, clickhouse-c and pg-clickhouse-c. Let's take a look at just three of the changes in these releases.

First up, character encoding. In the process of developing the benchmark for the chdb extension post, I discovered that pg-clickhouse-c wasn't validating character encodings on text columns. My colleague Philip quickly patched the library to raise an exception when any text- or json-based1type contains bytes that violate the database encoding.

This fix shipped in chdb v0.1.1, but we delayed pg\_clickhouse a bit to avoid errors for anyone with existing foreign tables that read invalidly-encoded data. pg\_clickhouse v0.11.0 adds a new foreign server option, `check_encoding`, that provides encoding error handlers. The options are:

`fail` (default): raise an error
`remove`: remove invalid bytes
`replace`: under the UTF-8 encoding, replace invalid bytes with the Unicode replacement character (`�`); same as `remove` for other encodings
`truncate`: truncate the text at the first invalid byte

chdb\_hook v0.1.2 provides the same option for its COPY and CREATE TABLEcommands. Both allow you to address errors resembling:

1ERROR:  invalid byte sequence for encoding "UTF8": 0x81

Copy command

Change the pg\_clickhouse server configuration `check_encoding` to eliminate the errors. The most legible will be `replace`:

1ALTER SERVER ch_server_name OPTIONS (ADD check_encoding 'replace');

Copy command

For chdb\_hook, pass it as a COPY or CREATE TABLE option:

1CREATE TABLE logs () WITH (
2    copy_from      = 's3://chdb-lakedata-public/logs/logs-2026-08-26.csv',
3    format         = 'CSVWithNames',
4    check_encoding = 'replace'
5);

Copy command

For UTF-8 encoded databases, invalid bytes will be replaced with `�`,

1try=# SELECT * FROM ch_table ORDER BY id;
2 id |   name
3----+---------
4  1 | Barrack
5  2 | Ale�y
6  3 | Leopold
7  4 | An�n�e

Copy command

For other database encodings, the offending characters will simply be removed:

1try=# SELECT * FROM ch_table ORDER BY id;
2 id |   name
3----+---------
4  1 | Barrack
5  2 | Aley
6  3 | Leopold
7  4 | Ann

Copy command

If, on the other hand, you need to retain byte compatibility, you'll need to map the offending column to `bytea`, instead:

1ALTER FOREIGN TABLE ch_table ALTER name TYPE bytea;

Copy command

This will preserve the byte-for-byte binary data:

1try=# SELECT * FROM ch_table ORDER BY id;
2id |      name
3----+------------------
4  1 | \x4261727261636b
5  2 | \x416c650079
6  3 | \x4c656f706f6c64
7  4 | \x416e006e8165
8(4 rows)

Copy command

But be aware that conversions to text will fail.

ClickHouse supports a panoply of interval types: `IntervalNanosecond`, `IntervalHour`, `IntervalDay`, `IntervalYear`, and everything in between. In previous releases, pg\_clickhouse did not support these types; an attempt to import a ClickHouse table using one returned an error.

No more. pg\_clickhouse v0.11.0 and chdb\_hook 0.1.2 import these types as Postgres `interval` columns. So, given a ClickHouse table using, say, `IntervalMillisecond`, as in the `duration` column here:

1CREATE TABLE logs (
2    req_id    Int64                NOT NULL,
3    start_at  DateTime64(6, 'UTC') NOT NULL,
4    duration  IntervalMillisecond  NOT NULL,
5    resource  Text                 NOT NULL,
6    method    Enum8('GET' = 1, 'HEAD', 'POST', 'PUT', 'DELETE', 'PATCH') NOT NULL,
7    node_id   Int64                NOT NULL,
8    response  Int32                NOT NULL
9) ENGINE = MergeTree
10  ORDER BY start_at;

Copy command

On import, pg\_clickhouse creates a table with a `duration interval` column:

| Column | Type | Nullable | | --------- | --------------------------- | -------- | | req\_id | bigint | not null | | start\_at | timestamp(6) with time zone | not null | | duration | interval | not null | | resource | text | not null | | method | text | not null | | node\_id | bigint | not null | | response | integer | not null |

Of course pushdown also works. Say you want to count all the transactions that completed before the end of the day yesterday. Just add the duration to the start time:

1try=# EXPLAIN (VERBOSE, COSTS OFF)
2 SELECT COUNT(*)
3   FROM logs
4  WHERE start_at + duration < date_trunc('day', now());
5                                                QUERY PLAN
6-----------------------------------------------------------------------------------------------------------
7 Foreign Scan
8   Output: (count(*))
9   Relations: Aggregate on (logs)
10   Remote SQL: SELECT count(*) FROM "default".logs WHERE (((start_at + duration) < toStartOfDay(now64())))
11(4 rows)

Copy command

The `EXPLAIN (VERBOSE)` output shows the remote query that executes on ClickHouse, which plainly pushes down `start_at + duration` for execution in ClickHouse (along with the `COUNT()` aggregate2, of course).

The same pattern applies chdb\_hook v0.1.2: it imports chDB interval typesas Postgres `interval` values. Both extensions also allow the interval types to be imported as `bigint`s, instead. Simply create the foreign or copy target table with `duration bigint` and the extension will do the rest.

The pg\_clickhouse http driver has supported the JSON type since v0.1, and the binary driver since v0.3. However, although it would push down a JSON property accessor, e.g.,

1SELECT * FROM things ORDER BY data ->> 'name';

Copy command

ClickHouse would return an error:

1DB::Exception: Data types Variant/Dynamic are not allowed in ORDER BY keys, because it can lead to unexpected results.
2Consider using a subcolumn with a specific data type instead

Copy command

This error derives from the implementation of ClickHouse JSON objects: ClickHouse wants to know a JSON property exists to sort. ClickHouse 25.3+ parameterized sub-columns assure property presence. An example:

1CREATE TABLE things (
2    id    Int32 NOT NULL,
3    data  JSON(
4      id      UInt32,
5      name    String,
6      size    Enum('small', 'medium', 'large'),
7      stocked Bool
8    ) NOT NULL
9) ENGINE = MergeTree PARTITION BY id ORDER BY (id);

Copy command

Previously, pg\_clickhouse was unable to import parameterized JSON columns, but v0.11.0 (and chdb\_hook v0.1.2), simply maps it to jsonb (or json), and now `ORDER BY` on a property properly pushes down:

1try=# SELECT * FROM things ORDER BY data ->> 'name';
2 id |                              data
3----+-----------------------------------------------------------------
4  4 | {"id": 4, "name": "doodad", "size": "large", "stocked": false}
5  3 | {"id": 3, "name": "gizmo", "size": "medium", "stocked": true}
6  2 | {"id": 2, "name": "sprocket", "size": "small", "stocked": true}
7  1 | {"id": 1, "name": "widget", "size": "large", "stocked": true}
8(4 rows)

Copy command

Similarly pg\_clickhouse v0.11.0 and chdb\_hook v0.1.2 improved support for unflattened Nested types, as in this example:

1CREATE TABLE visits(
2    visit_id  UInt64,
3    user_id   UInt64,
4    goals     Nested(
5        serial    UInt32,
6        order_id  String
7    )
8) ENGINE = MergeTree ORDER BY visit_id SETTINGS flatten_nested = 0;

Copy command

The flatten\_nested=0 instructs ClickHouse to create a single `goals`column formatted as an array of `Tuple(serial UInt32, order_id String)`(rather than separate array columns for each field). Previously, neither extension supported this structure. Now they offer two mappings.

By default IMPORT FOREIGN SCHEMA and chdb\_hook's COPY and CREATE TABLE commands map an unflattened Nested column to a two-dimensional text array:

| Column | Type | | --------- | ------------- | | visit\_id | numeric(20,0) | | user\_id | numeric(20,0) | | goals | text\[\]\[\] |

This maps each item in a Nested value to an array of the textual representation of each type:

1try=# SELECT * FROM nest_bin.visits WHERE visit_id < 3 ORDER BY visit_id;
2 visit_id | user_id |      goals      
3----------+---------+-----------------
4        1 |       1 | {{1,xx},{2,yy}}
5(1 row)

Copy command

The `goals` array contains two arrays with two text values each, the first for `serial`, the second for `order_id`. This structure preserves the data at the expense of its data type, although an INSERT on a pg\_clickhouse table properly converts types before inserting into ClickHouse:

1INSERT INTO visits
2VALUES (2, 2, ARRAY[ ['3', 'aa'], ['4', 'bb'] ]);

Copy command

But we can do better. pg\_clickhouse v0.11.0 also allows Nested values to map to custom composite types, as long as the order, type, and naming align perfectly. Given the Nested type defined for `goals`:

1Tuple(serial UInt32, order_id String)

Copy command

We can create a type with the corresponding names and types and slot it into the foreign table:

1CREATE TYPE goal_type AS (serial bigint, order_id text);
2ALTER FOREIGN TABLE visits ALTER goals TYPE goal_type[];

Copy command

And now the Nested tuples translate to the composite type:

1try=# SELECT * FROM nest_bin.visits WHERE visit_id < 3 ORDER BY visit_id;
2 visit_id | user_id |        goals        
3----------+---------+---------------------
4        1 |       1 | {"(1,xx)","(2,yy)"}
5        2 |       2 | {"(3,aa)","(4,bb)"}

Copy command

Naturally we can also INSERT data in this format:

1INSERT INTO visits
2VALUES (3, 3, ARRAY[row(5, 'jj'), row(6, 'zz')]::goal_type[]);

Copy command

The same pattern applies to chdb\_hook v0.1.2: When working with nested data exported from ClickHouse or chDB, the Postgres target table can use a multidimensional array of values or an array of an appropriately structured composite type:

1CREATE TYPE event_status AS ENUM ('new', 'done');
2CREATE TYPE event_point AS (x integer, y integer);
3CREATE TYPE event_label AS (key text, value bigint);
4CREATE TYPE event_item AS (id integer, name text);
5
6CREATE TABLE events (
7    status event_status,
8    point  event_point,
9    labels event_label[],
10    items  event_item[]
11);

Copy command

Then use the appropriate definitions for the data types in the `COPY` query (or rely on one of the `*WithNamesAndTypes` formats) to import the data.

1COPY events FROM 's3://chdb-lakedata-public/examples/events.parquet' (
2    structure $$
3        status Enum8('new' = 1, 'done' = 2),
4        point  Tuple(Int32, Int32),
5        labels Map(String, Int64),
6        items  Array(Tuple(id Int32, name String))
7    $$
8);

Copy command

Here we've used an `Array()` for the nested type; if the data was exported from ClickHouse with unflattened (flatten\_nested=0) structure, you can use `Nested`, instead:

1COPY events FROM 's3://chdb-lakedata-public/examples/events.parquet' (
2    structure $$
3        status Enum8('new' = 1, 'done' = 2),
4        point  Tuple(Int32, Int32),
5        labels Map(String, Int64),
6        items  Nested(id Int32, name String)
7    $$
8);

Copy command

pg\_clickhouse v0.11.0 ships a number of other improvements worth mentioning:

As sharp-eyed readers no doubt noticed, in addition to interval mappings, the large integer types now map to appropriate Postgres numerics, and a number of other ClickHouse data types now map to appropriate Postgres counterparts:

| ClickHouse | PostgreSQL | | --------------- | --------------- | | Int128 | numeric(39,0) | | Int256 | numeric(77,0) | | UInt64 | numeric(20,0) | | UInt128 | numeric(39,0) | | UInt256 | numeric(78,0) | | BFloat16 | float4 | | Time | time | | Time64(P) | time | | Tuple(...) | text\[\] | | Map(K,V) | text\[\]\[\] | | LineString | path | | MultiLineString | path\[\] | | MultiPolygon | polygon\[\]\[\] | | Point | point | | Ring | polygon | | Polygon | polygon\[\] | The same mappings apply to chdb extension v0.1.2.

The original `clickhouse_raw_query()` function, deprecated in v0.10.0, has been dropped. Update your code to use `clickhouse_query(server, sql)` to read rows and `CALL clickhouse_perform(server, sql)` to run statements that return none.
This release drops support for PostgreSQL 13, which has been unsupported by the Postgres community since September, 2025.
A community contribution, added pushdown for the PostgreSQL `sha224()`, `sha256()`, `sha384()`, and `sha512()` functions, along with supported constant-algorithm calls to the pgcrypto extension's `digest()`function.

Have a look at the complete pg\_clickhouse changes and chdb changes for more details, including bug fixes. Then get them from the usual places. For pg\_clickhouse:

PGXN
GitHub
Docker

And for the chdb extension:

PGXN
GitHub

1. Yes of course JSON prefers UTF-8 by definition, except when it's not. JSON data in Postgres must always use the database encoding. ↩ 2. Unfortunately, ClickHouse interval types do not yet support aggregates themselves, so `avg(duration)`, for example, will fail. But do watch for avg and sum support in 26.10. ↩

Get started with ClickHouse Managed Postgres today

Interested in seeing how ClickHouse Managed Postgres works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

---

Share this post