SET datestyle = 'ISO'; CREATE SERVER import_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'import_test', driver 'http'); CREATE SERVER import_loopback_bin FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'import_test', driver 'binary'); CREATE SCHEMA clickhouse; CREATE SCHEMA clickhouse_bin; CREATE SCHEMA clickhouse_limit; CREATE SCHEMA clickhouse_except; CREATE USER MAPPING FOR CURRENT_USER SERVER import_loopback; CREATE USER MAPPING FOR CURRENT_USER SERVER import_loopback_bin; CREATE SERVER import_admin FOREIGN DATA WRAPPER clickhouse_fdw; CREATE USER MAPPING FOR CURRENT_USER SERVER import_admin; CALL clickhouse_perform('import_admin', 'DROP DATABASE IF EXISTS import_test'); CALL clickhouse_perform('import_admin', 'CREATE DATABASE import_test'); CALL clickhouse_perform('import_admin', 'DROP DATABASE IF EXISTS import_test_2'); CALL clickhouse_perform('import_admin', 'CREATE DATABASE import_test_2'); -- numeric types CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.ints ( c1 Int8, c2 Int16, c3 Int32, c4 Int64, c5 UInt8, c6 UInt16, c7 UInt32, c8 UInt64, c9 Float32, c10 Nullable(Float64) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.ints SELECT number, number + 1, number + 2, number + 3, number + 4, number + 5, number + 6, number + 7, number + 8.1, number + 9.2 FROM numbers(10);'); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.ints SELECT number, number + 1, number + 2, number + 3, number + 4, number + 5, number + 6, number + 7, number + 8.1, NULL FROM numbers(10, 2);'); CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.types ( c1 Date, c2 DateTime, c3 String, c4 FixedString(5), c5 UUID, c6 Enum8(''one'' = 1, ''two'' = 2), c7 Enum16(''one'' = 1, ''two'' = 2, ''three'' = 3), c9 Nullable(FixedString(50)), c8 LowCardinality(String) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.types SELECT addDays(toDate(''1990-01-01''), number), addMinutes(addSeconds(addDays(toDateTime(''1990-01-01 10:00:00'', ''UTC''), number), number), number), format(''number {0}'', toString(number)), format(''num {0}'', toString(number)), format(''f4bf890f-f9dc-4332-ad5c-0c18e73f28e{0}'', toString(number)), ''two'', ''three'', toString(number), format(''cardinal {0}'', toString(number)) FROM numbers(10);'); CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.types2 ( c1 LowCardinality(Nullable(String)) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1) SETTINGS allow_nullable_key = 1; '); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.types2 SELECT format(''cardinal {0}'', toString(number + 1)) FROM numbers(10);'); CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.ip ( c1 IPv4, c2 IPv6 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); CALL clickhouse_perform('import_admin', $$ INSERT INTO import_test.ip VALUES ('116.106.34.242', '2001:44c8:129:2632:33:0:252:2'), ('116.106.34.243', '2a02:e980:1e::1'), ('116.106.34.244', '::1'); $$); -- array types CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.arrays ( c1 Array(Int), c2 Array(String) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.arrays SELECT [number, number + 1], [format(''num{0}'', toString(number)), format(''num{0}'', toString(number + 1))] FROM numbers(10);'); -- tuple CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.tuples ( c1 Int8, c2 Tuple(Int, String, Float32), c3 Nested(a Int, b Int), c4 Int16 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.tuples SELECT number, (number, toString(number), number + 1.0), [toInt32(number),1,1], [toInt32(number),2,2], toInt16(number) FROM numbers(10);'); -- datetime with timezones CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test.timezones ( t1 DateTime64(6,''UTC''), t2 DateTime64(6,''Europe/Berlin''), t4 DateTime(''Europe/Berlin''), t5 DateTime64(6)) ENGINE = MergeTree ORDER BY (t1) SETTINGS index_granularity=8192;'); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.timezones VALUES ( ''2020-01-01 11:00:00'', ''2020-01-01 11:00:00'', ''2020-01-01 11:00:00'', ''2020-01-01 11:00:00'')'); CALL clickhouse_perform('import_admin', 'INSERT INTO import_test.timezones VALUES ( ''2020-01-01 12:00:00'', ''2020-01-01 12:00:00'', ''2020-01-01 12:00:00'', ''2020-01-01 12:00:00'')'); -- Injection attempt should import nothing. IMPORT FOREIGN SCHEMA "import_test' OR database = 'public" FROM SERVER import_loopback INTO clickhouse; \det clickhouse.* List of foreign tables Schema | Table | Server --------+-------+-------- (0 rows) -- Should succeed. IMPORT FOREIGN SCHEMA import_test FROM SERVER import_loopback INTO clickhouse; NOTICE: pg_clickhouse: ClickHouse type was translated to type for column "c2", please create composite type and alter the column if needed \d+ clickhouse.ints; Foreign table "clickhouse.ints" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------------------+-----------+----------+---------+-------------+---------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | smallint | | not null | | | plain | | c3 | integer | | not null | | | plain | | c4 | bigint | | not null | | | plain | | c5 | smallint | | not null | | | plain | | c6 | integer | | not null | | | plain | | c7 | bigint | | not null | | | plain | | c8 | bigint | | not null | | | plain | | c9 | real | | not null | | | plain | | c10 | double precision | | | | | plain | | Not-null constraints: "ints_c1_not_null" NOT NULL "c1" "ints_c2_not_null" NOT NULL "c2" "ints_c3_not_null" NOT NULL "c3" "ints_c4_not_null" NOT NULL "c4" "ints_c5_not_null" NOT NULL "c5" "ints_c6_not_null" NOT NULL "c6" "ints_c7_not_null" NOT NULL "c7" "ints_c8_not_null" NOT NULL "c8" "ints_c9_not_null" NOT NULL "c9" Server: import_loopback FDW options: (database 'import_test', table_name 'ints', engine 'MergeTree') \d+ clickhouse.types; Foreign table "clickhouse.types" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | date | | not null | | | plain | | c2 | timestamp with time zone | | not null | | | plain | | c3 | text | | not null | | | extended | | c4 | character varying(5) | | not null | | | extended | | c5 | uuid | | not null | | | plain | | c6 | text | | not null | | | extended | | c7 | text | | not null | | | extended | | c9 | character varying(50) | | | | | extended | | c8 | text | | not null | | | extended | | Not-null constraints: "types_c1_not_null" NOT NULL "c1" "types_c2_not_null" NOT NULL "c2" "types_c3_not_null" NOT NULL "c3" "types_c4_not_null" NOT NULL "c4" "types_c5_not_null" NOT NULL "c5" "types_c6_not_null" NOT NULL "c6" "types_c7_not_null" NOT NULL "c7" "types_c8_not_null" NOT NULL "c8" Server: import_loopback FDW options: (database 'import_test', table_name 'types', engine 'MergeTree') \d+ clickhouse.types2; Foreign table "clickhouse.types2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | text | | | | | extended | | Server: import_loopback FDW options: (database 'import_test', table_name 'types2', engine 'MergeTree') \d+ clickhouse.arrays; Foreign table "clickhouse.arrays" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | integer[] | | not null | | | extended | | c2 | text[] | | not null | | | extended | | Not-null constraints: "arrays_c1_not_null" NOT NULL "c1" "arrays_c2_not_null" NOT NULL "c2" Server: import_loopback FDW options: (database 'import_test', table_name 'arrays', engine 'MergeTree') \d+ clickhouse.tuples; Foreign table "clickhouse.tuples" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | text | | not null | | | extended | | c3.a | integer[] | | not null | | | extended | | c3.b | integer[] | | not null | | | extended | | c4 | smallint | | not null | | | plain | | Not-null constraints: "tuples_c1_not_null" NOT NULL "c1" "tuples_c2_not_null" NOT NULL "c2" "tuples_c3.a_not_null" NOT NULL "c3.a" "tuples_c3.b_not_null" NOT NULL "c3.b" "tuples_c4_not_null" NOT NULL "c4" Server: import_loopback FDW options: (database 'import_test', table_name 'tuples', engine 'MergeTree') \d+ clickhouse.timezones; Foreign table "clickhouse.timezones" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------+-----------+----------+---------+-------------+---------+--------------+------------- t1 | timestamp with time zone | | not null | | | plain | | t2 | timestamp with time zone | | not null | | | plain | | t4 | timestamp with time zone | | not null | | | plain | | t5 | timestamp with time zone | | not null | | | plain | | Not-null constraints: "timezones_t1_not_null" NOT NULL "t1" "timezones_t2_not_null" NOT NULL "t2" "timezones_t4_not_null" NOT NULL "t4" "timezones_t5_not_null" NOT NULL "t5" Server: import_loopback FDW options: (database 'import_test', table_name 'timezones', engine 'MergeTree') \d+ clickhouse.ip; Foreign table "clickhouse.ip" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------+-----------+----------+---------+-------------+---------+--------------+------------- c1 | inet | | not null | | | main | | c2 | inet | | not null | | | main | | Not-null constraints: "ip_c1_not_null" NOT NULL "c1" "ip_c2_not_null" NOT NULL "c2" Server: import_loopback FDW options: (database 'import_test', table_name 'ip', engine 'MergeTree') SELECT * FROM clickhouse.ints ORDER BY c1 DESC LIMIT 4; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 ----+----+----+----+----+----+----+----+------+------ 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19.1 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18.1 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17.1 | 18.2 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16.1 | 17.2 (4 rows) SELECT * FROM clickhouse.types ORDER BY c1 LIMIT 2; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c9 | c8 ------------+------------------------+----------+-------+--------------------------------------+-----+-------+----+------------ 1990-01-01 | 1990-01-01 02:00:00-08 | number 0 | num 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | two | three | 0 | cardinal 0 1990-01-02 | 1990-01-02 02:01:01-08 | number 1 | num 1 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e1 | two | three | 1 | cardinal 1 (2 rows) SELECT * FROM clickhouse.types2 ORDER BY c1 LIMIT 2; c1 ------------- cardinal 1 cardinal 10 (2 rows) SELECT * FROM clickhouse.arrays ORDER BY c1 LIMIT 2; c1 | c2 -------+------------- {0,1} | {num0,num1} {1,2} | {num1,num2} (2 rows) SELECT * FROM clickhouse.tuples ORDER BY c1 LIMIT 2; c1 | c2 | c3.a | c3.b | c4 ----+---------+---------+---------+---- 0 | (0,0,1) | {0,1,1} | {0,2,2} | 0 1 | (1,1,2) | {1,1,1} | {1,2,2} | 1 (2 rows) SELECT * FROM clickhouse.timezones ORDER BY t1 LIMIT 2; t1 | t2 | t4 | t5 ------------------------+------------------------+------------------------+------------------------ 2020-01-01 03:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 03:00:00-08 2020-01-01 04:00:00-08 | 2020-01-01 03:00:00-08 | 2020-01-01 03:00:00-08 | 2020-01-01 04:00:00-08 (2 rows) SELECT * FROM clickhouse.ip ORDER BY c1; c1 | c2 ----------------+------------------------------- 116.106.34.242 | 2001:44c8:129:2632:33:0:252:2 116.106.34.243 | 2a02:e980:1e::1 116.106.34.244 | ::1 (3 rows) IMPORT FOREIGN SCHEMA "import_test" FROM SERVER import_loopback_bin INTO clickhouse_bin; NOTICE: pg_clickhouse: ClickHouse type was translated to type for column "c2", please create composite type and alter the column if needed \d+ clickhouse_bin.ints; Foreign table "clickhouse_bin.ints" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------------------+-----------+----------+---------+-------------+---------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | smallint | | not null | | | plain | | c3 | integer | | not null | | | plain | | c4 | bigint | | not null | | | plain | | c5 | smallint | | not null | | | plain | | c6 | integer | | not null | | | plain | | c7 | bigint | | not null | | | plain | | c8 | bigint | | not null | | | plain | | c9 | real | | not null | | | plain | | c10 | double precision | | | | | plain | | Not-null constraints: "ints_c1_not_null" NOT NULL "c1" "ints_c2_not_null" NOT NULL "c2" "ints_c3_not_null" NOT NULL "c3" "ints_c4_not_null" NOT NULL "c4" "ints_c5_not_null" NOT NULL "c5" "ints_c6_not_null" NOT NULL "c6" "ints_c7_not_null" NOT NULL "c7" "ints_c8_not_null" NOT NULL "c8" "ints_c9_not_null" NOT NULL "c9" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'ints', engine 'MergeTree') \d+ clickhouse_bin.types; Foreign table "clickhouse_bin.types" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | date | | not null | | | plain | | c2 | timestamp with time zone | | not null | | | plain | | c3 | text | | not null | | | extended | | c4 | character varying(5) | | not null | | | extended | | c5 | uuid | | not null | | | plain | | c6 | text | | not null | | | extended | | c7 | text | | not null | | | extended | | c9 | character varying(50) | | | | | extended | | c8 | text | | not null | | | extended | | Not-null constraints: "types_c1_not_null" NOT NULL "c1" "types_c2_not_null" NOT NULL "c2" "types_c3_not_null" NOT NULL "c3" "types_c4_not_null" NOT NULL "c4" "types_c5_not_null" NOT NULL "c5" "types_c6_not_null" NOT NULL "c6" "types_c7_not_null" NOT NULL "c7" "types_c8_not_null" NOT NULL "c8" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'types', engine 'MergeTree') \d+ clickhouse_bin.types2; Foreign table "clickhouse_bin.types2" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | text | | | | | extended | | Server: import_loopback_bin FDW options: (database 'import_test', table_name 'types2', engine 'MergeTree') \d+ clickhouse_bin.arrays; Foreign table "clickhouse_bin.arrays" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | integer[] | | not null | | | extended | | c2 | text[] | | not null | | | extended | | Not-null constraints: "arrays_c1_not_null" NOT NULL "c1" "arrays_c2_not_null" NOT NULL "c2" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'arrays', engine 'MergeTree') \d+ clickhouse_bin.tuples; Foreign table "clickhouse_bin.tuples" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | text | | not null | | | extended | | c3.a | integer[] | | not null | | | extended | | c3.b | integer[] | | not null | | | extended | | c4 | smallint | | not null | | | plain | | Not-null constraints: "tuples_c1_not_null" NOT NULL "c1" "tuples_c2_not_null" NOT NULL "c2" "tuples_c3.a_not_null" NOT NULL "c3.a" "tuples_c3.b_not_null" NOT NULL "c3.b" "tuples_c4_not_null" NOT NULL "c4" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'tuples', engine 'MergeTree') \d+ clickhouse_bin.timezones; Foreign table "clickhouse_bin.timezones" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------+-----------+----------+---------+-------------+---------+--------------+------------- t1 | timestamp with time zone | | not null | | | plain | | t2 | timestamp with time zone | | not null | | | plain | | t4 | timestamp with time zone | | not null | | | plain | | t5 | timestamp with time zone | | not null | | | plain | | Not-null constraints: "timezones_t1_not_null" NOT NULL "t1" "timezones_t2_not_null" NOT NULL "t2" "timezones_t4_not_null" NOT NULL "t4" "timezones_t5_not_null" NOT NULL "t5" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'timezones', engine 'MergeTree') \d+ clickhouse_bin.ip; Foreign table "clickhouse_bin.ip" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------+-----------+----------+---------+-------------+---------+--------------+------------- c1 | inet | | not null | | | main | | c2 | inet | | not null | | | main | | Not-null constraints: "ip_c1_not_null" NOT NULL "c1" "ip_c2_not_null" NOT NULL "c2" Server: import_loopback_bin FDW options: (database 'import_test', table_name 'ip', engine 'MergeTree') SELECT * FROM clickhouse_bin.ints ORDER BY c1 DESC LIMIT 4; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 ----+----+----+----+----+----+----+----+------+------ 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19.1 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18.1 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17.1 | 18.2 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16.1 | 17.2 (4 rows) SELECT * FROM clickhouse_bin.types ORDER BY c1 LIMIT 2; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c9 | c8 ------------+------------------------+----------+-------+--------------------------------------+-----+-------+----+------------ 1990-01-01 | 1990-01-01 02:00:00-08 | number 0 | num 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | two | three | 0 | cardinal 0 1990-01-02 | 1990-01-02 02:01:01-08 | number 1 | num 1 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e1 | two | three | 1 | cardinal 1 (2 rows) SELECT * FROM clickhouse_bin.types2 ORDER BY c1 LIMIT 2; c1 ------------- cardinal 1 cardinal 10 (2 rows) SELECT * FROM clickhouse_bin.arrays ORDER BY c1 LIMIT 2; c1 | c2 -------+------------- {0,1} | {num0,num1} {1,2} | {num1,num2} (2 rows) SELECT * FROM clickhouse_bin.tuples ORDER BY c1 LIMIT 2; c1 | c2 | c3.a | c3.b | c4 ----+---------+---------+---------+---- 0 | (0,0,1) | {0,1,1} | {0,2,2} | 0 1 | (1,1,2) | {1,1,1} | {1,2,2} | 1 (2 rows) SELECT * FROM clickhouse_bin.timezones ORDER BY t1 LIMIT 2; t1 | t2 | t4 | t5 ------------------------+------------------------+------------------------+------------------------ 2020-01-01 03:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 02:00:00-08 | 2020-01-01 03:00:00-08 2020-01-01 04:00:00-08 | 2020-01-01 03:00:00-08 | 2020-01-01 03:00:00-08 | 2020-01-01 04:00:00-08 (2 rows) SELECT * FROM clickhouse.ip ORDER BY c1; c1 | c2 ----------------+------------------------------- 116.106.34.242 | 2001:44c8:129:2632:33:0:252:2 116.106.34.243 | 2a02:e980:1e::1 116.106.34.244 | ::1 (3 rows) IMPORT FOREIGN SCHEMA "import_test" LIMIT TO (ints, types) FROM SERVER import_loopback INTO clickhouse_limit; \d+ clickhouse_limit.ints; Foreign table "clickhouse_limit.ints" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+------------------+-----------+----------+---------+-------------+---------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | smallint | | not null | | | plain | | c3 | integer | | not null | | | plain | | c4 | bigint | | not null | | | plain | | c5 | smallint | | not null | | | plain | | c6 | integer | | not null | | | plain | | c7 | bigint | | not null | | | plain | | c8 | bigint | | not null | | | plain | | c9 | real | | not null | | | plain | | c10 | double precision | | | | | plain | | Not-null constraints: "ints_c1_not_null" NOT NULL "c1" "ints_c2_not_null" NOT NULL "c2" "ints_c3_not_null" NOT NULL "c3" "ints_c4_not_null" NOT NULL "c4" "ints_c5_not_null" NOT NULL "c5" "ints_c6_not_null" NOT NULL "c6" "ints_c7_not_null" NOT NULL "c7" "ints_c8_not_null" NOT NULL "c8" "ints_c9_not_null" NOT NULL "c9" Server: import_loopback FDW options: (database 'import_test', table_name 'ints', engine 'MergeTree') \d+ clickhouse_limit.types; Foreign table "clickhouse_limit.types" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | date | | not null | | | plain | | c2 | timestamp with time zone | | not null | | | plain | | c3 | text | | not null | | | extended | | c4 | character varying(5) | | not null | | | extended | | c5 | uuid | | not null | | | plain | | c6 | text | | not null | | | extended | | c7 | text | | not null | | | extended | | c9 | character varying(50) | | | | | extended | | c8 | text | | not null | | | extended | | Not-null constraints: "types_c1_not_null" NOT NULL "c1" "types_c2_not_null" NOT NULL "c2" "types_c3_not_null" NOT NULL "c3" "types_c4_not_null" NOT NULL "c4" "types_c5_not_null" NOT NULL "c5" "types_c6_not_null" NOT NULL "c6" "types_c7_not_null" NOT NULL "c7" "types_c8_not_null" NOT NULL "c8" Server: import_loopback FDW options: (database 'import_test', table_name 'types', engine 'MergeTree') \d+ clickhouse_limit.arrays; \d+ clickhouse_limit.tuples; IMPORT FOREIGN SCHEMA "import_test" EXCEPT (ints, types) FROM SERVER import_loopback INTO clickhouse_except; NOTICE: pg_clickhouse: ClickHouse type was translated to type for column "c2", please create composite type and alter the column if needed \d+ clickhouse_except.ints; \d+ clickhouse_except.types; \d+ clickhouse_except.arrays; Foreign table "clickhouse_except.arrays" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | integer[] | | not null | | | extended | | c2 | text[] | | not null | | | extended | | Not-null constraints: "arrays_c1_not_null" NOT NULL "c1" "arrays_c2_not_null" NOT NULL "c2" Server: import_loopback FDW options: (database 'import_test', table_name 'arrays', engine 'MergeTree') \d+ clickhouse_except.tuples; Foreign table "clickhouse_except.tuples" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+-----------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | smallint | | not null | | | plain | | c2 | text | | not null | | | extended | | c3.a | integer[] | | not null | | | extended | | c3.b | integer[] | | not null | | | extended | | c4 | smallint | | not null | | | plain | | Not-null constraints: "tuples_c1_not_null" NOT NULL "c1" "tuples_c2_not_null" NOT NULL "c2" "tuples_c3.a_not_null" NOT NULL "c3.a" "tuples_c3.b_not_null" NOT NULL "c3.b" "tuples_c4_not_null" NOT NULL "c4" Server: import_loopback FDW options: (database 'import_test', table_name 'tuples', engine 'MergeTree') -- check custom database CALL clickhouse_perform('import_admin', 'CREATE TABLE import_test_2.custom_option (a Int64) ENGINE = MergeTree ORDER BY (a)'); IMPORT FOREIGN SCHEMA "import_test_2" FROM SERVER import_loopback INTO clickhouse; EXPLAIN VERBOSE SELECT * FROM clickhouse.custom_option; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on clickhouse.custom_option (cost=10.00..20.00 rows=1000 width=8) Output: a Remote SQL: SELECT a FROM import_test_2.custom_option (3 rows) ALTER FOREIGN TABLE clickhouse.custom_option OPTIONS (DROP database); EXPLAIN VERBOSE SELECT * FROM clickhouse.custom_option; QUERY PLAN --------------------------------------------------------------------------------- Foreign Scan on clickhouse.custom_option (cost=10.00..20.00 rows=1000 width=8) Output: a Remote SQL: SELECT a FROM import_test.custom_option (3 rows) -- check overflows. CALL clickhouse_perform('import_admin', $$ INSERT INTO import_test.ints (c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) VALUES ( -- Min values -128, -32768, -2147483648, -9223372036854775808, 0, 0, 0, 0, 1.175494351e-38, 2.2250738585072014e-308 ), ( -- Max values 127, 32767, 2147483647, 9223372036854775807, 255, 65535, 4294967295, 18446744073709551615, 3.402823466e+38, 1.7976931348623158e+308 ) $$); SELECT * FROM clickhouse_query('import_admin', $$ SELECT c1, c2, c3, c4, c5, c6, c7, c9, c10 FROM import_test.ints WHERE c1 IN (127, -128) ORDER BY c1; $$) AS t(c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7 text, c9 text, c10 text); c1 | c2 | c3 | c4 | c5 | c6 | c7 | c9 | c10 ------+--------+-------------+----------------------+-----+-------+------------+---------------+------------------------- -128 | -32768 | -2147483648 | -9223372036854775808 | 0 | 0 | 0 | 1.1754942e-38 | 2.2250738585072014e-308 127 | 32767 | 2147483647 | 9223372036854775807 | 255 | 65535 | 4294967295 | 3.4028233e+38 | 1.7976931348623157e+308 (2 rows) -- Error on 18446744073709551615. SELECT * FROM clickhouse_bin.ints WHERE c1 IN (127, -128) ORDER BY c1; ERROR: pg_clickhouse: value 18446744073709551615 is out of range of bigint DETAIL: Remote Query: SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 FROM import_test.ints WHERE ((c1 IN (127,(-128)))) ORDER BY c1 ASC NULLS LAST SELECT * FROM clickhouse.ints WHERE c1 IN (127, -128) ORDER BY c1; ERROR: pg_clickhouse: value 18446744073709551615 is out of range of bigint DETAIL: Remote Query: SELECT c1, c2, c3, c4, c5, c6, c7, c8, c9, c10 FROM import_test.ints WHERE ((c1 IN (127,(-128)))) ORDER BY c1 ASC NULLS LAST -- Ignore 18446744073709551615 SELECT * FROM clickhouse_bin.ints WHERE c1 = -128 UNION SELECT c1, c2, c3, c4, c5, c6, c7, NULL, c9, c10 FROM clickhouse_bin.ints WHERE c1 = 127 ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 ------+--------+-------------+----------------------+-----+-------+------------+----+---------------+------------------------- -128 | -32768 | -2147483648 | -9223372036854775808 | 0 | 0 | 0 | 0 | 1.1754942e-38 | 2.2250738585072014e-308 127 | 32767 | 2147483647 | 9223372036854775807 | 255 | 65535 | 4294967295 | | 3.4028233e+38 | 1.7976931348623157e+308 (2 rows) SELECT * FROM clickhouse.ints WHERE c1 = -128 UNION SELECT c1, c2, c3, c4, c5, c6, c7, NULL, c9, c10 FROM clickhouse.ints WHERE c1 = 127 ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c9 | c10 ------+--------+-------------+----------------------+-----+-------+------------+----+---------------+------------------------- -128 | -32768 | -2147483648 | -9223372036854775808 | 0 | 0 | 0 | 0 | 1.1754942e-38 | 2.2250738585072014e-308 127 | 32767 | 2147483647 | 9223372036854775807 | 255 | 65535 | 4294967295 | | 3.4028233e+38 | 1.7976931348623157e+308 (2 rows) DROP USER MAPPING FOR CURRENT_USER SERVER import_loopback; DROP USER MAPPING FOR CURRENT_USER SERVER import_loopback_bin; CALL clickhouse_perform('import_admin', 'DROP DATABASE import_test'); CALL clickhouse_perform('import_admin', 'DROP DATABASE import_test_2'); DROP SERVER import_loopback_bin CASCADE; NOTICE: drop cascades to 7 other objects DETAIL: drop cascades to foreign table clickhouse_bin.arrays drop cascades to foreign table clickhouse_bin.ints drop cascades to foreign table clickhouse_bin.ip drop cascades to foreign table clickhouse_bin.timezones drop cascades to foreign table clickhouse_bin.tuples drop cascades to foreign table clickhouse_bin.types drop cascades to foreign table clickhouse_bin.types2 DROP SERVER import_loopback CASCADE; NOTICE: drop cascades to 15 other objects DETAIL: drop cascades to foreign table clickhouse.arrays drop cascades to foreign table clickhouse.ints drop cascades to foreign table clickhouse.ip drop cascades to foreign table clickhouse.timezones drop cascades to foreign table clickhouse.tuples drop cascades to foreign table clickhouse.types drop cascades to foreign table clickhouse.types2 drop cascades to foreign table clickhouse_limit.ints drop cascades to foreign table clickhouse_limit.types drop cascades to foreign table clickhouse_except.arrays drop cascades to foreign table clickhouse_except.ip drop cascades to foreign table clickhouse_except.timezones drop cascades to foreign table clickhouse_except.tuples drop cascades to foreign table clickhouse_except.types2 drop cascades to foreign table clickhouse.custom_option