SET datestyle = 'ISO'; CREATE SERVER binary_inserts_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'binary_inserts_test', driver 'binary'); CREATE USER MAPPING FOR CURRENT_USER SERVER binary_inserts_loopback; SELECT clickhouse_raw_query('drop database if exists binary_inserts_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('create database binary_inserts_test'); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.ints ( c1 Int8, c2 Int16, c3 Int32, c4 Int64 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.uints ( c1 UInt8, c2 UInt16, c3 UInt32, c4 UInt64, c5 Bool ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.floats ( c1 Float32, c2 Float64 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1) SETTINGS allow_floating_point_partition_key=1; '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.null_ints ( c1 Int8, c2 Nullable(Int32) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.complex ( c1 Int32, c2 Date, c3 DateTime, c4 String, c5 FixedString(10), c6 LowCardinality(String), c7 Date32, c8 DateTime64(3) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.arrays ( c1 Int32, c2 Array(Int32) ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) SELECT clickhouse_raw_query('CREATE TABLE binary_inserts_test.addr ( c1 UUID, c2 IPv4, c3 IPv6 ) ENGINE = MergeTree PARTITION BY c1 ORDER BY (c1); '); clickhouse_raw_query ---------------------- (1 row) CREATE SCHEMA binary_inserts_test; IMPORT FOREIGN SCHEMA binary_inserts_test FROM SERVER binary_inserts_loopback INTO binary_inserts_test; SET search_path = binary_inserts_test, public; /* ints */ INSERT INTO ints SELECT i, i + 1, i + 2, i+ 3 FROM generate_series(1, 3) i; SELECT * FROM ints ORDER BY c1; c1 | c2 | c3 | c4 ----+----+----+---- 1 | 2 | 3 | 4 2 | 3 | 4 | 5 3 | 4 | 5 | 6 (3 rows) INSERT INTO ints (c1, c4, c3, c2) SELECT i, i + 1, i + 2, i+ 3 FROM generate_series(4, 6) i; SELECT * FROM ints ORDER BY c1; c1 | c2 | c3 | c4 ----+----+----+---- 1 | 2 | 3 | 4 2 | 3 | 4 | 5 3 | 4 | 5 | 6 4 | 7 | 6 | 5 5 | 8 | 7 | 6 6 | 9 | 8 | 7 (6 rows) /* check dropping columns (that will change attnums) */ ALTER TABLE ints DROP COLUMN c1; ALTER TABLE ints ADD COLUMN c1 SMALLINT; INSERT INTO ints (c1, c2, c3, c4) SELECT i, i + 1, i + 2, i+ 3 FROM generate_series(7, 8) i; SELECT c1, c2, c3, c4 FROM ints ORDER BY c1; c1 | c2 | c3 | c4 ----+----+----+---- 1 | 2 | 3 | 4 2 | 3 | 4 | 5 3 | 4 | 5 | 6 4 | 7 | 6 | 5 5 | 8 | 7 | 6 6 | 9 | 8 | 7 7 | 8 | 9 | 10 8 | 9 | 10 | 11 (8 rows) /* check other number types */ INSERT INTO uints SELECT i, i + 1, i + 2, i+ 3, (i % 2)::bool FROM generate_series(1, 3) i; SELECT * FROM uints ORDER BY c1; c1 | c2 | c3 | c4 | c5 ----+----+----+----+---- 1 | 2 | 3 | 4 | t 2 | 3 | 4 | 5 | f 3 | 4 | 5 | 6 | t (3 rows) INSERT INTO floats SELECT i * 1.1, i + 2.1 FROM generate_series(1, 3) i; SELECT * FROM floats ORDER BY c1; c1 | c2 -----+----- 1.1 | 3.1 2.2 | 4.1 3.3 | 5.1 (3 rows) /* check nullable */ INSERT INTO null_ints SELECT i, case WHEN i % 2 = 0 THEN NULL ELSE i END FROM generate_series(1, 10) i; INSERT INTO null_ints(c1) SELECT i FROM generate_series(11, 13) i; SELECT * FROM null_ints ORDER BY c1; c1 | c2 ----+---- 1 | 1 2 | 3 | 3 4 | 5 | 5 6 | 7 | 7 8 | 9 | 9 10 | 11 | 12 | 13 | (13 rows) SELECT * FROM null_ints ORDER BY c1; c1 | c2 ----+---- 1 | 1 2 | 3 | 3 4 | 5 | 5 6 | 7 | 7 8 | 9 | 9 10 | 11 | 12 | 13 | (13 rows) /* check dates and strings */ ALTER TABLE complex ALTER COLUMN c8 SET DATA TYPE timestamp(3); \d+ complex Foreign table "binary_inserts_test.complex" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | integer | | not null | | | plain | | c2 | date | | not null | | | plain | | c3 | timestamp with time zone | | not null | | | plain | | c4 | text | | not null | | | extended | | c5 | character varying(10) | | not null | | | extended | | c6 | text | | not null | | | extended | | c7 | date | | not null | | | plain | | c8 | timestamp(3) without time zone | | not null | | | plain | | Not-null constraints: "complex_c1_not_null" NOT NULL "c1" "complex_c2_not_null" NOT NULL "c2" "complex_c3_not_null" NOT NULL "c3" "complex_c4_not_null" NOT NULL "c4" "complex_c5_not_null" NOT NULL "c5" "complex_c6_not_null" NOT NULL "c6" "complex_c7_not_null" NOT NULL "c7" "complex_c8_not_null" NOT NULL "c8" Server: binary_inserts_loopback FDW options: (database 'binary_inserts_test', table_name 'complex', engine 'MergeTree') INSERT INTO complex VALUES (1, '2020-06-01', '2020-06-02 10:01:02', 't1', 'fix_t1', 'low1', '2020-06-01', '2020-06-02 10:01:02.123'), (2, '2020-06-02', '2020-06-03 10:01:02', 5, 'fix_t2', 'low2', '2020-06-02', '2020-06-03 11:01:02.234'), (3, '2020-06-03', '2020-06-04 10:01:02', 5, 'fix_t3', 'low3', '2020-06-03', '2020-06-04 12:01:02'); SET session timezone = 'UTC'; INSERT INTO complex VALUES (4, '1970-01-01', '1970-01-01 00:00:00', 5, 'fix_t4', 'low4', '1970-01-01', '1970-01-01 00:00:00'), (5, '2000-01-01', '2000-01-01 00:00:00', 5, 'fix_t5', 'low5', '2000-01-01', '2000-01-01 00:00:00'); SELECT * FROM complex ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+------------+------------------------+----+--------+------+------------+------------------------- 1 | 2020-06-01 | 2020-06-02 17:01:02+00 | t1 | fix_t1 | low1 | 2020-06-01 | 2020-06-02 17:01:02.123 2 | 2020-06-02 | 2020-06-03 17:01:02+00 | 5 | fix_t2 | low2 | 2020-06-02 | 2020-06-03 18:01:02.234 3 | 2020-06-03 | 2020-06-04 17:01:02+00 | 5 | fix_t3 | low3 | 2020-06-03 | 2020-06-04 19:01:02 4 | 1970-01-01 | 1970-01-01 00:00:00+00 | 5 | fix_t4 | low4 | 1970-01-01 | 1970-01-01 00:00:00 5 | 2000-01-01 | 2000-01-01 00:00:00+00 | 5 | fix_t5 | low5 | 2000-01-01 | 2000-01-01 00:00:00 (5 rows) RESET timezone; SELECT * FROM complex ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 ----+------------+------------------------+----+--------+------+------------+------------------------- 1 | 2020-06-01 | 2020-06-02 10:01:02-07 | t1 | fix_t1 | low1 | 2020-06-01 | 2020-06-02 10:01:02.123 2 | 2020-06-02 | 2020-06-03 10:01:02-07 | 5 | fix_t2 | low2 | 2020-06-02 | 2020-06-03 11:01:02.234 3 | 2020-06-03 | 2020-06-04 10:01:02-07 | 5 | fix_t3 | low3 | 2020-06-03 | 2020-06-04 12:01:02 4 | 1970-01-01 | 1969-12-31 16:00:00-08 | 5 | fix_t4 | low4 | 1970-01-01 | 1969-12-31 16:00:00 5 | 2000-01-01 | 1999-12-31 16:00:00-08 | 5 | fix_t5 | low5 | 2000-01-01 | 1999-12-31 16:00:00 (5 rows) /* check arrays */ INSERT INTO arrays VALUES (1, ARRAY[1,2]), (2, ARRAY[3,4,5]), (3, ARRAY[6,4]); SELECT * FROM arrays ORDER BY c1; c1 | c2 ----+--------- 1 | {1,2} 2 | {3,4,5} 3 | {6,4} (3 rows) /* Check UUIDs and IPs */ \d addr Foreign table "binary_inserts_test.addr" Column | Type | Collation | Nullable | Default | FDW options --------+------+-----------+----------+---------+------------- c1 | uuid | | not null | | c2 | inet | | not null | | c3 | inet | | not null | | Server: binary_inserts_loopback FDW options: (database 'binary_inserts_test', table_name 'addr', engine 'MergeTree') INSERT INTO addr VALUES ('61f0c404-5cb3-11e7-907b-a6006ad3dba0', '116.106.34.242', '2001:44c8:129:2632:33:0:252:2'), ('00000000-0000-0000-0000-000000000000', '127.0.0.1', '::ffff:127.0.0.1'), ('C62848ED-7316-4D15-92F3-9BB71EB69640', '183.247.232.58', '2a02:e980:1e::1') ; SELECT * FROM addr ORDER BY c1; c1 | c2 | c3 --------------------------------------+----------------+------------------------------- 00000000-0000-0000-0000-000000000000 | 127.0.0.1 | ::ffff:127.0.0.1 61f0c404-5cb3-11e7-907b-a6006ad3dba0 | 116.106.34.242 | 2001:44c8:129:2632:33:0:252:2 c62848ed-7316-4d15-92f3-9bb71eb69640 | 183.247.232.58 | 2a02:e980:1e::1 (3 rows) DROP USER MAPPING FOR CURRENT_USER SERVER binary_inserts_loopback; SELECT clickhouse_raw_query('DROP DATABASE binary_inserts_test'); clickhouse_raw_query ---------------------- (1 row) DROP SERVER binary_inserts_loopback CASCADE; NOTICE: drop cascades to 7 other objects DETAIL: drop cascades to foreign table addr drop cascades to foreign table arrays drop cascades to foreign table complex drop cascades to foreign table floats drop cascades to foreign table ints drop cascades to foreign table null_ints drop cascades to foreign table uints