ydb/yql/essentials/tests/postgresql/cases/arrays.out

--
-- 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