diff options
| author | Maxim Yurchuk <maxim-yurchuk@ydb.tech> | 2024-11-20 17:37:57 +0000 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-11-20 17:37:57 +0000 |
| commit | f76323e9b295c15751e51e3443aa47a36bee8023 (patch) | |
| tree | 4113c8cad473a33e0f746966e0cf087252fa1d7a /yql/essentials/tests/postgresql/original/cases/arrays.out | |
| parent | 753ecb8d410a4cb459c26f3a0082fb2d1724fe63 (diff) | |
| parent | a7b9a6afea2a9d7a7bfac4c5eb4c1a8e60adb9e6 (diff) | |
| download | ydb-f76323e9b295c15751e51e3443aa47a36bee8023.tar.gz | |
Merge pull request #11788 from ydb-platform/mergelibs-241120-1113
Library import 241120-1113
Diffstat (limited to 'yql/essentials/tests/postgresql/original/cases/arrays.out')
| -rw-r--r-- | yql/essentials/tests/postgresql/original/cases/arrays.out | 2436 |
1 files changed, 2436 insertions, 0 deletions
diff --git a/yql/essentials/tests/postgresql/original/cases/arrays.out b/yql/essentials/tests/postgresql/original/cases/arrays.out new file mode 100644 index 0000000000..4923cf36d6 --- /dev/null +++ b/yql/essentials/tests/postgresql/original/cases/arrays.out @@ -0,0 +1,2436 @@ +-- +-- ARRAYS +-- +CREATE TABLE arrtest ( + a int2[], + b int4[][][], + c name[], + d text[][], + e float8[], + f char(5)[], + g varchar(5)[] +); +-- +-- only the 'e' array is 0-based, the others are 1-based. +-- +INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g) + VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}'); +UPDATE arrtest SET e[0] = '1.1'; +UPDATE arrtest SET e[1] = '2.2'; +INSERT INTO arrtest (f) + VALUES ('{"too long"}'); +ERROR: value too long for type character(5) +INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g) + VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}', + '{{"elt1", "elt2"}}', '{"3.4", "6.7"}', + '{"abc","abcde"}', '{"abc","abcde"}'); +INSERT INTO arrtest (a, b[1:2], c, d[1:2]) + VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}'); +INSERT INTO arrtest (b[2]) VALUES(now()); -- error, type mismatch +ERROR: subscripted assignment to "b" requires type integer but expression is of type timestamp with time zone +LINE 1: INSERT INTO arrtest (b[2]) VALUES(now()); + ^ +HINT: You will need to rewrite or cast the expression. +INSERT INTO arrtest (b[1:2]) VALUES(now()); -- error, type mismatch +ERROR: subscripted assignment to "b" requires type integer[] but expression is of type timestamp with time zone +LINE 1: INSERT INTO arrtest (b[1:2]) VALUES(now()); + ^ +HINT: You will need to rewrite or cast the expression. +SELECT * FROM arrtest; + a | b | c | d | e | f | g +-------------+-----------------+-----------+---------------+-----------------+-----------------+------------- + {1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | [0:1]={1.1,2.2} | {} | {} + {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde} + {} | {3,4} | {foo,bar} | {bar,foo} | | | +(3 rows) + +SELECT arrtest.a[1], + arrtest.b[1][1][1], + arrtest.c[1], + arrtest.d[1][1], + arrtest.e[0] + FROM arrtest; + a | b | c | d | e +----+---+--------+------+----- + 1 | 0 | | | 1.1 + 11 | | foobar | elt1 | + | | foo | | +(3 rows) + +SELECT a[1], b[1][1][1], c[1], d[1][1], e[0] + FROM arrtest; + a | b | c | d | e +----+---+--------+------+----- + 1 | 0 | | | 1.1 + 11 | | foobar | elt1 | + | | foo | | +(3 rows) + +SELECT a[1:3], + b[1:1][1:2][1:2], + c[1:2], + d[1:1][1:2] + FROM arrtest; + a | b | c | d +------------+-----------------+-----------+--------------- + {1,2,3} | {{{0,0},{1,2}}} | {} | {} + {11,12,23} | {} | {foobar} | {{elt1,elt2}} + {} | {} | {foo,bar} | {} +(3 rows) + +SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c + FROM arrtest; + a | b | c +---+---+--- + 1 | 3 | + 1 | 2 | 1 + | 1 | 1 +(3 rows) + +SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c + FROM arrtest; + a | b | c +-------+-----------------+------- + [1:5] | [1:1][1:2][1:2] | + [1:3] | [1:2][1:2] | [1:1] + | [1:2] | [1:2] +(3 rows) + +-- returns nothing +SELECT * + FROM arrtest + WHERE a[1] < 5 and + c = '{"foobar"}'::_name; + a | b | c | d | e | f | g +---+---+---+---+---+---+--- +(0 rows) + +UPDATE arrtest + SET a[1:2] = '{16,25}' + WHERE NOT a = '{}'::_int2; +UPDATE arrtest + SET b[1:1][1:1][1:2] = '{113, 117}', + b[1:1][1:2][2:2] = '{142, 147}' + WHERE array_dims(b) = '[1:1][1:2][1:2]'; +UPDATE arrtest + SET c[2:2] = '{"new_word"}' + WHERE array_dims(c) is not null; +SELECT a,b,c FROM arrtest; + a | b | c +---------------+-----------------------+------------------- + {16,25,3,4,5} | {{{113,142},{1,147}}} | {} + {} | {3,4} | {foo,new_word} + {16,25,23} | {{3,4},{4,5}} | {foobar,new_word} +(3 rows) + +SELECT a[1:3], + b[1:1][1:2][1:2], + c[1:2], + d[1:1][2:2] + FROM arrtest; + a | b | c | d +------------+-----------------------+-------------------+---------- + {16,25,3} | {{{113,142},{1,147}}} | {} | {} + {} | {} | {foo,new_word} | {} + {16,25,23} | {} | {foobar,new_word} | {{elt2}} +(3 rows) + +SELECT b[1:1][2][2], + d[1:1][2] + FROM arrtest; + b | d +-----------------------+--------------- + {{{113,142},{1,147}}} | {} + {} | {} + {} | {{elt1,elt2}} +(3 rows) + +INSERT INTO arrtest(a) VALUES('{1,null,3}'); +SELECT a FROM arrtest; + a +--------------- + {16,25,3,4,5} + {} + {16,25,23} + {1,NULL,3} +(4 rows) + +UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL; +SELECT a FROM arrtest WHERE a[2] IS NULL; + a +----------------- + [4:4]={NULL} + {1,NULL,3,NULL} +(2 rows) + +DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL; +SELECT a,b,c FROM arrtest; + a | b | c +---------------+-----------------------+------------------- + {16,25,3,4,5} | {{{113,142},{1,147}}} | {} + {16,25,23} | {{3,4},{4,5}} | {foobar,new_word} + [4:4]={NULL} | {3,4} | {foo,new_word} +(3 rows) + +-- 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 ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; + int4 +--------------- + {{1,2},{4,5}} +(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) + +select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; + int4 +--------------- + {{5,6},{8,9}} +(1 row) + +-- +-- check subscription corner cases +-- +-- More subscripts than MAXDIM (6) +SELECT ('{}'::int[])[1][2][3][4][5][6][7]; +ERROR: number of array dimensions (7) exceeds the maximum allowed (6) +-- NULL index yields NULL when selecting +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1]; + int4 +------ + +(1 row) + +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1]; + int4 +------ + +(1 row) + +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1]; + int4 +------ + +(1 row) + +-- NULL index in assignment is an error +UPDATE arrtest + SET c[NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null +UPDATE arrtest + SET c[NULL:1] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null +UPDATE arrtest + SET c[1:NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null +-- Un-subscriptable type +SELECT (now())[1]; +ERROR: cannot subscript type timestamp with time zone because it does not support subscripting +LINE 1: SELECT (now())[1]; + ^ +-- test slices with empty lower and/or upper index +CREATE TEMP TABLE arrtest_s ( + a int2[], + b int2[][] +); +INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}'); +INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}'); +SELECT * FROM arrtest_s; + a | b +-------------------+-------------------------------------- + {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} + [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} +(2 rows) + +SELECT a[:3], b[:2][:2] FROM arrtest_s; + a | b +-----------+--------------------------- + {1,2,3} | {{1,2},{4,5}} + {1,2,3,4} | {{1,2,3},{4,5,6},{7,8,9}} +(2 rows) + +SELECT a[2:], b[2:][2:] FROM arrtest_s; + a | b +-----------+--------------- + {2,3,4,5} | {{5,6},{8,9}} + {3,4,5} | {{9}} +(2 rows) + +SELECT a[:], b[:] FROM arrtest_s; + a | b +-------------+--------------------------- + {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} + {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} +(2 rows) + +-- updates +UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}' + WHERE array_lower(a,1) = 1; +SELECT * FROM arrtest_s; + a | b +-------------------+-------------------------------------- + [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} + {11,12,13,4,5} | {{11,12,3},{14,15,6},{7,8,9}} +(2 rows) + +UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}'; +SELECT * FROM arrtest_s; + a | b +---------------------+--------------------------------------- + [0:4]={1,2,3,23,24} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}} + {11,12,23,24,25} | {{11,12,3},{14,25,26},{7,28,29}} +(2 rows) + +UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; +SELECT * FROM arrtest_s; + a | b +------------------------+--------------------------------------- + [0:4]={11,12,13,14,15} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}} + {11,12,13,14,15} | {{11,12,3},{14,25,26},{7,28,29}} +(2 rows) + +UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small +ERROR: source array too small +INSERT INTO arrtest_s VALUES(NULL, NULL); +UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null +ERROR: array slice subscript must provide both boundaries +DETAIL: When assigning to a slice of an empty array value, slice boundaries must be fully specified. +-- check with fixed-length-array type, such as point +SELECT f1[0:1] FROM POINT_TBL; +ERROR: slices of fixed-length arrays not implemented +SELECT f1[0:] FROM POINT_TBL; +ERROR: slices of fixed-length arrays not implemented +SELECT f1[:1] FROM POINT_TBL; +ERROR: slices of fixed-length arrays not implemented +SELECT f1[:] FROM POINT_TBL; +ERROR: slices of fixed-length arrays not implemented +-- subscript assignments to fixed-width result in NULL if previous value is NULL +UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *; + f1 +---- + +(1 row) + +INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *; + f1 +---- + +(1 row) + +-- NULL assignments get ignored +UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *; + f1 +--------- + (10,10) +(1 row) + +-- but non-NULL subscript assignments work +UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *; + f1 +----------- + (-10,-10) +(1 row) + +-- but not to expand the range +UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *; +ERROR: array subscript out of range +-- +-- test array extension +-- +CREATE TEMP TABLE arrtest1 (i int[], t text[]); +insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); +select * from arrtest1; + i | t +--------------+--------------------- + {1,2,NULL,4} | {one,two,NULL,four} +(1 row) + +update arrtest1 set i[2] = 22, t[2] = 'twenty-two'; +select * from arrtest1; + i | t +---------------+---------------------------- + {1,22,NULL,4} | {one,twenty-two,NULL,four} +(1 row) + +update arrtest1 set i[5] = 5, t[5] = 'five'; +select * from arrtest1; + i | t +-----------------+--------------------------------- + {1,22,NULL,4,5} | {one,twenty-two,NULL,four,five} +(1 row) + +update arrtest1 set i[8] = 8, t[8] = 'eight'; +select * from arrtest1; + i | t +-----------------------------+------------------------------------------------- + {1,22,NULL,4,5,NULL,NULL,8} | {one,twenty-two,NULL,four,five,NULL,NULL,eight} +(1 row) + +update arrtest1 set i[0] = 0, t[0] = 'zero'; +select * from arrtest1; + i | t +-------------------------------------+------------------------------------------------------------ + [0:8]={0,1,22,NULL,4,5,NULL,NULL,8} | [0:8]={zero,one,twenty-two,NULL,four,five,NULL,NULL,eight} +(1 row) + +update arrtest1 set i[-3] = -3, t[-3] = 'minus-three'; +select * from arrtest1; + i | t +---------------------------------------------------+----------------------------------------------------------------------------------- + [-3:8]={-3,NULL,NULL,0,1,22,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,zero,one,twenty-two,NULL,four,five,NULL,NULL,eight} +(1 row) + +update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve']; +select * from arrtest1; + i | t +-----------------------------------------------------+--------------------------------------------------------------------------------- + [-3:8]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,8} | [-3:8]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,eight} +(1 row) + +update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20']; +select * from arrtest1; + i | t +---------------------------------------------------------------+----------------------------------------------------------------------------------------- + [-3:10]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20} | [-3:10]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20} +(1 row) + +update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22']; +select * from arrtest1; + i | t +-----------------------------------------------------------------------+-------------------------------------------------------------------------------------------------- + [-3:12]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22} | [-3:12]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22} +(1 row) + +update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26']; +select * from arrtest1; + i | t +-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------- + [-3:16]={-3,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-3:16]={minus-three,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26} +(1 row) + +update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13']; +select * from arrtest1; + i | t +--------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------- + [-5:16]={-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-5:16]={m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26} +(1 row) + +update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null]; +select * from arrtest1; + i | t +-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------ + [-7:16]={-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-7:16]={m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26} +(1 row) + +update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20']; +select * from arrtest1; + i | t +-----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------ + [-12:16]={-22,NULL,-20,NULL,NULL,-17,NULL,-15,-14,-13,NULL,NULL,10,11,12,NULL,4,5,NULL,NULL,18,NULL,20,NULL,22,NULL,NULL,NULL,26} | [-12:16]={m22,NULL,m20,NULL,NULL,m17,NULL,m15,m14,m13,NULL,NULL,ten,eleven,twelve,NULL,four,five,NULL,NULL,p18,NULL,p20,NULL,p22,NULL,NULL,NULL,p26} +(1 row) + +delete from arrtest1; +insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); +select * from arrtest1; + i | t +--------------+--------------------- + {1,2,NULL,4} | {one,two,NULL,four} +(1 row) + +update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5']; +select * from arrtest1; + i | t +------------------------+---------------------------- + [0:5]={0,1,2,NULL,4,5} | [0:5]={z,p1,p2,NULL,p4,p5} +(1 row) + +-- +-- array expressions and operators +-- +-- table creation and INSERTs +CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]); +INSERT INTO arrtest2 VALUES( + ARRAY[[[113,142],[1,147]]], + ARRAY[1.1,1.2,1.3]::float8[], + ARRAY[1.1,1.2,1.3], + ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]], + ARRAY['19620326','19931223','19970117']::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); +-- expressions +SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM ( + SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f +) AS t; + 131 | 221 +-----+----- + 131 | 221 +(1 row) + +SELECT ARRAY[[[[[['hello'],['world']]]]]]; + array +--------------------------- + {{{{{{hello},{world}}}}}} +(1 row) + +SELECT ARRAY[ARRAY['hello'],ARRAY['world']]; + array +------------------- + {{hello},{world}} +(1 row) + +SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY"; + ARRAY +----------------------------------------------- + {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32} +(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) + +-- functions +SELECT array_append(array[42], 6) AS "{42,6}"; + {42,6} +-------- + {42,6} +(1 row) + +SELECT array_prepend(6, array[42]) AS "{6,42}"; + {6,42} +-------- + {6,42} +(1 row) + +SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; + {1,2,3,4} +----------- + {1,2,3,4} +(1 row) + +SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}"; + {{1,2},{3,4},{5,6}} +--------------------- + {{1,2},{3,4},{5,6}} +(1 row) + +SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}"; + {{3,4},{5,6},{1,2}} +--------------------- + {{3,4},{5,6},{1,2}} +(1 row) + +SELECT array_position(ARRAY[1,2,3,4,5], 4); + array_position +---------------- + 4 +(1 row) + +SELECT array_position(ARRAY[5,3,4,2,1], 4); + array_position +---------------- + 3 +(1 row) + +SELECT array_position(ARRAY[[1,2],[3,4]], 3); +ERROR: searching for elements in multidimensional arrays is not supported +SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); + array_position +---------------- + 2 +(1 row) + +SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat'); + array_position +---------------- + 7 +(1 row) + +SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL); + array_position +---------------- + +(1 row) + +SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL); + array_position +---------------- + 6 +(1 row) + +SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat'); + array_position +---------------- + 8 +(1 row) + +SELECT array_positions(NULL, 10); + array_positions +----------------- + +(1 row) + +SELECT array_positions(NULL, NULL::int); + array_positions +----------------- + +(1 row) + +SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4); + array_positions +----------------- + {4,10} +(1 row) + +SELECT array_positions(ARRAY[[1,2],[3,4]], 4); +ERROR: searching for elements in multidimensional arrays is not supported +SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL); + array_positions +----------------- + {} +(1 row) + +SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL); + array_positions +----------------- + {4,10} +(1 row) + +SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10 + FROM generate_series(1,100) g(i)), + 'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1); + array_length +-------------- + 10 +(1 row) + +DO $$ +DECLARE + o int; + a int[] := ARRAY[1,2,3,2,3,1,2]; +BEGIN + o := array_position(a, 2); + WHILE o IS NOT NULL + LOOP + RAISE NOTICE '%', o; + o := array_position(a, 2, o + 1); + END LOOP; +END +$$ LANGUAGE plpgsql; +NOTICE: 2 +NOTICE: 4 +NOTICE: 7 +SELECT array_position('[2:4]={1,2,3}'::int[], 1); + array_position +---------------- + 2 +(1 row) + +SELECT array_positions('[2:4]={1,2,3}'::int[], 1); + array_positions +----------------- + {2} +(1 row) + +SELECT + array_position(ids, (1, 1)), + array_positions(ids, (1, 1)) + FROM +(VALUES + (ARRAY[(0, 0), (1, 1)]), + (ARRAY[(1, 1)]) +) AS f (ids); + array_position | array_positions +----------------+----------------- + 2 | {2} + 1 | {1} +(2 rows) + +-- operators +SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]]; + a +--------------- + {16,25,3,4,5} +(1 row) + +SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE"; + FALSE +------- + f +(1 row) + +SELECT ARRAY[1,2] || 3 AS "{1,2,3}"; + {1,2,3} +--------- + {1,2,3} +(1 row) + +SELECT 0 || ARRAY[1,2] AS "{0,1,2}"; + {0,1,2} +--------- + {0,1,2} +(1 row) + +SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}"; + {1,2,3,4} +----------- + {1,2,3,4} +(1 row) + +SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY"; + ARRAY +-------------------------------------- + {{{hello,world}},{{happy,birthday}}} +(1 row) + +SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}"; + {{1,2},{3,4},{5,6}} +--------------------- + {{1,2},{3,4},{5,6}} +(1 row) + +SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; + {0,0,1,1,2,2} +--------------- + {0,0,1,1,2,2} +(1 row) + +SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; + {0,1,2,3} +----------- + {0,1,2,3} +(1 row) + +SELECT ARRAY[1.1] || ARRAY[2,3,4]; + ?column? +------------- + {1.1,2,3,4} +(1 row) + +SELECT array_agg(x) || array_agg(x) FROM (VALUES (ROW(1,2)), (ROW(3,4))) v(x); + ?column? +----------------------------------- + {"(1,2)","(3,4)","(1,2)","(3,4)"} +(1 row) + +SELECT ROW(1,2) || array_agg(x) FROM (VALUES (ROW(3,4)), (ROW(5,6))) v(x); + ?column? +--------------------------- + {"(1,2)","(3,4)","(5,6)"} +(1 row) + +SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} +(6 rows) + +SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} +(6 rows) + +SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} + 53 | {38,17} | {AAAAAAAAAAA21658} + 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} +(8 rows) + +SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} + 53 | {38,17} | {AAAAAAAAAAA21658} + 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} +(8 rows) + +SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} +(3 rows) + +SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} + 53 | {38,17} | {AAAAAAAAAAA21658} + 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} +(11 rows) + +SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; + seqno | i | t +-------+---------------+---------------------------------------------------------------------------------------------------------------------------- + 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 101 | {} | {} +(4 rows) + +SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(1 row) + +SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} + 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} + 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} + 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} + 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} + 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} + 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} + 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} + 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} + 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} + 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} + 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 16 | {14,63,85,11} | {AAAAAA66777} + 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} + 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} + 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} + 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} + 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} + 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} + 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} + 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} + 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} + 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} + 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} + 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} + 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} + 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} + 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} + 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} + 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} + 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} + 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} + 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} + 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} + 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} + 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} + 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} + 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} + 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} + 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} + 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} + 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} + 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} + 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} + 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} + 53 | {38,17} | {AAAAAAAAAAA21658} + 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} + 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} + 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} + 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} + 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} + 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} + 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} + 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} + 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} + 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} + 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} + 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} + 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} + 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} + 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} + 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} + 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} + 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} + 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} + 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} + 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} + 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} + 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} + 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} + 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} + 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} + 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} + 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} + 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} + 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} + 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} + 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} + 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} + 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} + 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} + 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} + 96 | {23,97,43} | {AAAAAAAAAA646,A87088} + 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} + 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} + 101 | {} | {} + 102 | {NULL} | {NULL} +(102 rows) + +SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(1 row) + +SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; + seqno | i | t +-------+--------+-------- + 102 | {NULL} | {NULL} +(1 row) + +SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(1 row) + +SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; + seqno | i | t +-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} +(4 rows) + +SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; + seqno | i | t +-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} +(4 rows) + +SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; + seqno | i | t +-------+------------------+-------------------------------------------------------------------- + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} + 96 | {23,97,43} | {AAAAAAAAAA646,A87088} +(3 rows) + +SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; + seqno | i | t +-------+------------------+-------------------------------------------------------------------- + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} + 96 | {23,97,43} | {AAAAAAAAAA646,A87088} +(3 rows) + +SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; + seqno | i | t +-------+------+-------------------------------------------------------------------- + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} +(1 row) + +SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; + seqno | i | t +-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} + 96 | {23,97,43} | {AAAAAAAAAA646,A87088} +(6 rows) + +SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; + seqno | i | t +-------+--------------------+----------------------------------------------------------------------------------------------------------- + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 101 | {} | {} +(3 rows) + +SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(1 row) + +SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno; + seqno | i | t +-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} + 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} + 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} + 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} + 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} + 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} + 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} + 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} + 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} + 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} + 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} + 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} + 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} + 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} + 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} + 16 | {14,63,85,11} | {AAAAAA66777} + 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} + 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} + 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} + 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} + 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} + 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} + 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} + 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} + 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} + 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} + 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} + 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} + 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} + 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} + 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} + 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} + 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} + 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} + 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} + 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} + 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} + 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} + 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} + 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} + 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} + 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} + 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} + 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} + 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} + 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} + 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} + 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} + 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} + 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} + 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} + 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} + 53 | {38,17} | {AAAAAAAAAAA21658} + 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} + 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} + 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} + 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} + 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} + 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} + 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} + 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} + 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} + 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} + 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} + 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} + 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} + 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} + 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} + 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} + 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} + 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} + 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} + 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} + 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} + 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} + 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} + 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} + 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} + 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} + 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} + 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} + 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} + 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} + 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} + 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} + 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} + 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} + 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} + 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} + 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} + 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} + 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} + 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} + 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} + 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} + 96 | {23,97,43} | {AAAAAAAAAA646,A87088} + 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} + 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} + 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} + 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} + 101 | {} | {} + 102 | {NULL} | {NULL} +(102 rows) + +SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno; + seqno | i | t +-------+---+--- +(0 rows) + +SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno; + seqno | i | t +-------+----+---- + 101 | {} | {} +(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) + +-- errors +select 33 * any ('{1,2,3}'); +ERROR: op ANY/ALL (array) requires operator to yield boolean +LINE 1: select 33 * any ('{1,2,3}'); + ^ +select 33 * any (44); +ERROR: op ANY/ALL (array) requires array on right side +LINE 1: select 33 * any (44); + ^ +-- 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); +insert into arr_tbl values ('{1,2,3}'); +insert into arr_tbl values ('{1,2}'); +-- failure expected: +insert into arr_tbl values ('{1,2,3}'); +ERROR: duplicate key value violates unique constraint "arr_tbl_f1_key" +DETAIL: Key (f1)=({1,2,3}) already exists. +insert into arr_tbl values ('{2,3,4}'); +insert into arr_tbl values ('{1,5,3}'); +insert into arr_tbl values ('{1,2,10}'); +set enable_seqscan to off; +set enable_bitmapscan to off; +select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}'; + f1 +---------- + {1,2,10} + {1,5,3} +(2 rows) + +select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}'; + f1 +---------- + {1,2,3} + {1,2,10} +(2 rows) + +-- test ON CONFLICT DO UPDATE with arrays +create temp table arr_pk_tbl (pk int4 primary key, f1 int[]); +insert into arr_pk_tbl values (1, '{1,2,3}'); +insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk) + do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3] + returning pk, f1; + pk | f1 +----+--------- + 1 | {3,2,5} +(1 row) + +insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk) + do update set f1[1] = excluded.f1[1], + f1[2] = excluded.f1[2], + f1[3] = excluded.f1[3] + returning pk, f1; + pk | f1 +----+------------ + 1 | {6,7,NULL} +(1 row) + +-- note: if above selects don't produce the expected tuple order, +-- then you didn't get an indexscan plan, and something is busted. +reset enable_seqscan; +reset enable_bitmapscan; +-- 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. +select array[]; +ERROR: cannot determine type of empty array +LINE 1: select array[]; + ^ +HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[]. +-- 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 '{0 second ,0 second}'::interval[]; + interval +--------------- + {"@ 0","@ 0"} +(1 row) + +select '{ { "," } , { 3 } }'::text[]; + text +------------- + {{","},{3}} +(1 row) + +select ' { { " 0 second " , 0 second } }'::text[]; + text +------------------------------- + {{" 0 second ","0 second"}} +(1 row) + +select '{ + 0 second, + @ 1 hour @ 42 minutes @ 20 seconds + }'::interval[]; + interval +------------------------------------ + {"@ 0","@ 1 hour 42 mins 20 secs"} +(1 row) + +select array[]::text[]; + array +------- + {} +(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[]); +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}'); +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}'); +SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; + max | min | max | min | max | min +-----------+---------+--------------------------+--------------------------+---------+------- + {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6} +(1 row) + +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}'); +SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; + max | min | max | min | max | min +---------------+---------+--------------------------------+--------------------------+-----------------------+------- + {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{grey,red},{blue,blue}} | {2.1,3.3,1.8,1.7,1.6} | {1.6} +(1 row) + +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}'); +SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; + max | min | max | min | max | min +---------------+---------+--------------------------------+------------------------------+-----------------------+------- + {3,3,2,4,5,6} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6} +(1 row) + +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL); +SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; + max | min | max | min | max | min +---------------+---------+--------------------------------+------------------------------+-----------------------+------- + {4,2,6,7,8,1} | {1,2,3} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6} +(1 row) + +INSERT INTO arraggtest (f1, f2, f3) VALUES +('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}'); +SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; + max | min | max | min | max | min +---------------+-----+--------------------------------+------------------------------+-----------------------+------- + {4,2,6,7,8,1} | {} | {{white,yellow},{pink,orange}} | {{black,red},{green,orange}} | {2.1,3.3,1.8,1.7,1.6} | {1.6} +(1 row) + +-- A few simple tests for arrays of composite types +create type comptype as (f1 int, f2 text); +create table comptable (c1 comptype, c2 comptype[]); +-- XXX would like to not have to specify row() construct types here ... +insert into comptable + values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]); +-- check that implicitly named array type _comptype isn't a problem +create type _comptype as enum('fooey'); +select * from comptable; + c1 | c2 +---------+----------------------- + (1,foo) | {"(2,bar)","(3,baz)"} +(1 row) + +select c2[2].f2 from comptable; + f2 +----- + baz +(1 row) + +drop type _comptype; +drop table comptable; +drop type comptype; +create or replace function unnest1(anyarray) +returns setof anyelement as $$ +select $1[s] from generate_subscripts($1,1) g(s); +$$ language sql immutable; +create or replace function unnest2(anyarray) +returns setof anyelement as $$ +select $1[s1][s2] from generate_subscripts($1,1) g1(s1), + generate_subscripts($1,2) g2(s2); +$$ language sql immutable; +select * from unnest1(array[1,2,3]); + unnest1 +--------- + 1 + 2 + 3 +(3 rows) + +select * from unnest2(array[[1,2,3],[4,5,6]]); + unnest2 +--------- + 1 + 2 + 3 + 4 + 5 + 6 +(6 rows) + +drop function unnest1(anyarray); +drop function unnest2(anyarray); +select array_fill(null::integer, array[3,3],array[2,2]); + array_fill +----------------------------------------------------------------- + [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} +(1 row) + +select array_fill(null::integer, array[3,3]); + array_fill +------------------------------------------------------ + {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} +(1 row) + +select array_fill(null::text, array[3,3],array[2,2]); + array_fill +----------------------------------------------------------------- + [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} +(1 row) + +select array_fill(null::text, array[3,3]); + array_fill +------------------------------------------------------ + {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} +(1 row) + +select array_fill(7, array[3,3],array[2,2]); + array_fill +-------------------------------------- + [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}} +(1 row) + +select array_fill(7, array[3,3]); + array_fill +--------------------------- + {{7,7,7},{7,7,7},{7,7,7}} +(1 row) + +select array_fill('juhu'::text, array[3,3],array[2,2]); + array_fill +----------------------------------------------------------------- + [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}} +(1 row) + +select array_fill('juhu'::text, array[3,3]); + array_fill +------------------------------------------------------ + {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}} +(1 row) + +select a, a = '{}' as is_eq, array_dims(a) + from (select array_fill(42, array[0]) as a) ss; + a | is_eq | array_dims +----+-------+------------ + {} | t | +(1 row) + +select a, a = '{}' as is_eq, array_dims(a) + from (select array_fill(42, '{}') as a) ss; + a | is_eq | array_dims +----+-------+------------ + {} | t | +(1 row) + +select a, a = '{}' as is_eq, array_dims(a) + from (select array_fill(42, '{}', '{}') as a) ss; + a | is_eq | array_dims +----+-------+------------ + {} | t | +(1 row) + +-- raise exception +select array_fill(1, null, array[2,2]); +ERROR: dimension array or low bound array cannot be null +select array_fill(1, array[2,2], null); +ERROR: dimension array or low bound array cannot be null +select array_fill(1, array[2,2], '{}'); +ERROR: wrong number of array subscripts +DETAIL: Low bound array has different size than dimensions array. +select array_fill(1, array[3,3], array[1,1,1]); +ERROR: wrong number of array subscripts +DETAIL: Low bound array has different size than dimensions array. +select array_fill(1, array[1,2,null]); +ERROR: dimension values cannot be null +select array_fill(1, array[[1,2],[3,4]]); +ERROR: wrong number of array subscripts +DETAIL: Dimension array must be one dimensional. +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) + +-- array_agg(anynonarray) +select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss; + array_agg +-------------------------------------- + {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14} +(1 row) + +select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; + array_agg +--------------------------------- + {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4} +(1 row) + +select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; + array_agg +--------------------------------------- + {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL} +(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(distinct ar order by ar desc) + from (select array[i / 2] from generate_series(1,10) a(i)) b(ar); + array_agg +--------------------------- + {{5},{4},{3},{2},{1},{0}} +(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 unnest(array[1,2,3]); + unnest +-------- + 1 + 2 + 3 +(3 rows) + +select * from unnest(array[1,2,3]); + unnest +-------- + 1 + 2 + 3 +(3 rows) + +select unnest(array[1,2,3,4.5]::float8[]); + unnest +-------- + 1 + 2 + 3 + 4.5 +(4 rows) + +select unnest(array[1,2,3,4.5]::numeric[]); + unnest +-------- + 1 + 2 + 3 + 4.5 +(4 rows) + +select unnest(array[1,2,3,null,4,null,null,5,6]); + unnest +-------- + 1 + 2 + 3 + + 4 + + + 5 + 6 +(9 rows) + +select unnest(array[1,2,3,null,4,null,null,5,6]::text[]); + unnest +-------- + 1 + 2 + 3 + + 4 + + + 5 + 6 +(9 rows) + +select abs(unnest(array[1,2,null,-3])); + abs +----- + 1 + 2 + + 3 +(4 rows) + +select array_remove(array[1,2,2,3], 2); + array_remove +-------------- + {1,3} +(1 row) + +select array_remove(array[1,2,2,3], 5); + array_remove +-------------- + {1,2,2,3} +(1 row) + +select array_remove(array[1,NULL,NULL,3], NULL); + array_remove +-------------- + {1,3} +(1 row) + +select array_remove(array['A','CC','D','C','RR'], 'RR'); + array_remove +-------------- + {A,CC,D,C} +(1 row) + +select array_remove(array[1.0, 2.1, 3.3], 1); + array_remove +-------------- + {2.1,3.3} +(1 row) + +select array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed +ERROR: removing elements from multidimensional arrays is not supported +select array_remove(array['X','X','X'], 'X') = '{}'; + ?column? +---------- + t +(1 row) + +select array_replace(array[1,2,5,4],5,3); + array_replace +--------------- + {1,2,3,4} +(1 row) + +select array_replace(array[1,2,5,4],5,NULL); + array_replace +--------------- + {1,2,NULL,4} +(1 row) + +select array_replace(array[1,2,NULL,4,NULL],NULL,5); + array_replace +--------------- + {1,2,5,4,5} +(1 row) + +select array_replace(array['A','B','DD','B'],'B','CC'); + array_replace +--------------- + {A,CC,DD,CC} +(1 row) + +select array_replace(array[1,NULL,3],NULL,NULL); + array_replace +--------------- + {1,NULL,3} +(1 row) + +select array_replace(array['AB',NULL,'CDE'],NULL,'12'); + array_replace +--------------- + {AB,12,CDE} +(1 row) + +-- 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[]); +insert into t1 (f1[5].q1) values(42); +select * from t1; + f1 +----------------- + [5:5]={"(42,)"} +(1 row) + +update t1 set f1[5].q2 = 43; +select * from t1; + f1 +------------------- + [5:5]={"(42,43)"} +(1 row) + +-- 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 type textandtext as (c1 text, c2 text); +create temp table dest (f1 textandtext[]); +insert into dest select array[row(f1,f1)::textandtext] from src; +select length(md5((f1[1]).c2)) from dest; + length +-------- + 32 +(1 row) + +delete from src; +select length(md5((f1[1]).c2)) from dest; + length +-------- + 32 +(1 row) + +truncate table src; +drop table src; +select length(md5((f1[1]).c2)) from dest; + length +-------- + 32 +(1 row) + +drop table dest; +drop type textandtext; +-- Tests for polymorphic-array form of width_bucket() +-- this exercises the varwidth and float8 code paths +SELECT + op, + width_bucket(op::numeric, ARRAY[1, 3, 5, 10.0]::numeric[]) AS wb_n1, + width_bucket(op::numeric, ARRAY[0, 5.5, 9.99]::numeric[]) AS wb_n2, + width_bucket(op::numeric, ARRAY[-6, -5, 2.0]::numeric[]) AS wb_n3, + width_bucket(op::float8, ARRAY[1, 3, 5, 10.0]::float8[]) AS wb_f1, + width_bucket(op::float8, ARRAY[0, 5.5, 9.99]::float8[]) AS wb_f2, + width_bucket(op::float8, ARRAY[-6, -5, 2.0]::float8[]) AS wb_f3 +FROM (VALUES + (-5.2), + (-0.0000000001), + (0.000000000001), + (1), + (1.99999999999999), + (2), + (2.00000000000001), + (3), + (4), + (4.5), + (5), + (5.5), + (6), + (7), + (8), + (9), + (9.99999999999999), + (10), + (10.0000000000001) +) v(op); + op | wb_n1 | wb_n2 | wb_n3 | wb_f1 | wb_f2 | wb_f3 +------------------+-------+-------+-------+-------+-------+------- + -5.2 | 0 | 0 | 1 | 0 | 0 | 1 + -0.0000000001 | 0 | 0 | 2 | 0 | 0 | 2 + 0.000000000001 | 0 | 1 | 2 | 0 | 1 | 2 + 1 | 1 | 1 | 2 | 1 | 1 | 2 + 1.99999999999999 | 1 | 1 | 2 | 1 | 1 | 2 + 2 | 1 | 1 | 3 | 1 | 1 | 3 + 2.00000000000001 | 1 | 1 | 3 | 1 | 1 | 3 + 3 | 2 | 1 | 3 | 2 | 1 | 3 + 4 | 2 | 1 | 3 | 2 | 1 | 3 + 4.5 | 2 | 1 | 3 | 2 | 1 | 3 + 5 | 3 | 1 | 3 | 3 | 1 | 3 + 5.5 | 3 | 2 | 3 | 3 | 2 | 3 + 6 | 3 | 2 | 3 | 3 | 2 | 3 + 7 | 3 | 2 | 3 | 3 | 2 | 3 + 8 | 3 | 2 | 3 | 3 | 2 | 3 + 9 | 3 | 2 | 3 | 3 | 2 | 3 + 9.99999999999999 | 3 | 3 | 3 | 3 | 3 | 3 + 10 | 4 | 3 | 3 | 4 | 3 | 3 + 10.0000000000001 | 4 | 3 | 3 | 4 | 3 | 3 +(19 rows) + +-- ensure float8 path handles NaN properly +SELECT + op, + width_bucket(op, ARRAY[1, 3, 9, 'NaN', 'NaN']::float8[]) AS wb +FROM (VALUES + (-5.2::float8), + (4::float8), + (77::float8), + ('NaN'::float8) +) v(op); + op | wb +------+---- + -5.2 | 0 + 4 | 2 + 77 | 3 + NaN | 5 +(4 rows) + +-- these exercise the generic fixed-width code path +SELECT + op, + width_bucket(op, ARRAY[1, 3, 5, 10]) AS wb_1 +FROM generate_series(0,11) as op; + op | wb_1 +----+------ + 0 | 0 + 1 | 1 + 2 | 1 + 3 | 2 + 4 | 2 + 5 | 3 + 6 | 3 + 7 | 3 + 8 | 3 + 9 | 3 + 10 | 4 + 11 | 4 +(12 rows) + +SELECT width_bucket(now(), + array['yesterday', 'today', 'tomorrow']::timestamptz[]); + width_bucket +-------------- + 2 +(1 row) + +-- corner cases +SELECT width_bucket(5, ARRAY[3]); + width_bucket +-------------- + 1 +(1 row) + +SELECT width_bucket(5, '{}'); + width_bucket +-------------- + 0 +(1 row) + +-- error cases +SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); +ERROR: function width_bucket(text, integer[]) does not exist +LINE 1: SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +SELECT width_bucket(5, ARRAY[3, 4, NULL]); +ERROR: thresholds array must not contain NULLs +SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); +ERROR: thresholds must be one-dimensional array +-- 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 +SELECT trim_array(ARRAY[]::int[], 1); -- fail +ERROR: number of elements to trim must be between 0 and 0 |