pg_clickhouse & chdb updates: Encoding, nesting, and types

Out now on GitHub and PGXN, pg_clickhouse v0.11.0 and the chdb extension v0.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.

What a character #

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-based¹ type 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 TABLE commands. Both allow you to address errors resembling:

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

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');

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

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

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

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;

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)

But be aware that conversions to text will fail.

Intervalid #

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;

On import, pg_clickhouse creates a table with a duration interval column:

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)

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() aggregate², of course).

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

Nesting instinct #

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

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

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

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)

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;

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:

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)

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'] ]);

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)

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[];

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)"}

Naturally we can also INSERT data in this format:

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

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

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

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

Odds and ends #

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