ClickHouse/pg_clickhouse v0.11.0

Release v0.11.0

on GitHub

This release makes binary-compatible changes to the v0.10 releases. Once installed, any existing use of pg_clickhouse v0.10 will benefit from its improvements on reload. The one exception is clickhouse_raw_query(), which has been dropped in this version and will no longer work. We recommend running this command to drop this function:

ALTER EXTENSION pg_clickhouse UPDATE TO '0.11';

🚨 Compatibility

• Removed clickhouse_raw_query(), deprecated in v0.10.0. Use clickhouse_query(server, sql) to read rows and CALL clickhouse_perform(server, sql) to run statements that return none; both take a foreign server rather than a connection string (#346).

• Removed the fetch_size server and table options, deprecated in v0.10.0. It's no longer necessary as both the http and the binary driver stream results by ClickHouse Native protocol Block (#381).

• This release validates the character encoding of text and JSON data fetched from ClickHouse, raising an error for violations of the Postgres database encoding. Add the new encoding_check option to any servers that return invalidly-encoded data to eliminate the errors:

  ALTER SERVER server_name OPTIONS (ADD encoding_check 'replace');

  Valid values are fail, replace, remove, and truncate. See the CREATE SERVER docs for details (#361)

⬆️ Dependencies

• Dropped support for PostgreSQL 13.
• Updated the vendored pg-clickhouse-c, which now parses unflattened Nested, SimpleAggregateFunction, and parameterized JSON types (#363).

⚡ Improvements

• IMPORT FOREIGN SCHEMA now preserves type modifiers through nested Array layers, so Array(Decimal(12,6)) imports as numeric(12,6)[]. It retains up to six digits of DateTime64(P) and Time64(P) precision, imports Time, Time64, and geometric types, and maps FixedString(N) to unconstrained text because ClickHouse counts bytes while PostgreSQL character limits count characters (#349).
• IMPORT FOREIGN SCHEMA now maps BFloat16 to real and Interval types to interval. IntervalNanosecond truncates to microseconds. Interval types also map to bigint transparently (#349, #374).
• IMPORT FOREIGN SCHEMA now maps Int128, Int256, UInt128, and UInt256 columns to numeric rather than erroring, and UInt64 to numeric rather than erroring above the bigint maximum. It declares the fewest digits, so UInt64 becomes numeric(20,0) (#355).
• A Tuple or Map column read into a PostgreSQL array now fills the array with each record's fields rather than a record literal, so {'k': 'v'} becomes as {{k,v}} rather than {"(k,v)"}. IMPORT FOREIGN SCHEMA declares such columns text[] and text[][]; the binary driver accepts those same types on INSERT, parsing each item as the field it fills. A composite or text column can still be mapped to a record (#349).
• IMPORT FOREIGN SCHEMA now correctly imports aggregate states with literal parameters or multiple arguments. It uses the first argument as the column type and stores the aggregate function name, without parameters, in a column option. Because AggregateFunction(count) has no argument type, it imports as bigint (#349, #363).
• IMPORT FOREIGN SCHEMA now imports Nested columns created with flatten_nested=0 as text[][], with one array per nested row. It also imports parameterized JSON as jsonb by default, which can be mapped manually to json or text, and reads SimpleAggregateFunction columns. These types previously caused errors (#363).
• Added pushdown for PostgreSQL sha224(), sha256(), sha384(), and sha512() functions, along with supported constant-algorithm calls to the pgcrypto extension's digest() function. Thanks to Siva Girish Ramesh for the PR (#360).
• On ClickHouse 26.9+, pushed-down cardinality now maps to arrayFlattenedLength, which counts every element across nested arrays as PostgreSQL does. Earlier ClickHouse versions still use length, which counts only the outer array (#367).
• Added pushdown for multidimensional array indexing in WHERE clauses (WHERE foo[1][1] = 'abc') (#373)

📚 Documentation

• Expanded the Composite Types section of the documentation to detail the behaviors of Array, Tuple, Map, and Nested data type mappings, all of which have been improved but have caveats.

🐞 Bug Fixes

• Fixed inserting interval values with HTTP driver. Values are now encoded based on destination's ClickHouse schema (#376).
• Fixed regexp_replace() pushdown passing flags to ClickHouse unmapped, which failed for n/p/t/w. Flags now translate as they do for other regular expression functions (#377).
• Fixed pushdown of date and timestamp arithmetic dropping sub-second interval precision, such as ts + interval '0.5 seconds' (#377).
• Fixed an issue where a check for ordered aggregates incorrectly handled custom aggregates that were not part of an extension. It now properly keeps custom aggregate execution local. Thanks to Minh Vu for the PR (#337)!
• Fixed precision loss when pushing down numeric pow() and power() expressions; they now execute locally in PostgreSQL. Thanks to Minh Vu for the PR (#339)!
• IMPORT FOREIGN SCHEMA now quotes strings in foreign table DDL as PostgreSQL literals rather than ClickHouse literals, so a ClickHouse name, database, or engine no longer doubles backslashes (#350).
• INSERT with the binary driver now rejects values wider than a FixedString(N) column rather than silently truncating them, matching HTTP driver errors (#349).
• Reading a ClickHouse string into a text column validates its bytes against the database encoding, raising an error rather than returning invalid text. Use the new encoding_check server option to remove or replace invalid bytes, or declare the column bytea to keep raw bytes (#359).

🆚 For more detail compare changes since v0.10.0.