ClickHouse/pg_clickhouse v0.3.0

Release v0.3.0

on GitHub

This release makes binary-compatible changes to the v0.2 releases. Once installed, any existing use of pg_clickhouse v0.2 will benefit from its improvements on reload. The only change that requires an upgrade revokes EXECUTE from clickhouse_raw_query(). We recommend running this command make this security-sensitive change:

ALTER EXTENSION pg_clickhouse UPDATE TO '0.3';

⚡ Improvements

• Added pushdown for re2 extension functions, if available, to their ClickHouse equivalents (e.g., re2match → match, re2extractall → extractAll). Thanks to Philip Dubé for the PR (#204).
• Added pushdown for fuzzystrmatch functions soundex() and levenshtein() (2-arg, mapped to editDistanceUTF8). Thanks to Philip Dubé for the PR (#210).
• Added mapping for JSON => jsonb to the binary driver (requires ClickHouse 24.10 or later).
• Added support for ClickHouse JSON mapped to Postgres json, supporting all the same operators and functions as the jsonb mapping.
• Added pushdown for to_char(timestamp[tz], fmt) to ClickHouse formatDateTime(), with strict format-string validation. Only pushes down when the format is a constant whose every keyword has an identical CH equivalent (YYYY, MM, DD, DDD, HH24, HH12, HH, MI, SS, Q, Mon, Dy, AM/PM, plus lowercase variants). Thanks to Philip Dubé for the PR (#244).
• Made builtin function pushdown opt-in: Postgres builtins now ship to ClickHouse only when explicitly mapped, so name or signature differences cannot silently alter results. Thanks to Philip Dubé for the PR (#245).
• Added explicit mappings for mod, pow/power, bit_count(bytea), and reverse(text) (→ reverseUTF8) to retain previously working pushdowns. Thanks to Philip Dubé for the PR (#245).

🐞 Bug Fixes

• Fixed EXPLAIN (VERBOSE) failing with "could not find window clause for winref N" when window functions push down to ClickHouse. Thanks to Philip Dubé for the PR (#223).
• Fixed the parsing of strings that start with [ in the http driver so that it no longer assumes it's the start of an array. Thanks to Kaushik Iska for the PR (#234).
• Fixed the parsing of strings in the http driver to distinguish a true NULL value from a string containing \N. Thanks to Kaushik Iska for the PR (#235).
• Fixed the column_name foreign-table column option being ignored by INSERT, which caused the binary engine to fail to match ClickHouse block columns and the HTTP engine to deparse PostgreSQL attribute names. Thanks to Philip Dubé for the PR (#231).
• Fixed length(text) and strpos(text, text) pushdown to map to lengthUTF8 and positionUTF8 rather than ClickHouse's byte-counting length and position, matching Postgres character semantics. Thanks to Philip Dubé for the PR (#245).
• Stopped pushing down asin, acos, atanh, and acosh: Postgres raises an error on out-of-range input where ClickHouse returns NaN. Evaluating locally preserves Postgres semantics. Thanks to Philip Dubé for the PR (#245).

📚 Documentation

• Added "Extension Pushdown" section to the reference docs, covering re2, intarray, and fuzzystrmatch support.
• Added recommendation to the reference docs to consider using the re2 extension and disabling Postgres regular expression pushdown.
• Documented the column_name foreign table column option in the reference docs.
• Added jsonb_extract_path_text() and jsonb_extract_path() to the list of push down functions in the reference docs, along with json_extract_path_text() and json_extract_path(), which are new in this release.
• Fixed the reversed descriptions of ->> and -> in the list of pushed down operators in the reference docs.

🚀 Distribution

• Added the ca-certificates package and the re2 extension to the OCI (née Docker) images.

🚨 Security Fixes

• Added SQL to revoke EXECUTE permission on clickhouse_raw_query() from PUBLIC. Leaving it executable by PUBLIC would allow any database user to reach internal services (metadata endpoints, private APIs, etc.) from the PostgreSQL server — a classic SSRF vector. This ensures that admins can limit access only to those who legitimately need to execute ad-hoc ClickHouse queries (e.g., a dedicated ClickHouse admin role). Thanks to Andrey Borodin for the PR (#228).

🆚 For more detail compare changes since v0.2.0.