-- -- ARRAYS -- CREATE TABLE arrtest ( a int2[], b int4[][][], c name[], d text[][], e float8[], f char(5)[], g varchar(5)[] ); -- test mixed slice/scalar subscripting select '{{1,2,3},{4,5,6},{7,8,9}}'::int[]; int4 --------------------------- {{1,2,3},{4,5,6},{7,8,9}} (1 row) select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[]; int4 -------------------------------------- [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} (1 row) -- test slices with empty lower and/or upper index CREATE TEMP TABLE arrtest_s ( a int2[], b int2[][] ); INSERT INTO arrtest_s VALUES(NULL, NULL); -- -- test array extension -- CREATE TEMP TABLE arrtest1 (i int[], t text[]); -- -- array expressions and operators -- -- table creation and INSERTs CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]); -- some more test data CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8); insert into arrtest_f values(1,'cat1',1.21); insert into arrtest_f values(2,'cat1',1.24); insert into arrtest_f values(3,'cat1',1.18); insert into arrtest_f values(4,'cat1',1.26); insert into arrtest_f values(5,'cat1',1.15); insert into arrtest_f values(6,'cat2',1.15); insert into arrtest_f values(7,'cat2',1.26); insert into arrtest_f values(8,'cat2',1.32); insert into arrtest_f values(9,'cat2',1.30); CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int); insert into arrtest_i values(1,'cat1',21); insert into arrtest_i values(2,'cat1',24); insert into arrtest_i values(3,'cat1',18); insert into arrtest_i values(4,'cat1',26); insert into arrtest_i values(5,'cat1',15); insert into arrtest_i values(6,'cat2',15); insert into arrtest_i values(7,'cat2',26); insert into arrtest_i values(8,'cat2',32); insert into arrtest_i values(9,'cat2',30); SELECT ARRAY[[[[[['hello'],['world']]]]]]; array --------------------------- {{{{{{hello},{world}}}}}} (1 row) SELECT ARRAY[ARRAY['hello'],ARRAY['world']]; array ------------------- {{hello},{world}} (1 row) -- with nulls SELECT '{1,null,3}'::int[]; int4 ------------ {1,NULL,3} (1 row) SELECT ARRAY[1,NULL,3]; array ------------ {1,NULL,3} (1 row) SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE"; FALSE ------- f (1 row) -- array casts SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}"; {1,2,3} --------- {1,2,3} (1 row) SELECT pg_typeof(ARRAY[1,2,3]::text[]::int[]::float8[]) AS "double precision[]"; double precision[] -------------------- double precision[] (1 row) SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}"; {{a,bc},{def,hijk}} --------------------- {{a,bc},{def,hijk}} (1 row) SELECT pg_typeof(ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[]) AS "character varying[]"; character varying[] --------------------- character varying[] (1 row) SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}"; {{{{{{a,bb,ccc}}}}}} ---------------------- {{{{{{a,bb,ccc}}}}}} (1 row) SELECT NULL::text[]::int[] AS "NULL"; NULL ------ (1 row) -- scalar op any/all (array) select 33 = any ('{1,2,3}'); ?column? ---------- f (1 row) select 33 = any ('{1,2,33}'); ?column? ---------- t (1 row) select 33 = all ('{1,2,33}'); ?column? ---------- f (1 row) select 33 >= all ('{1,2,33}'); ?column? ---------- t (1 row) -- boundary cases select null::int >= all ('{1,2,33}'); ?column? ---------- (1 row) select null::int >= all ('{}'); ?column? ---------- t (1 row) select null::int >= any ('{}'); ?column? ---------- f (1 row) -- cross-datatype select 33.4 = any (array[1,2,3]); ?column? ---------- f (1 row) select 33.4 > all (array[1,2,3]); ?column? ---------- t (1 row) -- nulls select 33 = any (null::int[]); ?column? ---------- (1 row) select null::int = any ('{1,2,3}'); ?column? ---------- (1 row) select 33 = any ('{1,null,3}'); ?column? ---------- (1 row) select 33 = any ('{1,null,33}'); ?column? ---------- t (1 row) select 33 = all (null::int[]); ?column? ---------- (1 row) select null::int = all ('{1,2,3}'); ?column? ---------- (1 row) select 33 = all ('{1,null,3}'); ?column? ---------- f (1 row) select 33 = all ('{33,null,33}'); ?column? ---------- (1 row) -- nulls later in the bitmap SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i))); ?column? ---------- (1 row) -- test indexes on arrays create temp table arr_tbl (f1 int[] unique); -- test ON CONFLICT DO UPDATE with arrays create temp table arr_pk_tbl (pk int4 primary key, f1 int[]); -- test [not] (like|ilike) (any|all) (...) select 'foo' like any (array['%a', '%o']); -- t ?column? ---------- t (1 row) select 'foo' like any (array['%a', '%b']); -- f ?column? ---------- f (1 row) select 'foo' like all (array['f%', '%o']); -- t ?column? ---------- t (1 row) select 'foo' like all (array['f%', '%b']); -- f ?column? ---------- f (1 row) select 'foo' not like any (array['%a', '%b']); -- t ?column? ---------- t (1 row) select 'foo' not like all (array['%a', '%o']); -- f ?column? ---------- f (1 row) select 'foo' ilike any (array['%A', '%O']); -- t ?column? ---------- t (1 row) select 'foo' ilike all (array['F%', '%O']); -- t ?column? ---------- t (1 row) -- -- General array parser tests -- -- none of the following should be accepted select '{{1,{2}},{2,3}}'::text[]; ERROR: malformed array literal: "{{1,{2}},{2,3}}" LINE 1: select '{{1,{2}},{2,3}}'::text[]; ^ DETAIL: Unexpected "{" character. select '{{},{}}'::text[]; ERROR: malformed array literal: "{{},{}}" LINE 1: select '{{},{}}'::text[]; ^ DETAIL: Unexpected "}" character. select E'{{1,2},\\{2,3}}'::text[]; ERROR: malformed array literal: "{{1,2},\{2,3}}" LINE 1: select E'{{1,2},\\{2,3}}'::text[]; ^ DETAIL: Unexpected "\" character. select '{{"1 2" x},{3}}'::text[]; ERROR: malformed array literal: "{{"1 2" x},{3}}" LINE 1: select '{{"1 2" x},{3}}'::text[]; ^ DETAIL: Unexpected array element. select '{}}'::text[]; ERROR: malformed array literal: "{}}" LINE 1: select '{}}'::text[]; ^ DETAIL: Junk after closing right brace. select '{ }}'::text[]; ERROR: malformed array literal: "{ }}" LINE 1: select '{ }}'::text[]; ^ DETAIL: Junk after closing right brace. -- none of the above should be accepted -- all of the following should be accepted select '{}'::text[]; text ------ {} (1 row) select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[]; text ----------------------------------------------- {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}} (1 row) select '{ { "," } , { 3 } }'::text[]; text ------------- {{","},{3}} (1 row) select ' { { " 0 second " , 0 second } }'::text[]; text ------------------------------- {{" 0 second ","0 second"}} (1 row) select '[0:1]={1.1,2.2}'::float8[]; float8 ----------------- [0:1]={1.1,2.2} (1 row) -- all of the above should be accepted -- tests for array aggregates CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]); create table comptable (c1 comptype, c2 comptype[]); drop table comptable; select string_to_array('1|2|3', '|'); string_to_array ----------------- {1,2,3} (1 row) select string_to_array('1|2|3|', '|'); string_to_array ----------------- {1,2,3,""} (1 row) select string_to_array('1||2|3||', '||'); string_to_array ----------------- {1,2|3,""} (1 row) select string_to_array('1|2|3', ''); string_to_array ----------------- {1|2|3} (1 row) select string_to_array('', '|'); string_to_array ----------------- {} (1 row) select string_to_array('1|2|3', NULL); string_to_array ----------------- {1,|,2,|,3} (1 row) select string_to_array(NULL, '|') IS NULL; ?column? ---------- t (1 row) select string_to_array('abc', ''); string_to_array ----------------- {abc} (1 row) select string_to_array('abc', '', 'abc'); string_to_array ----------------- {NULL} (1 row) select string_to_array('abc', ','); string_to_array ----------------- {abc} (1 row) select string_to_array('abc', ',', 'abc'); string_to_array ----------------- {NULL} (1 row) select string_to_array('1,2,3,4,,6', ','); string_to_array ----------------- {1,2,3,4,"",6} (1 row) select string_to_array('1,2,3,4,,6', ',', ''); string_to_array ------------------ {1,2,3,4,NULL,6} (1 row) select string_to_array('1,2,3,4,*,6', ',', '*'); string_to_array ------------------ {1,2,3,4,NULL,6} (1 row) select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v); v | is null ---+--------- 1 | f 2 | f 3 | f (3 rows) select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v); v | is null ---+--------- 1 | f 2 | f 3 | f | f (4 rows) select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v); v | is null -----+--------- 1 | f 2|3 | f | f (3 rows) select v, v is null as "is null" from string_to_table('1|2|3', '') g(v); v | is null -------+--------- 1|2|3 | f (1 row) select v, v is null as "is null" from string_to_table('', '|') g(v); v | is null ---+--------- (0 rows) select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v); v | is null ---+--------- 1 | f | | f 2 | f | | f 3 | f (5 rows) select v, v is null as "is null" from string_to_table(NULL, '|') g(v); v | is null ---+--------- (0 rows) select v, v is null as "is null" from string_to_table('abc', '') g(v); v | is null -----+--------- abc | f (1 row) select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v); v | is null ---+--------- | t (1 row) select v, v is null as "is null" from string_to_table('abc', ',') g(v); v | is null -----+--------- abc | f (1 row) select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v); v | is null ---+--------- | t (1 row) select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v); v | is null ---+--------- 1 | f 2 | f 3 | f 4 | f | f 6 | f (6 rows) select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v); v | is null ---+--------- 1 | f 2 | f 3 | f 4 | f | t 6 | f (6 rows) select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v); v | is null ---+--------- 1 | f 2 | f 3 | f 4 | f | t 6 | f (6 rows) select array_to_string(NULL::int4[], ',') IS NULL; ?column? ---------- t (1 row) select array_to_string('{}'::int4[], ','); array_to_string ----------------- (1 row) select array_to_string(array[1,2,3,4,NULL,6], ','); array_to_string ----------------- 1,2,3,4,6 (1 row) select array_to_string(array[1,2,3,4,NULL,6], ',', '*'); array_to_string ----------------- 1,2,3,4,*,6 (1 row) select array_to_string(array[1,2,3,4,NULL,6], NULL); array_to_string ----------------- (1 row) select array_to_string(array[1,2,3,4,NULL,6], ',', NULL); array_to_string ----------------- 1,2,3,4,6 (1 row) select array_to_string(string_to_array('1|2|3', '|'), '|'); array_to_string ----------------- 1|2|3 (1 row) select array_length(array[1,2,3], 1); array_length -------------- 3 (1 row) select array_length(array[[1,2,3], [4,5,6]], 0); array_length -------------- (1 row) select array_length(array[[1,2,3], [4,5,6]], 1); array_length -------------- 2 (1 row) select array_length(array[[1,2,3], [4,5,6]], 2); array_length -------------- 3 (1 row) select array_length(array[[1,2,3], [4,5,6]], 3); array_length -------------- (1 row) select cardinality(NULL::int[]); cardinality ------------- (1 row) select cardinality('{}'::int[]); cardinality ------------- 0 (1 row) select cardinality(array[1,2,3]); cardinality ------------- 3 (1 row) select cardinality('[2:4]={5,6,7}'::int[]); cardinality ------------- 3 (1 row) select cardinality('{{1,2}}'::int[]); cardinality ------------- 2 (1 row) select cardinality('{{1,2},{3,4},{5,6}}'::int[]); cardinality ------------- 6 (1 row) select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]); cardinality ------------- 8 (1 row) select array_agg(unique1) from tenk1 where unique1 < -15; array_agg ----------- (1 row) -- array_agg(anyarray) select array_agg(ar) from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar); array_agg --------------- {{1,2},{3,4}} (1 row) select array_agg(ar) from (select array_agg(array[i, i+1, i-1]) from generate_series(1,2) a(i)) b(ar); array_agg --------------------- {{{1,2,0},{2,3,1}}} (1 row) select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i); array_agg --------------------------------------------- {{2.2,2.3,2.4},{3.2,3.3,3.4},{4.2,4.3,4.4}} (1 row) select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i); array_agg ----------------------------------- {{Hello,9},{Hello,10},{Hello,11}} (1 row) select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i); array_agg -------------------------------------- {{1,1,2},{2,2,3},{3,NULL,4},{4,4,5}} (1 row) -- errors select array_agg('{}'::int[]) from generate_series(1,2); ERROR: cannot accumulate empty arrays select array_agg(null::int[]) from generate_series(1,2); ERROR: cannot accumulate null arrays select array_agg(ar) from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar); ERROR: cannot accumulate arrays of different dimensionality select * from unnest(array[1,2,3]); unnest -------- 1 2 3 (3 rows) -- array(select array-value ...) select array(select array[i,i/2] from generate_series(1,5) i); array --------------------------------- {{1,0},{2,1},{3,1},{4,2},{5,2}} (1 row) select array(select array['Hello', i::text] from generate_series(9,11) i); array ----------------------------------- {{Hello,9},{Hello,10},{Hello,11}} (1 row) -- Insert/update on a column that is array of composite create temp table t1 (f1 int8_tbl[]); -- Check that arrays of composites are safely detoasted when needed create temp table src (f1 text); insert into src select string_agg(random()::text,'') from generate_series(1,10000); create temp table dest (f1 textandtext[]); drop table src; drop table dest; -- trim_array SELECT arr, trim_array(arr, 2) FROM (VALUES ('{1,2,3,4,5,6}'::bigint[]), ('{1,2}'), ('[10:16]={1,2,3,4,5,6,7}'), ('[-15:-10]={1,2,3,4,5,6}'), ('{{1,10},{2,20},{3,30},{4,40}}')) v(arr); arr | trim_array -------------------------------+----------------- {1,2,3,4,5,6} | {1,2,3,4} {1,2} | {} [10:16]={1,2,3,4,5,6,7} | {1,2,3,4,5} [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} {{1,10},{2,20},{3,30},{4,40}} | {{1,10},{2,20}} (5 rows) SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail ERROR: number of elements to trim must be between 0 and 3 SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail ERROR: number of elements to trim must be between 0 and 3