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 (3 rows)
 503
 504 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno;
 505  seqno |           i           |                                                                     t
 506 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 507     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 508     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 509     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 510     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 511 (4 rows)
 512
 513 SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno;
 514  seqno |           i           |                                                                     t
 515 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 516     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 517     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 518     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 519     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 520 (4 rows)
 521
 522 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno;
 523  seqno |        i         |                                 t
 524 -------+------------------+--------------------------------------------------------------------
 525     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 526     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 527     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 528 (3 rows)
 529
 530 SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno;
 531  seqno |        i         |                                 t
 532 -------+------------------+--------------------------------------------------------------------
 533     15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309}
 534     79 | {45}             | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 535     96 | {23,97,43}       | {AAAAAAAAAA646,A87088}
 536 (3 rows)
 537
 538 SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 539  seqno |  i   |                                 t
 540 -------+------+--------------------------------------------------------------------
 541     79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 542 (1 row)
 543
 544 SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno;
 545  seqno |           i           |                                                                     t
 546 -------+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------
 547     15 | {17,14,16,63,67}      | {AA6416,AAAAAAAAAA646,AAAAA95309}
 548     22 | {11,6,56,62,53,30}    | {AAAAAAAA72908}
 549     45 | {99,45}               | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 550     72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407}
 551     79 | {45}                  | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908}
 552     96 | {23,97,43}            | {AAAAAAAAAA646,A87088}
 553 (6 rows)
 554
 555 SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno;
 556  seqno |         i          |                                                     t
 557 -------+--------------------+-----------------------------------------------------------------------------------------------------------
 558     22 | {11,6,56,62,53,30} | {AAAAAAAA72908}
 559     45 | {99,45}            | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}
 560 (2 rows)
 561
 562 -- array casts
 563 SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}";
 564  {1,2,3}
 565 ---------
 566  {1,2,3}
 567 (1 row)
 568
 569 SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE";
 570  TRUE
 571 ------
 572  t
 573 (1 row)
 574
 575 SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}";
 576  {{a,bc},{def,hijk}}
 577 ---------------------
 578  {{a,bc},{def,hijk}}
 579 (1 row)
 580
 581 SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE";
 582  TRUE
 583 ------
 584  t
 585 (1 row)
 586
 587 SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}";
 588  {{{{{{a,bb,ccc}}}}}}
 589 ----------------------
 590  {{{{{{a,bb,ccc}}}}}}
 591 (1 row)
 592
 593 -- scalar op any/all (array)
 594 select 33 = any ('{1,2,3}');
 595  ?column?
 596 ----------
 597  f
 598 (1 row)
 599
 600 select 33 = any ('{1,2,33}');
 601  ?column?
 602 ----------
 603  t
 604 (1 row)
 605
 606 select 33 = all ('{1,2,33}');
 607  ?column?
 608 ----------
 609  f
 610 (1 row)
 611
 612 select 33 >= all ('{1,2,33}');
 613  ?column?
 614 ----------
 615  t
 616 (1 row)
 617
 618 -- boundary cases
 619 select null::int >= all ('{1,2,33}');
 620  ?column?
 621 ----------
 622
 623 (1 row)
 624
 625 select null::int >= all ('{}');
 626  ?column?
 627 ----------
 628  t
 629 (1 row)
 630
 631 select null::int >= any ('{}');
 632  ?column?
 633 ----------
 634  f
 635 (1 row)
 636
 637 -- cross-datatype
 638 select 33.4 = any (array[1,2,3]);
 639  ?column?
 640 ----------
 641  f
 642 (1 row)
 643
 644 select 33.4 > all (array[1,2,3]);
 645  ?column?
 646 ----------
 647  t
 648 (1 row)
 649
 650 -- errors
 651 select 33 * any ('{1,2,3}');
 652 ERROR:  op ANY/ALL (array) requires operator to yield boolean
 653 LINE 1: select 33 * any ('{1,2,3}');
 654                   ^
 655 select 33 * any (44);
 656 ERROR:  op ANY/ALL (array) requires array on right side
 657 LINE 1: select 33 * any (44);
 658                   ^
 659 -- nulls
 660 select 33 = any (null::int[]);
 661  ?column?
 662 ----------
 663
 664 (1 row)
 665
 666 select null::int = any ('{1,2,3}');
 667  ?column?
 668 ----------
 669
 670 (1 row)
 671
 672 select 33 = any ('{1,null,3}');
 673  ?column?
 674 ----------
 675
 676 (1 row)
 677
 678 select 33 = any ('{1,null,33}');
 679  ?column?
 680 ----------
 681  t
 682 (1 row)
 683
 684 select 33 = all (null::int[]);
 685  ?column?
 686 ----------
 687
 688 (1 row)
 689
 690 select null::int = all ('{1,2,3}');
 691  ?column?
 692 ----------
 693
 694 (1 row)
 695
 696 select 33 = all ('{1,null,3}');
 697  ?column?
 698 ----------
 699  f
 700 (1 row)
 701
 702 select 33 = all ('{33,null,33}');
 703  ?column?
 704 ----------
 705
 706 (1 row)
 707
 708 -- test indexes on arrays
 709 create temp table arr_tbl (f1 int[] unique);
 710 NOTICE:  CREATE TABLE / UNIQUE will create implicit index "arr_tbl_f1_key" for table "arr_tbl"
 711 insert into arr_tbl values ('{1,2,3}');
 712 insert into arr_tbl values ('{1,2}');
 713 -- failure expected:
 714 insert into arr_tbl values ('{1,2,3}');
 715 ERROR:  duplicate key value violates unique constraint "arr_tbl_f1_key"
 716 insert into arr_tbl values ('{2,3,4}');
 717 insert into arr_tbl values ('{1,5,3}');
 718 insert into arr_tbl values ('{1,2,10}');
 719 set enable_seqscan to off;
 720 set enable_bitmapscan to off;
 721 select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
 722     f1
 723 ----------
 724  {1,2,10}
 725  {1,5,3}
 726 (2 rows)
 727
 728 select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}';
 729     f1
 730 ----------
 731  {1,2,3}
 732  {1,2,10}
 733 (2 rows)
 734
 735 -- note: if above selects don't produce the expected tuple order,
 736 -- then you didn't get an indexscan plan, and something is busted.
 737 reset enable_seqscan;
 738 reset enable_bitmapscan;
 739 -- test [not] (like|ilike) (any|all) (...)
 740 select 'foo' like any (array['%a', '%o']); -- t
 741  ?column?
 742 ----------
 743  t
 744 (1 row)
 745
 746 select 'foo' like any (array['%a', '%b']); -- f
 747  ?column?
 748 ----------
 749  f
 750 (1 row)
 751
 752 select 'foo' like all (array['f%', '%o']); -- t
 753  ?column?
 754 ----------
 755  t
 756 (1 row)
 757
 758 select 'foo' like all (array['f%', '%b']); -- f
 759  ?column?
 760 ----------
 761  f
 762 (1 row)
 763
 764 select 'foo' not like any (array['%a', '%b']); -- t
 765  ?column?
 766 ----------
 767  t
 768 (1 row)
 769
 770 select 'foo' not like all (array['%a', '%o']); -- f
 771  ?column?
 772 ----------
 773  f
 774 (1 row)
 775
 776 select 'foo' ilike any (array['%A', '%O']); -- t
 777  ?column?
 778 ----------
 779  t
 780 (1 row)
 781
 782 select 'foo' ilike all (array['F%', '%O']); -- t
 783  ?column?
 784 ----------
 785  t
 786 (1 row)
 787
 788 --
 789 -- General array parser tests
 790 --
 791 -- none of the following should be accepted
 792 select '{{1,{2}},{2,3}}'::text[];
 793 ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
 794 LINE 1: select '{{1,{2}},{2,3}}'::text[];
 795                ^
 796 select '{{},{}}'::text[];
 797 ERROR:  malformed array literal: "{{},{}}"
 798 LINE 1: select '{{},{}}'::text[];
 799                ^
 800 select E'{{1,2},\\{2,3}}'::text[];
 801 ERROR:  malformed array literal: "{{1,2},\{2,3}}"
 802 LINE 1: select E'{{1,2},\\{2,3}}'::text[];
 803                ^
 804 select '{{"1 2" x},{3}}'::text[];
 805 ERROR:  malformed array literal: "{{"1 2" x},{3}}"
 806 LINE 1: select '{{"1 2" x},{3}}'::text[];
 807                ^
 808 select '{}}'::text[];
 809 ERROR:  malformed array literal: "{}}"
 810 LINE 1: select '{}}'::text[];
 811                ^
 812 select '{ }}'::text[];
 813 ERROR:  malformed array literal: "{ }}"
 814 LINE 1: select '{ }}'::text[];
 815                ^
 816 select array[];
 817 ERROR:  cannot determine type of empty array
 818 LINE 1: select array[];
 819                ^
 820 HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
 821 -- none of the above should be accepted
 822 -- all of the following should be accepted
 823 select '{}'::text[];
 824  text
 825 ------
 826  {}
 827 (1 row)
 828
 829 select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
 830                      text
 831 -----------------------------------------------
 832  {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
 833 (1 row)
 834
 835 select '{0 second  ,0 second}'::interval[];
 836    interval
 837 ---------------
 838  {"@ 0","@ 0"}
 839 (1 row)
 840
 841 select '{ { "," } , { 3 } }'::text[];
 842     text
 843 -------------
 844  {{","},{3}}
 845 (1 row)
 846
 847 select '  {   {  "  0 second  "   ,  0 second  }   }'::text[];
 848              text
 849 -------------------------------
 850  {{"  0 second  ","0 second"}}
 851 (1 row)
 852
 853 select '{
 854            0 second,
 855            @ 1 hour @ 42 minutes @ 20 seconds
 856          }'::interval[];
 857               interval
 858 ------------------------------------
 859  {"@ 0","@ 1 hour 42 mins 20 secs"}
 860 (1 row)
 861
 862 select array[]::text[];
 863  array
 864 -------
 865  {}
 866 (1 row)
 867
 868 select '[0:1]={1.1,2.2}'::float8[];
 869      float8
 870 -----------------
 871  [0:1]={1.1,2.2}
 872 (1 row)
 873
 874 -- all of the above should be accepted
 875 -- tests for array aggregates
 876 CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
 877 INSERT INTO arraggtest (f1, f2, f3) VALUES
 878 ('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
 879 INSERT INTO arraggtest (f1, f2, f3) VALUES
 880 ('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
 881 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 882     max    |   min   |           max            |           min            |   max   |  min
 883 -----------+---------+--------------------------+--------------------------+---------+-------
 884  {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
 885 (1 row)
 886
 887 INSERT INTO arraggtest (f1, f2, f3) VALUES
 888 ('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
 889 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 890       max      |   min   |              max               |           min            |          max          |  min
 891 ---------------+---------+--------------------------------+--------------------------+-----------------------+-------
 892  {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}
 893 (1 row)
 894
 895 INSERT INTO arraggtest (f1, f2, f3) VALUES
 896 ('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
 897 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 898       max      |   min   |              max               |             min              |          max          |  min
 899 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 900  {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}
 901 (1 row)
 902
 903 INSERT INTO arraggtest (f1, f2, f3) VALUES
 904 ('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
 905 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 906       max      |   min   |              max               |             min              |          max          |  min
 907 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 908  {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}
 909 (1 row)
 910
 911 INSERT INTO arraggtest (f1, f2, f3) VALUES
 912 ('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
 913 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 914       max      | min |              max               |             min              |          max          |  min
 915 ---------------+-----+--------------------------------+------------------------------+-----------------------+-------
 916  {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}
 917 (1 row)
 918
 919 -- A few simple tests for arrays of composite types
 920 create type comptype as (f1 int, f2 text);
 921 create table comptable (c1 comptype, c2 comptype[]);
 922 -- XXX would like to not have to specify row() construct types here ...
 923 insert into comptable
 924   values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
 925 -- check that implicitly named array type _comptype isn't a problem
 926 create type _comptype as enum('fooey');
 927 select * from comptable;
 928    c1    |          c2
 929 ---------+-----------------------
 930  (1,foo) | {"(2,bar)","(3,baz)"}
 931 (1 row)
 932
 933 select c2[2].f2 from comptable;
 934  f2
 935 -----
 936  baz
 937 (1 row)
 938
 939 drop type _comptype;
 940 drop table comptable;
 941 drop type comptype;
 942 create or replace function unnest1(anyarray)
 943 returns setof anyelement as $$
 944 select $1[s] from generate_subscripts($1,1) g(s);
 945 $$ language sql immutable;
 946 create or replace function unnest2(anyarray)
 947 returns setof anyelement as $$
 948 select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
 949                    generate_subscripts($1,2) g2(s2);
 950 $$ language sql immutable;
 951 select * from unnest1(array[1,2,3]);
 952  unnest1
 953 ---------
 954        1
 955        2
 956        3
 957 (3 rows)
 958
 959 select * from unnest2(array[[1,2,3],[4,5,6]]);
 960  unnest2
 961 ---------
 962        1
 963        2
 964        3
 965        4
 966        5
 967        6
 968 (6 rows)
 969
 970 drop function unnest1(anyarray);
 971 drop function unnest2(anyarray);
 972 select array_fill(null::integer, array[3,3],array[2,2]);
 973                            array_fill
 974 -----------------------------------------------------------------
 975  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 976 (1 row)
 977
 978 select array_fill(null::integer, array[3,3]);
 979                       array_fill
 980 ------------------------------------------------------
 981  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 982 (1 row)
 983
 984 select array_fill(null::text, array[3,3],array[2,2]);
 985                            array_fill
 986 -----------------------------------------------------------------
 987  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 988 (1 row)
 989
 990 select array_fill(null::text, array[3,3]);
 991                       array_fill
 992 ------------------------------------------------------
 993  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 994 (1 row)
 995
 996 select array_fill(7, array[3,3],array[2,2]);
 997               array_fill
 998 --------------------------------------
 999  [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}}
1000 (1 row)
1001
1002 select array_fill(7, array[3,3]);
1003         array_fill
1004 ---------------------------
1005  {{7,7,7},{7,7,7},{7,7,7}}
1006 (1 row)
1007
1008 select array_fill('juhu'::text, array[3,3],array[2,2]);
1009                            array_fill
1010 -----------------------------------------------------------------
1011  [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1012 (1 row)
1013
1014 select array_fill('juhu'::text, array[3,3]);
1015                       array_fill
1016 ------------------------------------------------------
1017  {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1018 (1 row)
1019
1020 -- raise exception
1021 select array_fill(1, null, array[2,2]);
1022 ERROR:  dimension array or low bound array cannot be NULL
1023 select array_fill(1, array[2,2], null);
1024 ERROR:  dimension array or low bound array cannot be NULL
1025 select array_fill(1, array[3,3], array[1,1,1]);
1026 ERROR:  wrong number of array_subscripts
1027 DETAIL:  Low bound array has different size than dimensions array.
1028 select array_fill(1, array[1,2,null]);
1029 ERROR:  dimension values cannot be null
1030 select string_to_array('1|2|3', '|');
1031  string_to_array
1032 -----------------
1033  {1,2,3}
1034 (1 row)
1035
1036 select string_to_array('1|2|3|', '|');
1037  string_to_array
1038 -----------------
1039  {1,2,3,""}
1040 (1 row)
1041
1042 select string_to_array('1||2|3||', '||');
1043  string_to_array
1044 -----------------
1045  {1,2|3,""}
1046 (1 row)
1047
1048 select string_to_array('1|2|3', '');
1049  string_to_array
1050 -----------------
1051  {1|2|3}
1052 (1 row)
1053
1054 select string_to_array('', '|');
1055  string_to_array
1056 -----------------
1057
1058 (1 row)
1059
1060 select string_to_array('1|2|3', NULL);
1061  string_to_array
1062 -----------------
1063
1064 (1 row)
1065
1066 select string_to_array(NULL, '|');
1067  string_to_array
1068 -----------------
1069
1070 (1 row)
1071
1072 select array_to_string(string_to_array('1|2|3', '|'), '|');
1073  array_to_string
1074 -----------------
1075  1|2|3
1076 (1 row)
1077
1078 select array_length(array[1,2,3], 1);
1079  array_length
1080 --------------
1081             3
1082 (1 row)
1083
1084 select array_length(array[[1,2,3], [4,5,6]], 0);
1085  array_length
1086 --------------
1087
1088 (1 row)
1089
1090 select array_length(array[[1,2,3], [4,5,6]], 1);
1091  array_length
1092 --------------
1093             2
1094 (1 row)
1095
1096 select array_length(array[[1,2,3], [4,5,6]], 2);
1097  array_length
1098 --------------
1099             3
1100 (1 row)
1101
1102 select array_length(array[[1,2,3], [4,5,6]], 3);
1103  array_length
1104 --------------
1105
1106 (1 row)
1107
1108 select cardinality(array[1,2,3]);
1109  cardinality
1110 -------------
1111            3
1112 (1 row)
1113
1114 select cardinality(array[[1,2,3], [4,5,6]]);
1115  cardinality
1116 -------------
1117            2
1118 (1 row)
1119
1120 select c, cardinality(c), d, cardinality(d) from arrtest;
1121          c         | cardinality |       d       | cardinality
1122 -------------------+-------------+---------------+-------------
1123  {}                |             | {}            |
1124  {foobar,new_word} |           2 | {{elt1,elt2}} |           1
1125  {foo,new_word}    |           2 | {bar,foo}     |           2
1126 (3 rows)
1127
1128 select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
1129               array_agg
1130 --------------------------------------
1131  {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
1132 (1 row)
1133
1134 select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1135             array_agg
1136 ---------------------------------
1137  {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}
1138 (1 row)
1139
1140 select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1141                array_agg
1142 ---------------------------------------
1143  {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL}
1144 (1 row)
1145
1146 select cardinality(array_agg(unique1)) from tenk1 where unique1 < 15;
1147  cardinality
1148 -------------
1149           15
1150 (1 row)
1151
1152 select array_agg(unique1) from tenk1 where unique1 < -15;
1153  array_agg
1154 -----------
1155
1156 (1 row)
1157
1158 select unnest(array[1,2,3]);
1159  unnest
1160 --------
1161       1
1162       2
1163       3
1164 (3 rows)
1165
1166 select * from unnest(array[1,2,3]);
1167  unnest
1168 --------
1169       1
1170       2
1171       3
1172 (3 rows)
1173
1174 select unnest(array[1,2,3,4.5]::float8[]);
1175  unnest
1176 --------
1177       1
1178       2
1179       3
1180     4.5
1181 (4 rows)
1182
1183 select unnest(array[1,2,3,4.5]::numeric[]);
1184  unnest
1185 --------
1186       1
1187       2
1188       3
1189     4.5
1190 (4 rows)
1191
1192 select unnest(array[1,2,3,null,4,null,null,5,6]);
1193  unnest
1194 --------
1195       1
1196       2
1197       3
1198
1199       4
1200
1201
1202       5
1203       6
1204 (9 rows)
1205
1206 select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
1207  unnest
1208 --------
1209  1
1210  2
1211  3
1212
1213  4
1214
1215
1216  5
1217  6
1218 (9 rows)
1219