set time zone 'PRC';
SET DATESTYLE='ISO, MDY';
CREATE SCHEMA cstore_array_test;
SET current_schema = cstore_array_test;
DROP TABLE IF EXISTS row_array_type;
CREATE TABLE row_array_type(
id int,
c_bool_a bool[],
c_int1_a int1[],
c_int2_a int2[],
c_int4_a int4[],
c_int8_a int8[],
c_float4_a float4[],
c_float8_a float8[],
c_numeric_a numeric(10,1)[],
c_money_a money[],
c_bpchar_a bpchar[],
c_char_a "char"[],
c_varchar_a varchar[],
c_nvarchar2 nvarchar2[],
c_text_a text[],
c_bytea_a bytea[],
c_smalldatetime_a smalldatetime[],
c_abstime_a abstime[],
c_reltime_a reltime[],
c_data_a date[],
c_time_a time[],
c_timetz_a timetz[],
c_timestamp_a timestamp[],
c_timestamptz_a timestamptz[],
c_tinterval_a tinterval[],
c_interval_a interval[],
c_inet_a inet[],
c_cidr_a cidr[],
c_bit_a bit(3)[],
c_varbit_a varbit[])
DISTRIBUTE BY hash(id);
DROP TABLE IF EXISTS cstore_support_array_type;
CREATE TABLE cstore_support_array_type (
id int,
c_bool_a bool[],
c_int1_a int1[],
c_int2_a int2[],
c_int4_a int4[],
c_int8_a int8[],
c_float4_a float4[],
c_float8_a float8[],
c_numeric_a numeric(10,1)[],
c_money_a money[],
c_bpchar_a bpchar[],
c_char_a "char"[],
c_varchar_a varchar[],
c_nvarchar2 nvarchar2[],
c_text_a text[],
c_bytea_a bytea[],
c_smalldatetime_a smalldatetime[],
c_abstime_a abstime[],
c_reltime_a reltime[],
c_data_a date[],
c_time_a time[],
c_timetz_a timetz[],
c_timestamp_a timestamp[],
c_timestamptz_a timestamptz[],
c_tinterval_a tinterval[],
c_interval_a interval[],
c_inet_a inet[],
c_cidr_a cidr[],
c_bit_a bit(3)[],
c_varbit_a varbit[])
WITH (orientation=column) DISTRIBUTE BY hash(id);
insert into row_array_type values(1, '{true, false, true, false}', '{0, 128, 255}', '{-32768, 0, 32767}', '{-2147483648, 0, 2147483647}', '{-9223372036854775808, 0, 9223372036854775807}', '{1.4013e-45, 0, 1.17549e-38, 3.40282e+38}', '{4.94065645841247e-324, 2.2250738585072e-308, 2.2250738585072e-308, 1.79769313486231e+308}', '{999999999.9, -999999999.9, 0, 0.9}', '{-92233720368547758.08, 0, 92233720368547758.07}', '{"abc", "def", "ghi", "jkl"}', '{"a", "d", "g", "j"}', '{"abc", "def", "ghi", "jkl"}', '{"abc", "def", "ghi", "jkl"}', '{"abc", "def", "ghi", "jkl"}', '{"abc", "def", "ghi", "jkl"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-18 23:59:59"}', '{"00:00:00", "01:01:01", "23:59:59"}', '{"1970-01-01", "2020-02-02", "2038-01-19"}', '{"00:00:00", "01:01:01", "23:59:59"}', '{"00:00:00", "01:01:01", "23:59:59"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"[\"1970-01-01 00:00:00+08\" \"2020-02-02 20:02:00+08\"]","[\"1990-01-01 00:00:00+08\" \"2020-02-02 20:02:00+08\"]"}', '{"2000 years", "2 months", "20 days"}', '{"192.168.0.1", "10.0.0.1", "127.0.0.1"}', '{"192.168.0.1/32", "10.0.0.1/32", "127.0.0.1/32"}', '{100, 100, 111}', '{1001, 10, 11111}');
insert into row_array_type values(2, '{true, true, true, true}', '{111, 111, 111}', '{111, 111, 111}', '{111, 111, 111}', '{111, 111, 111}', '{111, 111, 111, 111}', '{111, 111, 111, 111}', '{111, 111, 111, 111}', '{111, 111, 111}', '{"aaa", "aaa", "aaa", "aaa"}', '{"a", "a", "a", "a"}', '{"aaa", "aaa", "aaa", "aaa"}', '{"aaa", "aaa", "aaa", "aaa"}', '{"aaa", "aaa", "aaa", "aaa"}', '{"aaa", "aaa", "aaa", "aaa"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"1970-01-01 00:00:00", "1970-01-01 00:00:00", "1970-01-01 00:00:00"}', '{"01:01:01", "01:01:01", "01:01:01"}', '{"1970-01-01", "1970-01-01", "1970-01-01"}', '{"01:01:01", "01:01:01", "01:01:01"}', '{"01:01:01", "01:01:01", "01:01:01"}', '{"1970-01-01 00:00:00", "1970-01-01 00:00:00", "1970-01-01 00:00:00"}', '{"1970-01-01 00:00:00", "1970-01-01 00:00:00", "1970-01-01 00:00:00"}', '{"[\"1970-01-01 00:00:00+08\" \"1970-01-01 00:00:00+08\"]","[\"1970-01-01 00:00:00+08\" \"1970-01-01 00:00:00+08\"]"}', '{"2000 years", "2000 years", "2000 years"}', '{"192.168.0.1", "192.168.0.1", "192.168.0.1"}', '{"192.168.0.1/32", "192.168.0.1/32", "192.168.0.1/32"}', '{111, 111, 111}', '{11111, 11111, 11111}');
insert into row_array_type values(3, '{false, false, true, false}', '{50, 100, 100}', '{-50, 30000, 30000}', '{-99999, 8000000, 8000000}', '{-123456789, 1234567890, 1234567890}', '{-55.5, 123.56, 123.56, 123.56}', '{-10, 10000.1, 10000.1, 10000.1}', '{-5.5, 5.5, 5.5, 5.5}', '{-12.3, 12.3, 12.3}', '{"abc", "def", "def", "def"}', '{"a", "d", "d", "d"}', '{"abc", "def", "def", "def"}', '{"abc", "def", "def", "def"}', '{"abc", "def", "def", "def"}', '{"abc", "def", "def", "def"}', '{"2000-01-01 12:30:40", "2020-02-02 20:02:00", "2020-02-02 20:02:00"}', '{"2000-01-01 12:30:40", "2020-02-02 20:02:00", "2020-02-02 20:02:00"}', '{"12:34:56", "10:00:00", "10:00:00"}', '{"2000-01-01", "2020-02-02", "2020-02-02"}', '{"00:00:00", "01:01:01", "01:01:01"}', '{"00:00:00", "01:01:01", "23:59:59"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"1970-01-01 00:00:00", "2020-02-02 20:02:00", "2038-01-19 03:14:00"}', '{"[\"1970-01-01 00:00:00+08\" \"2020-02-02 20:02:00+08\"]","[\"1990-01-01 00:00:00+08\" \"2020-02-02 20:02:00+08\"]"}', '{"2000 years", "2 months", "20 days"}', '{"192.168.0.1", "10.0.0.1", "127.0.0.1"}', '{"192.168.0.1/32", "10.0.0.1/32", "127.0.0.1/32"}', '{100, 100, 111}', '{1001, 10, 11111}');
insert into row_array_type values(4, '{false, NULL, true, false}', '{50, NULL, 100}', '{-50, NULL, 30000}', '{-99999, NULL, 8000000}', '{-123456789, NULL, 1234567890}', '{-55.5, NULL, 123.56, 123.56}', '{-10, 10000.1, NULL, 10000.1}', '{-5.5, 5.5, NULL, 5.5}', '{-12.3, NULL, 12.3}', '{"abc", "def", NULL, "def"}', '{"a", NULL, "d", "d"}', '{"abc", NULL, "def", "def"}', '{"abc", "def", NULL, "def"}', '{"abc", NULL, "def", "def"}', '{"abc", "def", NULL, "def"}', '{"2000-01-01 12:30:40", NULL, "2020-02-02 20:02:00"}', '{"2000-01-01 12:30:40", NULL, "2020-02-02 20:02:00"}', '{"12:34:56", NULL, "10:00:00"}', '{"2000-01-01", NULL, "2020-02-02"}', '{"00:00:00", NULL, "01:01:01"}', '{"00:00:00", NULL, "23:59:59"}', '{"1970-01-01 00:00:00", NULL, "2038-01-19 03:14:00"}', '{"1970-01-01 00:00:00", NULL, "2038-01-19 03:14:00"}', '{"[\"1970-01-01 00:00:00+08\" \"2020-02-02 20:02:00+08\"]", NULL}', '{"2000 years", NULL, "20 days"}', '{"192.168.0.1", NULL, "127.0.0.1"}', '{"192.168.0.1/32", NULL, "127.0.0.1/32"}', '{100, NULL, 111}', '{1001, NULL, 11111}');
insert into row_array_type values(5, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
-- INSERT
insert into cstore_support_array_type select * from row_array_type;
-- COPY
COPY cstore_support_array_type FROM stdin;
1 {t,f,t,f} {0,128,255} {-32768,0,32767} {-2147483648,0,2147483647} {-9223372036854775808,0,9223372036854775807} {1.40129846e-45,0,1.17549001e-38,3.40282002e+38} {4.94065645841246544e-324,2.2250738585071999e-308,2.2250738585071999e-308,1.79769313486230992e+308} {999999999.9,-999999999.9,0.0,.9} {"-$92,233,720,368,547,758.08",$0.00,"$92,233,720,368,547,758.07"} {abc,def,ghi,jkl} {a,d,g,j} {abc,def,ghi,jkl} {abc,def,ghi,jkl} {abc,def,ghi,jkl} {"\\\\x616263","\\\\x646566","\\\\x676869","\\\\x6a6b6c"} {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"} {"1970-01-01 00:00:00+08","2020-02-02 20:02:00+08","2038-01-18 23:59:59+08"} {00:00:00,01:01:01,23:59:59} {1970-01-01,2020-02-02,2038-01-19} {00:00:00,01:01:01,23:59:59} {00:00:00+08,01:01:01+08,23:59:59+08} {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"} {"1970-01-01 00:00:00+08","2020-02-02 20:02:00+08","2038-01-19 03:14:00+08"} {"[\\"1970-01-01 00:00:00+08\\" \\"2020-02-02 20:02:00+08\\"]","[\\"1990-01-01 00:00:00+08\\" \\"2020-02-02 20:02:00+08\\"]"} {"2000 years","2 mons","20 days"} {192.168.0.1,10.0.0.1,127.0.0.1} {192.168.0.1/32,10.0.0.1/32,127.0.0.1/32} {100,100,111} {1001,10,11111}
2 {t,t,t,t} {111,111,111} {111,111,111} {111,111,111} {111,111,111} {111,111,111,111} {111,111,111,111} {111.0,111.0,111.0,111.0} {$111.00,$111.00,$111.00} {aaa,aaa,aaa,aaa} {a,a,a,a} {aaa,aaa,aaa,aaa} {aaa,aaa,aaa,aaa} {aaa,aaa,aaa,aaa} {"\\\\x616161","\\\\x616161","\\\\x616161","\\\\x616161"} {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"} {"1970-01-01 00:00:00+08","1970-01-01 00:00:00+08","1970-01-01 00:00:00+08"} {01:01:01,01:01:01,01:01:01} {1970-01-01,1970-01-01,1970-01-01} {01:01:01,01:01:01,01:01:01} {01:01:01+08,01:01:01+08,01:01:01+08} {"1970-01-01 00:00:00","1970-01-01 00:00:00","1970-01-01 00:00:00"} {"1970-01-01 00:00:00+08","1970-01-01 00:00:00+08","1970-01-01 00:00:00+08"} {"[\\"1970-01-01 00:00:00+08\\" \\"1970-01-01 00:00:00+08\\"]","[\\"1970-01-01 00:00:00+08\\" \\"1970-01-01 00:00:00+08\\"]"} {"2000 years","2000 years","2000 years"} {192.168.0.1,192.168.0.1,192.168.0.1} {192.168.0.1/32,192.168.0.1/32,192.168.0.1/32} {111,111,111} {11111,11111,11111}
3 {f,f,t,f} {50,100,100} {-50,30000,30000} {-99999,8000000,8000000} {-123456789,1234567890,1234567890} {-55.5,123.559998,123.559998,123.559998} {-10,10000.1000000000004,10000.1000000000004,10000.1000000000004} {-5.5,5.5,5.5,5.5} {-$12.30,$12.30,$12.30} {abc,def,def,def} {a,d,d,d} {abc,def,def,def} {abc,def,def,def} {abc,def,def,def} {"\\\\x616263","\\\\x646566","\\\\x646566","\\\\x646566"} {"2000-01-01 12:31:00","2020-02-02 20:02:00","2020-02-02 20:02:00"} {"2000-01-01 12:30:40+08","2020-02-02 20:02:00+08","2020-02-02 20:02:00+08"} {12:34:56,10:00:00,10:00:00} {2000-01-01,2020-02-02,2020-02-02} {00:00:00,01:01:01,01:01:01} {00:00:00+08,01:01:01+08,23:59:59+08} {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"} {"1970-01-01 00:00:00+08","2020-02-02 20:02:00+08","2038-01-19 03:14:00+08"} {"[\\"1970-01-01 00:00:00+08\\" \\"2020-02-02 20:02:00+08\\"]","[\\"1990-01-01 00:00:00+08\\" \\"2020-02-02 20:02:00+08\\"]"} {"2000 years","2 mons","20 days"} {192.168.0.1,10.0.0.1,127.0.0.1} {192.168.0.1/32,10.0.0.1/32,127.0.0.1/32} {100,100,111} {1001,10,11111}
4 {f,NULL,t,f} {50,NULL,100} {-50,NULL,30000} {-99999,NULL,8000000} {-123456789,NULL,1234567890} {-55.5,NULL,123.559998,123.559998} {-10,10000.1000000000004,NULL,10000.1000000000004} {-5.5,5.5,NULL,5.5} {-$12.30,NULL,$12.30} {abc,def,NULL,def} {a,NULL,d,d} {abc,NULL,def,def} {abc,def,NULL,def} {abc,NULL,def,def} {"\\\\x616263","\\\\x646566",NULL,"\\\\x646566"} {"2000-01-01 12:31:00",NULL,"2020-02-02 20:02:00"} {"2000-01-01 12:30:40+08",NULL,"2020-02-02 20:02:00+08"} {12:34:56,NULL,10:00:00} {2000-01-01,NULL,2020-02-02} {00:00:00,NULL,01:01:01} {00:00:00+08,NULL,23:59:59+08} {"1970-01-01 00:00:00",NULL,"2038-01-19 03:14:00"} {"1970-01-01 00:00:00+08",NULL,"2038-01-19 03:14:00+08"} {"[\\"1970-01-01 00:00:00+08\\" \\"2020-02-02 20:02:00+08\\"]",NULL} {"2000 years",NULL,"20 days"} {192.168.0.1,NULL,127.0.0.1} {192.168.0.1/32,NULL,127.0.0.1/32} {100,NULL,111} {1001,NULL,11111}
5 \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N
\.
;
-- ANALYZE
analyze cstore_support_array_type;
-- SELECT
select * from cstore_support_array_type order by 1;
select id, c_bool_a from cstore_support_array_type where false = ANY(c_bool_a) order by 1;
select id, c_bool_a from cstore_support_array_type where true = ALL(c_bool_a) order by 1;
select id, c_int1_a from cstore_support_array_type where 50 < ANY(c_int1_a) order by 1;
select id, c_int1_a from cstore_support_array_type where 100 < ALL(c_int1_a) order by 1;
select id, c_int4_a from cstore_support_array_type where 0 > ANY(c_int4_a) order by 1;
select id, c_int4_a from cstore_support_array_type where 0 > ALL(c_int4_a) order by 1;
select id, c_float8_a from cstore_support_array_type where 10000.1 < ANY(c_float8_a) order by 1;
select id, c_float8_a from cstore_support_array_type where 111 <= ALL(c_float8_a) order by 1;
select id, c_numeric_a from cstore_support_array_type where 0 > ANY(c_numeric_a) order by 1;
select id, c_numeric_a from cstore_support_array_type where 111 >= ALL(c_numeric_a) order by 1;
select id, c_varchar_a from cstore_support_array_type where 'def' = ANY(c_varchar_a) order by 1;
select id, c_varchar_a from cstore_support_array_type where 'aaa' = ALL(c_varchar_a) order by 1;
select id, c_text_a from cstore_support_array_type where 'aaa' = ANY(c_text_a) order by 1;
select id, c_text_a from cstore_support_array_type where 'def' = ALL(c_text_a) order by 1;
select id, c_timestamp_a from cstore_support_array_type where '1970-01-01 00:00:00'::timestamp > ANY(c_timestamp_a) order by 1;
select id, c_timestamp_a from cstore_support_array_type where '1970-01-01 00:00:00'::timestamp = ALL(c_timestamp_a) order by 1;
select id, c_timestamp_a from cstore_support_array_type where '2020-02-02 20:02:00+08'::timestamptz < ANY(c_timestamp_a) order by 1;
select id, c_timestamp_a from cstore_support_array_type where '2020-02-02 20:02:00+08'::timestamptz < ALL(c_timestamp_a) order by 1;
-- sal_emp
DROP TABLE IF EXISTS sal_emp;
CREATE TABLE sal_emp(id int, name text, pay_by_quarter integer[], schedule text[][]) WITH (orientation=column) DISTRIBUTE BY hash(id);
INSERT INTO sal_emp VALUES(1, 'Bill', '{10000, 10000, 10000, 10000}', '{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp VALUES(1, 'Carol', '{20000, 25000, 25000, 25000}', '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
INSERT INTO sal_emp VALUES(1, 'Bill', ARRAY[10000, 10000, 10000, 10000], ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);
INSERT INTO sal_emp VALUES(1, 'Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);
VACUUM FULL sal_emp;
SELECT * FROM sal_emp WHERE 25000 = ANY (pay_by_quarter) order by 1;
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter) order by 1;
-- not in
CREATE TABLE row_not_in(id int, c1 int, c2 int)
DISTRIBUTE BY hash(id)
PARTITION BY range(c1) (
partition p1 values less than(100),
partition p2 values less than(maxvalue)
);
INSERT INTO row_not_in values (1, 10, NULL);
INSERT INTO row_not_in values (1, 500, 100);
INSERT INTO row_not_in values (1, 500, 200);
INSERT INTO row_not_in values (1, 500, 300);
CREATE TABLE cstore_not_in(id int, c1 int, c2 int)
WITH (orientation=column) DISTRIBUTE BY hash(id)
PARTITION BY range(c1) (
partition p1 values less than(100),
partition p2 values less than(maxvalue)
);
INSERT INTO cstore_not_in SELECT * FROM row_not_in;
SELECT c2 not in(1,2,3) FROM cstore_not_in;
-- join
CREATE TABLE row_table_join(id int, c1 int[])
DISTRIBUTE BY hash(id);
insert into row_table_join values(1, '{1,2,3}');
insert into row_table_join values(2, '{2,2,2}');
insert into row_table_join values(3, '{3,3,3}');
insert into row_table_join values(1, '{1}');
insert into row_table_join values(2, '{2}');
insert into row_table_join values(3, '{3}');
insert into row_table_join values(1, '{1,2,3}');
insert into row_table_join values(2, '{2,3,1}');
insert into row_table_join values(3, '{3,1,2}');
insert into row_table_join values(4, '{3,1,2}');
CREATE TABLE cstore_table_join_1(id int, c1 int[])
WITH (orientation=column) DISTRIBUTE BY hash(id);
CREATE TABLE cstore_table_join_2(id int, c1 int[])
WITH (orientation=column) DISTRIBUTE BY hash(id);
INSERT INTO cstore_table_join_1 SELECT * FROM row_table_join;
INSERT INTO cstore_table_join_2 values(5, '{3,4,5}');
INSERT INTO cstore_table_join_2 SELECT * FROM row_table_join WHERE id < 3;
SELECT * FROM cstore_table_join_1 INNER JOIN cstore_table_join_2 ON cstore_table_join_1.c1=cstore_table_join_2.c1 ORDER BY 1;
SELECT * FROM cstore_table_join_1 WHERE 1 = ANY (c1) INTERSECT SELECT * FROM cstore_table_join_2 WHERE 2 = ANY(c1) ORDER BY 1;
-- group
SELECT c1, count(1) FROM cstore_table_join_1 GROUP BY c1 ORDER BY 1;
SELECT count(c1) FROM cstore_table_join_1;
SELECT count(distinct c1) FROM cstore_table_join_1;
SELECT distinct id,c1 FROM cstore_table_join_1 ORDER BY id;
-- clear
DROP SCHEMA cstore_array_test CASCADE;