SET datestyle = 'ISO'; CREATE SERVER binary_decimal_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'decimal_test', driver 'binary'); CREATE SERVER http_decimal_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'decimal_test', driver 'http'); CREATE USER MAPPING FOR CURRENT_USER SERVER binary_decimal_loopback; CREATE USER MAPPING FOR CURRENT_USER SERVER http_decimal_loopback; CREATE SERVER decimal_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER decimal_admin; CALL clickhouse_perform('decimal_admin', 'DROP DATABASE IF EXISTS decimal_test'); CALL clickhouse_perform('decimal_admin', 'CREATE DATABASE decimal_test'); \set ECHO errors CALL clickhouse_perform('decimal_admin', format($$ CREATE TABLE decimal_test.decimals ( id Int32 NOT NULL, dec Decimal(8, 0) NOT NULL, dec32 Decimal32(4) NOT NULL, dec64 Decimal64(6) NOT NULL, dec128 Decimal128(8) NOT NULL, dec0 Decimal%s NOT NULL, f64 Float64 NOT NULL ) ENGINE = MergeTree PARTITION BY id ORDER BY (id); $$, :'bare_dec')); CREATE SCHEMA dec_bin; CREATE SCHEMA dec_http; IMPORT FOREIGN SCHEMA "decimal_test" FROM SERVER binary_decimal_loopback INTO dec_bin; \d dec_bin.decimals Foreign table "dec_bin.decimals" Column | Type | Collation | Nullable | Default | FDW options --------+------------------+-----------+----------+---------+------------- id | integer | | not null | | dec | numeric(8,0) | | not null | | dec32 | numeric(9,4) | | not null | | dec64 | numeric(18,6) | | not null | | dec128 | numeric(38,8) | | not null | | dec0 | numeric(10,0) | | not null | | f64 | double precision | | not null | | Server: binary_decimal_loopback FDW options: (database 'decimal_test', table_name 'decimals', engine 'MergeTree') IMPORT FOREIGN SCHEMA "decimal_test" FROM SERVER http_decimal_loopback INTO dec_http; \d dec_http.decimals Foreign table "dec_http.decimals" Column | Type | Collation | Nullable | Default | FDW options --------+------------------+-----------+----------+---------+------------- id | integer | | not null | | dec | numeric(8,0) | | not null | | dec32 | numeric(9,4) | | not null | | dec64 | numeric(18,6) | | not null | | dec128 | numeric(38,8) | | not null | | dec0 | numeric(10,0) | | not null | | f64 | double precision | | not null | | Server: http_decimal_loopback FDW options: (database 'decimal_test', table_name 'decimals', engine 'MergeTree') INSERT INTO dec_bin.decimals (id, dec, dec32, dec64, dec128, dec0, f64) VALUES (1, 42::NUMERIC, 98.6::NUMERIC, 102.4::NUMERIC, 1024.003::NUMERIC, 66::NUMERIC, 1024.003), (2, 9999, 9999.9999, 9999999.999999, 99999999999.99999999, 9999999999, 2048.006), (3, -9999, -9999.9999, -9999999.999999, -99999999999.99999999, -9999999999, -2048.006) ; INSERT INTO dec_http.decimals VALUES (4, 1000000::NUMERIC, 10000::NUMERIC, 3000000000::NUMERIC, 400000000000::NUMERIC, 67::NUMERIC, 4096.012), (5, -1, -0.0001, -0.000001, -0.00000001, -1111111111, -1.5), (6, 0, 0, 0, 0, 0, 0) ; SELECT * FROM dec_bin.decimals ORDER BY id; id | dec | dec32 | dec64 | dec128 | dec0 | f64 ----+---------+------------+-------------------+-----------------------+-------------+----------- 1 | 42 | 98.6000 | 102.400000 | 1024.00300000 | 66 | 1024.003 2 | 9999 | 9999.9999 | 9999999.999999 | 99999999999.99999999 | 9999999999 | 2048.006 3 | -9999 | -9999.9999 | -9999999.999999 | -99999999999.99999999 | -9999999999 | -2048.006 4 | 1000000 | 10000.0000 | 3000000000.000000 | 400000000000.00000000 | 67 | 4096.012 5 | -1 | -0.0001 | -0.000001 | -0.00000001 | -1111111111 | -1.5 6 | 0 | 0.0000 | 0.000000 | 0.00000000 | 0 | 0 (6 rows) SELECT * FROM dec_http.decimals ORDER BY id; id | dec | dec32 | dec64 | dec128 | dec0 | f64 ----+---------+------------+-------------------+-----------------------+-------------+----------- 1 | 42 | 98.6000 | 102.400000 | 1024.00300000 | 66 | 1024.003 2 | 9999 | 9999.9999 | 9999999.999999 | 99999999999.99999999 | 9999999999 | 2048.006 3 | -9999 | -9999.9999 | -9999999.999999 | -99999999999.99999999 | -9999999999 | -2048.006 4 | 1000000 | 10000.0000 | 3000000000.000000 | 400000000000.00000000 | 67 | 4096.012 5 | -1 | -0.0001 | -0.000001 | -0.00000001 | -1111111111 | -1.5 6 | 0 | 0.0000 | 0.000000 | 0.00000000 | 0 | 0 (6 rows) -- Casting to an unconstrained numeric pushes down to the max decimal size, -- Decimal(76, 38), but results should not have trailing zeros. EXPLAIN (VERBOSE, COSTS OFF) SELECT round(avg(f64)::numeric, 4) FROM dec_bin.decimals; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (round((avg(f64))::numeric, 4)) Relations: Aggregate on (decimals) Remote SQL: SELECT round(cast(avgOrNull(f64), 'Nullable(Decimal(76, 38))'), 4) FROM decimal_test.decimals (4 rows) SELECT round(avg(f64)::numeric, 4) FROM dec_bin.decimals; round ---------- 853.0858 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT round(avg(f64)::numeric) FROM dec_bin.decimals WHERE id = 5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (round((avg(f64))::numeric, 0)) Relations: Aggregate on (decimals) Remote SQL: SELECT round(cast(avgOrNull(f64), 'Nullable(Decimal(76, 38))'), 0) FROM decimal_test.decimals WHERE ((id = 5)) (4 rows) SELECT round(avg(f64)::numeric) FROM dec_bin.decimals WHERE id = 5; round ------- -2 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT round(avg(f64)::numeric, 4) FROM dec_http.decimals; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Foreign Scan Output: (round((avg(f64))::numeric, 4)) Relations: Aggregate on (decimals) Remote SQL: SELECT round(cast(avgOrNull(f64), 'Nullable(Decimal(76, 38))'), 4) FROM decimal_test.decimals (4 rows) SELECT round(avg(f64)::numeric, 4) FROM dec_http.decimals; round ---------- 853.0858 (1 row) EXPLAIN (VERBOSE, COSTS OFF) SELECT round(avg(f64)::numeric) FROM dec_http.decimals WHERE id = 5; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Foreign Scan Output: (round((avg(f64))::numeric, 0)) Relations: Aggregate on (decimals) Remote SQL: SELECT round(cast(avgOrNull(f64), 'Nullable(Decimal(76, 38))'), 0) FROM decimal_test.decimals WHERE ((id = 5)) (4 rows) SELECT round(avg(f64)::numeric) FROM dec_http.decimals WHERE id = 5; round ------- -2 (1 row) CALL clickhouse_perform('decimal_admin', 'DROP DATABASE decimal_test'); DROP USER MAPPING FOR CURRENT_USER SERVER binary_decimal_loopback; DROP USER MAPPING FOR CURRENT_USER SERVER http_decimal_loopback; DROP SERVER binary_decimal_loopback CASCADE; NOTICE: drop cascades to foreign table dec_bin.decimals DROP SERVER http_decimal_loopback CASCADE; NOTICE: drop cascades to foreign table dec_http.decimals