granicus.if.org Git - postgresql/blob - src/test/regress/expected/arrays.out

   1 --
   2 -- ARRAYS
   3 --
   4 CREATE TABLE arrtest (
   5         a                       int2[],
   6         b                       int4[][][],
   7         c                       name[],
   8         d                       text[][],
   9         e                       float8[],
  10         f                       char(5)[],
  11         g                       varchar(5)[]
  12 );
  13 --
  14 -- only the 'e' array is 0-based, the others are 1-based.
  15 --
  16 INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g)
  17    VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}');
  18 UPDATE arrtest SET e[0] = '1.1';
  19 UPDATE arrtest SET e[1] = '2.2';
  20 INSERT INTO arrtest (f)
  21    VALUES ('{"too long"}');
  22 ERROR:  value too long for type character(5)
  23 INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g)
  24    VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}',
  25            '{{"elt1", "elt2"}}', '{"3.4", "6.7"}',
  26            '{"abc","abcde"}', '{"abc","abcde"}');
  27 INSERT INTO arrtest (a, b[1:2], c, d[1:2])
  28    VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}');
  29 SELECT * FROM arrtest;
  30       a      |        b        |     c     |       d       |        e        |        f        |      g
  31 -------------+-----------------+-----------+---------------+-----------------+-----------------+-------------
  32  {1,2,3,4,5} | {{{0,0},{1,2}}} | {}        | {}            | [0:1]={1.1,2.2} | {}              | {}
  33  {11,12,23}  | {{3,4},{4,5}}   | {foobar}  | {{elt1,elt2}} | {3.4,6.7}       | {"abc  ",abcde} | {abc,abcde}
  34  {}          | {3,4}           | {foo,bar} | {bar,foo}     |                 |                 |
  35 (3 rows)
  36
  37 SELECT arrtest.a[1],
  38           arrtest.b[1][1][1],
  39           arrtest.c[1],
  40           arrtest.d[1][1],
  41           arrtest.e[0]
  42    FROM arrtest;
  43  a  | b |   c    |  d   |  e
  44 ----+---+--------+------+-----
  45   1 | 0 |        |      | 1.1
  46  11 |   | foobar | elt1 |
  47     |   | foo    |      |
  48 (3 rows)
  49
  50 SELECT a[1], b[1][1][1], c[1], d[1][1], e[0]
  51    FROM arrtest;
  52  a  | b |   c    |  d   |  e
  53 ----+---+--------+------+-----
  54   1 | 0 |        |      | 1.1
  55  11 |   | foobar | elt1 |
  56     |   | foo    |      |
  57 (3 rows)
  58
  59 SELECT a[1:3],
  60           b[1:1][1:2][1:2],
  61           c[1:2],
  62           d[1:1][1:2]
  63    FROM arrtest;
  64      a      |        b        |     c     |       d
  65 ------------+-----------------+-----------+---------------
  66  {1,2,3}    | {{{0,0},{1,2}}} | {}        | {}
  67  {11,12,23} | {}              | {foobar}  | {{elt1,elt2}}
  68  {}         | {}              | {foo,bar} | {}
  69 (3 rows)
  70
  71 SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c
  72    FROM arrtest;
  73  a | b | c
  74 ---+---+---
  75  1 | 3 |
  76  1 | 2 | 1
  77    | 1 | 1
  78 (3 rows)
  79
  80 SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c
  81    FROM arrtest;
  82    a   |        b        |   c
  83 -------+-----------------+-------
  84  [1:5] | [1:1][1:2][1:2] |
  85  [1:3] | [1:2][1:2]      | [1:1]
  86        | [1:2]           | [1:2]
  87 (3 rows)
  88
  89 -- returns nothing
  90 SELECT *
  91    FROM arrtest
  92    WHERE a[1] < 5 and
  93          c = '{"foobar"}'::_name;
  94  a | b | c | d | e | f | g
  95 ---+---+---+---+---+---+---
  96 (0 rows)
  97
  98 UPDATE arrtest
  99   SET a[1:2] = '{16,25}'
 100   WHERE NOT a = '{}'::_int2;
 101 UPDATE arrtest
 102   SET b[1:1][1:1][1:2] = '{113, 117}',
 103       b[1:1][1:2][2:2] = '{142, 147}'
 104   WHERE array_dims(b) = '[1:1][1:2][1:2]';
 105 UPDATE arrtest
 106   SET c[2:2] = '{"new_word"}'
 107   WHERE array_dims(c) is not null;
 108 SELECT a,b,c FROM arrtest;
 109        a       |           b           |         c
 110 ---------------+-----------------------+-------------------
 111  {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
 112  {}            | {3,4}                 | {foo,new_word}
 113  {16,25,23}    | {{3,4},{4,5}}         | {foobar,new_word}
 114 (3 rows)
 115
 116 SELECT a[1:3],
 117           b[1:1][1:2][1:2],
 118           c[1:2],
 119           d[1:1][2:2]
 120    FROM arrtest;
 121      a      |           b           |         c         |    d
 122 ------------+-----------------------+-------------------+----------
 123  {16,25,3}  | {{{113,142},{1,147}}} | {}                | {}
 124  {}         | {}                    | {foo,new_word}    | {}
 125  {16,25,23} | {}                    | {foobar,new_word} | {{elt2}}
 126 (3 rows)
 127
 128 INSERT INTO arrtest(a) VALUES('{1,null,3}');
 129 SELECT a FROM arrtest;
 130        a
 131 ---------------
 132  {16,25,3,4,5}
 133  {}
 134  {16,25,23}
 135  {1,NULL,3}
 136 (4 rows)
 137
 138 UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL;
 139 SELECT a FROM arrtest WHERE a[2] IS NULL;
 140         a
 141 -----------------
 142  [4:4]={NULL}
 143  {1,NULL,3,NULL}
 144 (2 rows)
 145
 146 DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL;
 147 SELECT a,b,c FROM arrtest;
 148        a       |           b           |         c
 149 ---------------+-----------------------+-------------------
 150  {16,25,3,4,5} | {{{113,142},{1,147}}} | {}
 151  {16,25,23}    | {{3,4},{4,5}}         | {foobar,new_word}
 152  [4:4]={NULL}  | {3,4}                 | {foo,new_word}
 153 (3 rows)
 154
 155 --
 156 -- test array extension
 157 --
 158 CREATE TEMP TABLE arrtest1 (i int[], t text[]);
 159 insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
 160 select * from arrtest1;
 161       i       |          t
 162 --------------+---------------------
 163  {1,2,NULL,4} | {one,two,NULL,four}
 164 (1 row)
 165
 166 update arrtest1 set i[2] = 22, t[2] = 'twenty-two';
 167 select * from arrtest1;
 168        i       |             t
 169 ---------------+----------------------------
 170  {1,22,NULL,4} | {one,twenty-two,NULL,four}
 171 (1 row)
 172
 173 update arrtest1 set i[5] = 5, t[5] = 'five';
 174 select * from arrtest1;
 175         i        |                t
 176 -----------------+---------------------------------
 177  {1,22,NULL,4,5} | {one,twenty-two,NULL,four,five}
 178 (1 row)
 179
 180 update arrtest1 set i[8] = 8, t[8] = 'eight';
 181 select * from arrtest1;
 182               i              |                        t
 183 -----------------------------+-------------------------------------------------
 184  {1,22,NULL,4,5,NULL,NULL,8} | {one,twenty-two,NULL,four,five,NULL,NULL,eight}
 185 (1 row)
 186
 187 update arrtest1 set i[0] = 0, t[0] = 'zero';
 188 select * from arrtest1;
 189                   i                  |                             t
 190 -------------------------------------+------------------------------------------------------------
 191  [0:8]={0,1,22,NULL,4,5,NULL,NULL,8} | [0:8]={zero,one,twenty-two,NULL,four,five,NULL,NULL,eight}
 192 (1 row)
 193
 194 update arrtest1 set i[-3] = -3, t[-3] = 'minus-three';
 195 select * from arrtest1;
 196                          i                         |                                         t
 197 ---------------------------------------------------+-----------------------------------------------------------------------------------
 198  [-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}
 199 (1 row)
 200
 201 update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve'];
 202 select * from arrtest1;
 203                           i                          |                                        t
 204 -----------------------------------------------------+---------------------------------------------------------------------------------
 205  [-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}
 206 (1 row)
 207
 208 update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20'];
 209 select * from arrtest1;
 210                                i                               |                                            t
 211 ---------------------------------------------------------------+-----------------------------------------------------------------------------------------
 212  [-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}
 213 (1 row)
 214
 215 update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22'];
 216 select * from arrtest1;
 217                                    i                                   |                                                t
 218 -----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------
 219  [-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}
 220 (1 row)
 221
 222 update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26'];
 223 select * from arrtest1;
 224                                             i                                            |                                                          t
 225 -----------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
 226  [-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}
 227 (1 row)
 228
 229 update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13'];
 230 select * from arrtest1;
 231                                                 i                                                 |                                                          t
 232 --------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------
 233  [-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}
 234 (1 row)
 235
 236 update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null];
 237 select * from arrtest1;
 238                                                      i                                                     |                                                              t
 239 -----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------
 240  [-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}
 241 (1 row)
 242
 243 update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20'];
 244 select * from arrtest1;
 245                                                                  i                                                                 |                                                                          t
 246 -----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------
 247  [-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}
 248 (1 row)
 249
 250 delete from arrtest1;
 251 insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']);
 252 select * from arrtest1;
 253       i       |          t
 254 --------------+---------------------
 255  {1,2,NULL,4} | {one,two,NULL,four}
 256 (1 row)
 257
 258 update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5'];
 259 select * from arrtest1;
 260            i            |             t
 261 ------------------------+----------------------------
 262  [0:5]={0,1,2,NULL,4,5} | [0:5]={z,p1,p2,NULL,p4,p5}
 263 (1 row)
 264
 265 --
 266 -- array expressions and operators
 267 --
 268 -- table creation and INSERTs
 269 CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]);
 270 INSERT INTO arrtest2 VALUES(
 271   ARRAY[[[113,142],[1,147]]],
 272   ARRAY[1.1,1.2,1.3]::float8[],
 273   ARRAY[1.1,1.2,1.3],
 274   ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]],
 275   ARRAY['19620326','19931223','19970117']::timestamp[]
 276 );
 277 -- some more test data
 278 CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8);
 279 insert into arrtest_f values(1,'cat1',1.21);
 280 insert into arrtest_f values(2,'cat1',1.24);
 281 insert into arrtest_f values(3,'cat1',1.18);
 282 insert into arrtest_f values(4,'cat1',1.26);
 283 insert into arrtest_f values(5,'cat1',1.15);
 284 insert into arrtest_f values(6,'cat2',1.15);
 285 insert into arrtest_f values(7,'cat2',1.26);
 286 insert into arrtest_f values(8,'cat2',1.32);
 287 insert into arrtest_f values(9,'cat2',1.30);
 288 CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int);
 289 insert into arrtest_i values(1,'cat1',21);
 290 insert into arrtest_i values(2,'cat1',24);
 291 insert into arrtest_i values(3,'cat1',18);
 292 insert into arrtest_i values(4,'cat1',26);
 293 insert into arrtest_i values(5,'cat1',15);
 294 insert into arrtest_i values(6,'cat2',15);
 295 insert into arrtest_i values(7,'cat2',26);
 296 insert into arrtest_i values(8,'cat2',32);
 297 insert into arrtest_i values(9,'cat2',30);
 298 -- expressions
 299 SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM (
 300   SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f
 301 ) AS t;
 302  131 | 221
 303 -----+-----
 304  131 | 221
 305 (1 row)
 306
 307 SELECT ARRAY[[[[[['hello'],['world']]]]]];
 308            array
 309 ---------------------------
 310  {{{{{{hello},{world}}}}}}
 311 (1 row)
 312
 313 SELECT ARRAY[ARRAY['hello'],ARRAY['world']];
 314        array
 315 -------------------
 316  {{hello},{world}}
 317 (1 row)
 318
 319 SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY";
 320                      ARRAY
 321 -----------------------------------------------
 322  {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32}
 323 (1 row)
 324
 325 -- with nulls
 326 SELECT '{1,null,3}'::int[];
 327     int4
 328 ------------
 329  {1,NULL,3}
 330 (1 row)
 331
 332 SELECT ARRAY[1,NULL,3];
 333    array
 334 ------------
 335  {1,NULL,3}
 336 (1 row)
 337
 338 -- functions
 339 SELECT array_append(array[42], 6) AS "{42,6}";
 340  {42,6}
 341 --------
 342  {42,6}
 343 (1 row)
 344
 345 SELECT array_prepend(6, array[42]) AS "{6,42}";
 346  {6,42}
 347 --------
 348  {6,42}
 349 (1 row)
 350
 351 SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
 352  {1,2,3,4}
 353 -----------
 354  {1,2,3,4}
 355 (1 row)
 356
 357 SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
 358  {{1,2},{3,4},{5,6}}
 359 ---------------------
 360  {{1,2},{3,4},{5,6}}
 361 (1 row)
 362
 363 SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
 364  {{3,4},{5,6},{1,2}}
 365 ---------------------
 366  {{3,4},{5,6},{1,2}}
 367 (1 row)
 368
 369 -- operators
 370 SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
 371        a
 372 ---------------
 373  {16,25,3,4,5}
 374 (1 row)
 375
 376 SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
 377  FALSE
 378 -------
 379  f
 380 (1 row)
 381
 382 SELECT ARRAY[1,2] || 3 AS "{1,2,3}";
 383  {1,2,3}
 384 ---------
 385  {1,2,3}
 386 (1 row)
 387
 388 SELECT 0 || ARRAY[1,2] AS "{0,1,2}";
 389  {0,1,2}
 390 ---------
 391  {0,1,2}
 392 (1 row)
 393
 394 SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}";
 395  {1,2,3,4}
 396 -----------
 397  {1,2,3,4}
 398 (1 row)
 399
 400 SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY";
 401                 ARRAY
 402 --------------------------------------
 403  {{{hello,world}},{{happy,birthday}}}
 404 (1 row)
 405
 406 SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}";
 407  {{1,2},{3,4},{5,6}}
 408 ---------------------
 409  {{1,2},{3,4},{5,6}}
 410 (1 row)
 411
 412 SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}";
 413  {0,0,1,1,2,2}
 414 ---------------
 415  {0,0,1,1,2,2}
 416 (1 row)
 417
 418 SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}";
 419  {0,1,2,3}
 420 -----------
 421  {0,1,2,3}
 422 (1 row)
 423
 424 SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno;
 425  seqno |                i                |                                                                 t
 426 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 427      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 428     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 429     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 430     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 431     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 432    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 433 (6 rows)
 434
 435 SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno;
 436  seqno |                i                |                                                                 t
 437 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 438      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 439     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 440     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 441     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 442     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 443    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 444 (6 rows)
 445
 446 SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno;
 447  seqno |                i                |                                                                 t
 448 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 449      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 450     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 451     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 452     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 453     53 | {38,17}                         | {AAAAAAAAAAA21658}
 454     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 455     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 456     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 457 (8 rows)
 458
 459 SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno;
 460  seqno |                i                |                                                                 t
 461 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 462      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 463     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 464     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 465     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 466     53 | {38,17}                         | {AAAAAAAAAAA21658}
 467     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 468     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 469     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 470 (8 rows)
 471
 472 SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno;
 473  seqno |                i                |                                                                 t
 474 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 475      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 476     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 477     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 478 (3 rows)
 479
 480 SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno;
 481  seqno |                i                |                                                                 t
 482 -------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------
 483      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 484     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 485     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 486     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 487     53 | {38,17}                         | {AAAAAAAAAAA21658}
 488     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 489     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 490     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 491     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 492     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 493    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 494 (11 rows)
 495
 496 SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno;
 497  seqno |       i       |                                                             t
 498 -------+---------------+----------------------------------------------------------------------------------------------------------------------------
 499     40 | {34}          | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
 500     74 | {32}          | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 501     98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 502    101 | {}            | {}
 503 (4 rows)
 504
 505 SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno;
 506  seqno | i  | t
 507 -------+----+----
 508    101 | {} | {}
 509 (1 row)
 510
 511 SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno;
 512  seqno |                i                |                                                                                                       t
 513 -------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 514      1 | {92,75,71,52,64,83}             | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
 515      2 | {3,6}                           | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
 516      3 | {37,64,95,43,3,41,13,30,11,43}  | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
 517      4 | {71,39,99,55,33,75,45}          | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
 518      5 | {50,42,77,50,4}                 | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
 519      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 520      7 | {12,51,88,64,8}                 | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
 521      8 | {60,84}                         | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
 522      9 | {56,52,35,27,80,44,81,22}       | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
 523     10 | {71,5,45}                       | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
 524     11 | {41,86,74,48,22,74,47,50}       | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
 525     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 526     13 | {3,52,34,23}                    | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
 527     14 | {78,57,19}                      | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
 528     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 529     16 | {14,63,85,11}                   | {AAAAAA66777}
 530     17 | {7,10,81,85}                    | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
 531     18 | {1}                             | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
 532     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 533     20 | {72,89,70,51,54,37,8,49,79}     | {AAAAAA58494}
 534     21 | {2,8,65,10,5,79,43}             | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
 535     22 | {11,6,56,62,53,30}              | {AAAAAAAA72908}
 536     23 | {40,90,5,38,72,40,30,10,43,55}  | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
 537     24 | {94,61,99,35,48}                | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
 538     25 | {31,1,10,11,27,79,38}           | {AAAAAAAAAAAAAAAAAA59334,45449}
 539     26 | {71,10,9,69,75}                 | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
 540     27 | {94}                            | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
 541     28 | {14,33,6,34,14}                 | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
 542     29 | {39,21}                         | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
 543     30 | {26,81,47,91,34}                | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 544     31 | {80,24,18,21,54}                | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
 545     32 | {58,79,82,80,67,75,98,10,41}    | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
 546     33 | {74,73}                         | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
 547     34 | {70,45}                         | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
 548     35 | {23,40}                         | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
 549     36 | {79,82,14,52,30,5,79}           | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
 550     37 | {53,11,81,39,3,78,58,64,74}     | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
 551     38 | {59,5,4,95,28}                  | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
 552     39 | {82,43,99,16,74}                | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
 553     40 | {34}                            | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
 554     41 | {19,26,63,12,93,73,27,94}       | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
 555     42 | {15,76,82,75,8,91}              | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
 556     43 | {39,87,91,97,79,28}             | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
 557     44 | {40,58,68,29,54}                | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
 558     45 | {99,45}                         | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 559     46 | {53,24}                         | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
 560     47 | {98,23,64,12,75,61}             | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
 561     48 | {76,14}                         | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
 562     49 | {56,5,54,37,49}                 | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
 563     50 | {20,12,37,64,93}                | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
 564     51 | {47}                            | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
 565     52 | {89,0}                          | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
 566     53 | {38,17}                         | {AAAAAAAAAAA21658}
 567     54 | {70,47}                         | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
 568     55 | {47,79,47,64,72,25,71,24,93}    | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
 569     56 | {33,7,60,54,93,90,77,85,39}     | {AAAAAAAAAAAAAAAAAA32918,AA42406}
 570     57 | {23,45,10,42,36,21,9,96}        | {AAAAAAAAAAAAAAAAAAA70415}
 571     58 | {92}                            | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
 572     59 | {9,69,46,77}                    | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
 573     60 | {62,2,59,38,89}                 | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
 574     61 | {72,2,44,95,54,54,13}           | {AAAAAAAAAAAAAAAAAAA91804}
 575     62 | {83,72,29,73}                   | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
 576     63 | {11,4,61,87}                    | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
 577     64 | {26,19,34,24,81,78}             | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 578     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 579     66 | {31,23,70,52,4,33,48,25}        | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
 580     67 | {31,94,7,10}                    | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
 581     68 | {90,43,38}                      | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
 582     69 | {67,35,99,85,72,86,44}          | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
 583     70 | {56,70,83}                      | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
 584     71 | {74,26}                         | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
 585     72 | {22,1,16,78,20,91,83}           | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 586     73 | {88,25,96,78,65,15,29,19}       | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
 587     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 588     75 | {12,96,83,24,71,89,55}          | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
 589     76 | {92,55,10,7}                    | {AAAAAAAAAAAAAAA67062}
 590     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 591     78 | {55,89,44,84,34}                | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
 592     79 | {45}                            | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 593     80 | {74,89,44,80,0}                 | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
 594     81 | {63,77,54,48,61,53,97}          | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
 595     82 | {34,60,4,79,78,16,86,89,42,50}  | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 596     83 | {14,10}                         | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
 597     84 | {11,83,35,13,96,94}             | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
 598     85 | {39,60}                         | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
 599     86 | {33,81,72,74,45,36,82}          | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
 600     87 | {57,27,50,12,97,68}             | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
 601     88 | {41,90,77,24,6,24}              | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 602     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 603     90 | {88,75}                         | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
 604     91 | {78}                            | {AAAAAAAAAAAAA62007,AAA99043}
 605     92 | {85,63,49,45}                   | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
 606     93 | {11}                            | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
 607     94 | {98,9,85,62,88,91,60,61,38,86}  | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
 608     95 | {47,77}                         | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
 609     96 | {23,97,43}                      | {AAAAAAAAAA646,A87088}
 610     97 | {54,2,86,65}                    | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 611     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 612     99 | {37,86}                         | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
 613    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 614    101 | {}                              | {}
 615    102 | {NULL}                          | {NULL}
 616 (102 rows)
 617
 618 SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno;
 619  seqno | i | t
 620 -------+---+---
 621 (0 rows)
 622
 623 SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno;
 624  seqno | i  | t
 625 -------+----+----
 626    101 | {} | {}
 627 (1 row)
 628
 629 SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno;
 630  seqno |   i    |   t
 631 -------+--------+--------
 632    102 | {NULL} | {NULL}
 633 (1 row)
 634
 635 SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno;
 636  seqno | i | t
 637 -------+---+---
 638 (0 rows)
 639
 640 SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno;
 641  seqno | i | t
 642 -------+---+---
 643 (0 rows)
 644
 645 SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno;
 646  seqno | i  | t
 647 -------+----+----
 648    101 | {} | {}
 649 (1 row)
 650
 651 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 652  seqno |           i           |                                                                     t
 653 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 654     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 655     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 656     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 657     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 658 (4 rows)
 659
 660 SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 661  seqno |           i           |                                                                     t
 662 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 663     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 664     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 665     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 666     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 667 (4 rows)
 668
 669 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 670  seqno |        i         |                                 t
 671 -------+------------------+--------------------------------------------------------------------
 672     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 673     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 674     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 675 (3 rows)
 676
 677 SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 678  seqno |        i         |                                 t
 679 -------+------------------+--------------------------------------------------------------------
 680     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 681     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 682     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 683 (3 rows)
 684
 685 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 686  seqno |  i   |                                 t
 687 -------+------+--------------------------------------------------------------------
 688     79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 689 (1 row)
 690
 691 SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 692  seqno |           i           |                                                                     t
 693 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 694     15 | {17,14,16,63,67}      | {AA6416,AAAAAAAAAA646,AAAAA95309}
 695     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 696     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 697     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 698     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 699     96 | {23,97,43}            | {AAAAAAAAAA646,A87088}
 700 (6 rows)
 701
 702 SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 703  seqno |         i          |                                                     t
 704 -------+--------------------+-----------------------------------------------------------------------------------------------------------
 705     22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
 706     45 | {99,45}            | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 707    101 | {}                 | {}
 708 (3 rows)
 709
 710 SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno;
 711  seqno | i  | t
 712 -------+----+----
 713    101 | {} | {}
 714 (1 row)
 715
 716 SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno;
 717  seqno |                i                |                                                                                                       t
 718 -------+---------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 719      1 | {92,75,71,52,64,83}             | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038}
 720      2 | {3,6}                           | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793}
 721      3 | {37,64,95,43,3,41,13,30,11,43}  | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246}
 722      4 | {71,39,99,55,33,75,45}          | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557}
 723      5 | {50,42,77,50,4}                 | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104}
 724      6 | {39,35,5,94,17,92,60,32}        | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657}
 725      7 | {12,51,88,64,8}                 | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946}
 726      8 | {60,84}                         | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407}
 727      9 | {56,52,35,27,80,44,81,22}       | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000}
 728     10 | {71,5,45}                       | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249}
 729     11 | {41,86,74,48,22,74,47,50}       | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557}
 730     12 | {17,99,18,52,91,72,0,43,96,23}  | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576}
 731     13 | {3,52,34,23}                    | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658}
 732     14 | {78,57,19}                      | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909}
 733     15 | {17,14,16,63,67}                | {AA6416,AAAAAAAAAA646,AAAAA95309}
 734     16 | {14,63,85,11}                   | {AAAAAA66777}
 735     17 | {7,10,81,85}                    | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356}
 736     18 | {1}                             | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374}
 737     19 | {52,82,17,74,23,46,69,51,75}    | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938}
 738     20 | {72,89,70,51,54,37,8,49,79}     | {AAAAAA58494}
 739     21 | {2,8,65,10,5,79,43}             | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420}
 740     22 | {11,6,56,62,53,30}              | {AAAAAAAA72908}
 741     23 | {40,90,5,38,72,40,30,10,43,55}  | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562}
 742     24 | {94,61,99,35,48}                | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219}
 743     25 | {31,1,10,11,27,79,38}           | {AAAAAAAAAAAAAAAAAA59334,45449}
 744     26 | {71,10,9,69,75}                 | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009}
 745     27 | {94}                            | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254}
 746     28 | {14,33,6,34,14}                 | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601}
 747     29 | {39,21}                         | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194}
 748     30 | {26,81,47,91,34}                | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240}
 749     31 | {80,24,18,21,54}                | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938}
 750     32 | {58,79,82,80,67,75,98,10,41}    | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533}
 751     33 | {74,73}                         | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796}
 752     34 | {70,45}                         | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242}
 753     35 | {23,40}                         | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084}
 754     36 | {79,82,14,52,30,5,79}           | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598}
 755     37 | {53,11,81,39,3,78,58,64,74}     | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611}
 756     38 | {59,5,4,95,28}                  | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387}
 757     39 | {82,43,99,16,74}                | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620}
 758     40 | {34}                            | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623}
 759     41 | {19,26,63,12,93,73,27,94}       | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666}
 760     42 | {15,76,82,75,8,91}              | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587}
 761     43 | {39,87,91,97,79,28}             | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946}
 762     44 | {40,58,68,29,54}                | {AAAAAAA81898,AAAAAA66777,AAAAAA98232}
 763     45 | {99,45}                         | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 764     46 | {53,24}                         | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621}
 765     47 | {98,23,64,12,75,61}             | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466}
 766     48 | {76,14}                         | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037}
 767     49 | {56,5,54,37,49}                 | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587}
 768     50 | {20,12,37,64,93}                | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955}
 769     51 | {47}                            | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452}
 770     52 | {89,0}                          | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862}
 771     53 | {38,17}                         | {AAAAAAAAAAA21658}
 772     54 | {70,47}                         | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322}
 773     55 | {47,79,47,64,72,25,71,24,93}    | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737}
 774     56 | {33,7,60,54,93,90,77,85,39}     | {AAAAAAAAAAAAAAAAAA32918,AA42406}
 775     57 | {23,45,10,42,36,21,9,96}        | {AAAAAAAAAAAAAAAAAAA70415}
 776     58 | {92}                            | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119}
 777     59 | {9,69,46,77}                    | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955}
 778     60 | {62,2,59,38,89}                 | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875}
 779     61 | {72,2,44,95,54,54,13}           | {AAAAAAAAAAAAAAAAAAA91804}
 780     62 | {83,72,29,73}                   | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617}
 781     63 | {11,4,61,87}                    | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938}
 782     64 | {26,19,34,24,81,78}             | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240}
 783     65 | {61,5,76,59,17}                 | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012}
 784     66 | {31,23,70,52,4,33,48,25}        | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836}
 785     67 | {31,94,7,10}                    | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946}
 786     68 | {90,43,38}                      | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643}
 787     69 | {67,35,99,85,72,86,44}          | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955}
 788     70 | {56,70,83}                      | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242}
 789     71 | {74,26}                         | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557}
 790     72 | {22,1,16,78,20,91,83}           | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 791     73 | {88,25,96,78,65,15,29,19}       | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598}
 792     74 | {32}                            | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956}
 793     75 | {12,96,83,24,71,89,55}          | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052}
 794     76 | {92,55,10,7}                    | {AAAAAAAAAAAAAAA67062}
 795     77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066}
 796     78 | {55,89,44,84,34}                | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620}
 797     79 | {45}                            | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 798     80 | {74,89,44,80,0}                 | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007}
 799     81 | {63,77,54,48,61,53,97}          | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121}
 800     82 | {34,60,4,79,78,16,86,89,42,50}  | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104}
 801     83 | {14,10}                         | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119}
 802     84 | {11,83,35,13,96,94}             | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183}
 803     85 | {39,60}                         | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154}
 804     86 | {33,81,72,74,45,36,82}          | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176}
 805     87 | {57,27,50,12,97,68}             | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505}
 806     88 | {41,90,77,24,6,24}              | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433}
 807     89 | {40,32,17,6,30,88}              | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673}
 808     90 | {88,75}                         | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526}
 809     91 | {78}                            | {AAAAAAAAAAAAA62007,AAA99043}
 810     92 | {85,63,49,45}                   | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089}
 811     93 | {11}                            | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383}
 812     94 | {98,9,85,62,88,91,60,61,38,86}  | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587}
 813     95 | {47,77}                         | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483}
 814     96 | {23,97,43}                      | {AAAAAAAAAA646,A87088}
 815     97 | {54,2,86,65}                    | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643}
 816     98 | {38,34,32,89}                   | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845}
 817     99 | {37,86}                         | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356}
 818    100 | {85,32,57,39,49,84,32,3,30}     | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523}
 819    101 | {}                              | {}
 820    102 | {NULL}                          | {NULL}
 821 (102 rows)
 822
 823 SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno;
 824  seqno | i | t
 825 -------+---+---
 826 (0 rows)
 827
 828 SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno;
 829  seqno | i  | t
 830 -------+----+----
 831    101 | {} | {}
 832 (1 row)
 833
 834 -- array casts
 835 SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
 836  {1,2,3}
 837 ---------
 838  {1,2,3}
 839 (1 row)
 840
 841 SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE";
 842  TRUE
 843 ------
 844  t
 845 (1 row)
 846
 847 SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
 848  {{a,bc},{def,hijk}}
 849 ---------------------
 850  {{a,bc},{def,hijk}}
 851 (1 row)
 852
 853 SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE";
 854  TRUE
 855 ------
 856  t
 857 (1 row)
 858
 859 SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
 860  {{{{{{a,bb,ccc}}}}}}
 861 ----------------------
 862  {{{{{{a,bb,ccc}}}}}}
 863 (1 row)
 864
 865 -- scalar op any/all (array)
 866 select 33 = any ('{1,2,3}');
 867  ?column?
 868 ----------
 869  f
 870 (1 row)
 871
 872 select 33 = any ('{1,2,33}');
 873  ?column?
 874 ----------
 875  t
 876 (1 row)
 877
 878 select 33 = all ('{1,2,33}');
 879  ?column?
 880 ----------
 881  f
 882 (1 row)
 883
 884 select 33 >= all ('{1,2,33}');
 885  ?column?
 886 ----------
 887  t
 888 (1 row)
 889
 890 -- boundary cases
 891 select null::int >= all ('{1,2,33}');
 892  ?column?
 893 ----------
 894
 895 (1 row)
 896
 897 select null::int >= all ('{}');
 898  ?column?
 899 ----------
 900  t
 901 (1 row)
 902
 903 select null::int >= any ('{}');
 904  ?column?
 905 ----------
 906  f
 907 (1 row)
 908
 909 -- cross-datatype
 910 select 33.4 = any (array[1,2,3]);
 911  ?column?
 912 ----------
 913  f
 914 (1 row)
 915
 916 select 33.4 > all (array[1,2,3]);
 917  ?column?
 918 ----------
 919  t
 920 (1 row)
 921
 922 -- errors
 923 select 33 * any ('{1,2,3}');
 924 ERROR:  op ANY/ALL (array) requires operator to yield boolean
 925 LINE 1: select 33 * any ('{1,2,3}');
 926                   ^
 927 select 33 * any (44);
 928 ERROR:  op ANY/ALL (array) requires array on right side
 929 LINE 1: select 33 * any (44);
 930                   ^
 931 -- nulls
 932 select 33 = any (null::int[]);
 933  ?column?
 934 ----------
 935
 936 (1 row)
 937
 938 select null::int = any ('{1,2,3}');
 939  ?column?
 940 ----------
 941
 942 (1 row)
 943
 944 select 33 = any ('{1,null,3}');
 945  ?column?
 946 ----------
 947
 948 (1 row)
 949
 950 select 33 = any ('{1,null,33}');
 951  ?column?
 952 ----------
 953  t
 954 (1 row)
 955
 956 select 33 = all (null::int[]);
 957  ?column?
 958 ----------
 959
 960 (1 row)
 961
 962 select null::int = all ('{1,2,3}');
 963  ?column?
 964 ----------
 965
 966 (1 row)
 967
 968 select 33 = all ('{1,null,3}');
 969  ?column?
 970 ----------
 971  f
 972 (1 row)
 973
 974 select 33 = all ('{33,null,33}');
 975  ?column?
 976 ----------
 977
 978 (1 row)
 979
 980 -- test indexes on arrays
 981 create temp table arr_tbl (f1 int[] unique);
 982 insert into arr_tbl values ('{1,2,3}');
 983 insert into arr_tbl values ('{1,2}');
 984 -- failure expected:
 985 insert into arr_tbl values ('{1,2,3}');
 986 ERROR:  duplicate key value violates unique constraint "arr_tbl_f1_key"
 987 DETAIL:  Key (f1)=({1,2,3}) already exists.
 988 insert into arr_tbl values ('{2,3,4}');
 989 insert into arr_tbl values ('{1,5,3}');
 990 insert into arr_tbl values ('{1,2,10}');
 991 set enable_seqscan to off;
 992 set enable_bitmapscan to off;
 993 select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
 994     f1
 995 ----------
 996  {1,2,10}
 997  {1,5,3}
 998 (2 rows)
 999
