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 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) IMPORT FOREIGN SCHEMA "binary_inserts_test" FROM SERVER binary_inserts_loopback INTO 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 c7 SET DATA TYPE timestamp(3); \d+ complex Foreign table "public.complex" Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description --------+--------------------------------+-----------+----------+---------+-------------+----------+--------------+------------- c1 | integer | | not null | | | plain | | c2 | date | | not null | | | plain | | c3 | timestamp without time zone | | not null | | | plain | | c4 | text | | not null | | | extended | | c5 | character varying(10) | | not null | | | extended | | c6 | text | | not null | | | extended | | c7 | 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" 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-02 10:01:02.123'), (2, '2020-06-02', '2020-06-03 10:01:02', 5, 'fix_t2', 'low2', '2020-06-03 11:01:02.234'), (3, '2020-06-03', '2020-06-04 10:01:02', 5, 'fix_t3', 'low3', '2020-06-04 12:01:02'); SELECT * FROM complex ORDER BY c1; c1 | c2 | c3 | c4 | c5 | c6 | c7 ----+------------+---------------------+----+--------+------+---------------------------- 1 | 2020-06-01 | 2020-06-02 10:01:02 | t1 | fix_t1 | low1 | 2020-06-02 10:01:02.122999 2 | 2020-06-02 | 2020-06-03 10:01:02 | 5 | fix_t2 | low2 | 2020-06-03 11:01:02.234 3 | 2020-06-03 | 2020-06-04 10:01:02 | 5 | fix_t3 | low3 | 2020-06-04 12:01:02 (3 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) 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 6 other objects DETAIL: 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