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 DETAIL:  Key (f1)=({1,2,3}) already exists.
 717 insert into arr_tbl values ('{2,3,4}');
 718 insert into arr_tbl values ('{1,5,3}');
 719 insert into arr_tbl values ('{1,2,10}');
 720 set enable_seqscan to off;
 721 set enable_bitmapscan to off;
 722 select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}';
 723     f1
 724 ----------
 725  {1,2,10}
 726  {1,5,3}
 727 (2 rows)
 728
 729 select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}';
 730     f1
 731 ----------
 732  {1,2,3}
 733  {1,2,10}
 734 (2 rows)
 735
 736 -- note: if above selects don't produce the expected tuple order,
 737 -- then you didn't get an indexscan plan, and something is busted.
 738 reset enable_seqscan;
 739 reset enable_bitmapscan;
 740 -- test [not] (like|ilike) (any|all) (...)
 741 select 'foo' like any (array['%a', '%o']); -- t
 742  ?column?
 743 ----------
 744  t
 745 (1 row)
 746
 747 select 'foo' like any (array['%a', '%b']); -- f
 748  ?column?
 749 ----------
 750  f
 751 (1 row)
 752
 753 select 'foo' like all (array['f%', '%o']); -- t
 754  ?column?
 755 ----------
 756  t
 757 (1 row)
 758
 759 select 'foo' like all (array['f%', '%b']); -- f
 760  ?column?
 761 ----------
 762  f
 763 (1 row)
 764
 765 select 'foo' not like any (array['%a', '%b']); -- t
 766  ?column?
 767 ----------
 768  t
 769 (1 row)
 770
 771 select 'foo' not like all (array['%a', '%o']); -- f
 772  ?column?
 773 ----------
 774  f
 775 (1 row)
 776
 777 select 'foo' ilike any (array['%A', '%O']); -- t
 778  ?column?
 779 ----------
 780  t
 781 (1 row)
 782
 783 select 'foo' ilike all (array['F%', '%O']); -- t
 784  ?column?
 785 ----------
 786  t
 787 (1 row)
 788
 789 --
 790 -- General array parser tests
 791 --
 792 -- none of the following should be accepted
 793 select '{{1,{2}},{2,3}}'::text[];
 794 ERROR:  malformed array literal: "{{1,{2}},{2,3}}"
 795 LINE 1: select '{{1,{2}},{2,3}}'::text[];
 796                ^
 797 select '{{},{}}'::text[];
 798 ERROR:  malformed array literal: "{{},{}}"
 799 LINE 1: select '{{},{}}'::text[];
 800                ^
 801 select E'{{1,2},\\{2,3}}'::text[];
 802 ERROR:  malformed array literal: "{{1,2},\{2,3}}"
 803 LINE 1: select E'{{1,2},\\{2,3}}'::text[];
 804                ^
 805 select '{{"1 2" x},{3}}'::text[];
 806 ERROR:  malformed array literal: "{{"1 2" x},{3}}"
 807 LINE 1: select '{{"1 2" x},{3}}'::text[];
 808                ^
 809 select '{}}'::text[];
 810 ERROR:  malformed array literal: "{}}"
 811 LINE 1: select '{}}'::text[];
 812                ^
 813 select '{ }}'::text[];
 814 ERROR:  malformed array literal: "{ }}"
 815 LINE 1: select '{ }}'::text[];
 816                ^
 817 select array[];
 818 ERROR:  cannot determine type of empty array
 819 LINE 1: select array[];
 820                ^
 821 HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].
 822 -- none of the above should be accepted
 823 -- all of the following should be accepted
 824 select '{}'::text[];
 825  text
 826 ------
 827  {}
 828 (1 row)
 829
 830 select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[];
 831                      text
 832 -----------------------------------------------
 833  {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}
 834 (1 row)
 835
 836 select '{0 second  ,0 second}'::interval[];
 837    interval
 838 ---------------
 839  {"@ 0","@ 0"}
 840 (1 row)
 841
 842 select '{ { "," } , { 3 } }'::text[];
 843     text
 844 -------------
 845  {{","},{3}}
 846 (1 row)
 847
 848 select '  {   {  "  0 second  "   ,  0 second  }   }'::text[];
 849              text
 850 -------------------------------
 851  {{"  0 second  ","0 second"}}
 852 (1 row)
 853
 854 select '{
 855            0 second,
 856            @ 1 hour @ 42 minutes @ 20 seconds
 857          }'::interval[];
 858               interval
 859 ------------------------------------
 860  {"@ 0","@ 1 hour 42 mins 20 secs"}
 861 (1 row)
 862
 863 select array[]::text[];
 864  array
 865 -------
 866  {}
 867 (1 row)
 868
 869 select '[0:1]={1.1,2.2}'::float8[];
 870      float8
 871 -----------------
 872  [0:1]={1.1,2.2}
 873 (1 row)
 874
 875 -- all of the above should be accepted
 876 -- tests for array aggregates
 877 CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]);
 878 INSERT INTO arraggtest (f1, f2, f3) VALUES
 879 ('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}');
 880 INSERT INTO arraggtest (f1, f2, f3) VALUES
 881 ('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}');
 882 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 883     max    |   min   |           max            |           min            |   max   |  min
 884 -----------+---------+--------------------------+--------------------------+---------+-------
 885  {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6}
 886 (1 row)
 887
 888 INSERT INTO arraggtest (f1, f2, f3) VALUES
 889 ('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}');
 890 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 891       max      |   min   |              max               |           min            |          max          |  min
 892 ---------------+---------+--------------------------------+--------------------------+-----------------------+-------
 893  {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}
 894 (1 row)
 895
 896 INSERT INTO arraggtest (f1, f2, f3) VALUES
 897 ('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}');
 898 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 899       max      |   min   |              max               |             min              |          max          |  min
 900 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 901  {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}
 902 (1 row)
 903
 904 INSERT INTO arraggtest (f1, f2, f3) VALUES
 905 ('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL);
 906 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 907       max      |   min   |              max               |             min              |          max          |  min
 908 ---------------+---------+--------------------------------+------------------------------+-----------------------+-------
 909  {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}
 910 (1 row)
 911
 912 INSERT INTO arraggtest (f1, f2, f3) VALUES
 913 ('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}');
 914 SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest;
 915       max      | min |              max               |             min              |          max          |  min
 916 ---------------+-----+--------------------------------+------------------------------+-----------------------+-------
 917  {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}
 918 (1 row)
 919
 920 -- A few simple tests for arrays of composite types
 921 create type comptype as (f1 int, f2 text);
 922 create table comptable (c1 comptype, c2 comptype[]);
 923 -- XXX would like to not have to specify row() construct types here ...
 924 insert into comptable
 925   values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]);
 926 -- check that implicitly named array type _comptype isn't a problem
 927 create type _comptype as enum('fooey');
 928 select * from comptable;
 929    c1    |          c2
 930 ---------+-----------------------
 931  (1,foo) | {"(2,bar)","(3,baz)"}
 932 (1 row)
 933
 934 select c2[2].f2 from comptable;
 935  f2
 936 -----
 937  baz
 938 (1 row)
 939
 940 drop type _comptype;
 941 drop table comptable;
 942 drop type comptype;
 943 create or replace function unnest1(anyarray)
 944 returns setof anyelement as $$
 945 select $1[s] from generate_subscripts($1,1) g(s);
 946 $$ language sql immutable;
 947 create or replace function unnest2(anyarray)
 948 returns setof anyelement as $$
 949 select $1[s1][s2] from generate_subscripts($1,1) g1(s1),
 950                    generate_subscripts($1,2) g2(s2);
 951 $$ language sql immutable;
 952 select * from unnest1(array[1,2,3]);
 953  unnest1
 954 ---------
 955        1
 956        2
 957        3
 958 (3 rows)
 959
 960 select * from unnest2(array[[1,2,3],[4,5,6]]);
 961  unnest2
 962 ---------
 963        1
 964        2
 965        3
 966        4
 967        5
 968        6
 969 (6 rows)
 970
 971 drop function unnest1(anyarray);
 972 drop function unnest2(anyarray);
 973 select array_fill(null::integer, array[3,3],array[2,2]);
 974                            array_fill
 975 -----------------------------------------------------------------
 976  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 977 (1 row)
 978
 979 select array_fill(null::integer, array[3,3]);
 980                       array_fill
 981 ------------------------------------------------------
 982  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 983 (1 row)
 984
 985 select array_fill(null::text, array[3,3],array[2,2]);
 986                            array_fill
 987 -----------------------------------------------------------------
 988  [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 989 (1 row)
 990
 991 select array_fill(null::text, array[3,3]);
 992                       array_fill
 993 ------------------------------------------------------
 994  {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}}
 995 (1 row)
 996
 997 select array_fill(7, array[3,3],array[2,2]);
 998               array_fill
 999 --------------------------------------
