Loading post…
ClickhouseAnnouncement
pg_clickhouse & chdb updates: Encoding, nesting, and types
Summary
pg_clickhouse v0.11.0 and the chdb extension v0.1.2 continue their focus on cross-database compatibility between ClickHouse and PostgreSQL with several enhancements derived from header-only C libraries. A new check_encoding server option provides error handlers for invalid byte sequences in text columns: fail, remove, replace, and truncate, with replace displaying the Unicode replacement character for UTF-8 databases. ClickHouse Interval types such as IntervalMillisecond are now imported as PostgreSQL interval columns, and expressions like start_at + duration are pushed down to ClickHouse. Unflattened Nested columns are mapped to two-dimensional text arrays by default, or to arrays of composite types when a structure is specified; parameterized JSON columns are mapped to jsonb with proper ORDER BY pushdown. Additional type mappings include Int128 and UInt256 to numeric, BFloat16 to float4, and spatial types; the deprecated clickhouse_raw_query function was removed, and PostgreSQL 13 support was dropped.
Context
The post announces new releases of pg_clickhouse and the chdb extension, focusing on encoding, nesting, and type mapping improvements for cross-database compatibility.
Approach / What changed
Added check_encoding server option with four error handlers (fail, remove, replace, truncate); mapped ClickHouse Interval types to PostgreSQL interval with pushdown; mapped unflattened Nested to text[][] or composite type arrays; mapped parameterized JSON to jsonb; updated type mappings for large integers, time, and spatial types; removed deprecated clickhouse_raw_query function; dropped PostgreSQL 13 support.
Takeaways
The check_encoding option in pg_clickhouse v0.11.0 provides four handlers (fail, remove, replace, truncate) for invalid byte sequences in text columns imported from ClickHouse.
ClickHouse Interval types such as IntervalMillisecond are now imported as PostgreSQL interval columns, and expressions combining them with timestamps are pushed down to ClickHouse for execution.
Unflattened Nested columns in ClickHouse are mapped to two-dimensional text arrays by default, or to arrays of composite types when a structure specification is provided.
FAQ
How can I handle invalid UTF-8 bytes in ClickHouse text columns?
Set the check_encoding server option to 'replace' using ALTER SERVER; invalid bytes will be replaced with the Unicode replacement character for UTF-8 databases, or removed for other encodings. Alternative handlers are 'remove' and 'truncate'.
Can I use ClickHouse Interval types in PostgreSQL?
Yes, they are automatically imported as PostgreSQL interval columns. Expressions like start_at + duration are pushed down to ClickHouse, enabling efficient queries such as counting records before a certain date.
How does pg_clickhouse handle ClickHouse Nested types?
Unflattened Nested columns (with flatten_nested=0) are mapped to two-dimensional text arrays (text[][]) by default. You can also specify a structure to map them to arrays of composite types for better type fidelity.
Source and freshness
- Source
- clickhouse.com
- Published
- Added to Yomu
Summaries are generated from the original by a language model. Check the original before relying on it.
Keep reading
Related reading
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
The best engineering reads, once a week
One useful email. Unsubscribe anytime.