Description
pg_clickhouse is a PostgreSQL extension that enables remote query execution on ClickHouse databases, including a foreign data wrapper. It supports PostgreSQL 14 and higher and ClickHouse 23.3 and higher.Getting started
The simplest way to try pg_clickhouse is the Docker image, which contains the standard PostgreSQL Docker image with the pg_clickhouse and re2 extensions:Usage
Versioning policy
pg_clickhouse adheres to Semantic Versioning for its public releases.- The major version increments for API changes
- The minor version increments for backward compatible SQL changes
- The patch version increments for binary-only changes
- The library version (defined by
PG_MODULE_MAGICon PostgreSQL 18 and higher) includes the full semantic version, visible in the output of thepgch_version()function or the Postgrespg_get_loaded_modules()function. - The extension version (defined in the control file) includes only the major
and minor versions, visible in the
pg_catalog.pg_extensiontable, the output of thepg_available_extension_versions()function, and\dx pg_clickhouse.
v0.1.0 to v0.1.1, benefits all databases that have loaded v0.1 and
don’t need to run ALTER EXTENSION to benefit from the upgrade.
A release that increments the minor or major versions, on the other hand, will
be accompanied by SQL upgrade scripts, and all existing database that contain
the extension must run ALTER EXTENSION pg_clickhouse UPDATE to benefit from
the upgrade.
DDL SQL reference
The following SQL DDL expressions use pg_clickhouse.CREATE EXTENSION
Use CREATE EXTENSION to add pg_clickhouse to a database:WITH SCHEMA to install it into a specific schema (recommended):
ALTER EXTENSION
Use ALTER EXTENSION to change pg_clickhouse. Examples:-
After installing a new release of pg_clickhouse, use the
UPDATEclause: -
Use
SET SCHEMAto move the extension to a new schema:
DROP EXTENSION
Use DROP EXTENSION to remove pg_clickhouse from a database:CASCADE clause to drop them, too:
CREATE SERVER
Use CREATE SERVER to create a foreign server that connects to a ClickHouse server. Example:-
driver: The ClickHouse connection driver to use, either “binary” or “http”. Required. -
compression: Native-protocol compression for the “binary” driver, one of “none”, “lz4”, or “zstd”. Defaults to “lz4”. Ignored by the “http” driver. -
dbname: The ClickHouse database to use upon connecting. Defaults to “default”. -
host: The host name of the ClickHouse server. Defaults to “localhost”; -
port: The port to connect to on the ClickHouse server. Defaults as follows:- 9440 if
driveris “binary” andhostis a ClickHouse Cloud host - 9004 if
driveris “binary” andhostisn’t a ClickHouse Cloud host - 8443 if
driveris “http” andhostis a ClickHouse Cloud host - 8123 if
driveris “http” andhostisn’t a ClickHouse Cloud host
- 9440 if
-
min_tls_version: Minimum TLS protocol version to negotiate on connections that use TLS. One ofTLSv1,TLSv1.1,TLSv1.2, orTLSv1.3. Defaults to the TLS library’s own minimum. Applies to both drivers. -
secure: Controls TLS for the connection. One of:auto(default): use TLS whenhostis a ClickHouse Cloud host orportis a secure port; plaintext otherwise.on(ortrue/yes/1): always use TLS. Defaultsportto 8443 (“http”) or 9440 (“binary”).off(orfalse/no/0): never use TLS. Defaultsportto 8123 (“http”) or 9000 (“binary”).
-
encoding_check: Defines how to handle invalid characters under the database encoding when converting ClickHouse String and JSON values. One of:fail(default): raise an errorremoveremoves invalid bytesreplace: under the UTF-8 encoding, replaces invalid bytes with the Unicode replacement character (�); same asremovefor other encodingstruncatetruncates the text at the first invalid byte
-
settings: ClickHouse settings to send with foreign table queries and inserts on this server. Use syntax frompg_clickhouse.session_settings. Use foreign tablesettingsto override server settings.pg_clickhouse.session_settingsvalues override both for matching keys; see Settings Precedence for details. Example:pg_clickhouse checks the syntax when the option is set; ClickHouse checks the values when a query uses them.
ALTER SERVER
Use ALTER SERVER to change a foreign server. Example:DROP SERVER
Use DROP SERVER to remove a foreign server:CASCADE to
also drop those dependencies:
CREATE USER MAPPING
Use CREATE USER MAPPING to map a PostgreSQL user to a ClickHouse user. For example, to map the current PostgreSQL user to the remote ClickHouse user when connecting with thetaxi_srv foreign server:
user: The name of the ClickHouse user. Defaults to “default”.password: The password of the ClickHouse user.
ALTER USER MAPPING
Use ALTER USER MAPPING to change the definition of a user mapping:DROP USER MAPPING
Use DROP USER MAPPING to remove a user mapping:IMPORT FOREIGN SCHEMA
Use IMPORT FOREIGN SCHEMA to import all the tables defines in a ClickHouse database as foreign tables into a PostgreSQL schema:LIMIT TO to limit the import to specific tables:
EXCEPT to exclude tables:
-
Imported columns keep the type modifier of the ClickHouse type, including
those within
Arrays. In other words,Array(Decimal(12,6))imports asnumeric(12,6)[]. -
Nullablecolumns import withoutNOT NULL;Nullableinside anArraydoes not, because PostgreSQL arrays always allow NULL elements. -
AggregateFunctionandSimpleAggregateFunctioncolumns import withoutNOT NULL, whatever the ClickHouse declaration, see State Columns andNOT NULL. -
Tuplecolumns import astext[].MapandNestedcolumns created withflatten_nested=0import astext[][]. Each conversion emits aNOTICE. PostgreSQL’srecordpseudotype cannot define a table column, so each tuple becomes an array of values, each map becomes an two-dimensional array of key-value pairs, and each Nested value becomes an two-dimensional array of rows. To read fields as records, alter the column to an array of a matching composite type, see Manual Type Mappings for an example. -
DateTime64andTime64columns with precision greater than 6 (microseconds) also trigger aNOTICE, since PostgreSQL caps precision attimestamp(6)andtime(6). -
Column types without a PostgreSQL counterpart, including the legacy
Object('json')type that predates ClickHouseJSON, trigger an error.
CREATE FOREIGN TABLE
Use CREATE FOREIGN TABLE to create a foreign table that can query data from a ClickHouse database:-
database: The name of the remote database. Defaults to the database defined for the foreign server. -
table_name: The name of the remote table. Default to the name specified for the foreign table. -
engine: The table engine used by the ClickHouse table. ForCollapsingMergeTree()andAggregatingMergeTree(), pg_clickhouse automatically applies the parameters to function expressions executed on the table. -
settings: ClickHouse settings to send with queries and inserts on this table. Usepg_clickhouse.session_settingssyntax. Table settings override server settings for matching keys, so tables can use table-specific settings such as vector index options. ClickHouse uses one list of settings per query. Joins and subqueries that push down to ClickHouse combine settings from their tables. If tables set different values for one setting, pg_clickhouse ignores those table values. Server or session settings still apply. ANOTICEreports conflicting setting names and effective query values or fallback to ClickHouse defaults. See Settings Precedence.
-
column_name: The name of the column on the ClickHouse side, used in preference to the PostgreSQL attribute name when deparsing queries and inserts. Useful for mapping unquoted lowercase PostgreSQL column names to case-sensitive ClickHouse columns, e.g., -
AggregateFunction: The name of the aggregate function applied to an AggregateFunction Type column. Map the data type to the ClickHouse type passed to the function and specify the name of the aggregate function via the appropriate column option and pg_clickhouse will automatically appendMergeto an aggregate function evaluating the column. Declare the column nullable to avoid issues described in State Columns andNOT NULL. -
SimpleAggregateFunction: The name of the aggregate function applied to an SimpleAggregateFunction Type column. Map the data type to the ClickHouse type passed to the function and specify the name of the aggregate function via the appropriate column option.
State Columns and NOT NULL
Keep AggregateFunction and SimpleAggregateFunction columns nullable.
PostgreSQL 19 changes count(column) to count(*) for NOT NULL state
columns, which counts rows instead of merging states.
Take a ClickHouse table holding one count state per user, each state built
from 1,000 events:
NOT NULL, PostgreSQL counts states, which produces an invalid count:
NOT NULL, pg_clickhouse uses countMerge() to merge states, yielding
the proper 1000 count:
ALTER FOREIGN TABLE to drop the constraint from an existing foreign table:
NOT NULL only affects PostgreSQL. IMPORT FOREIGN
SCHEMA therefore imports state columns as nullable.
ALTER FOREIGN TABLE
Use ALTER FOREIGN TABLE to change the definition of a foreign table:DROP FOREIGN TABLE
Use DROP FOREIGN TABLE to remove a foreign table:CASCADE clause to drop them, too:
DML SQL reference
The SQL DML expressions below may use pg_clickhouse. Examples depend on these ClickHouse tables:EXPLAIN
The EXPLAIN command works as expected, but theVERBOSE option triggers the
ClickHouse “Remote SQL” query to be emitted:
SELECT
Use the SELECT statement to execute queries on pg_clickhouse tables just like any other tables:nodes table and join to it instead of the remote table:
node_id instead of the local column, and then join
to the lookup table later:
node_id, reducing
the number of rows that must be pulled back into Postgres from 1000 (all of
them) to just 8, one for each node.
Grouping Sets
pg_clickhouse runsGROUPING SETS, ROLLUP, CUBE, and GROUPING() in
ClickHouse using a GROUPING SETS clause. GROUPING() pushdown requires
ClickHouse 26.6+.
Postgres returns NULL for keys left out of a grouping set. ClickHouse does too,
except for types that cannot be Nullable, such as Array and Map. Keys of
these types return empty values instead, such as {} for an array.
Partitioned Tables
A PostgreSQL partitioned table can mix local partitions with foreign partitions backed by ClickHouse. A common layout offloads older data to ClickHouse while recent data stays in PostgreSQL:enable_partitionwise_aggregate enabled, PostgreSQL computes a partial
aggregate below Append, then a finalize aggregate above combines those
partials into result. pg_clickhouse pushes the foreign partition’s partial
down to ClickHouse:
When partial aggregates push down
PostgreSQL represents a partial aggregate as a transition state that the finalize step combines across partitions. pg_clickhouse can push a partition’s partial down only when it can express it as a ClickHouse value:- Decomposable aggregates whose transition state is already the final
value, push down directly:
count,sum,min,max,bool_and/every,bool_or,bit_and,bit_or, andbit_xor. avgover integers pushes its{count, sum}state as an array.avg,var_pop,var_samp,stddev_pop, andstddev_sampover floating point push their{N, sum, sum of squared deviations}state as an array.
FILTER (WHERE …) pushes down with these aggregate functions.
When they fall back
Aggregates whose transition state is PostgreSQL’s opaqueinternal type have
no portable representation, so the foreign partition instead fetches its rows
and aggregates them locally. This covers anything over numeric, plus
avg(bigint) and avg(interval). DISTINCT, ordered-set, and variadic
aggregates also fall back.
PREPARE, EXECUTE, DEALLOCATE
As of v0.1.2, pg_clickhouse supports parameterized queries, mainly created by the PREPARE command:{param:type}-style query parameters:
INSERT
Use the INSERT command to insert values into a remote ClickHouse table:COPY
Use the COPY command to insert a batch of rows into a remote ClickHouse table:⚠️ Batch API Limitations pg_clickhouse hasn’t yet implemented support for the PostgreSQL FDW batch insert API. Thus COPY currently uses INSERT statements to insert records. This will be improved in a future release.
UPDATE and DELETE
pg_clickhouse runs UPDATE and DELETE on a foreign table as a single ClickHouse lightweight UPDATE or lightweight DELETE statement:RETURNING- Joins, as with
UPDATE ... FROM,DELETE ... USING, orIN (SELECT ...) - Row-level triggers on the foreign table, or
WITH CHECK OPTIONviews
UPDATEs and DELETEs wait for the storage operation
to complete before returning, so subsequent queries see their changes.
ClickHouse reports no affected row count, so Postgres always reports UPDATE 0
or DELETE 0.
Lightweight DELETE requires a MergeTree family table. Lightweight
UPDATE requires ClickHouse 25.7+ and a MergeTree family table, other than
SummingMergeTree, AggregatingMergeTree, or GraphiteMergeTree, created with
SETTINGS enable_block_number_column = 1, enable_block_offset_column = 1.
For ClickHouse 25.7, also set allow_experimental_lightweight_update = 1.
Pass ClickHouse settings, such as lightweight_deletes_sync, as for other queries
(see Settings Precedence).
TRUNCATE
Use TRUNCATE to remove all rows from foreign tables. pg_clickhouse sends one ClickHouse TRUNCATE TABLE statement per table:LOAD
Use LOAD to load the pg_clickhouse shared library:SET
Use SET to set the pg_clickhouse custom configuration parameters.pg_clickhouse.session_settings
The pg_clickhouse.session_settings parameter configures ClickHouse
settings to be set on subsequent queries. Example:
join_use_nulls for outer joins,
group_by_use_nulls for grouping sets, and
transform_null_in for the IN family (see
IN and NULL Semantics).
date_time_output_format: the http driver requires it to be “iso”format_tsv_null_representation: the http driver requires the defaultoutput_format_tsv_crlf_end_of_linethe http driver requires the defaultsession_timezone: pg_clickhouse sets it to PostgreSQL’s TimeZone on ClickHouse 23.6 and later
pg_clickhouse.session_settings; either use shared library preloading or
simply use one of the objects in the extension to ensure it loads.
Foreign table queries and inserts combine settings by key. Later entries
override earlier entries:
- Built-in settings, shown above
- Foreign server
settings - Foreign table
settings pg_clickhouse.session_settings
settings 'final 0' overrides built-in final 1. Override that table option
with:
final 1 along with its other settings.
clickhouse_query() and
clickhouse_perform() use only built-in settings and
pg_clickhouse.session_settings; the settings options apply to queries and
inserts on foreign tables.
pg_clickhouse.pushdown_regex
The pg_clickhouse.pushdown_regex parameter controls whether pg_clickhouse
pushes down regular expression functions and operators. It does so by default;
set this parameter to false to prevent them from being pushed down:
pg_clickhouse.vector_planner_warnings
pg_clickhouse warns by default when a pgvector nearest neighbor search
cannot use a ClickHouse vector similarity index because of its ORDER BY
or LIMIT. Each warning explains why. Set
pg_clickhouse.vector_planner_warnings to false to silence these warnings:
ALTER ROLE
Use ALTER ROLE’sSET command to preload pg_clickhouse
and/or SET its parameters for specific roles:
RESET command to reset pg_clickhouse preloading
and/or parameters:
Preloading
If every or nearly every Postgres connection needs to use pg_clickhouse, consider using shared library preloading to automatically load it:session_preload_libraries
Loads the shared library for every new connection to PostgreSQL:
shared_preload_libraries
Loads the shared library into the PostgreSQL parent process at startup time:
Data Types
This table presents the preferred mapping of ClickHouse to PostgreSQL data types. IMPORT FOREIGN SCHEMA uses these mappings and derives the appropriate type modifiers and array dimensions. Use CREATE FOREIGN TABLE to declare alternate PostgreSQL types. Additional read targets list conversions supported bybinary driver beyond
PostgreSQL explicit casts. Empty cells still allow those casts. Values must
fit target types. These targets describe reads; writes follow separate
conversion rules.
Any column also reads into
text, varchar, or another string type. The value
takes the PostgreSQL type above, then renders through that type’s output
function. A ClickHouse string read into a text type is validated against the
database encoding, so bytes PostgreSQL cannot read raise an error. Declare a
String, FixedString, Enum, or JSON column BYTEA to read its bytes as
ClickHouse wrote them.
Input-compatible types can read strings using their PostgreSQL input function.
Composite types must have matching fields in matching order. To read a tuple
as an array, each field must convert to the array’s element type, and no field
can itself be an array.
When read as arrays, Map uses one row per key-value pair and Nested uses
one row per nested record. To read a tuple as box, provide two points; for
circle, provide a point and radius; for line, provide three coefficients.
Additional notes and details follow.
Type Coercion
With thebinary driver, alternate scalar types use PostgreSQL’s explicit
casts where available, and array elements are converted to the declared
element type. Unsupported conversions and out-of-range values raise errors.
With either driver, map ClickHouse Interval types to smallint, integer,
or bigint to read and write counts of their units. For example, an
IntervalDay value of 3 maps to the integer 3. Mapping
IntervalNanosecond to bigint preserves nanoseconds, while mapping to
interval truncates to microseconds.
Vector Types
When the pgvector extension is installed, declare ClickHouse float array columns asvector to read and write them as pgvector values:
BYTEA
ClickHouse does not provide the equivalent of the PostgreSQL BYTEA type, but allows any bytes to be stored in String type. In general ClickHouse strings should be mapped to the PostgreSQL TEXT, but when using binary data, map it to BYTEA. Example:SELECT query will output:
FixedString(N) column pads short values with nul bytes. A text column
drops that trailing padding, where a BYTEA column keeps every byte.
Composite Types
Array
ClickHouse Arrays map directly to PostgreSQL arrays, with equivalent semantics. The main differences is that Postgres multidimensional arrays must have array expressions with matching dimensions. An attempt to read a ClickHouse array with different dimensions, such as[[1], [2,3]],
triggers an error.
Array index access pushes down as appropriate, including multidimensional
index access. Examples:
Tuple
PostgreSQL provides no type corresponding to the ClickHouse Tuple type. IMPORT FOREIGN SCHEMA therefore maps Tuple columns map totext[], with each element appearing in the order defined for the
Tuple. pg_clickhouse emits a NOTICE when it makes such a mapping.
For example, given this ClickHouse table:
Map
PostgreSQL provides no type corresponding to the ClickHouse Map type. pg_clickhouse therefore Map types totext[][], with each key-value pair as
its own array. IMPORT FOREIGN SCHEMA uses this
mapping. One can INSERT maps as arrays, as well. An example:
Nested
By default, ClickHouse splits aNested column into one Array column per
field (flatten_nested=1). IMPORT FOREIGN SCHEMA reads these from
ClickHouse’s system.columns catalog and creates separate PostgreSQL array
columns, preserving dotted names such as items.a and items.b. For example,
given this foreign table:
WHERE clause using the usual array features,
including array subscript syntax:
flatten_nested=0 maps to an array with one
item per nested row. Each array item contains that row’s values:
c2 Nested(id Int64, name String) in this example, it would be:
Manual Type Mappings
IMPORT FOREIGN SCHEMA uses general-purpose PostgreSQL types. For example,
given a ClickHouse table using Enum, Tuple, Map, and unflattened
Nested (flatten_nested = 0) columns, such as:
key and value in this case). Nested and Map
become arrays of composites, while Tuple becomes one composite value.
Composite inserts into Nested columns require the binary driver.
SELECT
list:
WHERE clause --- as long as the field names are
identical:
INSERT using such composites is not yet supported.
Function and operator reference
Functions
These functions provide the interface to query a ClickHouse database.clickhouse_server_version
major.minor.patch, for the named
foreign server, connecting if necessary using the server’s options and the
current user’s user mapping:
SELECT version() query, and caches it for the life of the
connection.
clickhouse_query
driver,
credentials, database, and the connection cache.
The first argument is the name of a server created with CREATE SERVER. A
column definition list (AS name(col type, ...)) is required: PostgreSQL
needs the result shape before fetching rows, and it must match the columns the
query returns. Values are converted from ClickHouse to the declared types the
same way a foreign table column would be. Statements that return no results,
such as DDL, have nothing to declare; run them with
clickhouse_perform instead.
No role has EXECUTE access by default; GRANT to a role to allow it to use
the function.
clickhouse_perform
clickhouse_query has no result shape to declare. It
resolves the server the same way clickhouse_query does, reusing its
driver, credentials, database, and the connection cache.
As a procedure it must be invoked with CALL, not SELECT, and it returns no
rows. No role has EXECUTE access by default; GRANT to a role to allow it
to use the procedure.
Pushdown functions
pg_clickhouse pushes down a subset of the PostgreSQL builtin functions used in conditionals (HAVING and WHERE clauses). That subset maps to ClickHouse
equivalents as follows:
abs: absfactorial: factorialmod(int2/int4/int8/numeric): modulopow&power(float8, not numeric): powround: roundsin,cos,tan,atan,atan2,sinh,cosh,tanh,asinh,degrees,radians,pi: ClickHouse math functions of the same name.asin,acos,atanh,acoshare not pushed down: PG raises on out-of-range input where CH returnsNaN.date_part: passes PostgreSQL’s TimeZone to ClickHouse fortimestampandtimestamptzvalues.date_part('day'): toDayOfMonthdate_part('doy'): toDayOfYeardate_part('dow'): toDayOfWeekdate_part('year'): toYeardate_part('month'): toMonthdate_part('hour'): toHourdate_part('minute'): toMinutedate_part('second'): toSeconddate_part('quarter'): toQuarterdate_part('isoyear'): toISOYeardate_part('week'): toISOYeardate_part('epoch'): toISOYear
date_trunc: passes PostgreSQL’sTimeZoneto ClickHouse fortimestampandtimestamptzvalues.date_trunc('week'): toMondaydate_trunc('second'): toStartOfSeconddate_trunc('minute'): toStartOfMinutedate_trunc('hour'): toStartOfHourdate_trunc('day'): toStartOfDaydate_trunc('month'): toStartOfMonthdate_trunc('quarter'): toStartOfQuarterdate_trunc('year'): toStartOfYear
extract(field FROM source): same mappings asdate_part; passes PostgreSQL’sTimeZoneto ClickHouse fortimestampandtimestamptzvalues.date(timestamp)&date(timestamptz): toDate32; passes PostgreSQL’sTimeZoneto ClickHouse, as do casts betweendateandtimestamportimestamptz.AT TIME ZONE&timezone(zone, source): uses toString followed by toDateTime64 to preserve local date and time while changing time zone. When convertingtimestamptotimestamptz, local times skipped or repeated during daylight saving changes produce different results: ClickHouse chooses an earlier time than PostgreSQL.array_position: indexOf with nullIf to convert0toNULLand arraySlice when there’s a third argument for the search starting index; note thatnancurrently does not matcharray_cat: arrayConcatarray_append: arrayPushBackarray_prepend: arrayPushFrontarray_remove: arrayRemovecardinality: arrayFlattenedLength on ClickHouse 26.9+, otherwise length, which counts only the outer arrayarray_length(array, 1):nullIf(length(array), 0)array_length&cardinality: lengtharray_to_string: arrayStringConcatstring_to_array: splitByStringsplit_part: splitByString + array subscripttrim_array: arrayResizearray_fill: arrayWithConstantarray_reverse: arrayReversearray_shuffle: arrayShufflearray_sample: arrayRandomSamplearray_sort: arraySort / arrayReverseSortbtrim: trimBothltrim: trimLeftrtrim: trimRightconcat_ws: concatWithSeparatorlower(text): lowerUTF8upper(text): upperUTF8substring(text, ...)&substr(text, ...): substringUTF8substring(bytea, ...)&substr(bytea, ...): substringlength(text): lengthUTF8length(bytea)&octet_length: lengthreverse(text): reverseUTF8reverse(bytea): reversestrpos: positionUTF8regexp_like: matchregexp_match: extractGroups if the regular expression contains parenthesized subexpressions; otherwise extractAll sliced with arraySlice.regexp_replace: replaceRegexpOne or replaceRegexpOne when thegflag is presentregexp_split_to_array: splitByRegexpmd5: MD5sha224,sha256,sha384, andsha512: Corresponding ClickHouse SHA functionsencode(bytea, fmt)whenfmtis a string constant (case-insensitive):encode(bytea, 'hex'): hex wrapped in lower, since PostgreSQL emits lowercase hex.encode(bytea, 'base64'): base64Encode wrapped in replaceRegexpAll to reproduce PostgreSQL’s MIME (RFC 2045) line break every 76 characters.encode(bytea, 'base64url')(PostgreSQL 19+): base64URLEncode, which matches PostgreSQL’s RFC 4648 URL alphabet without padding.
json_extract_path_text: sub-column syntaxjson_extract_path: toJSONString + sub-column syntaxjsonb_extract_path_text: sub-column syntaxjsonb_extract_path: toJSONString + sub-column syntaxbit_count(bytea): bitCountto_timestamp(float8): toDateTime64to_char(timestamp[tz], fmt): formatDateTime whenfmtis a string constant whose every keyword has a faithful ClickHouse equivalent. See to_char() under Compatibility Notes for the supported keywords. Otherwise the function evaluates locally in PostgreSQL. Passes PostgreSQL’sTimeZoneto ClickHouse fortimestampandtimestamptzvalues.statement_timestamp,transaction_timestamp, &clock_timestamp: nowInBlock64 (nowInBlock64(9, $session_timezone))CURRENT_DATE: now and toDate (toDate(now($session_timezone)))now,CURRENT_TIMESTAMP, &LOCALTIMESTAMP: now64 (now64(9, $session_timezone))CURRENT_TIMESTAMP(n)&LOCALTIMESTAMP(n): now64 (now64(n, $session_timezone))CURRENT_DATABASE: Passed as value from PostgreSQL function.CURRENT_SCHEMA: Passed as value from PostgreSQL function.CURRENT_CATALOG: Passed as value from PostgreSQL function.CURRENT_USER: Passed as value from PostgreSQL function.USER: Passed as value from PostgreSQL function.CURRENT_ROLE: Passed as value from PostgreSQL function.SESSION_USER: Passed as value from PostgreSQL function.
Pushdown operators
- Array slice (
arr[L:U]): arraySlice @>(array contains): hasAll<@(array contained by): hasAll&&(array overlap): hasAny~(regexp match): match!~(regexp not match): match~*(case insensitive regexp no match): match!~*(case insensitive regexp not match): match->>(JSON/JSONB extract element as text): sub-column syntax->(JSON/JSONB extract): toJSONString + sub-column syntaxjsonbsubscripts (data['a']['b']): toJSONString + sub-column syntax. Pushdown requires a column with constant, non-NULL text subscripts. Integer, numeric text, and non-constant subscripts evaluate locally./(integer division): intDiv^(exponent): pow|/(square root): sqrt||/(cube root): cbrt@(absolute value): abs&(bitwise and): bitAnd|(bitwise or): bitOr#(bitwise xor): bitXor~(bitwise not): bitNot<<(shift left): bitShiftLeft>>(shift right): bitShiftRight
IN and NULL semantics
ClickHouse evaluatesIN under two-valued logic: when the probe finds no
match it returns 0 even if a NULL is involved, where PostgreSQL computes
NULL. To preserve PostgreSQL semantics, pg_clickhouse pushes down the IN
family over a constant list or array (IN, NOT IN, = ANY, = ALL, <> ANY, <> ALL) unconditionally: the native or cheap form where it can prove
neither the probe nor an array element can be NULL, or a guarded CASE form
otherwise that checks for NULL values at runtime instead, computing
PostgreSQL’s exact three-valued answer (TRUE, FALSE, NULL) in every context,
including value positions like a SELECT list or GROUP BY.
A NOT IN (SELECT ...) filter over nullable columns also pushes down,
deparsed with compensating guards that keep PostgreSQL’s behavior: a set
containing a NULL disqualifies every row, and a NULL probe passes only against
an empty set. Each guard is omitted when a NOT NULL declaration proves it
unnecessary. Unlike the array forms above, this guard only applies in a plain
filter condition (or under NOT); we still do not push down IN (SELECT ...)
(in a value position) nor grouped/aggregated subquery bodies. Declaring
columns NOT NULL maximizes pushdown by letting the cheaper unguarded form
ship instead; IMPORT FOREIGN SCHEMA does so automatically for non-Nullable
ClickHouse columns. The proof follows non-NULL constants, NOT NULL columns,
and basic arithmetic (+, -, *, unary -) over them.
These rules assume ClickHouse’s default transform_null_in = 0, which
pg_clickhouse sets on every query through its built-in
session settings so that a ClickHouse server
profile cannot silently change it. Setting
transform_null_in = 1 breaks the semantics of every pushed IN.
Custom functions
These custom functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse functions with no PostgreSQL equivalents. If any of these functions can’t be pushed down they will raise an exception.Extension pushdown
pg_clickhouse recognizes functions from select core and third-party extensions, pushing them down to their ClickHouse equivalents.re2
All re2 extension operators and functions push down 1:1 to ClickHouse:@~→ matchre2match→ matchre2extract→ extractre2extractall→ extractAllre2regexpextract→ regexpExtractre2extractgroups→ extractGroupsre2replaceregexpone→ replaceRegexpOnere2replaceregexpall→ replaceRegexpAllre2countmatches→ countMatchesre2countmatchescaseinsensitive→ countMatchesCaseInsensitivere2multimatchany→ multiMatchAnyre2multimatchanyindex→ multiMatchAnyIndexre2multimatchallindices→ multiMatchAllIndices
intarray
One intarray function pushes down to ClickHouse:idx→ indexOf
fuzzystrmatch
Two fuzzystrmatch functions push down to ClickHouse:soundex: soundexlevenshtein(2-arg): editDistanceUTF8
pgvector
These pgvector operators and functions on thevector type push down to
ClickHouse:
<->,l2_distance: L2Distance<=>,cosine_distance: cosineDistance<#>: negated dotProductinner_product: dotProduct<+>,l1_distance: L1Distancevector_dims: length=: equals<>: notEquals
LIMIT to ClickHouse,
along with any WHERE conditions:
EXPLAIN (VERBOSE) shows the remote query:
<#> returns the negative inner product, so pg_clickhouse sorts
it as dotProduct(...) DESC, the form ClickHouse vector similarity indexes
serve. <#> and inner_product require ClickHouse 23.8 or later, where
dotProduct accepts arrays; earlier versions evaluate them in Postgres.
ClickHouse computes distances of Array(Float32) columns in Float32, as
pgvector does.
A tiebreaker, such as ORDER BY embedding <=> '[...]', id, also pushes
down, but ClickHouse does not use a vector similarity index for a sort with
more than one key.
For a nearest neighbor search, EXPLAIN shows the distance function and the
ClickHouse vector similarity index that can serve it. If no index can, it
says why:
EXPLAIN reads index definitions from ClickHouse’s
system.data_skipping_indices table. If that lookup fails, EXPLAIN
continues without index details. ClickHouse can’t use an index, and instead
compares every row, when:
- The index uses a different distance function than the search
- The column has no index
- The sort is descending, or has more than one key
- The query has no
LIMIT, or one overmax_limit_for_vector_search_queries - A
WHEREcondition doesn’t push down, so neither does theLIMIT - The server is older than ClickHouse 25.8
EXPLAIN names an index, ClickHouse
may choose to filter rows first and compare them exactly, for example when a
WHERE condition matches the primary key.
Query planning also issues a warning when a pgvector nearest neighbor search
cannot use a ClickHouse vector similarity index because of its
ORDER BY or
LIMIT. Use EXPLAIN for additional detail. Set
pg_clickhouse.vector_planner_warnings
to false to silence these warnings.vector constants to Array(Float32), so equality
compares the same Float32 values ClickHouse stores in Array(Float32)
columns. vector_norm, l2_normalize, subvector, and other pgvector
functions do not push down: ClickHouse’s equivalents differ for zero
vectors, negative offsets, or older versions. See Vector Types
under Data Types for supported column types.
pgcrypto
digest(text, text)anddigest(bytea, text): Corresponding ClickHouse hash function when the algorithm is a constantmd5,sha1,sha224,sha256,sha384, orsha512(matched case-insensitively).
Pushdown casts
pg_clickhouse pushes down casts such asCAST(x AS bigint) for compatible
data types. For incompatible types the pushdown will fail; if x in this
example is a ClickHouse UInt64, ClickHouse will refuse to cast the value.
In order to push down casts to incompatible data types, pg_clickhouse provides
the following functions. They raise an exception in PostgreSQL if they’re not
pushed down.
Pushdown aggregates
These PostgreSQL aggregate functions pushdown to ClickHouse.- any_value
- array_agg
- avg
- bit_and
- bit_or
- bit_xor
- bool_and / every
- bool_or
- count
- corr
- covarpop
- covarsamp
- min
- max
- stddev_pop
- stddev_samp / stddev
- string_agg
- sum
- var_op
- var_samp /variance
Custom aggregates
These custom aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse aggregate functions with no PostgreSQL equivalents. If any of these functions can’t be pushed down they will raise an exception.Pushdown ordered set aggregates
These ordered-set aggregate functions map to ClickHouse parametric aggregate functions by passing their direct argument as a parameter and theirORDER BY expressions as arguments. For example, this PostgreSQL query:
ORDER BY suffixes DESC and NULLS FIRST
aren’t supported and will raise an error.
percentile_cont(double): quantilepercentile_cont(double[]): quantilespercentile_disc(double): quantileExactLowpercentile_disc(double[]): quantilesExactLow
Custom Ordered Set Aggregates
These custom ordered-set aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse parametric aggregate functions. If any of these functions cannot be pushed down they will raise an exception.quantile(double): quantilequantileExact(double): quantileExact
Custom Ordered Set Aggregates
These custom ordered-set aggregate functions created by pg_clickhouse provide foreign query pushdown for select ClickHouse parametric aggregate functions. If any of these functions cannot be pushed down they will raise an exception.Pushdown window functions
These PostgreSQL window functions push down to ClickHouse withOVER (PARTITION BY ... ORDER BY ...) clauses, including frame specifications
where applicable.
- row_number
- rank
- dense_rank
- ntile
- cume_dist
- percent_rank
- lead
- lag
- first_value
- last_value
- nth_value
min/max(withOVERclause)
row_number, rank, dense_rank, ntile, cume_dist,
percent_rank) omit their frame clause during pushdown because ClickHouse
rejects frame specifications on these functions.
Compatibility notes
Regular expressions
While pg_clickhouse pushes down regular expressions to ClickHouse equivalents when pg_clickhouse.pushdown_regex is true (the default), and makes an effort to ensure a basic level of compatibility, be aware of the differences between the two and how pg_clickhouse handles them.-
PostgreSQL supports POSIX Regular Expressions while ClickHouse supports
RE2 Regular Expressions. Beware of differences in behavior: write RE2
when the regular expression will be evaluated by ClickHouse (e.g., in a
WHEREclause) and POSIX when it will be evaluated by Postgres (e.g., in aSELECTclause). -
pg_clickhouse pushes down the Postgres flags by prepending them to
ClickHouse regular expression inside
(?). For example:Becomes -
The only flags both support, and therefore can be used when evaluated by
ClickHouse, are:
RE2 supports only these flags; don’t use any other Postgres flags.
-
This table summarizes the affects of the various flags (and no flag, which
is the same as
s) when matching newlines and line endings. Note that in Postgres,mandpprevent negated character classes ([^xyz]) from matching a newline, while the ClickHouse equivalents do not. Otherwise, the behaviors are the same in ClickHouse as in Postgres: - Any other flags passed to regular expression functions will prevent pushdown of the function.
-
The exception is
regexp_replace(), which also supports thegflag. Whengis set, pg_clickhouse usesreplaceRegexpAll()instead ofreplaceRegexpOne()and removes the flag before prepending other flags. -
The replacement argument to Postgres
regexp_replace()supports\&to refer to the entire match, while in ClickHouse supports\0for the entire match. Be sure to use\0when the function pushes down to ClickHouse. -
Postgres
regexp_matchreturnsNULLwhen there are no matches, while the expressions it pushes down to return an empty array. UseCOALESCE()to return an empty array instead ofNULLto compare return values compatibly. For example:
to_char()
PostgreSQL to_char() for timestamp and timestamp with time zone pushes
down to ClickHouse formatDateTime only when the format argument is a
non-NULL string constant whose every PostgreSQL keyword has a byte-for-byte
identical ClickHouse equivalent. If the format is dynamic or contains any
unsupported keyword or modifier, the call falls back to local evaluation in
PostgreSQL. pg_clickhouse never pushes down a partial translation, so output
remains compatible.
Two-argument to_char() forms over numeric, interval, and other
non-timestamp types never push down; ClickHouse formatDateTime only formats
date-time values.
Translated keywords
Quoted text and literals
Text wrapped in"..." passes through verbatim, with any literal % doubled
to %% to escape ClickHouse’s specifier prefix. A \" outside quotes also
passes through as a literal ". Inside "...", backslash only escapes ";
other backslash sequences are treated as literal text.