1000  [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}}
1001 (1 row)
1002
1003 select array_fill(7, array[3,3]);
1004         array_fill
1005 ---------------------------
1006  {{7,7,7},{7,7,7},{7,7,7}}
1007 (1 row)
1008
1009 select array_fill('juhu'::text, array[3,3],array[2,2]);
1010                            array_fill
1011 -----------------------------------------------------------------
1012  [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1013 (1 row)
1014
1015 select array_fill('juhu'::text, array[3,3]);
1016                       array_fill
1017 ------------------------------------------------------
1018  {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}}
1019 (1 row)
1020
1021 -- raise exception
1022 select array_fill(1, null, array[2,2]);
1023 ERROR:  dimension array or low bound array cannot be NULL
1024 select array_fill(1, array[2,2], null);
1025 ERROR:  dimension array or low bound array cannot be NULL
1026 select array_fill(1, array[3,3], array[1,1,1]);
1027 ERROR:  wrong number of array subscripts
1028 DETAIL:  Low bound array has different size than dimensions array.
1029 select array_fill(1, array[1,2,null]);
1030 ERROR:  dimension values cannot be null
1031 select string_to_array('1|2|3', '|');
1032  string_to_array
1033 -----------------
1034  {1,2,3}
1035 (1 row)
1036
1037 select string_to_array('1|2|3|', '|');
1038  string_to_array
1039 -----------------
1040  {1,2,3,""}
1041 (1 row)
1042
1043 select string_to_array('1||2|3||', '||');
1044  string_to_array
1045 -----------------
1046  {1,2|3,""}
1047 (1 row)
1048
1049 select string_to_array('1|2|3', '');
1050  string_to_array
1051 -----------------
1052  {1|2|3}
1053 (1 row)
1054
1055 select string_to_array('', '|');
1056  string_to_array
1057 -----------------
1058  {}
1059 (1 row)
1060
1061 select string_to_array('1|2|3', NULL);
1062  string_to_array
1063 -----------------
1064  {1,|,2,|,3}
1065 (1 row)
1066
1067 select string_to_array(NULL, '|') IS NULL;
1068  ?column?
1069 ----------
1070  t
1071 (1 row)
1072
1073 select string_to_array('abc', '');
1074  string_to_array
1075 -----------------
1076  {abc}
1077 (1 row)
1078
1079 select string_to_array('abc', '', 'abc');
1080  string_to_array
1081 -----------------
1082  {NULL}
1083 (1 row)
1084
1085 select string_to_array('abc', ',');
1086  string_to_array
1087 -----------------
1088  {abc}
1089 (1 row)
1090
1091 select string_to_array('abc', ',', 'abc');
1092  string_to_array
1093 -----------------
1094  {NULL}
1095 (1 row)
1096
1097 select string_to_array('1,2,3,4,,6', ',');
1098  string_to_array
1099 -----------------
1100  {1,2,3,4,"",6}
1101 (1 row)
1102
1103 select string_to_array('1,2,3,4,,6', ',', '');
1104  string_to_array
1105 ------------------
1106  {1,2,3,4,NULL,6}
1107 (1 row)
1108
1109 select string_to_array('1,2,3,4,*,6', ',', '*');
1110  string_to_array
1111 ------------------
1112  {1,2,3,4,NULL,6}
1113 (1 row)
1114
1115 select array_to_string(NULL::int4[], ',') IS NULL;
1116  ?column?
1117 ----------
1118  t
1119 (1 row)
1120
1121 select array_to_string('{}'::int4[], ',');
1122  array_to_string
1123 -----------------
1124
1125 (1 row)
1126
1127 select array_to_string(array[1,2,3,4,NULL,6], ',');
1128  array_to_string
1129 -----------------
1130  1,2,3,4,6
1131 (1 row)
1132
1133 select array_to_string(array[1,2,3,4,NULL,6], ',', '*');
1134  array_to_string
1135 -----------------
1136  1,2,3,4,*,6
1137 (1 row)
1138
1139 select array_to_string(array[1,2,3,4,NULL,6], NULL);
1140  array_to_string
1141 -----------------
1142
1143 (1 row)
1144
1145 select array_to_string(array[1,2,3,4,NULL,6], ',', NULL);
1146  array_to_string
1147 -----------------
1148  1,2,3,4,6
1149 (1 row)
1150
1151 select array_to_string(string_to_array('1|2|3', '|'), '|');
1152  array_to_string
1153 -----------------
1154  1|2|3
1155 (1 row)
1156
1157 select array_length(array[1,2,3], 1);
1158  array_length
1159 --------------
1160             3
1161 (1 row)
1162
1163 select array_length(array[[1,2,3], [4,5,6]], 0);
1164  array_length
1165 --------------
1166
1167 (1 row)
1168
1169 select array_length(array[[1,2,3], [4,5,6]], 1);
1170  array_length
1171 --------------
1172             2
1173 (1 row)
1174
1175 select array_length(array[[1,2,3], [4,5,6]], 2);
1176  array_length
1177 --------------
1178             3
1179 (1 row)
1180
1181 select array_length(array[[1,2,3], [4,5,6]], 3);
1182  array_length
1183 --------------
1184
1185 (1 row)
1186
1187 select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss;
1188               array_agg
1189 --------------------------------------
1190  {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14}
1191 (1 row)
1192
1193 select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1194             array_agg
1195 ---------------------------------
1196  {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4}
1197 (1 row)
1198
1199 select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss;
1200                array_agg
1201 ---------------------------------------
1202  {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL}
1203 (1 row)
1204
1205 select array_agg(unique1) from tenk1 where unique1 < -15;
1206  array_agg
1207 -----------
1208
1209 (1 row)
1210
1211 select unnest(array[1,2,3]);
1212  unnest
1213 --------
1214       1
1215       2
1216       3
1217 (3 rows)
1218
1219 select * from unnest(array[1,2,3]);
1220  unnest
1221 --------
1222       1
1223       2
1224       3
1225 (3 rows)
1226
1227 select unnest(array[1,2,3,4.5]::float8[]);
1228  unnest
1229 --------
1230       1
1231       2
1232       3
1233     4.5
1234 (4 rows)
1235
1236 select unnest(array[1,2,3,4.5]::numeric[]);
1237  unnest
1238 --------
1239       1
1240       2
1241       3
1242     4.5
1243 (4 rows)
1244
1245 select unnest(array[1,2,3,null,4,null,null,5,6]);
1246  unnest
1247 --------
1248       1
1249       2
1250       3
1251
1252       4
1253
1254
1255       5
1256       6
1257 (9 rows)
1258
1259 select unnest(array[1,2,3,null,4,null,null,5,6]::text[]);
1260  unnest
1261 --------
1262  1
1263  2
1264  3
1265
1266  4
1267
1268
1269  5
1270  6
1271 (9 rows)
1272
1273 -- Insert/update on a column that is array of composite
1274 create temp table t1 (f1 int8_tbl[]);
1275 insert into t1 (f1[5].q1) values(42);
1276 select * from t1;
1277        f1
1278 -----------------
1279  [5:5]={"(42,)"}
1280 (1 row)
1281
1282 update t1 set f1[5].q2 = 43;
1283 select * from t1;
1284         f1
1285 -------------------
1286  [5:5]={"(42,43)"}
1287 (1 row)
1288