SET datestyle = 'ISO'; SELECT pgch_version() ~ '^\d+\.\d+\.\d+$'; ?column? ---------- t (1 row) CREATE SERVER functions_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'functions_test', driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER functions_loopback; CREATE SERVER functions_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER functions_admin; CALL clickhouse_perform('functions_admin', 'DROP DATABASE IF EXISTS functions_test'); CALL clickhouse_perform('functions_admin', 'CREATE DATABASE functions_test'); CALL clickhouse_perform('functions_admin', $$ CREATE TABLE functions_test.t1 (a int, b int, c DateTime) ENGINE = MergeTree ORDER BY (a); $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t1 VALUES (1, 1, '2019-01-01 10:00:00'), (2, 2, '2019-01-02 10:00:00'), (2, 2, '2019-01-02 11:00:00'), (2, 3, '2019-01-02 10:00:00') $$); CALL clickhouse_perform('functions_admin', $$ drop dictionary if exists functions_test.t3_dict $$); CALL clickhouse_perform('functions_admin', ' create table functions_test.t3 (a Int32, b Nullable(Int32)) engine = MergeTree() order by a'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t3_map (key1 Int32, key2 String, val String) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t4 (val String) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t5 (ts DateTime) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t6 (i64 Int64, f64 Float64) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t7(dt Date) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', 'CREATE TABLE functions_test.t8 (ts DateTime) engine=TinyLog();'); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t5 VALUES ('2025-10-15T20:12:25'), ('2026-11-16T32:13:26'), ('2027-12-17T22:14:27'), ('2028-01-18T23:15:28'), ('2029-02-19T01:16:29'), ('2030-03-20T02:16:30') $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t7 VALUES ('2025-10-15'), ('2024-11-16'), ('2023-12-17'), ('2022-01-18'), ('2021-02-19'), ('2020-03-20') $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t8 VALUES ('2026-08-02T12:00:00'), ('2026-08-03T12:00:00'), ('2026-08-04T12:00:00'), ('2026-08-05T12:00:00'), ('2026-08-06T12:00:00'), ('2026-08-07T12:00:00'), ('2026-08-08T12:00:00') $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t6 VALUES (20423, 20423.123), (2042323443, 2042323443.232), (0, 0), (1774996811, 1774996811.8384), $$); CREATE FOREIGN TABLE t1 (a int, b int, c timestamp) SERVER functions_loopback; CREATE FOREIGN TABLE t2 (a int, b int, c timestamp with time zone) SERVER functions_loopback OPTIONS (table_name 't1'); CREATE FOREIGN TABLE t3 (a int, b int) SERVER functions_loopback; CREATE FOREIGN TABLE t3_map (key1 int, key2 text, val text) SERVER functions_loopback; CREATE FOREIGN TABLE t4 (val text) SERVER functions_loopback; CREATE FOREIGN TABLE t5 (ts timestamp) SERVER functions_loopback; CREATE FOREIGN TABLE t6 (i64 BIGINT, f64 FLOAT8) SERVER functions_loopback; CREATE FOREIGN TABLE t7 (ts date) SERVER functions_loopback; CREATE FOREIGN TABLE t8 (ts timestamp) SERVER functions_loopback; CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t3 SELECT number+1, number+2 FROM numbers(10); $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t3_map SELECT toString(number+1), 'key' || toString(number+1), 'val' || toString(number+1) FROM numbers(10); $$); CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t4 SELECT 'val' || toString(number+1) FROM numbers(2); $$); CALL clickhouse_perform('functions_admin', $$ create dictionary functions_test.t3_dict (key1 Int32, key2 String, val String) primary key key1, key2 source(clickhouse(host '127.0.0.1' port 9000 db 'functions_test' table 't3_map' user 'default' password '')) layout(complex_key_hashed()) lifetime(10); $$); -- check coalesce((cast as Nullable... EXPLAIN (VERBOSE, COSTS OFF) SELECT coalesce(a::text, b::text, c::text) FROM t1 GROUP BY a, b, c; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: COALESCE((a)::text, (b)::text, (c)::text), a, b, c Relations: Aggregate on (t1) Remote SQL: SELECT COALESCE(CAST(a AS Nullable(String)), CAST(b AS Nullable(String)), CAST(c AS String)), a, b, c FROM functions_test.t1 GROUP BY a, b, c (4 rows) SELECT coalesce(a::text, b::text, c::text) FROM t1 GROUP BY a, b, c; coalesce ---------- 2 1 2 2 (4 rows) -- check IN functions EXPLAIN (VERBOSE, COSTS OFF) SELECT a, sum(b) FROM t1 WHERE a IN (1,2,3) GROUP BY a; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan Output: a, (sum(b)) Relations: Aggregate on (t1) Remote SQL: SELECT a, sum(b) FROM functions_test.t1 WHERE ((a IN (1,2,3))) GROUP BY a (4 rows) SELECT a, sum(b) FROM t1 WHERE a IN (1,2,3) GROUP BY a; a | sum ---+----- 2 | 7 1 | 1 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT a, sum(b) FROM t1 WHERE a NOT IN (1,2,3) GROUP BY a; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan Output: a, (sum(b)) Relations: Aggregate on (t1) Remote SQL: SELECT a, sum(b) FROM functions_test.t1 WHERE ((a NOT IN (1,2,3))) GROUP BY a (4 rows) SELECT a, sum(b) FROM t1 WHERE a NOT IN (1,2,3) GROUP BY a; a | sum ---+----- (0 rows) -- check aggregates. EXPLAIN (VERBOSE, COSTS OFF) SELECT argMin(a, b) FROM t1; QUERY PLAN ---------------------------------------------------------- Foreign Scan Output: (argmin(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT argMin(a, b) FROM functions_test.t1 (4 rows) SELECT argMin(a, b) FROM t1; argmin -------- 1 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT argMax(a, b) FROM t1; QUERY PLAN ---------------------------------------------------------- Foreign Scan Output: (argmax(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT argMax(a, b) FROM functions_test.t1 (4 rows) SELECT argMax(a, b) FROM t1; argmax -------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT argMin(a, c) FROM t1; QUERY PLAN ---------------------------------------------------------- Foreign Scan Output: (argmin(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT argMin(a, c) FROM functions_test.t1 (4 rows) SELECT argMin(a, c) FROM t1; argmin -------- 1 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT argMax(a, c) FROM t1; QUERY PLAN ---------------------------------------------------------- Foreign Scan Output: (argmax(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT argMax(a, c) FROM functions_test.t1 (4 rows) SELECT argMax(a, c) FROM t1; argmax -------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqExact(a) FROM t1; QUERY PLAN ---------------------------------------------------------- Foreign Scan Output: (uniqexact(a)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqExact(a) FROM functions_test.t1 (4 rows) SELECT uniqExact(a) FROM t1; uniqexact ----------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqExact(a) FILTER(WHERE b>1) FROM t1; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan Output: (uniqexact(a) FILTER (WHERE (b > 1))) Relations: Aggregate on (t1) Remote SQL: SELECT uniqExactIf(a,(((b > 1)) > 0)) FROM functions_test.t1 (4 rows) SELECT uniqExact(a) FILTER(WHERE b>1) FROM t1; uniqexact ----------- 1 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqExact(a, b) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqexact(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqExact(a, b) FROM functions_test.t1 (4 rows) SELECT uniq(a, b) FROM t1; uniq ------ 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniq(a, c) FROM t1; QUERY PLAN -------------------------------------------------------- Foreign Scan Output: (uniq(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniq(a, c) FROM functions_test.t1 (4 rows) SELECT uniq(a, c) FROM t1; uniq ------ 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqExact(a, b) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqexact(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqExact(a, b) FROM functions_test.t1 (4 rows) SELECT uniqExact(a, b) FROM t1; uniqexact ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqExact(a, c) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqexact(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqExact(a, c) FROM functions_test.t1 (4 rows) SELECT uniqExact(a, c) FROM t1; uniqexact ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqCombined(a, b) FROM t1; QUERY PLAN ---------------------------------------------------------------- Foreign Scan Output: (uniqcombined(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqCombined(a, b) FROM functions_test.t1 (4 rows) SELECT uniqCombined(a, b) FROM t1; uniqcombined -------------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqCombined(a, c) FROM t1; QUERY PLAN ---------------------------------------------------------------- Foreign Scan Output: (uniqcombined(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqCombined(a, c) FROM functions_test.t1 (4 rows) SELECT uniqCombined(a, c) FROM t1; uniqcombined -------------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqCombined64(a, b) FROM t1; QUERY PLAN ------------------------------------------------------------------ Foreign Scan Output: (uniqcombined64(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqCombined64(a, b) FROM functions_test.t1 (4 rows) SELECT uniqCombined64(a, b) FROM t1; uniqcombined64 ---------------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqCombined64(a, c) FROM t1; QUERY PLAN ------------------------------------------------------------------ Foreign Scan Output: (uniqcombined64(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqCombined64(a, c) FROM functions_test.t1 (4 rows) SELECT uniqCombined64(a, c) FROM t1; uniqcombined64 ---------------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqHLL12(a, b) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqhll12(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqHLL12(a, b) FROM functions_test.t1 (4 rows) SELECT uniqHLL12(a, b) FROM t1; uniqhll12 ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqHLL12(a, c) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqhll12(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqHLL12(a, c) FROM functions_test.t1 (4 rows) SELECT uniqHLL12(a, c) FROM t1; uniqhll12 ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqTheta(a, b) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqtheta(a, b)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqTheta(a, b) FROM functions_test.t1 (4 rows) SELECT uniqTheta(a, b) FROM t1; uniqtheta ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT uniqTheta(a, c) FROM t1; QUERY PLAN ------------------------------------------------------------- Foreign Scan Output: (uniqtheta(a, c)) Relations: Aggregate on (t1) Remote SQL: SELECT uniqTheta(a, c) FROM functions_test.t1 (4 rows) SELECT uniqTheta(a, c) FROM t1; uniqtheta ----------- 3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT quantile(0.25) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN -------------------------------------------------------------------------- Foreign Scan Output: (quantile('0.25'::double precision) WITHIN GROUP (ORDER BY a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantile(0.25)(a) FROM functions_test.t1 (4 rows) SELECT quantile(0.25) WITHIN GROUP (ORDER BY a) FROM t1; quantile ---------- 1.75 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT quantile(a) FROM t1; QUERY PLAN --------------------------------------------------------- Foreign Scan Output: (quantile(a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantile(a) FROM functions_test.t1 (4 rows) SELECT quantile(a) FROM t1; quantile ---------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT quantileExact(0.75) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan Output: (quantileexact('0.75'::double precision) WITHIN GROUP (ORDER BY a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantileExact(0.75)(a) FROM functions_test.t1 (4 rows) SELECT quantileExact(0.75) WITHIN GROUP (ORDER BY a) FROM t1; quantileexact --------------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT quantileExact(a) FROM t1; QUERY PLAN -------------------------------------------------------------- Foreign Scan Output: (quantileexact(a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantileExact(a) FROM functions_test.t1 (4 rows) SELECT quantileExact(a) FROM t1; quantileexact --------------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((a)::double precision))) Relations: Aggregate on (t1) Remote SQL: SELECT quantile(0.25)(a) FROM functions_test.t1 (4 rows) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FROM t1; percentile_cont ----------------- 1.75 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (percentile_cont('0.95'::double precision) WITHIN GROUP (ORDER BY (date_part('epoch'::text, timezone('UTC'::text, c))))) Relations: Aggregate on (t1) Remote SQL: SELECT quantile(0.95)(toUnixTimestamp(toTimeZone(c, 'UTC'))) FROM functions_test.t1 (4 rows) SELECT percentile_cont(0.95) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; percentile_cont ----------------- 1546426260 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (percentile_cont('0.25'::double precision) WITHIN GROUP (ORDER BY ((a)::double precision)) FILTER (WHERE (b = 1))) Relations: Aggregate on (t1) Remote SQL: SELECT quantileIf(0.25)(a,(((b = 1)) > 0)) FROM functions_test.t1 (4 rows) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; percentile_cont ----------------- 1 (1 row) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a NULLS LAST) FROM t1; percentile_cont ----------------- 1.75 (1 row) SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a DESC) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "DESC" in aggregate expressions SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a NULLS FIRST) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "NULLS FIRST" in aggregate expressions SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY a USING >) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "DESC" in aggregate expressions EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_cont('{0.25,0.5,0.75}'::double precision[]) WITHIN GROUP (ORDER BY ((a)::double precision))) Relations: Aggregate on (t1) Remote SQL: SELECT quantiles(0.25,0.5,0.75)(a) FROM functions_test.t1 (4 rows) SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FROM t1; percentile_cont ----------------- {1.75,2,2} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(ARRAY[0.9, 0.95, 0.99]) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_cont('{0.9,0.95,0.99}'::double precision[]) WITHIN GROUP (ORDER BY (date_part('epoch'::text, timezone('UTC'::text, c))))) Relations: Aggregate on (t1) Remote SQL: SELECT quantiles(0.9,0.95,0.99)(toUnixTimestamp(toTimeZone(c, 'UTC'))) FROM functions_test.t1 (4 rows) SELECT percentile_cont(ARRAY[0.9, 0.95, 0.99]) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; percentile_cont ------------------------------------ {1546425720,1546426260,1546426692} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_cont('{0.25,0.5,0.75}'::double precision[]) WITHIN GROUP (ORDER BY ((a)::double precision)) FILTER (WHERE (b = 1))) Relations: Aggregate on (t1) Remote SQL: SELECT quantilesIf(0.25,0.5,0.75)(a,(((b = 1)) > 0)) FROM functions_test.t1 (4 rows) SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; percentile_cont ----------------- {1,1,1} (1 row) SELECT percentile_cont(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a NULLS LAST) FROM t1; percentile_cont ----------------- {1.75,2,2} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan Output: (percentile_disc('0.25'::double precision) WITHIN GROUP (ORDER BY a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantileExactLow(0.25)(a) FROM functions_test.t1 (4 rows) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a) FROM t1; percentile_disc ----------------- 2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (percentile_disc('0.95'::double precision) WITHIN GROUP (ORDER BY (date_part('epoch'::text, timezone('UTC'::text, c))))) Relations: Aggregate on (t1) Remote SQL: SELECT quantileExactLow(0.95)(toUnixTimestamp(toTimeZone(c, 'UTC'))) FROM functions_test.t1 (4 rows) SELECT percentile_disc(0.95) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; percentile_disc ----------------- 1546426800 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; QUERY PLAN -------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_disc('0.25'::double precision) WITHIN GROUP (ORDER BY a) FILTER (WHERE (b = 1))) Relations: Aggregate on (t1) Remote SQL: SELECT quantileExactLowIf(0.25)(a,(((b = 1)) > 0)) FROM functions_test.t1 (4 rows) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; percentile_disc ----------------- 1 (1 row) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a NULLS LAST) FROM t1; percentile_disc ----------------- 2 (1 row) SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a DESC) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "DESC" in aggregate expressions SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a NULLS FIRST) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "NULLS FIRST" in aggregate expressions SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY a USING >) FROM t1; ERROR: pg_clickhouse: ClickHouse does not support "DESC" in aggregate expressions EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FROM t1; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_disc('{0.25,0.5,0.75}'::double precision[]) WITHIN GROUP (ORDER BY a)) Relations: Aggregate on (t1) Remote SQL: SELECT quantilesExactLow(0.25,0.5,0.75)(a) FROM functions_test.t1 (4 rows) SELECT percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FROM t1; percentile_disc ----------------- {2,2,2} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(ARRAY[0.9, 0.95, 0.99]) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_disc('{0.9,0.95,0.99}'::double precision[]) WITHIN GROUP (ORDER BY (date_part('epoch'::text, timezone('UTC'::text, c))))) Relations: Aggregate on (t1) Remote SQL: SELECT quantilesExactLow(0.9,0.95,0.99)(toUnixTimestamp(toTimeZone(c, 'UTC'))) FROM functions_test.t1 (4 rows) SELECT percentile_disc(ARRAY[0.9, 0.95, 0.99]) WITHIN GROUP (ORDER BY date_part('epoch', timezone('UTC', c))) FROM t1; percentile_disc ------------------------------------ {1546426800,1546426800,1546426800} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (percentile_disc('{0.25,0.5,0.75}'::double precision[]) WITHIN GROUP (ORDER BY a) FILTER (WHERE (b = 1))) Relations: Aggregate on (t1) Remote SQL: SELECT quantilesExactLowIf(0.25,0.5,0.75)(a,(((b = 1)) > 0)) FROM functions_test.t1 (4 rows) SELECT percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a) FILTER (WHERE b = 1) FROM t1; percentile_disc ----------------- {1,1,1} (1 row) SELECT percentile_disc(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY a NULLS LAST) FROM t1; percentile_disc ----------------- {2,2,2} (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_trunc('dAy', c at time zone 'UTC') as d1 FROM t1 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_trunc('dAy'::text, (c AT TIME ZONE 'UTC'::text))) Relations: Aggregate on (t1) Remote SQL: SELECT toStartOfDay(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toStartOfDay(toTimeZone(c, 'UTC'))) ORDER BY toStartOfDay(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_trunc('day', c at time zone 'UTC') as d1 FROM t1 GROUP BY d1 ORDER BY d1; d1 ------------------------ 2018-12-31 16:00:00-08 2019-01-01 16:00:00-08 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_trunc('day', c at time zone 'UTC') as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_trunc('day'::text, (c AT TIME ZONE 'UTC'::text))) Relations: Aggregate on (t2) Remote SQL: SELECT toStartOfDay(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toStartOfDay(toTimeZone(c, 'UTC'))) ORDER BY toStartOfDay(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_trunc('day', c at time zone 'UTC') as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 --------------------- 2018-12-31 16:00:00 2019-01-01 16:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('day'::text, timezone('UTC'::text, c)) as d1 FROM t1 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('day'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t1) Remote SQL: SELECT toDayOfMonth(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toDayOfMonth(toTimeZone(c, 'UTC'))) ORDER BY toDayOfMonth(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_part('day'::text, timezone('UTC'::text, c)) as d1 FROM t1 GROUP BY d1 ORDER BY d1; d1 ---- 1 2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('day'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('day'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t2) Remote SQL: SELECT toDayOfMonth(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toDayOfMonth(toTimeZone(c, 'UTC'))) ORDER BY toDayOfMonth(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_part('day'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 ---- 1 2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('doy'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('doy'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t2) Remote SQL: SELECT toDayOfYear(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toDayOfYear(toTimeZone(c, 'UTC'))) ORDER BY toDayOfYear(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_part('doy'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 ---- 1 2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('dow'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('dow'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t2) Remote SQL: SELECT toDayOfWeek(toTimeZone(c, 'UTC'), 2) FROM functions_test.t1 GROUP BY (toDayOfWeek(toTimeZone(c, 'UTC'), 2)) ORDER BY toDayOfWeek(toTimeZone(c, 'UTC'), 2) ASC NULLS LAST (4 rows) SELECT date_part('dow'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 ---- 2 3 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('minuTe'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('minuTe'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t2) Remote SQL: SELECT toMinute(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toMinute(toTimeZone(c, 'UTC'))) ORDER BY toMinute(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_part('minuTe'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 ---- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_trunc('SeCond', c at time zone 'UTC') as d1 FROM t1 GROUP BY d1 ORDER BY d1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_trunc('SeCond'::text, (c AT TIME ZONE 'UTC'::text))) Relations: Aggregate on (t1) Remote SQL: SELECT toStartOfSecond(toDateTime64(toTimeZone(c, 'UTC'), 1)) FROM functions_test.t1 GROUP BY (toStartOfSecond(toDateTime64(toTimeZone(c, 'UTC'), 1))) ORDER BY toStartOfSecond(toDateTime64(toTimeZone(c, 'UTC'), 1)) ASC NULLS LAST (4 rows) SELECT date_trunc('SeCond', c at time zone 'UTC') as d1 FROM t1 GROUP BY d1 ORDER BY d1; d1 ------------------------ 2019-01-01 02:00:00-08 2019-01-02 02:00:00-08 2019-01-02 03:00:00-08 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('ePoch'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('ePoch'::text, timezone('UTC'::text, c))) Relations: Aggregate on (t2) Remote SQL: SELECT toUnixTimestamp(toTimeZone(c, 'UTC')) FROM functions_test.t1 GROUP BY (toUnixTimestamp(toTimeZone(c, 'UTC'))) ORDER BY toUnixTimestamp(toTimeZone(c, 'UTC')) ASC NULLS LAST (4 rows) SELECT date_part('ePoch'::text, timezone('UTC'::text, c)) as d1 FROM t2 GROUP BY d1 ORDER BY d1; d1 ------------ 1546336800 1546423200 1546426800 (3 rows) -- Dynamic unit argument cannot push down, stays local. EXPLAIN (VERBOSE, COSTS OFF) SELECT date_trunc(t4.val, t5.ts) FROM t4, t5 LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: date_trunc(t4.val, t5.ts) Relations: (t4) INNER JOIN (t5) Remote SQL: SELECT r1.val, r2.ts FROM functions_test.t4 r1 ALL INNER JOIN functions_test.t5 r2 ON (TRUE) LIMIT 1 (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part(t4.val, t5.ts) FROM t4, t5 LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: date_part(t4.val, t5.ts) Relations: (t4) INNER JOIN (t5) Remote SQL: SELECT r1.val, r2.ts FROM functions_test.t4 r1 ALL INNER JOIN functions_test.t5 r2 ON (TRUE) LIMIT 1 (4 rows) -- Unsupported non-ASCII unit raises a clean error. SELECT date_trunc('É'::text, ts) FROM t5 LIMIT 1; ERROR: unit "É" not recognized for type timestamp without time zone EXPLAIN (VERBOSE, COSTS OFF) SELECT ltrim(val) AS a, btrim(val) AS b, rtrim(val) AS c FROM t4 GROUP BY a,b,c ORDER BY a; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (ltrim(val)), (btrim(val)), (rtrim(val)) Relations: Aggregate on (t4) Remote SQL: SELECT ltrim(val), trimBoth(val), rtrim(val) FROM functions_test.t4 GROUP BY (ltrim(val)), (trimBoth(val)), (rtrim(val)) ORDER BY ltrim(val) ASC NULLS LAST (4 rows) SELECT ltrim(val) AS a, btrim(val) AS b, rtrim(val) AS c FROM t4 GROUP BY a,b,c ORDER BY a; a | b | c ------+------+------ val1 | val1 | val1 val2 | val2 | val2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ltrim(val, 'av') AS a, btrim(val, '1l2') AS b, rtrim(val, 'l1') AS c FROM t4 GROUP BY a,b,c ORDER BY a; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (ltrim(val, 'av'::text)), (btrim(val, '1l2'::text)), (rtrim(val, 'l1'::text)) Relations: Aggregate on (t4) Remote SQL: SELECT ltrim(val, 'av'), trimBoth(val, '1l2'), rtrim(val, 'l1') FROM functions_test.t4 GROUP BY (ltrim(val, 'av')), (trimBoth(val, '1l2')), (rtrim(val, 'l1')) ORDER BY ltrim(val, 'av') ASC NULLS LAST (4 rows) SELECT ltrim(val, 'av') AS a, btrim(val, '1l2') AS b, rtrim(val, 'l1') AS c FROM t4 GROUP BY a,b,c ORDER BY a; a | b | c ----+----+------ l1 | va | va l2 | va | val2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT strpos(val, 'val') AS a FROM t4 GROUP BY a ORDER BY a; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (strpos(val, 'val'::text)) Relations: Aggregate on (t4) Remote SQL: SELECT positionUTF8(val, 'val') FROM functions_test.t4 GROUP BY (positionUTF8(val, 'val')) ORDER BY positionUTF8(val, 'val') ASC NULLS LAST (4 rows) SELECT strpos(val, 'val') AS a FROM t4 GROUP BY a ORDER BY a; a --- 1 (1 row) --- check dictGet EXPLAIN (VERBOSE, COSTS OFF) SELECT a, dictGet('functions_test.t3_dict', 'val', (a, 'key' || a::text)) as val, sum(b) FROM t3 GROUP BY a, val ORDER BY a; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: a, (dictget('functions_test.t3_dict'::text, 'val'::text, ROW(a, ('key'::text || (a)::text)))), (sum(b)) Relations: Aggregate on (t3) Remote SQL: SELECT a, dictGet('functions_test.t3_dict', 'val', (a,('key' || CAST(a AS String)))), sum(b) FROM functions_test.t3 GROUP BY a, (dictGet('functions_test.t3_dict', 'val', (a,('key' || CAST(a AS String))))) ORDER BY a ASC NULLS LAST (4 rows) SELECT a, dictGet('functions_test.t3_dict', 'val', (a, 'key' || a::text)) as val, sum(b) FROM t3 GROUP BY a, val ORDER BY a LIMIT 3; a | val | sum ---+------+----- 1 | val1 | 2 2 | val2 | 3 3 | val3 | 4 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT a, dictGet('functions_test.t3_dict', 'val', (1, 'key' || a::text)) as val, sum(b) FROM t3 GROUP BY a, val ORDER BY a; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: a, (dictget('functions_test.t3_dict'::text, 'val'::text, ROW(1, ('key'::text || (a)::text)))), (sum(b)) Relations: Aggregate on (t3) Remote SQL: SELECT a, dictGet('functions_test.t3_dict', 'val', (cast(1 as Int32),('key' || CAST(a AS String)))), sum(b) FROM functions_test.t3 GROUP BY a, (dictGet('functions_test.t3_dict', 'val', (cast(1 as Int32),('key' || CAST(a AS String))))) ORDER BY a ASC NULLS LAST (4 rows) SELECT a, dictGet('functions_test.t3_dict', 'val', (1, 'key' || a::text)) as val, sum(b) FROM t3 GROUP BY a, val ORDER BY a LIMIT 3; a | val | sum ---+------+----- 1 | val1 | 2 2 | | 3 3 | | 4 (3 rows) -- Check date_part mappings. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('year', ts) = '2027'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toYear(ts) = 2027)) (3 rows) SELECT ts FROM t5 WHERE date_part('year', ts) = '2027'; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('month', ts) = '11'; QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMonth(ts) = 11)) (3 rows) SELECT ts FROM t5 WHERE date_part('month', ts) = '11'; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('day', ts) = '18'; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfMonth(ts) = 18)) (3 rows) SELECT ts FROM t5 WHERE date_part('day', ts) = '18'; ts --------------------- 2028-01-18 15:15:28 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('hour', ts) = '20'; QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toHour(ts) = 20)) (3 rows) SELECT ts FROM t5 WHERE date_part('hour', ts) = '20'; ts --------------------- 2025-10-15 13:12:25 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('minute', ts) = '16'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMinute(ts) = 16)) (3 rows) SELECT ts FROM t5 WHERE date_part('minute', ts) = '16'; ts --------------------- 2029-02-18 17:16:29 2030-03-19 19:16:30 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('second', ts) = '26'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toSecond(ts) = 26)) (3 rows) SELECT ts FROM t5 WHERE date_part('second', ts) = '26'; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('doy', ts) = '351'; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfYear(ts) = 351)) (3 rows) SELECT ts FROM t5 WHERE date_part('doy', ts) = '351'; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('dow', ts) = '2'; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfWeek(ts, 2) = 2)) (3 rows) SELECT ts FROM t5 WHERE date_part('dow', ts) = '2'; ts --------------------- 2026-11-17 00:13:26 2028-01-18 15:15:28 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT date_part('dow', ts) AS dow FROM t8 GROUP BY dow ORDER BY dow; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (date_part('dow'::text, ts)) Relations: Aggregate on (t8) Remote SQL: SELECT toDayOfWeek(ts, 2) FROM functions_test.t8 GROUP BY (toDayOfWeek(ts, 2)) ORDER BY toDayOfWeek(ts, 2) ASC NULLS LAST (4 rows) SELECT date_part('dow', ts) AS dow FROM t8 GROUP BY dow ORDER BY dow; dow ----- 0 1 2 3 4 5 6 (7 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('quarter', ts) = '1'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toQuarter(ts) = 1)) (3 rows) SELECT ts FROM t5 WHERE date_part('quarter', ts) = '1'; ts --------------------- 2028-01-18 15:15:28 2029-02-18 17:16:29 2030-03-19 19:16:30 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('isoyear', ts) = '2025'; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toISOYear(ts) = 2025)) (3 rows) SELECT ts FROM t5 WHERE date_part('isoyear', ts) = '2025'; ts --------------------- 2025-10-15 13:12:25 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('week', ts) = '47'; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toISOWeek(ts) = 47)) (3 rows) SELECT ts FROM t5 WHERE date_part('week', ts) = '47'; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('epoch', ts) > '1866158180'; QUERY PLAN ------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toUnixTimestamp(ts) > 1866158180)) (3 rows) SELECT ts FROM t5 WHERE date_part('epoch', ts) > '1866158180'; ts --------------------- 2029-02-18 17:16:29 2030-03-19 19:16:30 (2 rows) -- Check date_part from date. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('year', ts::date) = 2027; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toYear(cast(cast(ts, 'Nullable(Date)'), 'Nullable(DateTime)')) = 2027)) (3 rows) SELECT ts FROM t5 WHERE date_part('year', ts::date) = 2027; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('month', ts::date) = 11; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMonth(cast(cast(ts, 'Nullable(Date)'), 'Nullable(DateTime)')) = 11)) (3 rows) SELECT ts FROM t5 WHERE date_part('month', ts::date) = 11; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_part('day', ts::date) = 18; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfMonth(cast(cast(ts, 'Nullable(Date)'), 'Nullable(DateTime)')) = 18)) (3 rows) SELECT ts FROM t5 WHERE date_part('day', ts::date) = 18; ts --------------------- 2028-01-18 15:15:28 (1 row) -- Check EXTRACT mappings. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(year FROM ts) = 2027; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toYear(ts) = 2027)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(year FROM ts) = 2027; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(month FROM ts) = 11; QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMonth(ts) = 11)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(month FROM ts) = 11; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(day FROM ts) = 18; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfMonth(ts) = 18)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(day FROM ts) = 18; ts --------------------- 2028-01-18 15:15:28 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(hour FROM ts) = 20; QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toHour(ts) = 20)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(hour FROM ts) = 20; ts --------------------- 2025-10-15 13:12:25 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(minute FROM ts) = 16; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMinute(ts) = 16)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(minute FROM ts) = 16; ts --------------------- 2029-02-18 17:16:29 2030-03-19 19:16:30 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(second FROM ts) = 26; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toSecond(ts) = 26)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(second FROM ts) = 26; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(doy FROM ts) = 351; QUERY PLAN -------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfYear(ts) = 351)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(doy FROM ts) = 351; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(dow FROM ts) = 2; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfWeek(ts, 2) = 2)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(dow FROM ts) = 2; ts --------------------- 2026-11-17 00:13:26 2028-01-18 15:15:28 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(quarter FROM ts) = 1; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toQuarter(ts) = 1)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(quarter FROM ts) = 1; ts --------------------- 2028-01-18 15:15:28 2029-02-18 17:16:29 2030-03-19 19:16:30 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(isoyear FROM ts) = 2025; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toISOYear(ts) = 2025)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(isoyear FROM ts) = 2025; ts --------------------- 2025-10-15 13:12:25 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(week FROM ts) = 47; QUERY PLAN ----------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toISOWeek(ts) = 47)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(week FROM ts) = 47; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(epoch FROM ts) > 1866158180; QUERY PLAN ------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toUnixTimestamp(ts) > 1866158180)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(epoch FROM ts) > 1866158180; ts --------------------- 2029-02-18 17:16:29 2030-03-19 19:16:30 (2 rows) -- Check extract from date. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(year FROM ts::date) = 2027; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toYear(cast(ts, 'Nullable(Date)')) = 2027)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(year FROM ts::date) = 2027; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(month FROM ts::date) = 11; QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMonth(cast(ts, 'Nullable(Date)')) = 11)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(month FROM ts::date) = 11; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE EXTRACT(day FROM ts::date) = 18; QUERY PLAN -------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toDayOfMonth(cast(ts, 'Nullable(Date)')) = 18)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(day FROM ts::date) = 18; ts --------------------- 2028-01-18 15:15:28 (1 row) -- Check date_trunc mappings. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('year', ts) = '2026-01-01'::date; QUERY PLAN ------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfYear(ts) = '2026-01-01')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('year', ts) = '2026-01-01'::date; ts --------------------- 2026-11-17 00:13:26 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('month', ts) = '2027-12-01'::date; QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfMonth(ts) = '2027-12-01')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('month', ts) = '2027-12-01'::date; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('day', ts) = '2028-01-18'::date; QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfDay(ts) = '2028-01-18')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('day', ts) = '2028-01-18'::date; ts --------------------- 2028-01-18 15:15:28 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('hour', ts) = '2029-02-19T01:00:00'; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfHour(ts) = '2029-02-19 01:00:00')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('hour', ts) = '2029-02-19T01:00:00'; ts --------------------- 2029-02-18 17:16:29 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('minute', ts) = '2025-10-15T20:12:00'; QUERY PLAN ------------------------------------------------------------------------------------------------------ Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfMinute(ts) = '2025-10-15 20:12:00')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('minute', ts) = '2025-10-15T20:12:00'; ts --------------------- 2025-10-15 13:12:25 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('second', ts) = '2027-12-17T22:14:27'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfSecond(toDateTime64(ts, 1)) = '2027-12-17 22:14:27')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('second', ts) = '2027-12-17T22:14:27'; ts --------------------- 2027-12-17 14:14:27 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('week', ts) = '2028-01-17'::date; QUERY PLAN -------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toMonday(ts) = '2028-01-17')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('week', ts) = '2028-01-17'::date; ts --------------------- 2028-01-18 15:15:28 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE date_trunc('quarter', ts) = '2027-10-01'::date; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toStartOfQuarter(ts) = '2027-10-01')) (3 rows) SELECT ts FROM t5 WHERE date_trunc('quarter', ts) = '2027-10-01'::date; ts --------------------- 2027-12-17 14:14:27 (1 row) -- Check hashing functions. EXPLAIN (VERBOSE, COSTS OFF) SELECT key1, val FROM t3_map WHERE md5(val) LIKE 'a%' ORDER BY key1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t3_map Output: key1, val Remote SQL: SELECT key1, val FROM functions_test.t3_map WHERE ((lower(hex(MD5(val))) LIKE 'a%')) ORDER BY key1 ASC NULLS LAST (3 rows) SELECT key1 FROM t3_map WHERE md5(val) LIKE 'a%' ORDER BY key1; key1 ------ 4 9 (2 rows) -- Check to_timestamp(float8). EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t6 WHERE to_timestamp(i64) = to_timestamp(0); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64, f64 Remote SQL: SELECT i64, f64 FROM functions_test.t6 WHERE ((toDateTime64(i64, 6, 'UTC') = '1970-01-01 00:00:00')) (3 rows) SELECT * FROM t6 WHERE to_timestamp(i64) = to_timestamp(0); i64 | f64 -----+----- 0 | 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t6 WHERE to_timestamp(f64) = to_timestamp(0); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64, f64 Remote SQL: SELECT i64, f64 FROM functions_test.t6 WHERE ((toDateTime64(f64, 6, 'UTC') = '1970-01-01 00:00:00')) (3 rows) SELECT * FROM t6 WHERE to_timestamp(f64) = to_timestamp(0); i64 | f64 -----+----- 0 | 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t6 WHERE to_timestamp(i64) = to_timestamp(0); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64, f64 Remote SQL: SELECT i64, f64 FROM functions_test.t6 WHERE ((toDateTime64(i64, 6, 'UTC') = '1970-01-01 00:00:00')) (3 rows) SELECT * FROM t6 WHERE to_timestamp(i64) = to_timestamp(2042323443); i64 | f64 ------------+---------------- 2042323443 | 2042323443.232 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t6 WHERE to_timestamp(f64) = to_timestamp(2042323443); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64, f64 Remote SQL: SELECT i64, f64 FROM functions_test.t6 WHERE ((toDateTime64(f64, 6, 'UTC') = '2034-09-20 00:04:03')) (3 rows) SELECT * FROM t6 WHERE to_timestamp(f64) = to_timestamp(2042323443); i64 | f64 -----+----- (0 rows) SELECT * FROM t6 WHERE to_timestamp(f64) = to_timestamp(2042323443.232); i64 | f64 ------------+---------------- 2042323443 | 2042323443.232 (1 row) -- Check current_*-type functions. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < NOW(); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < now64())) (3 rows) SELECT * FROM t1 WHERE c < NOW() ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < statement_timestamp(); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < nowInBlock64(6, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < statement_timestamp() ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < transaction_timestamp(); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < nowInBlock64(6, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < transaction_timestamp() ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < clock_timestamp(); QUERY PLAN ---------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < nowInBlock64(6, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < clock_timestamp() ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) -- Check SQL Value functions. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < CURRENT_DATE; QUERY PLAN ------------------------------------------------------------------------------------------------------ Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < toDate(now('America/Los_Angeles')))) (3 rows) SELECT * FROM t1 WHERE c < CURRENT_DATE ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < CURRENT_TIMESTAMP; QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < now64(6, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < CURRENT_TIMESTAMP ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < CURRENT_TIMESTAMP(3); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < now64(3, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < CURRENT_TIMESTAMP(3) ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < LOCALTIMESTAMP; QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < now64(6, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < LOCALTIMESTAMP ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE c < LOCALTIMESTAMP(3); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((c < now64(3, 'America/Los_Angeles'))) (3 rows) SELECT * FROM t1 WHERE c < LOCALTIMESTAMP(3) ORDER BY a LIMIT 2; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 (2 rows) -- Use with other functions. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE date_part('year', c) < date_part('year', CURRENT_DATE); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((toYear(c) < toYear(cast(toDate(now('America/Los_Angeles')), 'Nullable(DateTime)')))) (3 rows) SELECT * FROM t1 WHERE date_part('year', c) < date_part('year', CURRENT_DATE); a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 2 | 2 | 2019-01-02 03:00:00 2 | 3 | 2019-01-02 02:00:00 (4 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE date_trunc('day', c) < date_trunc('day', CURRENT_TIMESTAMP) - INTERVAL '1 day' ORDER BY a LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((toStartOfDay(c) < (toStartOfDay(now64(6, 'America/Los_Angeles')) - INTERVAL 1 DAY))) ORDER BY a ASC NULLS LAST LIMIT 2 (3 rows) SELECT * FROM t1 WHERE date_trunc('day', c) < date_trunc('day', CURRENT_TIMESTAMP) - INTERVAL '1 day' ORDER BY a; a | b | c ---+---+--------------------- 1 | 1 | 2019-01-01 02:00:00 2 | 2 | 2019-01-02 02:00:00 2 | 2 | 2019-01-02 03:00:00 2 | 3 | 2019-01-02 02:00:00 (4 rows) -- Month and compound intervals push down as INTERVAL chains (issue #61). EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE date_trunc('day', c) < date_trunc('day', CURRENT_TIMESTAMP) - INTERVAL '6 months' ORDER BY a LIMIT 2; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((toStartOfDay(c) < (toStartOfDay(now64(6, 'America/Los_Angeles')) - INTERVAL 6 MONTH))) ORDER BY a ASC NULLS LAST LIMIT 2 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE date_trunc('day', c) > date_trunc('day', CURRENT_TIMESTAMP) + INTERVAL '1 month 2 days 3 hours' ORDER BY a LIMIT 2; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((toStartOfDay(c) > (toStartOfDay(now64(6, 'America/Los_Angeles')) + INTERVAL 1 MONTH + INTERVAL 2 DAY + INTERVAL 10800 SECOND))) ORDER BY a ASC NULLS LAST LIMIT 2 (3 rows) \unset ECHO NOTICE: CURRENT_TIME PUSHED DOWN: t NOTICE: CURRENT_TIME(n) PUSHED DOWN: t NOTICE: CURRENT_USER PUSHED DOWN: t NOTICE: USER PUSHED DOWN: t NOTICE: CURRENT_ROLE PUSHED DOWN: t NOTICE: SESSION_USER PUSHED DOWN: t NOTICE: CURRENT_SCHEMA PUSHED DOWN: t NOTICE: CURRENT_SCHEMA() PUSHED DOWN: t NOTICE: CURRENT_CATALOG PUSHED DOWN: t NOTICE: CURRENT_DATABASE() PUSHED DOWN: t SELECT * FROM t4 WHERE val <> CURRENT_USER; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> USER; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> CURRENT_ROLE; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> SESSION_USER; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> CURRENT_SCHEMA; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> CURRENT_SCHEMA(); val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> CURRENT_CATALOG; val ------ val1 val2 (2 rows) SELECT * FROM t4 WHERE val <> CURRENT_DATABASE(); val ------ val1 val2 (2 rows) -- Test concat_ws. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t3_map WHERE concat_ws('/', key2, val) = 'key4/val4'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t3_map Output: key1, key2, val Remote SQL: SELECT key1, key2, val FROM functions_test.t3_map WHERE ((concat_ws('/', key2, val) = 'key4/val4')) (3 rows) SELECT * FROM t3_map WHERE concat_ws('/', key2, val) = 'key4/val4'; key1 | key2 | val ------+------+------ 4 | key4 | val4 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t1 WHERE concat_ws(',', a, b, 'foo', c) = '2,3,foo,2019-01-02 10:00:00'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.t1 Output: a, b, c Remote SQL: SELECT a, b, c FROM functions_test.t1 WHERE ((concat_ws(',', a, b, 'foo', c) = '2,3,foo,2019-01-02 10:00:00')) (3 rows) SELECT * FROM t1 WHERE concat_ws(',', a, b, 'foo', c) = '2,3,foo,2019-01-02 10:00:00'; a | b | c ---+---+--------------------- 2 | 3 | 2019-01-02 02:00:00 (1 row) -- Test fuzzystrmatch pushdown. CREATE EXTENSION IF NOT EXISTS fuzzystrmatch; -- soundex pushes down with same name. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t4 WHERE soundex(val) = 'V400'; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((soundex(val) = 'V400')) (3 rows) SELECT * FROM t4 WHERE soundex(val) = 'V400'; val ------ val1 val2 (2 rows) -- 2-arg levenshtein pushes down as editDistanceUTF8. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t4 WHERE levenshtein(val, 'val1') <= 1; QUERY PLAN ---------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((editDistanceUTF8(val, 'val1') <= 1)) (3 rows) SELECT * FROM t4 WHERE levenshtein(val, 'val1') <= 1; val ------ val1 val2 (2 rows) -- 5-arg levenshtein (custom costs) evaluates locally. EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM t4 WHERE levenshtein(val, 'val1', 1, 1, 2) <= 1; QUERY PLAN ------------------------------------------------------------- Foreign Scan on public.t4 Output: val Filter: (levenshtein(t4.val, 'val1'::text, 1, 1, 2) <= 1) Remote SQL: SELECT val FROM functions_test.t4 (4 rows) DROP EXTENSION fuzzystrmatch; -- to_char: pushdown of formats whose every keyword has a faithful -- formatDateTime equivalent. EXPLAIN (VERBOSE, COSTS OFF) SELECT to_char(ts, 'YYYY-MM-DD HH24:MI:SS') FROM t5 ORDER BY 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: to_char(ts, 'YYYY-MM-DD HH24:MI:SS'::text) Remote SQL: SELECT ts FROM functions_test.t5 ORDER BY formatDateTime(ts, '%Y-%m-%d %H:%i:%S') ASC NULLS LAST (3 rows) SELECT to_char(ts, 'YYYY-MM-DD HH24:MI:SS') FROM t5 ORDER BY 1; to_char --------------------- 2025-10-15 13:12:25 2026-11-17 00:13:26 2027-12-17 14:14:27 2028-01-18 15:15:28 2029-02-18 17:16:29 2030-03-19 19:16:30 (6 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT to_char(ts, 'YY/MM/DD HH12:MI AM Q DDD Mon Dy') FROM t5 ORDER BY 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: to_char(ts, 'YY/MM/DD HH12:MI AM Q DDD Mon Dy'::text) Remote SQL: SELECT ts FROM functions_test.t5 ORDER BY formatDateTime(ts, '%y/%m/%d %I:%i %p %Q %j %b %a') ASC NULLS LAST (3 rows) SELECT to_char(ts, 'YY/MM/DD HH12:MI AM Q DDD Mon Dy') FROM t5 ORDER BY 1; to_char --------------------------------- 25/10/15 01:12 PM 4 288 Oct Wed 26/11/17 12:13 AM 4 321 Nov Tue 27/12/17 02:14 PM 4 351 Dec Fri 28/01/18 03:15 PM 1 018 Jan Tue 29/02/18 05:16 PM 1 049 Feb Sun 30/03/19 07:16 PM 1 078 Mar Tue (6 rows) -- Quoted literal text and a literal % round-trip via formatDateTime escaping. EXPLAIN (VERBOSE, COSTS OFF) SELECT to_char(ts, '"Year=" YYYY "%"') FROM t5 ORDER BY 1; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Foreign Scan on public.t5 Output: to_char(ts, '"Year=" YYYY "%"'::text) Remote SQL: SELECT ts FROM functions_test.t5 ORDER BY formatDateTime(ts, 'Year= %Y %%') ASC NULLS LAST (3 rows) SELECT to_char(ts, '"Year=" YYYY "%"') FROM t5 ORDER BY 1; to_char -------------- Year= 2025 % Year= 2026 % Year= 2027 % Year= 2028 % Year= 2029 % Year= 2030 % (6 rows) -- to_char: refusal cases evaluate locally rather than pushing wrong output. -- Padded month name (Month) -- CH formatDateTime cannot blank-pad. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE to_char(ts, 'Month') = 'October '; QUERY PLAN ---------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Filter: (to_char(t5.ts, 'Month'::text) = 'October '::text) Remote SQL: SELECT ts FROM functions_test.t5 (4 rows) -- Single-digit year token (Y) -- CH has no equivalent. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE to_char(ts, 'Y') = '5'; QUERY PLAN --------------------------------------------------- Foreign Scan on public.t5 Output: ts Filter: (to_char(t5.ts, 'Y'::text) = '5'::text) Remote SQL: SELECT ts FROM functions_test.t5 (4 rows) -- Ordinal suffix (TH) -- CH has no ordinal output. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE to_char(ts, 'DDTH') = '15TH'; QUERY PLAN --------------------------------------------------------- Foreign Scan on public.t5 Output: ts Filter: (to_char(t5.ts, 'DDTH'::text) = '15TH'::text) Remote SQL: SELECT ts FROM functions_test.t5 (4 rows) -- FM modifier suppresses padding -- CH always pads. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE to_char(ts, 'FMMM') = '10'; QUERY PLAN ------------------------------------------------------- Foreign Scan on public.t5 Output: ts Filter: (to_char(t5.ts, 'FMMM'::text) = '10'::text) Remote SQL: SELECT ts FROM functions_test.t5 (4 rows) -- Lowercase am/pm -- CH %p is always uppercase. EXPLAIN (VERBOSE, COSTS OFF) SELECT ts FROM t5 WHERE to_char(ts, 'HH12 am') = '08 pm'; QUERY PLAN ------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Filter: (to_char(t5.ts, 'HH12 am'::text) = '08 pm'::text) Remote SQL: SELECT ts FROM functions_test.t5 (4 rows) -- Dynamic format -- not a Const, so cannot be validated. EXPLAIN (VERBOSE, COSTS OFF) SELECT to_char(ts, t4.val) FROM t5, t4 LIMIT 1; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: to_char(t5.ts, t4.val) Relations: (t5) INNER JOIN (t4) Remote SQL: SELECT r1.ts, r2.val FROM functions_test.t5 r1 ALL INNER JOIN functions_test.t4 r2 ON (TRUE) LIMIT 1 (4 rows) -- reverse pushes down as reverseUTF8 to preserve code-point order on -- multi-byte input CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t4 VALUES ('Ωαβ'), ('hello') $$); EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE reverse(val) = 'βαΩ'; QUERY PLAN ------------------------------------------------------------------------------------ Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((reverseUTF8(val) = 'βαΩ')) (3 rows) SELECT val FROM t4 WHERE reverse(val) = 'βαΩ'; val ----- Ωαβ (1 row) -- bit_count(bytea) pushes down as bitCount (PG14+). SELECT current_setting('server_version_num')::int >= 140000 AS pg14 \gset \if :pg14 EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE bit_count(val::bytea) = 21 ORDER BY val; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((bitCount(CAST(val AS bytea(0))) = 21)) ORDER BY val ASC NULLS LAST (3 rows) SELECT val FROM t4 WHERE bit_count(val::bytea) = 21 ORDER BY val; val ------- hello (1 row) \endif -- mod(int, int) pushes down as modulo. EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE mod(a, 3) = 0 ORDER BY a; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((modulo(a, 3) = 0)) ORDER BY a ASC NULLS LAST (3 rows) SELECT a FROM t3 WHERE mod(a, 3) = 0 ORDER BY a; a --- 3 6 9 (3 rows) -- pow(float8, float8) and power(float8, float8) both push down as pow. EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE pow(f64, 2::float8) < 1 ORDER BY i64; QUERY PLAN ------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((pow(f64, 2) < 1)) ORDER BY i64 ASC NULLS LAST (3 rows) SELECT i64 FROM t6 WHERE pow(f64, 2::float8) < 1 ORDER BY i64; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE power(f64, 2::float8) < 1 ORDER BY i64; QUERY PLAN ------------------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((pow(f64, 2) < 1)) ORDER BY i64 ASC NULLS LAST (3 rows) SELECT i64 FROM t6 WHERE power(f64, 2::float8) < 1 ORDER BY i64; i64 ----- 0 (1 row) -- mod / pow / power on numeric push down too. EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE mod(a::numeric, 3::numeric) = 0 ORDER BY a; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((modulo(cast(a, 'Nullable(Decimal)'), 3) = 0)) ORDER BY a ASC NULLS LAST (3 rows) SELECT a FROM t3 WHERE mod(a::numeric, 3::numeric) = 0 ORDER BY a; a --- 3 6 9 (3 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE pow(a::numeric, 2::numeric) = 25; QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((pow(cast(a, 'Nullable(Decimal)'), 2) = 25)) (3 rows) SELECT a FROM t3 WHERE pow(a::numeric, 2::numeric) = 25; a --- 5 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE power(a::numeric, 2::numeric) = 25; QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((pow(cast(a, 'Nullable(Decimal)'), 2) = 25)) (3 rows) SELECT a FROM t3 WHERE power(a::numeric, 2::numeric) = 25; a --- 5 (1 row) -- abs() pushes down for int / float / numeric. EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE abs(a - 5) = 2 ORDER BY a; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((abs((a - 5)) = 2)) ORDER BY a ASC NULLS LAST (3 rows) SELECT a FROM t3 WHERE abs(a - 5) = 2 ORDER BY a; a --- 3 7 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE abs(f64) = 0; QUERY PLAN ------------------------------------------------------------------------ Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((abs(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE abs(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE abs(a::numeric) = 5; QUERY PLAN ----------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((abs(cast(a, 'Nullable(Decimal)')) = 5)) (3 rows) SELECT a FROM t3 WHERE abs(a::numeric) = 5; a --- 5 (1 row) -- factorial(int8) pushes down. EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE factorial(a) = 120; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((factorial(a) = 120)) (3 rows) SELECT a FROM t3 WHERE factorial(a) = 120; a --- 5 (1 row) -- round() pushes down for float8 and numeric. EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE round(f64) = 0; QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((round(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE round(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE round(a::numeric) = 5; QUERY PLAN ---------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((round(cast(a, 'Nullable(Decimal)'), 0) = 5)) (3 rows) SELECT a FROM t3 WHERE round(a::numeric) = 5; a --- 5 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT a FROM t3 WHERE round((a::numeric) / 3, 2) = 1.67; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t3 Output: a Remote SQL: SELECT a FROM functions_test.t3 WHERE ((round((cast(a, 'Nullable(Decimal)') / 3), 2) = 1.67)) (3 rows) SELECT a FROM t3 WHERE round((a::numeric) / 3, 2) = 1.67; a --- (0 rows) -- Trig functions push down at f64 = 0 where PG and CH agree exactly. EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND sin(f64) = 0; QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((sin(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND sin(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND cos(f64) = 1; QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((cos(f64) = 1)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND cos(f64) = 1; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND tan(f64) = 0; QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((tan(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND tan(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND atan(f64) = 0; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((atan(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND atan(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND atan2(f64, 1::float8) = 0; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((atan2(f64, 1) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND atan2(f64, 1::float8) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND sinh(f64) = 0; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((sinh(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND sinh(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND cosh(f64) = 1; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((cosh(f64) = 1)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND cosh(f64) = 1; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND tanh(f64) = 0; QUERY PLAN ----------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((tanh(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND tanh(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND asinh(f64) = 0; QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((asinh(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND asinh(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND degrees(f64) = 0; QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((degrees(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND degrees(f64) = 0; i64 ----- 0 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE i64 = 0 AND radians(f64) = 0; QUERY PLAN -------------------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((i64 = 0)) AND ((radians(f64) = 0)) (3 rows) SELECT i64 FROM t6 WHERE i64 = 0 AND radians(f64) = 0; i64 ----- 0 (1 row) -- pi() is immutable so PG constant-folds before deparse; the function name -- itself is never sent to CH but the remote literal proves the call worked. EXPLAIN (VERBOSE, COSTS OFF) SELECT i64 FROM t6 WHERE f64 < pi(); QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.t6 Output: i64 Remote SQL: SELECT i64 FROM functions_test.t6 WHERE ((f64 < 3.141592653589793)) (3 rows) -- lower(text) / upper(text) push down as lowerUTF8 / upperUTF8. CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t4 VALUES ('VAL3'), ('Mixed') $$); EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE lower(val) = 'val3'; QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((lowerUTF8(val) = 'val3')) (3 rows) SELECT val FROM t4 WHERE lower(val) = 'val3'; val ------ VAL3 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE upper(val) = 'VAL1'; QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((upperUTF8(val) = 'VAL1')) (3 rows) SELECT val FROM t4 WHERE upper(val) = 'VAL1'; val ------ val1 (1 row) -- substring/substr (text) push down as substringUTF8 (counts code points). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE substring(val, 1, 3) = 'val' ORDER BY val; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((substringUTF8(val, 1, 3) = 'val')) ORDER BY val ASC NULLS LAST (3 rows) SELECT val FROM t4 WHERE substring(val, 1, 3) = 'val' ORDER BY val; val ------ val1 val2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE substr(val, 2) = 'αβ'; QUERY PLAN ---------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((substringUTF8(val, 2) = 'αβ')) (3 rows) SELECT val FROM t4 WHERE substr(val, 2) = 'αβ'; val ----- Ωαβ (1 row) -- substring/substr (bytea) push down as substring (byte-based). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE substring(val::bytea, 1, 2) = 'va'::bytea ORDER BY val; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((substring(CAST(val AS bytea(0)), 1, 2) = 'va')) ORDER BY val ASC NULLS LAST (3 rows) SELECT val FROM t4 WHERE substring(val::bytea, 1, 2) = 'va'::bytea ORDER BY val; val ------ val1 val2 (2 rows) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE substring(val::bytea, 1, 2) = '\xcea9'::bytea; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((substring(CAST(val AS bytea(0)), 1, 2) = '\xce\xa9')) (3 rows) SELECT val FROM t4 WHERE substring(val::bytea, 1, 2) = '\xcea9'::bytea; val ----- Ωαβ (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE substr(val::bytea, 2) = 'al1'::bytea; QUERY PLAN ------------------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((substring(CAST(val AS bytea(0)), 2) = 'al1')) (3 rows) SELECT val FROM t4 WHERE substr(val::bytea, 2) = 'al1'::bytea; val ------ val1 (1 row) -- length(text) pushes down as lengthUTF8 (counts code points). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE length(val) = 3; QUERY PLAN ------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((lengthUTF8(val) = 3)) (3 rows) SELECT val FROM t4 WHERE length(val) = 3; val ----- Ωαβ (1 row) -- length(bytea) pushes down as length (counts bytes). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE length(val::bytea) = 6; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((length(CAST(val AS bytea(0))) = 6)) (3 rows) SELECT val FROM t4 WHERE length(val::bytea) = 6; val ----- Ωαβ (1 row) -- octet_length(text) / octet_length(bytea) push down as length. EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE octet_length(val) = 6; QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((length(val) = 6)) (3 rows) SELECT val FROM t4 WHERE octet_length(val) = 6; val ----- Ωαβ (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE octet_length(val::bytea) = 6; QUERY PLAN --------------------------------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((length(CAST(val AS bytea(0))) = 6)) (3 rows) SELECT val FROM t4 WHERE octet_length(val::bytea) = 6; val ----- Ωαβ (1 row) -- reverse(bytea) added in PG18, pushes down as CH reverse (byte-wise). SELECT current_setting('server_version_num')::int >= 180000 AS pg18 \gset \if :pg18 EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE reverse(val::bytea) = 'olleh'::bytea; SELECT val FROM t4 WHERE reverse(val::bytea) = 'olleh'::bytea; \endif -- date(timestamp) and date(timestamptz) push down as CH date (alias for toDate). EXPLAIN (VERBOSE, COSTS OFF) SELECT a, b FROM t1 WHERE date(c) = '2019-01-01'::date; QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.t1 Output: a, b Remote SQL: SELECT a, b FROM functions_test.t1 WHERE ((date(c) = '2019-01-01')) (3 rows) SELECT a, b FROM t1 WHERE date(c) = '2019-01-01'::date; a | b ---+--- 1 | 1 (1 row) -- Pin TZ for the timestamptz variant so PG and CH interpret c identically. SET timezone = 'UTC'; EXPLAIN (VERBOSE, COSTS OFF) SELECT a, b FROM t2 WHERE date(c) = '2019-01-01'::date; QUERY PLAN ----------------------------------------------------------------------------------- Foreign Scan on public.t2 Output: a, b Remote SQL: SELECT a, b FROM functions_test.t1 WHERE ((date(c) = '2019-01-01')) (3 rows) SELECT a, b FROM t2 WHERE date(c) = '2019-01-01'::date; a | b ---+--- 1 | 1 (1 row) RESET timezone; -- encode(bytea, 'hex') pushes down as lower(hex()): PG emits lowercase hex, -- ClickHouse hex() emits uppercase. GROUP BY forces it into the remote target. EXPLAIN (VERBOSE, COSTS OFF) SELECT encode(val::bytea, 'hex') AS h FROM t4 GROUP BY h ORDER BY h; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (encode((val)::bytea, 'hex'::text)) Relations: Aggregate on (t4) Remote SQL: SELECT lower(hex(CAST(val AS bytea(0)))) FROM functions_test.t4 GROUP BY (lower(hex(CAST(val AS bytea(0))))) ORDER BY lower(hex(CAST(val AS bytea(0)))) ASC NULLS LAST (4 rows) SELECT encode(val::bytea, 'hex') AS h FROM t4 GROUP BY h ORDER BY h; h -------------- 4d69786564 56414c33 68656c6c6f 76616c31 76616c32 cea9ceb1ceb2 (6 rows) -- encode(bytea, 'base64') pushes down as base64Encode wrapped to reproduce -- PG's MIME (RFC 2045) line break every 76 characters. EXPLAIN (VERBOSE, COSTS OFF) SELECT encode(val::bytea, 'base64') AS b FROM t4 GROUP BY b ORDER BY b; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (encode((val)::bytea, 'base64'::text)) Relations: Aggregate on (t4) Remote SQL: SELECT replaceRegexpAll(replaceRegexpAll(base64Encode(CAST(val AS bytea(0))), '(.{76})', '\\1\n'), '(=+)\n$', '\\1') FROM functions_test.t4 GROUP BY (replaceRegexpAll(replaceRegexpAll(base64Encode(CAST(val AS bytea(0))), '(.{76})', '\\1\n'), '(=+)\n$', '\\1')) ORDER BY replaceRegexpAll(replaceRegexpAll(base64Encode(CAST(val AS bytea(0))), '(.{76})', '\\1\n'), '(=+)\n$', '\\1') ASC NULLS LAST (4 rows) SELECT encode(val::bytea, 'base64') AS b FROM t4 GROUP BY b ORDER BY b; b ---------- TWl4ZWQ= VkFMMw== aGVsbG8= dmFsMQ== dmFsMg== zqnOsc6y (6 rows) -- Format name matches case-insensitively, like PG encode(). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE encode(val::bytea, 'HEX') = '68656c6c6f'; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Foreign Scan on public.t4 Output: val Remote SQL: SELECT val FROM functions_test.t4 WHERE ((lower(hex(CAST(val AS bytea(0)))) = '68656c6c6f')) (3 rows) -- 'escape' has no ClickHouse equivalent, so the filter evaluates locally -- (encode stays out of the remote SQL). EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE encode(val::bytea, 'escape') = 'hello'; QUERY PLAN --------------------------------------------------------------------- Foreign Scan on public.t4 Output: val Filter: (encode((t4.val)::bytea, 'escape'::text) = 'hello'::text) Remote SQL: SELECT val FROM functions_test.t4 (4 rows) -- A non-constant format cannot be validated, so the filter evaluates locally. EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE encode(val::bytea, val) = val; QUERY PLAN ------------------------------------------------------ Foreign Scan on public.t4 Output: val Filter: (encode((t4.val)::bytea, t4.val) = t4.val) Remote SQL: SELECT val FROM functions_test.t4 (4 rows) -- Inputs over 57 bytes exercise the 76-char MIME line breaks: 57 bytes ends in -- a trailing newline, 60 wraps once mid-string. Matching the pushed-down base64 -- against PG's own MIME output of the same bytes confirms they agree -- byte-for-byte. CALL clickhouse_perform('functions_admin', $$ INSERT INTO functions_test.t4 VALUES (repeat('a', 57)), (repeat('a', 60)) $$); SELECT octet_length(val) AS n FROM t4 WHERE encode(val::bytea, 'base64') IN ( encode(repeat('a', 57)::bytea, 'base64'), encode(repeat('a', 60)::bytea, 'base64') ) ORDER BY n; n ---- 57 60 (2 rows) -- encode(bytea, 'base64url') added in PG19, pushes down as base64URLEncode: -- RFC 4648 URL alphabet, unpadded, no line breaks (unlike MIME base64). SELECT current_setting('server_version_num')::int >= 190000 AS pg19 \gset \if :pg19 EXPLAIN (VERBOSE, COSTS OFF) SELECT encode(val::bytea, 'base64url') AS b FROM t4 GROUP BY b ORDER BY b; SELECT encode(val::bytea, 'base64url') AS b FROM t4 GROUP BY b ORDER BY b; -- The 60-byte input crosses base64's 76-char line break boundary, exercising -- that base64url emits no newline; matching against PG's own output of the same -- bytes confirms they agree byte-for-byte. SELECT octet_length(val) AS n FROM t4 WHERE encode(val::bytea, 'base64url') IN ( encode(repeat('a', 57)::bytea, 'base64url'), encode(repeat('a', 60)::bytea, 'base64url') ) ORDER BY n; \endif DROP USER MAPPING FOR CURRENT_USER SERVER functions_loopback; CALL clickhouse_perform('functions_admin', 'DROP DATABASE functions_test'); DROP SERVER functions_loopback CASCADE; NOTICE: drop cascades to 10 other objects DETAIL: drop cascades to foreign table t1 drop cascades to foreign table t2 drop cascades to foreign table t3 drop cascades to foreign table t3_map drop cascades to foreign table t4 drop cascades to foreign table t5 drop cascades to foreign table t6 drop cascades to foreign table t7 drop cascades to foreign table t8 drop cascades to foreign table times