SET datestyle = 'ISO'; -- Create servers for each engine. CREATE SERVER param_bin_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER param_bin_svr; CREATE SERVER param_http_svr FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'http'); CREATE USER MAPPING FOR CURRENT_USER SERVER param_http_svr; -- Create the schema in ClickHouse. CREATE SERVER param_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER param_admin; CALL clickhouse_perform('param_admin', 'DROP DATABASE IF EXISTS param_test'); CALL clickhouse_perform('param_admin', 'CREATE DATABASE param_test'); CALL clickhouse_perform('param_admin', $$ CREATE TABLE param_test.ft1 ( c1 Int, c2 Int, c3 String, c4 Date, c5 Date, c6 String, c7 String, c8 Array(String) ) ENGINE = MergeTree PARTITION BY c4 ORDER BY (c1) $$); -- Insert some data. CALL clickhouse_perform('param_admin', $$ INSERT INTO param_test.ft1 SELECT number, number % 10, toString(number), addDays(toDate('1970-01-01'), number % 100), addDays(toDate('1970-01-01'), number % 100), number % 10, number % 10, ['foo'] FROM numbers(1, 110) $$); CALL clickhouse_perform('param_admin', $$ CREATE TABLE param_test.mixed ( id Int, u UUID, ts DateTime64(3, 'UTC'), lts DateTime64(3), d Date32 ) ENGINE = MergeTree ORDER BY id $$); CALL clickhouse_perform('param_admin', $$ INSERT INTO param_test.mixed VALUES (1, '188dff2f-0205-4912-8210-2214e84482cd', '2026-09-28 23:59:59.500', '2026-09-28 23:59:59.500', '1960-01-01'), (2, '00000000-0000-0000-0000-000000000002', '2026-09-28 23:59:59.300', '2026-09-28 23:59:59.300', '2026-09-29') $$); CALL clickhouse_perform('param_admin', $$ CREATE TABLE param_test.typed (id Int, f Float64) ENGINE = MergeTree ORDER BY id $$); CALL clickhouse_perform('param_admin', $$ INSERT INTO param_test.typed VALUES (1, 1e-10), (2, 0.5), (3, inf), (4, -inf), (5, -0.) $$); -- =================================================================== -- binary foreign tables -- =================================================================== CREATE SCHEMA bin_test; CREATE FOREIGN TABLE bin_test.ft1 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'ft1', c8 text[] ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'ft1' ); CREATE FOREIGN TABLE bin_test.ft2 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'f21', c8 text[] ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'ft1' ); -- =================================================================== -- binary parameterized queries -- =================================================================== -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM bin_test.ft1 t1, bin_test.ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c3, t2.c3 Relations: (ft1 t1) INNER JOIN (ft2 t2) Remote SQL: SELECT r1.c3, r2.c3 FROM param_test.ft1 r1 ALL INNER JOIN param_test.ft1 r2 ON (TRUE) WHERE ((r2.c1 = 2)) AND ((r1.c1 = 1)) (4 rows) EXECUTE st1(1, 1); c3 | c3 ----+---- 1 | 1 (1 row) EXECUTE st1(101, 101); c3 | c3 -----+----- 101 | 101 (1 row) SET enable_hashjoin TO off; SET enable_sort TO off; -- subquery using stable function (can't be sent to remote, but is?) PREPARE st2(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM bin_test.ft2 t2 WHERE c1 > $1 AND date(c4) = date('1970-01-17')) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((toDate32(r3.c4, 'America/Los_Angeles') = '1970-01-17')) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st2(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st2(101, 121); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) RESET enable_hashjoin; RESET enable_sort; -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM bin_test.ft2 t2 WHERE c1 > $1 AND date(c5) = date('1970-01-17'::timestamptz)) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((toDate32(r3.c5, 'America/Los_Angeles') = toDate32(cast('1970-01-17 08:00:00' as DateTime64(6, 'UTC')), 'America/Los_Angeles'))) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st3(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st3(20, 30); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) -- custom plan should be chosen initially PREPARE st4(int) AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) -- once we try it enough times, should switch to generic plan EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) (3 rows) -- value of $1 should not be sent to remote PREPARE st5(text[], int) AS SELECT * FROM bin_test.ft1 t1 WHERE c8 = $1 and c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = {p1:Array(String)})) AND ((c1 = {p2:Int32})) (3 rows) EXECUTE st5('{foo}', 1); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} (1 row) -- altering FDW options requires replanning PREPARE st6 AS SELECT * FROM bin_test.ft1 t1 WHERE t1.c1 = t1.c2 ORDER BY t1.c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) PREPARE st7 AS INSERT INTO bin_test.ft1 (c1,c2,c3) VALUES (1001,101,'foo'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on bin_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying(10), 'ft1 '::character(10), NULL::text[] (3 rows) CALL clickhouse_perform('param_admin', 'RENAME TABLE param_test.ft1 TO param_test.t1'); ALTER FOREIGN TABLE bin_test.ft1 OPTIONS (SET table_name 't1'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.t1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) EXECUTE st6; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} 2 | 2 | 2 | 1970-01-03 00:00:00-08 | 1970-01-03 00:00:00 | 2 | 2 | {foo} 3 | 3 | 3 | 1970-01-04 00:00:00-08 | 1970-01-04 00:00:00 | 3 | 3 | {foo} 4 | 4 | 4 | 1970-01-05 00:00:00-08 | 1970-01-05 00:00:00 | 4 | 4 | {foo} 5 | 5 | 5 | 1970-01-06 00:00:00-08 | 1970-01-06 00:00:00 | 5 | 5 | {foo} 6 | 6 | 6 | 1970-01-07 00:00:00-08 | 1970-01-07 00:00:00 | 6 | 6 | {foo} 7 | 7 | 7 | 1970-01-08 00:00:00-08 | 1970-01-08 00:00:00 | 7 | 7 | {foo} 8 | 8 | 8 | 1970-01-09 00:00:00-08 | 1970-01-09 00:00:00 | 8 | 8 | {foo} 9 | 9 | 9 | 1970-01-10 00:00:00-08 | 1970-01-10 00:00:00 | 9 | 9 | {foo} (9 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on bin_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying(10), 'ft1 '::character(10), NULL::text[] (3 rows) CALL clickhouse_perform('param_admin', 'RENAME TABLE param_test.t1 TO param_test.ft1'); ALTER FOREIGN TABLE bin_test.ft1 OPTIONS (SET table_name 'ft1'); -- implicit parameter EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM bin_test.ft1 WHERE c1 = (SELECT 4); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on bin_test.ft1 Output: ft1.c1, ft1.c2 Remote SQL: SELECT c1, c2 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) InitPlan expr_1 -> Result Output: 4 (6 rows) SELECT c1, c2 FROM bin_test.ft1 WHERE c1 = (SELECT 4); c1 | c2 ----+---- 4 | 4 (1 row) -- UUID, fractional timestamp, and pre-1970 date parameters CREATE FOREIGN TABLE bin_test.mixed ( id int NOT NULL, u uuid, ts timestamptz, lts timestamp, d date ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'mixed' ); SET plan_cache_mode = force_generic_plan; PREPARE st8(uuid) AS SELECT id FROM bin_test.mixed WHERE u = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8('188dff2f-0205-4912-8210-2214e84482cd'); QUERY PLAN ----------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((u = {p1:UUID})) (3 rows) EXECUTE st8('188dff2f-0205-4912-8210-2214e84482cd'); id ---- 1 (1 row) PREPARE st9(timestamptz) AS SELECT id FROM bin_test.mixed WHERE ts <= $1 ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9('2026-09-28 23:59:59.999999+00'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((ts <= {p1:DateTime64(6, 'UTC')})) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st9('2026-09-28 23:59:59.999999+00'); id ---- 1 2 (2 rows) EXECUTE st9('2026-09-28 23:59:59.4+00'); id ---- 2 (1 row) PREPARE st10(timestamp) AS SELECT id FROM bin_test.mixed WHERE lts <= $1 ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10('2026-09-28 23:59:59.999999'); QUERY PLAN -------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((lts <= {p1:DateTime64(6)})) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st10('2026-09-28 23:59:59.999999'); id ---- 1 2 (2 rows) EXECUTE st10('2026-09-28 23:59:59.4'); id ---- 2 (1 row) PREPARE st11(date) AS SELECT id FROM bin_test.mixed WHERE d = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11('1960-01-01'); QUERY PLAN ------------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((d = {p1:Date32})) (3 rows) EXECUTE st11('1960-01-01'); id ---- 1 (1 row) PREPARE st12(interval) AS SELECT id FROM bin_test.mixed WHERE ts + $1 >= '2026-10-29 00:00:00+00' ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st12('1 month 0.5 seconds'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE (((toDateTime64(ts, 6) + toIntervalMonth(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 1)) + toIntervalDay(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 2)) + toIntervalMicrosecond(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 3))) >= cast('2026-10-29 00:00:00' as DateTime64(6, 'UTC')))) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st12('1 month 0.5 seconds'); id ---- 1 (1 row) EXECUTE st12('1 month 1 day'); id ---- 1 2 (2 rows) PREPARE st13(interval) AS SELECT id FROM bin_test.mixed WHERE d - $1 >= '1959-12-31' ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st13('1 day 1 microsecond'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on bin_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE (((toDateTime64(d, 6) - toIntervalMonth(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 1)) - toIntervalDay(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 2)) - toIntervalMicrosecond(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 3))) >= '1959-12-31 00:00:00')) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st13('1 day 1 microsecond'); id ---- 2 (1 row) EXECUTE st13('1 day'); id ---- 1 2 (2 rows) EXECUTE st13('-1 year'); id ---- 1 2 (2 rows) CREATE FOREIGN TABLE bin_test.typed ( id int NOT NULL, f float8 ) SERVER param_bin_svr OPTIONS( database 'param_test', table_name 'typed' ); PREPARE st14(float8) AS SELECT id FROM bin_test.typed WHERE f = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st14(1e-10); QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on bin_test.typed Output: id Remote SQL: SELECT id FROM param_test.typed WHERE ((f = {p1:Float64})) (3 rows) EXECUTE st14(1e-10); id ---- 1 (1 row) EXECUTE st14('Infinity'); id ---- 3 (1 row) EXECUTE st14('-Infinity'); id ---- 4 (1 row) EXECUTE st14('-0'); id ---- 5 (1 row) PREPARE st15(float8) AS SELECT id FROM bin_test.typed WHERE f > $1 ORDER BY id; EXECUTE st15('-Infinity'); id ---- 1 2 3 5 (4 rows) EXECUTE st15('-0'); id ---- 1 2 3 (3 rows) EXECUTE st15(0.25); id ---- 2 3 (2 rows) PREPARE st16(float8) AS SELECT id FROM bin_test.typed WHERE f <> $1 ORDER BY id; EXECUTE st16('NaN'); id ---- 1 2 3 4 5 (5 rows) RESET plan_cache_mode; -- cleanup DEALLOCATE st1; DEALLOCATE st2; DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; DEALLOCATE st6; DEALLOCATE st7; DEALLOCATE st8; DEALLOCATE st9; DEALLOCATE st10; DEALLOCATE st11; DEALLOCATE st12; DEALLOCATE st13; DEALLOCATE st14; DEALLOCATE st15; DEALLOCATE st16; -- =================================================================== -- http foreign tables -- =================================================================== CREATE SCHEMA http_test; CREATE FOREIGN TABLE http_test.ft1 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'ft1', c8 text[] ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'ft1' ); CREATE FOREIGN TABLE http_test.ft2 ( c1 int NOT NULL, c2 int NOT NULL, c3 text, c4 timestamptz, c5 timestamp, c6 varchar(10), c7 char(10) default 'f21', c8 text[] ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'ft1' ); -- =================================================================== -- http parameterized queries -- =================================================================== -- simple join PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM http_test.ft1 t1, http_test.ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st1(1, 2); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c3, t2.c3 Relations: (ft1 t1) INNER JOIN (ft2 t2) Remote SQL: SELECT r1.c3, r2.c3 FROM param_test.ft1 r1 ALL INNER JOIN param_test.ft1 r2 ON (TRUE) WHERE ((r2.c1 = 2)) AND ((r1.c1 = 1)) (4 rows) EXECUTE st1(1, 1); c3 | c3 ----+---- 1 | 1 (1 row) EXECUTE st1(101, 101); c3 | c3 -----+----- 101 | 101 (1 row) SET enable_hashjoin TO off; SET enable_sort TO off; -- subquery using stable function (can't be sent to remote, but is?) PREPARE st2(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM http_test.ft2 t2 WHERE c1 > $1 AND date(c4) = date('1970-01-17')) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st2(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((toDate32(r3.c4, 'America/Los_Angeles') = '1970-01-17')) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st2(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st2(101, 121); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) RESET enable_hashjoin; RESET enable_sort; -- subquery using immutable function (can be sent to remote) PREPARE st3(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM http_test.ft2 t2 WHERE c1 > $1 AND date(c5) = date('1970-01-17'::timestamptz)) ORDER BY c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 Relations: (ft1 t1) LEFT SEMI JOIN (ft2 t2) Remote SQL: SELECT r1.c1, r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM param_test.ft1 r1 LEFT SEMI JOIN param_test.ft1 r3 ON (((r3.c1 > 10)) AND ((toDate32(r3.c5, 'America/Los_Angeles') = toDate32(cast('1970-01-17 08:00:00' as DateTime64(6, 'UTC')), 'America/Los_Angeles'))) AND ((r1.c3 = r3.c3))) WHERE ((r1.c1 < 20)) ORDER BY r1.c1 ASC NULLS LAST (4 rows) EXECUTE st3(10, 20); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 16 | 6 | 16 | 1970-01-17 00:00:00-08 | 1970-01-17 00:00:00 | 6 | 6 | {foo} (1 row) EXECUTE st3(20, 30); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+----+----+----+----+---- (0 rows) -- custom plan should be chosen initially PREPARE st4(int) AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN ------------------------------------------------------------------------------------------ Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = 1)) (3 rows) -- once we try it enough times, should switch to generic plan EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st4(1); QUERY PLAN --------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) (3 rows) -- value of $1 should not be sent to remote PREPARE st5(text[], int) AS SELECT * FROM http_test.ft1 t1 WHERE c8 = $1 and c1 = $2; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN --------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = ['foo'])) AND ((c1 = 1)) (3 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st5('{foo}', 1); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c8 = {p1:Array(String)})) AND ((c1 = {p2:Int32})) (3 rows) EXECUTE st5('{foo}', 1); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} (1 row) -- altering FDW options requires replanning PREPARE st6 AS SELECT * FROM http_test.ft1 t1 WHERE t1.c1 = t1.c2 ORDER BY t1.c1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.ft1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) PREPARE st7 AS INSERT INTO http_test.ft1 (c1,c2,c3) VALUES (1001,101,'foo'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on http_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying(10), 'ft1 '::character(10), NULL::text[] (3 rows) CALL clickhouse_perform('param_admin', 'RENAME TABLE param_test.ft1 TO param_test.t1'); ALTER FOREIGN TABLE http_test.ft1 OPTIONS (SET table_name 't1'); EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st6; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.ft1 t1 Output: c1, c2, c3, c4, c5, c6, c7, c8 Remote SQL: SELECT c1, c2, c3, c4, c5, c6, c7, c8 FROM param_test.t1 WHERE ((c1 = c2)) ORDER BY c1 ASC NULLS LAST (3 rows) EXECUTE st6; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+----+----+------------------------+---------------------+----+------------+------- 1 | 1 | 1 | 1970-01-02 00:00:00-08 | 1970-01-02 00:00:00 | 1 | 1 | {foo} 2 | 2 | 2 | 1970-01-03 00:00:00-08 | 1970-01-03 00:00:00 | 2 | 2 | {foo} 3 | 3 | 3 | 1970-01-04 00:00:00-08 | 1970-01-04 00:00:00 | 3 | 3 | {foo} 4 | 4 | 4 | 1970-01-05 00:00:00-08 | 1970-01-05 00:00:00 | 4 | 4 | {foo} 5 | 5 | 5 | 1970-01-06 00:00:00-08 | 1970-01-06 00:00:00 | 5 | 5 | {foo} 6 | 6 | 6 | 1970-01-07 00:00:00-08 | 1970-01-07 00:00:00 | 6 | 6 | {foo} 7 | 7 | 7 | 1970-01-08 00:00:00-08 | 1970-01-08 00:00:00 | 7 | 7 | {foo} 8 | 8 | 8 | 1970-01-09 00:00:00-08 | 1970-01-09 00:00:00 | 8 | 8 | {foo} 9 | 9 | 9 | 1970-01-10 00:00:00-08 | 1970-01-10 00:00:00 | 9 | 9 | {foo} (9 rows) EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st7; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Insert on http_test.ft1 -> Result Output: 1001, 101, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying(10), 'ft1 '::character(10), NULL::text[] (3 rows) CALL clickhouse_perform('param_admin', 'RENAME TABLE param_test.t1 TO param_test.ft1'); ALTER FOREIGN TABLE http_test.ft1 OPTIONS (SET table_name 'ft1'); -- implicit parameter EXPLAIN (VERBOSE, COSTS OFF) SELECT c1, c2 FROM http_test.ft1 WHERE c1 = (SELECT 4); QUERY PLAN --------------------------------------------------------------------------- Foreign Scan on http_test.ft1 Output: ft1.c1, ft1.c2 Remote SQL: SELECT c1, c2 FROM param_test.ft1 WHERE ((c1 = {p1:Int32})) InitPlan expr_1 -> Result Output: 4 (6 rows) SELECT c1, c2 FROM http_test.ft1 WHERE c1 = (SELECT 4); c1 | c2 ----+---- 4 | 4 (1 row) -- UUID, fractional timestamp, and pre-1970 date parameters CREATE FOREIGN TABLE http_test.mixed ( id int NOT NULL, u uuid, ts timestamptz, lts timestamp, d date ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'mixed' ); SET plan_cache_mode = force_generic_plan; PREPARE st8(uuid) AS SELECT id FROM http_test.mixed WHERE u = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st8('188dff2f-0205-4912-8210-2214e84482cd'); QUERY PLAN ----------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((u = {p1:UUID})) (3 rows) EXECUTE st8('188dff2f-0205-4912-8210-2214e84482cd'); id ---- 1 (1 row) PREPARE st9(timestamptz) AS SELECT id FROM http_test.mixed WHERE ts <= $1 ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st9('2026-09-28 23:59:59.999999+00'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((ts <= {p1:DateTime64(6, 'UTC')})) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st9('2026-09-28 23:59:59.999999+00'); id ---- 1 2 (2 rows) EXECUTE st9('2026-09-28 23:59:59.4+00'); id ---- 2 (1 row) PREPARE st10(timestamp) AS SELECT id FROM http_test.mixed WHERE lts <= $1 ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st10('2026-09-28 23:59:59.999999'); QUERY PLAN -------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((lts <= {p1:DateTime64(6)})) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st10('2026-09-28 23:59:59.999999'); id ---- 1 2 (2 rows) EXECUTE st10('2026-09-28 23:59:59.4'); id ---- 2 (1 row) PREPARE st11(date) AS SELECT id FROM http_test.mixed WHERE d = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st11('1960-01-01'); QUERY PLAN ------------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE ((d = {p1:Date32})) (3 rows) EXECUTE st11('1960-01-01'); id ---- 1 (1 row) PREPARE st12(interval) AS SELECT id FROM http_test.mixed WHERE ts + $1 >= '2026-10-29 00:00:00+00' ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st12('1 month 0.5 seconds'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE (((toDateTime64(ts, 6) + toIntervalMonth(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 1)) + toIntervalDay(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 2)) + toIntervalMicrosecond(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 3))) >= cast('2026-10-29 00:00:00' as DateTime64(6, 'UTC')))) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st12('1 month 0.5 seconds'); id ---- 1 (1 row) EXECUTE st12('1 month 1 day'); id ---- 1 2 (2 rows) PREPARE st13(interval) AS SELECT id FROM http_test.mixed WHERE d - $1 >= '1959-12-31' ORDER BY id; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st13('1 day 1 microsecond'); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on http_test.mixed Output: id Remote SQL: SELECT id FROM param_test.mixed WHERE (((toDateTime64(d, 6) - toIntervalMonth(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 1)) - toIntervalDay(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 2)) - toIntervalMicrosecond(tupleElement({p1:Tuple(Int32, Int32, Int64)}, 3))) >= '1959-12-31 00:00:00')) ORDER BY id ASC NULLS LAST (3 rows) EXECUTE st13('1 day 1 microsecond'); id ---- 2 (1 row) EXECUTE st13('1 day'); id ---- 1 2 (2 rows) EXECUTE st13('-1 year'); id ---- 1 2 (2 rows) CREATE FOREIGN TABLE http_test.typed ( id int NOT NULL, f float8 ) SERVER param_http_svr OPTIONS( database 'param_test', table_name 'typed' ); PREPARE st14(float8) AS SELECT id FROM http_test.typed WHERE f = $1; EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st14(1e-10); QUERY PLAN -------------------------------------------------------------------------- Foreign Scan on http_test.typed Output: id Remote SQL: SELECT id FROM param_test.typed WHERE ((f = {p1:Float64})) (3 rows) EXECUTE st14(1e-10); id ---- 1 (1 row) EXECUTE st14('Infinity'); id ---- 3 (1 row) EXECUTE st14('-Infinity'); id ---- 4 (1 row) EXECUTE st14('-0'); id ---- 5 (1 row) PREPARE st15(float8) AS SELECT id FROM http_test.typed WHERE f > $1 ORDER BY id; EXECUTE st15('-Infinity'); id ---- 1 2 3 5 (4 rows) EXECUTE st15('-0'); id ---- 1 2 3 (3 rows) EXECUTE st15(0.25); id ---- 2 3 (2 rows) PREPARE st16(float8) AS SELECT id FROM http_test.typed WHERE f <> $1 ORDER BY id; EXECUTE st16('NaN'); id ---- 1 2 3 4 5 (5 rows) RESET plan_cache_mode; -- cleanup DEALLOCATE st1; DEALLOCATE st2; DEALLOCATE st3; DEALLOCATE st4; DEALLOCATE st5; DEALLOCATE st6; DEALLOCATE st7; DEALLOCATE st8; DEALLOCATE st9; DEALLOCATE st10; DEALLOCATE st11; DEALLOCATE st12; DEALLOCATE st13; DEALLOCATE st14; DEALLOCATE st15; DEALLOCATE st16; -- Clean up. DROP USER MAPPING FOR CURRENT_USER SERVER param_bin_svr; DROP SERVER param_bin_svr CASCADE; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to foreign table bin_test.ft1 drop cascades to foreign table bin_test.ft2 drop cascades to foreign table bin_test.mixed drop cascades to foreign table bin_test.typed DROP USER MAPPING FOR CURRENT_USER SERVER param_http_svr; DROP SERVER param_http_svr CASCADE; NOTICE: drop cascades to 4 other objects DETAIL: drop cascades to foreign table http_test.ft1 drop cascades to foreign table http_test.ft2 drop cascades to foreign table http_test.mixed drop cascades to foreign table http_test.typed