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; SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS import_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE DATABASE import_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE DATABASE import_test_2'); clickhouse_raw_query ---------------------- (1 row) -- integer types SELECT clickhouse_raw_query('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); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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);'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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);'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('INSERT INTO import_test.types SELECT addDays(toDate(''1990-01-01''), number), addMinutes(addSeconds(addDays(toDateTime(''1990-01-01 10:00:00''), 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);'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE import_test.types2 ( c1 LowCardinality(Nullable(String)) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1) SETTINGS allow_nullable_key = 1; '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('INSERT INTO import_test.types2 SELECT format(''cardinal {0}'', toString(number + 1)) FROM numbers(10);'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE import_test.ip ( c1 IPv4, c2 IPv6 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ 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'); $$); clickhouse_raw_query ---------------------- (1 row) -- array types SELECT clickhouse_raw_query('CREATE TABLE import_test.arrays ( c1 Array(Int), c2 Array(String) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('INSERT INTO import_test.arrays SELECT [number, number + 1], [format(''num{0}'', toString(number)), format(''num{0}'', toString(number + 1))] FROM numbers(10);'); clickhouse_raw_query ---------------------- (1 row) -- tuple SELECT clickhouse_raw_query('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); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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);'); clickhouse_raw_query ---------------------- (1 row) -- datetime with timezones SELECT clickhouse_raw_query('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;'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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'')'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('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'')'); clickhouse_raw_query ---------------------- (1 row) 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 without 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 without time zone | | not null | | | plain | | t2 | timestamp without time zone | | not null | | | plain | | t4 | timestamp without time zone | | not null | | | plain | | t5 | timestamp without 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 10:00:00 | number 0 | num 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | two | three | 0 | cardinal 0 1990-01-02 | 1990-01-02 10:01:01 | 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 11:00:00 | 2020-01-01 11:00:00 | 2020-01-01 11:00:00 | 2020-01-01 11:00:00 2020-01-01 12:00:00 | 2020-01-01 12:00:00 | 2020-01-01 12:00:00 | 2020-01-01 12:00:00 (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 without 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 without time zone | | not null | | | plain | | t2 | timestamp without time zone | | not null | | | plain | | t4 | timestamp without time zone | | not null | | | plain | | t5 | timestamp without 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 10:00:00 | number 0 | num 0 | f4bf890f-f9dc-4332-ad5c-0c18e73f28e0 | two | three | 0 | cardinal 0 1990-01-02 | 1990-01-02 10:01:01 | 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 11:00:00 | 2020-01-01 10:00:00 | 2020-01-01 10:00:00 | 2020-01-01 11:00:00 2020-01-01 12:00:00 | 2020-01-01 11:00:00 | 2020-01-01 11:00:00 | 2020-01-01 12:00:00 (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 without 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 SELECT clickhouse_raw_query('CREATE TABLE import_test_2.custom_option (a Int64) ENGINE = MergeTree ORDER BY (a)'); clickhouse_raw_query ---------------------- (1 row) 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. SELECT clickhouse_raw_query($$ 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 ) $$); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query($$ SELECT * FROM import_test.ints WHERE c1 IN (127, -128) ORDER BY c1; $$); clickhouse_raw_query -------------------------------------------------------------------------------------------------------------------------------------------------------- -128 -32768 -2147483648 -9223372036854775808 0 0 0 0 1.1754942e-38 2.2250738585072014e-308 + 127 32767 2147483647 9223372036854775807 255 65535 4294967295 18446744073709551615 3.4028233e38 1.7976931348623157e308+ (1 row) -- Error on 18446744073709551615. SELECT * FROM clickhouse_bin.ints WHERE c1 IN (127, -128) ORDER BY c1; ERROR: pg_clickhouse: error while reading row: value 18446744073709551615 is out of range of bigint SELECT * FROM clickhouse.ints WHERE c1 IN (127, -128) ORDER BY c1; ERROR: value "18446744073709551615" is out of range for type bigint -- 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 | -1 | 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; SELECT clickhouse_raw_query('DROP DATABASE import_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('DROP DATABASE import_test_2'); clickhouse_raw_query ---------------------- (1 row) 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