SET datestyle = 'ISO'; CREATE SERVER functions_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'functions_test', driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER functions_loopback; SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS functions_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE DATABASE functions_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ CREATE TABLE functions_test.t1 (a int, b int, c DateTime) ENGINE = MergeTree ORDER BY (a); $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ 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') $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ drop dictionary if exists functions_test.t3_dict $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query(' create table functions_test.t3 (a Int32, b Nullable(Int32)) engine = MergeTree() order by a'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE functions_test.t3_map (key1 Int32, key2 String, val String) engine=TinyLog();'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE functions_test.t4 (val String) engine=TinyLog();'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE functions_test.t5 (ts DateTime) engine=TinyLog();'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ 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') $$); clickhouse_raw_query ---------------------- (1 row) 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; SELECT clickhouse_raw_query($$ INSERT INTO functions_test.t3 SELECT number+1, number+2 FROM numbers(10); $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ INSERT INTO functions_test.t3_map SELECT toString(number+1), 'key' || toString(number+1), 'val' || toString(number+1) FROM numbers(10); $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ INSERT INTO functions_test.t4 SELECT 'val' || toString(number+1) FROM numbers(2); $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ 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); $$); clickhouse_raw_query ---------------------- (1 row) -- 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 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 ------------------------ 2019-01-01 00:00:00-08 2019-01-02 00: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 --------------------- 2019-01-01 00:00:00 2019-01-02 00: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')) FROM functions_test.t1 GROUP BY (toDayOfWeek(toTimeZone(c, 'UTC'))) ORDER BY toDayOfWeek(toTimeZone(c, 'UTC')) 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 10:00:00-08 2019-01-02 10:00:00-08 2019-01-02 11: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) 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 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 position(val, 'val') FROM functions_test.t4 GROUP BY (position(val, 'val')) ORDER BY position(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) = '2023'; QUERY PLAN ---------------------------------------------------------------------------- Foreign Scan on public.t5 Output: ts Remote SQL: SELECT ts FROM functions_test.t5 WHERE ((toYear(ts) = 2023)) (3 rows) SELECT ts FROM t5 WHERE date_part('year', ts) = '2027'; ts --------------------- 2027-12-17 22: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 08: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 23: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 20: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-19 01:16:29 2030-03-20 02: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 08: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 22: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)) (3 rows) SELECT ts FROM t5 WHERE date_part('dow', ts) = '2'; ts --------------------- 2026-11-17 08:13:26 2028-01-18 23:15:28 (2 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 23:15:28 2029-02-19 01:16:29 2030-03-20 02: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 20: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 08: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-19 01:16:29 2030-03-20 02: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 22: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 08: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 23: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 22: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 08: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 23: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 20: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-19 01:16:29 2030-03-20 02: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 08: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 22: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)) (3 rows) SELECT ts FROM t5 WHERE EXTRACT(dow FROM ts) = 2; ts --------------------- 2026-11-17 08:13:26 2028-01-18 23: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 23:15:28 2029-02-19 01:16:29 2030-03-20 02: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 20: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 08: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-19 01:16:29 2030-03-20 02: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 22: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 08: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 23: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 08: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 22: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 23: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-19 01: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 20: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 22: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 23: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 22:14:27 (1 row) -- check regexp_like. EXPLAIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE regexp_like('^val\d', val); ERROR: function regexp_like(unknown, text) does not exist LINE 1: ...AIN (VERBOSE, COSTS OFF) SELECT val FROM t4 WHERE regexp_lik... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. SELECT val FROM t4 WHERE regexp_like('^val\d', val); ERROR: function regexp_like(unknown, text) does not exist LINE 1: SELECT val FROM t4 WHERE regexp_like('^val\d', val); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. DROP USER MAPPING FOR CURRENT_USER SERVER functions_loopback; SELECT clickhouse_raw_query('DROP DATABASE functions_test'); clickhouse_raw_query ---------------------- (1 row) DROP SERVER functions_loopback CASCADE; NOTICE: drop cascades to 6 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