ClickHouse/pg_clickhouse v0.2.0

Release v0.2.0

on GitHub

This release makes binary-compatible changes to the v0.1 releases. Once installed, any existing use of pg_clickhouse v0.1 will benefit from its improvements on reload. The only new feature that requires an upgrade is the pgch_version() function. Run this command to add it to the extension:

ALTER EXTENSION pg_clickhouse UPDATE TO '0.2';

⚡ Improvements

• Changed the pushdown mappings for the current date and timestamp functions to account for the session time zone and Postgres-standard millisecond precision, as follows: * CURRENT_DATE -> toDate(now(TZ)) * CURRENT_TIMESTAMP and LOCALTIMESTAMP => now64(9, TZ) * CURRENT_TIMESTAMP(n) and LOCALTIMESTAMP(n) => now64(n, TZ) * clock_timestamp(), statement_timestamp() & transaction_timestamp() => nowInBlock64(n, TZ)
• Added pushdown for the CURRENT_TIME and LOCALTIME SQL Value Functions to toTime64(now64(6, TZ), 6), supported by ClickHouse 25.8+.
• Added pgch_version(), which returns the full semantic version. This is the same value visible in pg_get_loaded_modules(), but available in Postgres versions prior to 18, and without having to load pg_clickhouse in advance.
• Added support for pushing down the flags passed to regexp_like() by prepending them to the regular expression (e.g., (?i)foo). If any of the flags cannot be pushed down, the regular expression function will not be pushed own.
• Added pushdown for regexp_split_to_array() to splitByRegexp(), including pushdown of applicable flags.
• Added pushdown mappings for array functions: array_cat, array_append, array_remove, array_to_string, cardinality, array_length, array_prepend, string_to_array, trim_array, array_fill, array_reverse, array_shuffle, array_sample, array_sort.
• Added mapping for split_part() to pushdown splitByString()[n].
• Added pushdown for array operators: @> (hasAll), <@ (hasAll), && (hasAny).
• Array slice syntax (arr[L:U], arr[:U], arr[L:]) now pushes down as arraySlice().
• Added mapping for regexp_replace(4-arg) to pushdown to replaceRegexpAll() when the g flag is set, and to prepend compatible flags to the pushed down expression, or not to push down if any are not compatible.
• All regular expression functions with compatible flags and all regular expression operators now push down prepended with (?-s) unless the s flag is set, so that the behavior more closely approximates that of Postgres.
• Added the pg_clickhouse.pushdown_regex setting to prevent regular expressions from being pushed down.

⬆️ Dependency Updates

• Updated vendored clickhouse-cpp library to v2.6.1.

🐞 Bug Fixes

• Fixed a malformed type name in the error message when the http driver is unable to map a ClickHouse type to a Postgres type.
• Fixed reversal of the arguments passed to the ClickHouse match() function by the mapping from regexp_like().
• array_dims, array_ndims, array_lower, array_upper, array_replace, array_positions, array_fill (3-arg), array_sort (3-arg), and string_to_array(3-arg) now evaluate locally instead of being pushed to ClickHouse where they would fail.
• Changed pushdown for regexp_replace(3-arg) from replaceRegexpAll() to replaceRegexpOne().

📔 Notes

• Added tests to ensure that concat_ws() successfully pushes down to the compatible function of the same name (an alias for concatWithSeparator).

🆚 For more detail compare changes since v0.1.10.