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;
NOTICE: table "row_array_type" does not exist, skipping
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;
NOTICE: table "cstore_support_array_type" does not exist, skipping
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;
;
-- ANALYZE
analyze cstore_support_array_type;
-- SELECT
select * from cstore_support_array_type order by 1;
id | c_bool_a | c_int1_a | c_int2_a | c_int4_a | c_int8_a | c_float4_a | c_float8_a | c_numeric_a | c_money_a | c_bpchar_a | c_char_a | c_varchar_a | c_nvarchar2 | c_text_a | c_bytea_a | c_smalldatetime_a | c_abstime_a | c_reltime_a | c_data_a | c_time_a | c_timetz_a | c_timestamp_a | c_timestamptz_a | c_tinterval_a | c_interval_a | c_inet_a | c_cidr_a | c_bit_a | c_varbit_a
----+--------------+---------------+-------------------+----------------------------+----------------------------------------------+----------------------------------------+-----------------------------------------------------------------------------------------+-----------------------------------+--------------------------------------------------------------------+--------------------+--------------+--------------------+--------------------+--------------------+---------------------------------------------------+---------------------------------------------------------------------+------------------------------------------------------------------------------+------------------------------------------------------------------------+---------------------------------------------------------------------+------------------------------+---------------------------------------+---------------------------------------------------------------------+------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------------------------------------+------------------------------------------------+----------------+---------------------
1 | {t,f,t,f} | {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} | {"-$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"} | {"@ 0","@ 1 hour 1 min 1 sec","@ 23 hours 59 mins 59 secs"} | {"1970-01-01 00:00:00","2020-02-02 00:00:00","2038-01-19 00:00:00"} | {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}
1 | {t,f,t,f} | {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} | {"-$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"} | {"@ 0","@ 1 hour 1 min 1 sec","@ 23 hours 59 mins 59 secs"} | {"1970-01-01 00:00:00","2020-02-02 00:00:00","2038-01-19 00:00:00"} | {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"} | {"@ 1 hour 1 min 1 sec","@ 1 hour 1 min 1 sec","@ 1 hour 1 min 1 sec"} | {"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} | {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}
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"} | {"@ 1 hour 1 min 1 sec","@ 1 hour 1 min 1 sec","@ 1 hour 1 min 1 sec"} | {"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} | {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.56,123.56,123.56} | {-10,10000.1,10000.1,10000.1} | {-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 hours 34 mins 56 secs","@ 10 hours","@ 10 hours"} | {"2000-01-01 00:00:00","2020-02-02 00:00:00","2020-02-02 00:00:00"} | {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}
3 | {f,f,t,f} | {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.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 hours 34 mins 56 secs","@ 10 hours","@ 10 hours"} | {"2000-01-01 00:00:00","2020-02-02 00:00:00","2020-02-02 00:00:00"} | {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.56,123.56} | {-10,10000.1,NULL,10000.1} | {-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 hours 34 mins 56 secs",NULL,"@ 10 hours"} | {"2000-01-01 00:00:00",NULL,"2020-02-02 00:00:00"} | {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}
4 | {f,NULL,t,f} | {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.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 hours 34 mins 56 secs",NULL,"@ 10 hours"} | {"2000-01-01 00:00:00",NULL,"2020-02-02 00:00:00"} | {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 | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
5 | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
(10 rows)
select id, c_bool_a from cstore_support_array_type where false = ANY(c_bool_a) order by 1;
id | c_bool_a
----+--------------
1 | {t,f,t,f}
1 | {t,f,t,f}
3 | {f,f,t,f}
3 | {f,f,t,f}
4 | {f,NULL,t,f}
4 | {f,NULL,t,f}
(6 rows)
select id, c_bool_a from cstore_support_array_type where true = ALL(c_bool_a) order by 1;
id | c_bool_a
----+-----------
2 | {t,t,t,t}
2 | {t,t,t,t}
(2 rows)
select id, c_int1_a from cstore_support_array_type where 50 < ANY(c_int1_a) order by 1;
id | c_int1_a
----+---------------
1 | {0,128,255}
1 | {0,128,255}
2 | {111,111,111}
2 | {111,111,111}
3 | {50,100,100}
3 | {50,100,100}
4 | {50,NULL,100}
4 | {50,NULL,100}
(8 rows)
select id, c_int1_a from cstore_support_array_type where 100 < ALL(c_int1_a) order by 1;
id | c_int1_a
----+---------------
2 | {111,111,111}
2 | {111,111,111}
(2 rows)
select id, c_int4_a from cstore_support_array_type where 0 > ANY(c_int4_a) order by 1;
id | c_int4_a
----+----------------------------
1 | {-2147483648,0,2147483647}
1 | {-2147483648,0,2147483647}
3 | {-99999,8000000,8000000}
3 | {-99999,8000000,8000000}
4 | {-99999,NULL,8000000}
4 | {-99999,NULL,8000000}
(6 rows)
select id, c_int4_a from cstore_support_array_type where 0 > ALL(c_int4_a) order by 1;
id | c_int4_a
----+----------
(0 rows)
select id, c_float8_a from cstore_support_array_type where 10000.1 < ANY(c_float8_a) order by 1;
id | c_float8_a
----+-----------------------------------------------------------------------------------------
1 | {4.94065645841247e-324,2.2250738585072e-308,2.2250738585072e-308,1.79769313486231e+308}
1 | {4.94065645841247e-324,2.2250738585072e-308,2.2250738585072e-308,1.79769313486231e+308}
(2 rows)
select id, c_float8_a from cstore_support_array_type where 111 <= ALL(c_float8_a) order by 1;
id | c_float8_a
----+-------------------
2 | {111,111,111,111}
2 | {111,111,111,111}
(2 rows)
select id, c_numeric_a from cstore_support_array_type where 0 > ANY(c_numeric_a) order by 1;
id | c_numeric_a
----+-----------------------------------
1 | {999999999.9,-999999999.9,0.0,.9}
1 | {999999999.9,-999999999.9,0.0,.9}
3 | {-5.5,5.5,5.5,5.5}
3 | {-5.5,5.5,5.5,5.5}
4 | {-5.5,5.5,NULL,5.5}
4 | {-5.5,5.5,NULL,5.5}
(6 rows)
select id, c_numeric_a from cstore_support_array_type where 111 >= ALL(c_numeric_a) order by 1;
id | c_numeric_a
----+---------------------------
2 | {111.0,111.0,111.0,111.0}
2 | {111.0,111.0,111.0,111.0}
3 | {-5.5,5.5,5.5,5.5}
3 | {-5.5,5.5,5.5,5.5}
(4 rows)
select id, c_varchar_a from cstore_support_array_type where 'def' = ANY(c_varchar_a) order by 1;
id | c_varchar_a
----+--------------------
1 | {abc,def,ghi,jkl}
1 | {abc,def,ghi,jkl}
3 | {abc,def,def,def}
3 | {abc,def,def,def}
4 | {abc,NULL,def,def}
4 | {abc,NULL,def,def}
(6 rows)
select id, c_varchar_a from cstore_support_array_type where 'aaa' = ALL(c_varchar_a) order by 1;
id | c_varchar_a
----+-------------------
2 | {aaa,aaa,aaa,aaa}
2 | {aaa,aaa,aaa,aaa}
(2 rows)
select id, c_text_a from cstore_support_array_type where 'aaa' = ANY(c_text_a) order by 1;
id | c_text_a
----+-------------------
2 | {aaa,aaa,aaa,aaa}
2 | {aaa,aaa,aaa,aaa}
(2 rows)
select id, c_text_a from cstore_support_array_type where 'def' = ALL(c_text_a) order by 1;
id | c_text_a
----+----------
(0 rows)
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;
id | c_timestamp_a
----+---------------
(0 rows)
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;
id | c_timestamp_a
----+---------------------------------------------------------------------
2 | {"1970-01-01 00:00:00","1970-01-01 00:00:00","1970-01-01 00:00:00"}
2 | {"1970-01-01 00:00:00","1970-01-01 00:00:00","1970-01-01 00:00:00"}
(2 rows)
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;
id | c_timestamp_a
----+---------------------------------------------------------------------
1 | {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"}
1 | {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"}
3 | {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"}
3 | {"1970-01-01 00:00:00","2020-02-02 20:02:00","2038-01-19 03:14:00"}
4 | {"1970-01-01 00:00:00",NULL,"2038-01-19 03:14:00"}
4 | {"1970-01-01 00:00:00",NULL,"2038-01-19 03:14:00"}
(6 rows)
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;
id | c_timestamp_a
----+---------------
(0 rows)
-- sal_emp
DROP TABLE IF EXISTS sal_emp;
NOTICE: table "sal_emp" does not exist, skipping
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;
id | name | pay_by_quarter | schedule
----+-------+---------------------------+------------------------------------------
1 | Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
1 | Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter) order by 1;
id | name | pay_by_quarter | schedule
----+------+---------------------------+-------------------------------------------
1 | Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
1 | Bill | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
(2 rows)
-- 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;
?column?
----------
t
t
t
(4 rows)
-- 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;
id | c1 | id | c1
----+---------+----+---------
1 | {1,2,3} | 1 | {1,2,3}
1 | {1,2,3} | 1 | {1,2,3}
1 | {1,2,3} | 1 | {1,2,3}
1 | {1} | 1 | {1}
1 | {1,2,3} | 1 | {1,2,3}
2 | {2,2,2} | 2 | {2,2,2}
2 | {2} | 2 | {2}
2 | {2,3,1} | 2 | {2,3,1}
(8 rows)
SELECT * FROM cstore_table_join_1 WHERE 1 = ANY (c1) INTERSECT SELECT * FROM cstore_table_join_2 WHERE 2 = ANY(c1) ORDER BY 1;
id | c1
----+---------
1 | {1,2,3}
2 | {2,3,1}
(2 rows)
-- group
SELECT c1, count(1) FROM cstore_table_join_1 GROUP BY c1 ORDER BY 1;
c1 | count
---------+-------
{1} | 1
{1,2,3} | 2
{2} | 1
{2,2,2} | 1
{2,3,1} | 1
{3} | 1
{3,1,2} | 2
{3,3,3} | 1
(8 rows)
SELECT count(c1) FROM cstore_table_join_1;
count
-------
10
(1 row)
SELECT count(distinct c1) FROM cstore_table_join_1;
count
-------
8
(1 row)
SELECT distinct id,c1 FROM cstore_table_join_1 ORDER BY id;
id | c1
----+---------
1 | {1}
1 | {1,2,3}
2 | {2}
2 | {2,2,2}
2 | {2,3,1}
3 | {3}
3 | {3,1,2}
3 | {3,3,3}
4 | {3,1,2}
(9 rows)
-- clear
DROP SCHEMA cstore_array_test CASCADE;
NOTICE: drop cascades to 8 other objects
DETAIL: drop cascades to table row_array_type
drop cascades to table cstore_support_array_type
drop cascades to table sal_emp
drop cascades to table row_not_in
drop cascades to table cstore_not_in
drop cascades to table row_table_join
drop cascades to table cstore_table_join_1
drop cascades to table cstore_table_join_2