Release v0.2.0

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.

dev

Rolling pre-release for dev. Updated on every push.

customer-testdeb

Rolling pre-release for customer-testdeb. Updated on every push.

Release v0.1.10

This release makes binary-only changes. Once installed, any existing use of pg_clickhouse v0.1 will get its benefits on reload without needing to ALTER EXTENSION UPDATE.

⚡ Improvements

• Added mapping for the JSON and JSONB -> TEXT and ->> TEXT operators to be passed down to ClickHouse using its sub-column syntax. Thanks Kaushik Iska for the PR (#169).
• Added pushdown support for jsonb_extract_path_text() and jsonb_extract_path() to ClickHouse sub-column syntax. Thanks Kaushik Iska for the PR (#176).
• Added mapping to push down now() to now64 rather than now, as previously, because PostgreSQL's now() produces sub-second precision, so should its clickHouse equivalent.
• Added mappings to push down the Postgres statement_timestamp(), transaction_timestamp(), and clock_timestamp() functions to to nowInBlock64 (requires ClickHouse 25.8 or higher).
• Pushdown window functions (ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, CUME_DIST, PERCENT_RANK, MIN/MAX OVER) to ClickHouse instead of computing them locally. Thanks Kaushik Iska for the PR (#175).
• Pushdown bool_and/every as groupBitAnd, bool_or as groupBitOr, and string_agg as groupConcat to ClickHouse. Thanks Philip Dubé for the PR (#184).
• Added mapping to push down the Postgres "SQL Value Functions", including CURRENT_TIMESTAMP, CURRENT_USER, and CURRENT_DATABASE.
• Changed the behavior of CURRENT_DATABASE() to push down the name of the current Postgres database rather than to the ClickHouse current_database() function.
• Added result set streaming to the HTTP driver. The new fetch_size server and table option specifies the size of each batch to stream and defaults to 50000000, about 50MB. Set it to 0 to disable streaming altogether. A testing loading a 1GB table reduced memory consumption from over 1GB to 73MB peak. Thanks Kaushik Iska for the testing and PR (#181).

🐛 Bug Fixes

• Improved memory management, fixing potential crashes in out of memory situations. Thanks to Philip Dubé for the PRs (#173, #173).
• Fixed issue where the -Merge suffix was not consistently appended to aggregates on AggregateFunction columns. Thanks to Philip Dubé for the PR (#179).
• Fixed NTILE, CUME_DIST, and PERCENT_RANK pushdown failing because the FDW emitted a ROWS UNBOUNDED PRECEDING frame clause that ClickHouse rejects for ranking functions. Thanks Philip Dubé for the PR (#184).
• regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy, json_agg_strict, and jsonb_agg_strict now evaluate locally instead of being pushed to ClickHouse where they would fail. Thanks Philip Dubé for the PR (#184).

📔 Notes

• Eliminated use of a constant that required libcurl 7.87.0, restoring support for earlier versions.
• Introduced clang-tidy and integrated it into make lint and for use in CI. Thanks to Philip Dubé for the PR (#177).

---

🆚 For more detail compare changes since v0.1.6.

Release v0.1.6

This release makes binary-only changes. Once installed, any existing use of
pg_clickhouse v0.1 will get its benefits on reload without needing to
ALTER EXTENSION UPDATE.

⚡ Improvements

• Added support for the PostgreSQL to_timestamp(float8) function, mapped to fromUnixTimestamp(toInt64()) in ClickHouse.

🪲 Bug Fixes

• Disabled query pushdown for JSONB subscript syntax (e.g., col_name['field']) for now. Thanks Kaushik Iska for the PR (#161).
• Added query cancellation via Ctrl-C and statement_timeout to the binary driver. Thanks Kaushik Iska for the PR (#162) that fixed this issue (#41).
• Fixed LIKE, ILIKE, and regex operator pushdown, including ~~*, !~~*, ~, !~, ~*, !~*. Thanks Kaushik Iska for the PR (#164).

---

🆚 For more detail compare changes since v0.1.5.

Release v0.1.5

This release makes binary-only changes. Once installed, any existing use of pg_clickhouse v0.1 will get its benefits on reload without needing to ALTER EXTENSION UPDATE.

🚨 Security Fixes

• Changed the http driver connection function to raise an error if the database name contains line ending characters to prevent HTTP header injection.
• Fixed an SQL injection vulnerability in parsing of parameters to the engine 'CollapsingMergeTree($sign) option to CREATE FOREIGN TABLE.

🪲 Bug Fixes

• Fixed a crash due to an unexpected EOF while the http driver parses a response. Thanks to @serprex for the fix (#153)
• Fixed a crash due to an unchecked memory allocation while the http driver reads a response. Thanks to @serprex for the fix (#154).

---

🆚 For more detail compare changes since v0.1.4.

Release v0.1.4

This release makes binary-only changes. Once installed, any existing use of
pg_clickhouse v0.1 will get its benefits on reload without needing to
ALTER EXTENSION UPDATE.

⚡ Improvements

• Added support for the PostgreSQL md5() function, mapped to lower(hex(MD5())) in ClickHouse.
• Added support for mapping PostgreSQL BYTEA columns to ClickHouse String columns.
• Added explicit setting of format_tsv_null_representation and output_format_tsv_crlf_end_of_line to all http requests, as unexpected values will interfere with its operation.
• Improved the error message from the binary driver when attempting to insert a NULL into a column that is not Nullable(T).

🪲 Bug Fixes

• Fixed binary driver errors when attempting to insert a NULL value into Nullable Numeric, Text, Enum, UUID, and INET columns. Thanks to Rahul Mehta for the report (#140).
• Fixed http driver array parsing, which previously did not properly parse string values and would raise an error on values containing brackets ([]). Thanks to Philip Dubé for the spot (#142).
• Fixed a bug where the binary driver would raise an error on an empty array.

📔 Notes

• Refactored and improved the http engine's result processing, bringing it into closer alignment with the binary engine and removing double processing of row values.
• The http driver now ignores the following session settings from the pg_clickhouse.session_settings to prevent them from interfering with its operation: date_time_output_format, format_tsv_null_representation, and output_format_tsv_crlf_end_of_line.

---

🆚 For more detail compare changes since v0.1.3.

Release v0.1.3

This release makes binary-only changes. Once installed, any existing use of
pg_clickhouse v0.1 will get its benefits on reload without needing to
ALTER EXTENSION UPDATE.

⚡ Improvements

• Changed the default mapping for DateTime and DateTime64 values from TIMESTAMP to TIMESTAMPTZ, because ClickHouse stores DateTimes as a Unix timestamp, always normalized to UTC, even if it displays as a different time zone. As of the first bug fix listed below, pg_clickhouse (almost) always fetches these values in UTC, so can store them as TIMESTAMPTZ values.
• Implemented INSERT support for the UUID and INET (IPv4 and IPv6) types. Thanks to Rahul Mehta for the report (#127)!

🪲 Bug Fixes

• Fixed time zone conversion in the http engine. Does not work with parameterized execution on ClickHouse versions prior to 25.8 due to ClickHouse Issue 88088; recommend using the binary engine for tables with timestamp values on earlier ClickHouse versions to avoid the issue.
• Fixed a server crash when attempting to insert types not yet supported by the binary engine.

🚀 Distribution

• Added the security policy.

📔 Notes

• Scripted the generation of the TPC-H results table and updated it in the README.
• Cleaned up some comments and old references to postgres_fdw left from the original fork in 2019.
• Added tests demonstrating subqueries that pg_clickhouse does not yet push down, to be improved in future releases.

🏗️ Build Setup

• Added pre-commit hooks to lint the code, including for indentation enforced by pg_bsd_indent. A new workflow ensures consistency for these quality checks. Relatedly, a number of issues found by the linters have been corrected.
• Configured make installcheck to run the tests in parallel, resulting in far faster test execution on multi-core systems. Adjusted the schemas in which some of the tests work to ensure they don't stomp on each other.

---

🆚 For more detail compare changes since v0.1.2.

Release v0.1.2

This release makes binary-only changes. Once installed, any existing use of pg_clickhouse v0.1 will get its benefits on reload without needing to ALTER EXTENSION UPDATE.

⚡ Improvements

• Added support for parameterized execution, including PREPARE and EXECUTE, by converting PostgreSQL $1-style parameters to ClickHouse {param:type}-style parameters.
• Added support for inserting arrays to the http engine.

📚 Documentation

• Documented IMPORT FOREIGN SCHEMA identifier case preservation behavior.
• Fixed the Postgres Docker start and connect info in the tutorial.
• Added complete DML documentation to the reference docs, including the new PREPARE/EXECUTE support and INSERT, SET, COPY, as well as shared library preloading.

🪲 Bug Fixes

• Fixed the http engine's parsing of UUID arrays selected from ClickHouse.
• Fixed the binary engine's conversion of Date values, which in arrays ended up too large by several orders of magnitude (e.g., 2025-12-05 would be converted to 10529827-09-17 😱). Thanks to Tom Lane for the pointer to the proper function to easily convert epoch seconds to a date.
• Fixed a binary engine bug where dates and timestamps for epoch 0 (1970-01-01 00:00:00) rendered as NULL.
• Added support for the Date32 ClickHouse type.
• Fixed conversion of array_agg() to support ClickHouse versions prior to 23.8.
• Fixed the precision of fractional seconds in the binary engine's conversion of ClickHouse DateTime64 values to Postgres TIMESTAMP (#114).

📔 Notes

• Removed unused code designed to support custom PostgreSQL extensions: ajbool, ajtime, country, and istore.
• Tweaked cost estimation to encourage pushdown of min() and max().

📚 Documentation

• Added a note to the IMPORT FOREIGN SCHEMA documentation explaining how table and column names imported from ClickHouse will have their letter casing and blank spaces preserved if they have uppercase characters or blank spaces.
• Fixed the commands to start and connect to the pg_clickhouse Docker image in the tutorial.
• Documented the Postgres aggregate functions known (via new tests) to push down to ClickHouse.

---

🆚 For more detail compare changes since v0.1.1.

Release v0.1.1

This release makes binary-only changes. Once installed, any existing use of pg_clickhouse v0.1 will get its benefits on reload without needing to ALTER EXTENSION UPDATE.

⚡ Improvements

• Refactored the internal handling of the pg_clickhouse.session_settings GUC to parse the settings only once rather than for every query sent to ClickHouse

🪲 Bug Fixes

• Fixed a crash when sending an empty COPY FROM via the binary driver

⬆️ Dependency Updates

• Updated vendored clickhouse-cpp library

🏗️ Build Setup

• Fixed the Makefile targets so that calling make install without first calling make will properly create the versioned SQL file.

📚 Documentation

• Added a versioning policy the documentation
• Fixed the badges and broken TPC-H links in README.md
• Added PGXN installation instructions to README.md

---

🆚 For more detail compare changes since v0.1.0.