1000 select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}';
1001     f1
1002 ----------
1003  {1,2,3}
1004  {1,2,10}
1005 (2 rows)
1006
1007 -- note: if above selects don't produce the expected tuple order,
1008 -- then you didn't get an indexscan plan, and something is busted.
1009 reset enable_seqscan;
1010 reset enable_bitmapscan;
1011 -- test [not] (like|ilike) (any|all) (...)
1012 select 'foo' like any (array['%a', '%o']); -- t
1013  ?column?
1014 ----------
1015  t
1016 (1 row)
1017
1018 select 'foo' like any (array['%a', '%b']); -- f
1019  ?column?
1020 ----------
1021  f
1022 (1 row)
1023
1024 select 'foo' like all (array['f%', '%o']); -- t
1025  ?column?
1026 ----------
1027  t
1028 (1 row)
1029
1030 select 'foo' like all (array['f%', '%b']); -- f
1031  ?column?
1032 ----------
1033  f
1034 (1 row)
1035
1036 select 'foo' not like any (array['%a', '%b']); -- t
1037  ?column?
1038 ----------
1039  t
1040 (1 row)
1041
1042 select 'foo' not like all (array['%a', '%o']); -- f
1043  ?column?
1044 ----------
1045  f
1046 (1 row)
1047
1048 select 'foo' ilike any (array['%A', '%O']); -- t
1049  ?column?
1050 ----------
1051  t
1052 (1 row)
1053
1054 select 'foo' ilike all (array['F%', '%O']); -- t
1055  ?column?
1056 ----------
1057  t
1058 (1 row)
1059
1060 --
1061 -- General array parser tests
1062 --
1063 -- none of the following should be accepted
1064 select '{{1,{2}},{2,3}}'::text[];
1065 ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
1066 LINE 1: select '{{1,{2}},{2,3}}'::text[];
1067                ^
1068 select '{{},{}}'::text[];
1069 ERROR:  malformed array literal: "{{},{}}"
1070 LINE 1: select '{{},{}}'::text[];
1071                ^
1072 select E'{{1,2},\\{2,3}}'::text[];
1073 ERROR:  malformed array literal: "{{1,2},\{2,3}}"
1074 LINE 1: select E'{{1,2},\\{2,3}}'::text[];
1075                ^
1076 select '{{"1 2" x},{3}}'::text[];
1077 ERROR:  malformed array literal: "{{"1 2" x},{3}}"
1078 LINE 1: select '{{"1 2" x},{3}}'::text[];
1079                ^
1080 select '{}}'::text[];
1081 ERROR:  malformed array literal: "{}}"
1082 LINE 1: select '{}}'::text[];
1083                ^
1084 select '{ }}'::text[];
1085 ERROR:  malformed array literal: "{ }}"
1086 LINE 1: select '{ }}'::text[];
1087                ^
1088 select array[];
1089 ERROR:  cannot determine type of empty array
1090 LINE 1: select array[];
1091                ^
1092 HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
1093 -- none of the above should be accepted
1094 -- all of the following should be accepted
1095 select '{}'::text[];
1096  text
1097 ------
1098  {}
1099 (1 row)
1100
1101 select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
1102                      text
1103 -----------------------------------------------
1104  {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
1105 (1 row)
1106
1107 select '{0 second  ,0 second}'::interval[];
1108    interval
1109 ---------------
1110  {"@ 0","@ 0"}
1111 (1 row)
1112
1113 select '{ { "," } , { 3 } }'::text[];
1114     text
1115 -------------
1116  {{","},{3}}
1117 (1 row)
1118
1119 select '  {   {  "  0 second  "   ,  0 second  }   }'::text[];
1120              text
1121 -------------------------------
1122  {{"  0 second  ","0 second"}}
1123 (1 row)
1124
1125 select '{
1126            0 second,
1127            @ 1 hour @ 42 minutes @ 20 seconds
1128          }'::interval[];
1129               interval
1130 ------------------------------------
1131  {"@ 0","@ 1 hour 42 mins 20 secs"}
1132 (1 row)
1133
1134 select array[]::text[];
1135  array
1136 -------
1137  {}
1138 (1 row)
1139
1140 select '[0:1]={1.1,2.2}'::float8[];
1141      float8
1142 -----------------
1143  [0:1]={1.1,2.2}
1144 (1 row)
1145
1146 -- all of the above should be accepted
1147 -- tests for array aggregates
1148 CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
1149 INSERT INTO arraggtest (f1, f2, f3) VALUES
1150 ('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
1151 INSERT INTO arraggtest (f1, f2, f3) VALUES
1152 ('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
1153 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
1154     max    |   min   |           max            |           min            |   max   |  min
1155 -----------+---------+--------------------------+--------------------------+---------+-------
1156  {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
1157 (1 row)
1158
1159 INSERT INTO arraggtest (f1, f2, f3) VALUES
1160 ('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
1161 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
1162       max      |   min   |              max               |           min            |          max          |  min
1163 ---------------+---------+--------------------------------+--------------------------+-----------------------+-------
1164  {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}
1165 (1 row)
1166
1167 INSERT INTO arraggtest (f1, f2, f3) VALUES
1168 ('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
1169 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
1170       max      |   min   |              max               |             min              |          max          |  min
1171 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
1172  {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}
1173 (1 row)
1174
1175 INSERT INTO arraggtest (f1, f2, f3) VALUES
1176 ('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
1177 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
1178       max      |   min   |              max               |             min              |          max          |  min
1179 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
1180  {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}
1181 (1 row)
1182
1183 INSERT INTO arraggtest (f1, f2, f3) VALUES
1184 ('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
1185 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
1186       max      | min |              max               |             min              |          max          |  min
1187 ---------------+-----+--------------------------------+------------------------------+-----------------------+-------
1188  {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}
1189 (1 row)
1190
1191 -- A few simple tests for arrays of composite types
1192 create type comptype as (f1 int, f2 text);
1193 create table comptable (c1 comptype, c2 comptype[]);
1194 -- XXX would like to not have to specify row() construct types here ...
1195 insert into comptable
1196   values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
1197 -- check that implicitly named array type _comptype isn't a problem
1198 create type _comptype as enum('fooey');
1199 select * from comptable;
1200    c1    |          c2
1201 ---------+-----------------------
1202  (1,foo) | {"(2,bar)","(3,baz)"}
1203 (1 row)
1204
1205 select c2[2].f2 from comptable;
1206  f2
1207 -----
1208  baz
1209 (1 row)
1210
1211 drop type _comptype;
1212 drop table comptable;
1213 drop type comptype;
1214 create or replace function unnest1(anyarray)
1215 returns setof anyelement as $$
1216 select $1[s] from generate_subscripts($1,1) g(s);
1217 $$ language sql immutable;
1218 create or replace function unnest2(anyarray)
1219 returns setof anyelement as $$
1220 select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
1221                    generate_subscripts($1,2) g2(s2);
1222 $$ language sql immutable;
1223 select * from unnest1(array[1,2,3]);
1224  unnest1
1225 ---------
1226        1
1227        2
1228        3
1229 (3 rows)
1230
1231 select * from unnest2(array[[1,2,3],[4,5,6]]);
1232  unnest2
1233 ---------
1234        1
1235        2
1236        3
1237        4
1238        5
1239        6
1240 (6 rows)
1241
1242 drop function unnest1(anyarray);
1243 drop function unnest2(anyarray);
1244 select array_fill(null::integer, array[3,3],array[2,2]);
1245                            array_fill
1246 -----------------------------------------------------------------
1247  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
1248 (1 row)
1249
1250 select array_fill(null::integer, array[3,3]);
1251                       array_fill
1252 ------------------------------------------------------
1253  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
1254 (1 row)
1255
1256 select array_fill(null::text, array[3,3],array[2,2]);
1257                            array_fill
1258 -----------------------------------------------------------------
1259  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
1260 (1 row)
1261
1262 select array_fill(null::text, array[3,3]);
1263                       array_fill
1264 ------------------------------------------------------
1265  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
1266 (1 row)
1267
1268 select array_fill(7, array[3,3],array[2,2]);
1269               array_fill
1270 --------------------------------------
1271  [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}}
1272 (1 row)
1273
1274 select array_fill(7, array[3,3]);
1275         array_fill
1276 ---------------------------
1277  {{7,7,7},{7,7,7},{7,7,7}}
1278 (1 row)
1279
1280 select array_fill('juhu'::text, array[3,3],array[2,2]);
1281                            array_fill
1282 -----------------------------------------------------------------
1283  [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1284 (1 row)
1285
1286 select array_fill('juhu'::text, array[3,3]);
1287                       array_fill
1288 ------------------------------------------------------
1289  {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1290 (1 row)
1291
1292 -- raise exception
1293 select array_fill(1, null, array[2,2]);
1294 ERROR:  dimension array or low bound array cannot be null
1295 select array_fill(1, array[2,2], null);
1296 ERROR:  dimension array or low bound array cannot be null
1297 select array_fill(1, array[3,3], array[1,1,1]);
1298 ERROR:  wrong number of array subscripts
1299 DETAIL:  Low bound array has different size than dimensions array.
1300 select array_fill(1, array[1,2,null]);
1301 ERROR:  dimension values cannot be null
1302 select string_to_array('1|2|3', '|');
1303  string_to_array
1304 -----------------
1305  {1,2,3}
1306 (1 row)
1307
1308 select string_to_array('1|2|3|', '|');
1309  string_to_array
1310 -----------------
1311  {1,2,3,""}
1312 (1 row)
1313
1314 select string_to_array('1||2|3||', '||');
1315  string_to_array
1316 -----------------
1317  {1,2|3,""}
1318 (1 row)
1319
1320 select string_to_array('1|2|3', '');
1321  string_to_array
1322 -----------------
1323  {1|2|3}
1324 (1 row)
1325
1326 select string_to_array('', '|');
1327  string_to_array
1328 -----------------
1329  {}
1330 (1 row)
1331
1332 select string_to_array('1|2|3', NULL);
1333  string_to_array
1334 -----------------
1335  {1,|,2,|,3}
1336 (1 row)
1337
1338 select string_to_array(NULL, '|') IS NULL;
1339  ?column?
1340 ----------
1341  t
1342 (1 row)
1343
1344 select string_to_array('abc', '');
1345  string_to_array
1346 -----------------
1347  {abc}
1348 (1 row)
1349
1350 select string_to_array('abc', '', 'abc');
1351  string_to_array
1352 -----------------
1353  {NULL}
1354 (1 row)
1355
1356 select string_to_array('abc', ',');
1357  string_to_array
1358 -----------------
1359  {abc}
1360 (1 row)
1361
1362 select string_to_array('abc', ',', 'abc');
1363  string_to_array
1364 -----------------
1365  {NULL}
1366 (1 row)
1367
1368 select string_to_array('1,2,3,4,,6', ',');
1369  string_to_array
1370 -----------------
1371  {1,2,3,4,"",6}
1372 (1 row)
1373
1374 select string_to_array('1,2,3,4,,6', ',', '');
1375  string_to_array
1376 ------------------
1377  {1,2,3,4,NULL,6}
1378 (1 row)
1379
1380 select string_to_array('1,2,3,4,*,6', ',', '*');
1381  string_to_array
1382 ------------------
1383  {1,2,3,4,NULL,6}
1384 (1 row)
1385
1386 select array_to_string(NULL::int4[], ',') IS NULL;
1387  ?column?
1388 ----------
1389  t
1390 (1 row)
1391
1392 select array_to_string('{}'::int4[], ',');
1393  array_to_string
1394 -----------------
1395
1396 (1 row)
1397
1398 select array_to_string(array[1,2,3,4,NULL,6], ',');
1399  array_to_string
1400 -----------------
1401  1,2,3,4,6
1402 (1 row)
1403
1404 select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
1405  array_to_string
1406 -----------------
1407  1,2,3,4,*,6
1408 (1 row)
1409
1410 select array_to_string(array[1,2,3,4,NULL,6], NULL);
1411  array_to_string
1412 -----------------
1413
1414 (1 row)
1415
1416 select array_to_string(array[1,2,3,4,NULL,6], ',', NULL);
1417  array_to_string
1418 -----------------
1419  1,2,3,4,6
1420 (1 row)
1421
1422 select array_to_string(string_to_array('1|2|3', '|'), '|');
1423  array_to_string
1424 -----------------
1425  1|2|3
1426 (1 row)
1427
1428 select array_length(array[1,2,3], 1);
1429  array_length
1430 --------------
1431             3
1432 (1 row)
1433
1434 select array_length(array[[1,2,3], [4,5,6]], 0);
1435  array_length
1436 --------------
1437
1438 (1 row)
1439
1440 select array_length(array[[1,2,3], [4,5,6]], 1);
1441  array_length
1442 --------------
1443             2
1444 (1 row)
1445
1446 select array_length(array[[1,2,3], [4,5,6]], 2);
1447  array_length
1448 --------------
1449             3
1450 (1 row)
1451
1452 select array_length(array[[1,2,3], [4,5,6]], 3);
1453  array_length
1454 --------------
1455
1456 (1 row)
1457
1458 select cardinality(NULL::int[]);
1459  cardinality
1460 -------------
1461
1462 (1 row)
1463
1464 select cardinality('{}'::int[]);
1465  cardinality
1466 -------------
1467            0
1468 (1 row)
1469
1470 select cardinality(array[1,2,3]);
1471  cardinality
1472 -------------
1473            3
1474 (1 row)
1475
1476 select cardinality('[2:4]={5,6,7}'::int[]);
1477  cardinality
1478 -------------
1479            3
1480 (1 row)
1481
1482 select cardinality('{{1,2}}'::int[]);
1483  cardinality
1484 -------------
1485            2
1486 (1 row)
1487
1488 select cardinality('{{1,2},{3,4},{5,6}}'::int[]);
1489  cardinality
1490 -------------
1491            6
1492 (1 row)
1493
1494 select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]);
1495  cardinality
1496 -------------
1497            8
1498 (1 row)
1499
1500 select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
1501               array_agg
1502 --------------------------------------
1503  {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
1504 (1 row)
1505
1506 select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1507             array_agg
1508 ---------------------------------
1509  {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}
1510 (1 row)
1511
1512 select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1513                array_agg
1514 ---------------------------------------
1515  {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL}
1516 (1 row)
1517
1518 select array_agg(unique1) from tenk1 where unique1 < -15;
1519  array_agg
1520 -----------
1521
1522 (1 row)
1523
1524 select unnest(array[1,2,3]);
1525  unnest
1526 --------
1527       1
1528       2
1529       3
1530 (3 rows)
1531
1532 select * from unnest(array[1,2,3]);
1533  unnest
1534 --------
1535       1
1536       2
1537       3
1538 (3 rows)
1539
1540 select unnest(array[1,2,3,4.5]::float8[]);
1541  unnest
1542 --------
1543       1
1544       2
1545       3
1546     4.5
1547 (4 rows)
1548
1549 select unnest(array[1,2,3,4.5]::numeric[]);
1550  unnest
1551 --------
1552       1
1553       2
1554       3
1555     4.5
1556 (4 rows)
1557
1558 select unnest(array[1,2,3,null,4,null,null,5,6]);
1559  unnest
1560 --------
1561       1
1562       2
1563       3
1564
1565       4
1566
1567
1568       5
1569       6
1570 (9 rows)
1571
1572 select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
1573  unnest
1574 --------
1575  1
1576  2
1577  3
1578
1579  4
1580
1581
1582  5
1583  6
1584 (9 rows)
1585
1586 select abs(unnest(array[1,2,null,-3]));
1587  abs
1588 -----
1589    1
1590    2
1591
1592    3
1593 (4 rows)
1594
1595 select array_remove(array[1,2,2,3], 2);
1596  array_remove
1597 --------------
1598  {1,3}
1599 (1 row)
1600
1601 select array_remove(array[1,2,2,3], 5);
1602  array_remove
1603 --------------
1604  {1,2,2,3}
1605 (1 row)
1606
1607 select array_remove(array[1,NULL,NULL,3], NULL);
1608  array_remove
1609 --------------
1610  {1,3}
1611 (1 row)
1612
1613 select array_remove(array['A','CC','D','C','RR'], 'RR');
1614  array_remove
1615 --------------
1616  {A,CC,D,C}
1617 (1 row)
1618
1619 select array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed
1620 ERROR:  removing elements from multidimensional arrays is not supported
1621 select array_remove(array['X','X','X'], 'X') = '{}';
1622  ?column?
1623 ----------
1624  t
1625 (1 row)
1626
1627 select array_replace(array[1,2,5,4],5,3);
1628  array_replace
1629 ---------------
1630  {1,2,3,4}
1631 (1 row)
1632
1633 select array_replace(array[1,2,5,4],5,NULL);
1634  array_replace
1635 ---------------
1636  {1,2,NULL,4}
1637 (1 row)
1638
1639 select array_replace(array[1,2,NULL,4,NULL],NULL,5);
1640  array_replace
1641 ---------------
1642  {1,2,5,4,5}
1643 (1 row)
1644
1645 select array_replace(array['A','B','DD','B'],'B','CC');
1646  array_replace
1647 ---------------
1648  {A,CC,DD,CC}
1649 (1 row)
1650
1651 select array_replace(array[1,NULL,3],NULL,NULL);
1652  array_replace
1653 ---------------
1654  {1,NULL,3}
1655 (1 row)
1656
1657 select array_replace(array['AB',NULL,'CDE'],NULL,'12');
1658  array_replace
1659 ---------------
1660  {AB,12,CDE}
1661 (1 row)
1662
1663 -- Insert/update on a column that is array of composite
1664 create temp table t1 (f1 int8_tbl[]);
1665 insert into t1 (f1[5].q1) values(42);
1666 select * from t1;
1667        f1
1668 -----------------
1669  [5:5]={"(42,)"}
1670 (1 row)
1671
1672 update t1 set f1[5].q2 = 43;
1673 select * from t1;
1674         f1
1675 -------------------
1676  [5:5]={"(42,43)"}
1677 (1 row)
1678
1679 -- Check that arrays of composites are safely detoasted when needed
1680 create temp table src (f1 text);
1681 insert into src
1682   select string_agg(random()::text,'') from generate_series(1,10000);
1683 create type textandtext as (c1 text, c2 text);
1684 create temp table dest (f1 textandtext[]);
1685 insert into dest select array[row(f1,f1)::textandtext] from src;
1686 select length(md5((f1[1]).c2)) from dest;
1687  length
1688 --------
1689      32
1690 (1 row)
1691
1692 delete from src;
1693 select length(md5((f1[1]).c2)) from dest;
1694  length
1695 --------
1696      32
1697 (1 row)
1698
1699 truncate table src;
1700 drop table src;
1701 select length(md5((f1[1]).c2)) from dest;
1702  length
1703 --------
1704      32
1705 (1 row)
1706
1707 drop table dest;
1708 drop type textandtext;
1709 -- Tests for polymorphic-array form of width_bucket()
1710 -- this exercises the varwidth and float8 code paths
1711 SELECT
1712     op,
1713     width_bucket(op::numeric, ARRAY[1, 3, 5, 10.0]::numeric[]) AS wb_n1,
1714     width_bucket(op::numeric, ARRAY[0, 5.5, 9.99]::numeric[]) AS wb_n2,
1715     width_bucket(op::numeric, ARRAY[-6, -5, 2.0]::numeric[]) AS wb_n3,
1716     width_bucket(op::float8, ARRAY[1, 3, 5, 10.0]::float8[]) AS wb_f1,
1717     width_bucket(op::float8, ARRAY[0, 5.5, 9.99]::float8[]) AS wb_f2,
1718     width_bucket(op::float8, ARRAY[-6, -5, 2.0]::float8[]) AS wb_f3
1719 FROM (VALUES
1720   (-5.2),
1721   (-0.0000000001),
1722   (0.000000000001),
1723   (1),
1724   (1.99999999999999),
1725   (2),
1726   (2.00000000000001),
1727   (3),
1728   (4),
1729   (4.5),
1730   (5),
1731   (5.5),
1732   (6),
1733   (7),
1734   (8),
1735   (9),
1736   (9.99999999999999),
1737   (10),
1738   (10.0000000000001)
1739 ) v(op);
1740         op        | wb_n1 | wb_n2 | wb_n3 | wb_f1 | wb_f2 | wb_f3
1741 ------------------+-------+-------+-------+-------+-------+-------
1742              -5.2 |     0 |     0 |     1 |     0 |     0 |     1
1743     -0.0000000001 |     0 |     0 |     2 |     0 |     0 |     2
1744    0.000000000001 |     0 |     1 |     2 |     0 |     1 |     2
1745                 1 |     1 |     1 |     2 |     1 |     1 |     2
1746  1.99999999999999 |     1 |     1 |     2 |     1 |     1 |     2
1747                 2 |     1 |     1 |     3 |     1 |     1 |     3
1748  2.00000000000001 |     1 |     1 |     3 |     1 |     1 |     3
1749                 3 |     2 |     1 |     3 |     2 |     1 |     3
1750                 4 |     2 |     1 |     3 |     2 |     1 |     3
1751               4.5 |     2 |     1 |     3 |     2 |     1 |     3
1752                 5 |     3 |     1 |     3 |     3 |     1 |     3
1753               5.5 |     3 |     2 |     3 |     3 |     2 |     3
1754                 6 |     3 |     2 |     3 |     3 |     2 |     3
1755                 7 |     3 |     2 |     3 |     3 |     2 |     3
1756                 8 |     3 |     2 |     3 |     3 |     2 |     3
1757                 9 |     3 |     2 |     3 |     3 |     2 |     3
1758  9.99999999999999 |     3 |     3 |     3 |     3 |     3 |     3
1759                10 |     4 |     3 |     3 |     4 |     3 |     3
1760  10.0000000000001 |     4 |     3 |     3 |     4 |     3 |     3
1761 (19 rows)
1762
1763 -- ensure float8 path handles NaN properly
1764 SELECT
1765     op,
1766     width_bucket(op, ARRAY[1, 3, 9, 'NaN', 'NaN']::float8[]) AS wb
1767 FROM (VALUES
1768   (-5.2::float8),
1769   (4::float8),
1770   (77::float8),
1771   ('NaN'::float8)
1772 ) v(op);
1773   op  | wb
1774 ------+----
1775  -5.2 |  0
1776     4 |  2
1777    77 |  3
1778   NaN |  5
1779 (4 rows)
1780
1781 -- these exercise the generic fixed-width code path
1782 SELECT
1783     op,
1784     width_bucket(op, ARRAY[1, 3, 5, 10]) AS wb_1
1785 FROM generate_series(0,11) as op;
1786  op | wb_1
1787 ----+------
1788   0 |    0
1789   1 |    1
1790   2 |    1
1791   3 |    2
1792   4 |    2
1793   5 |    3
1794   6 |    3
1795   7 |    3
1796   8 |    3
1797   9 |    3
1798  10 |    4
1799  11 |    4
1800 (12 rows)
1801
1802 SELECT width_bucket(now(),
1803                     array['yesterday', 'today', 'tomorrow']::timestamptz[]);
1804  width_bucket
1805 --------------
1806             2
1807 (1 row)
1808
1809 -- corner cases
1810 SELECT width_bucket(5, ARRAY[3]);
1811  width_bucket
1812 --------------
1813             1
1814 (1 row)
1815
1816 SELECT width_bucket(5, '{}');
1817  width_bucket
1818 --------------
1819             0
1820 (1 row)
1821
1822 -- error cases
1823 SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]);
1824 ERROR:  function width_bucket(text, integer[]) does not exist
1825 LINE 1: SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]);
1826                ^
1827 HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
1828 SELECT width_bucket(5, ARRAY[3, 4, NULL]);
1829 ERROR:  thresholds array must not contain NULLs
1830 SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]);
1831 ERROR:  thresholds must be one-dimensional array