1-- 2-- ARRAYS 3-- 4CREATE 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-- 16INSERT 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}}}', '{}', '{}', '{}', '{}'); 18UPDATE arrtest SET e[0] = '1.1'; 19UPDATE arrtest SET e[1] = '2.2'; 20INSERT INTO arrtest (f) 21 VALUES ('{"too long"}'); 22ERROR: value too long for type character(5) 23INSERT 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"}'); 27INSERT INTO arrtest (a, b[1:2], c, d[1:2]) 28 VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}'); 29INSERT INTO arrtest (b[2]) VALUES(now()); -- error, type mismatch 30ERROR: subscripted assignment to "b" requires type integer but expression is of type timestamp with time zone 31LINE 1: INSERT INTO arrtest (b[2]) VALUES(now()); 32 ^ 33HINT: You will need to rewrite or cast the expression. 34INSERT INTO arrtest (b[1:2]) VALUES(now()); -- error, type mismatch 35ERROR: subscripted assignment to "b" requires type integer[] but expression is of type timestamp with time zone 36LINE 1: INSERT INTO arrtest (b[1:2]) VALUES(now()); 37 ^ 38HINT: You will need to rewrite or cast the expression. 39SELECT * FROM arrtest; 40 a | b | c | d | e | f | g 41-------------+-----------------+-----------+---------------+-----------------+-----------------+------------- 42 {1,2,3,4,5} | {{{0,0},{1,2}}} | {} | {} | [0:1]={1.1,2.2} | {} | {} 43 {11,12,23} | {{3,4},{4,5}} | {foobar} | {{elt1,elt2}} | {3.4,6.7} | {"abc ",abcde} | {abc,abcde} 44 {} | {3,4} | {foo,bar} | {bar,foo} | | | 45(3 rows) 46 47SELECT arrtest.a[1], 48 arrtest.b[1][1][1], 49 arrtest.c[1], 50 arrtest.d[1][1], 51 arrtest.e[0] 52 FROM arrtest; 53 a | b | c | d | e 54----+---+--------+------+----- 55 1 | 0 | | | 1.1 56 11 | | foobar | elt1 | 57 | | foo | | 58(3 rows) 59 60SELECT a[1], b[1][1][1], c[1], d[1][1], e[0] 61 FROM arrtest; 62 a | b | c | d | e 63----+---+--------+------+----- 64 1 | 0 | | | 1.1 65 11 | | foobar | elt1 | 66 | | foo | | 67(3 rows) 68 69SELECT a[1:3], 70 b[1:1][1:2][1:2], 71 c[1:2], 72 d[1:1][1:2] 73 FROM arrtest; 74 a | b | c | d 75------------+-----------------+-----------+--------------- 76 {1,2,3} | {{{0,0},{1,2}}} | {} | {} 77 {11,12,23} | {} | {foobar} | {{elt1,elt2}} 78 {} | {} | {foo,bar} | {} 79(3 rows) 80 81SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c 82 FROM arrtest; 83 a | b | c 84---+---+--- 85 1 | 3 | 86 1 | 2 | 1 87 | 1 | 1 88(3 rows) 89 90SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c 91 FROM arrtest; 92 a | b | c 93-------+-----------------+------- 94 [1:5] | [1:1][1:2][1:2] | 95 [1:3] | [1:2][1:2] | [1:1] 96 | [1:2] | [1:2] 97(3 rows) 98 99-- returns nothing 100SELECT * 101 FROM arrtest 102 WHERE a[1] < 5 and 103 c = '{"foobar"}'::_name; 104 a | b | c | d | e | f | g 105---+---+---+---+---+---+--- 106(0 rows) 107 108UPDATE arrtest 109 SET a[1:2] = '{16,25}' 110 WHERE NOT a = '{}'::_int2; 111UPDATE arrtest 112 SET b[1:1][1:1][1:2] = '{113, 117}', 113 b[1:1][1:2][2:2] = '{142, 147}' 114 WHERE array_dims(b) = '[1:1][1:2][1:2]'; 115UPDATE arrtest 116 SET c[2:2] = '{"new_word"}' 117 WHERE array_dims(c) is not null; 118SELECT a,b,c FROM arrtest; 119 a | b | c 120---------------+-----------------------+------------------- 121 {16,25,3,4,5} | {{{113,142},{1,147}}} | {} 122 {} | {3,4} | {foo,new_word} 123 {16,25,23} | {{3,4},{4,5}} | {foobar,new_word} 124(3 rows) 125 126SELECT a[1:3], 127 b[1:1][1:2][1:2], 128 c[1:2], 129 d[1:1][2:2] 130 FROM arrtest; 131 a | b | c | d 132------------+-----------------------+-------------------+---------- 133 {16,25,3} | {{{113,142},{1,147}}} | {} | {} 134 {} | {} | {foo,new_word} | {} 135 {16,25,23} | {} | {foobar,new_word} | {{elt2}} 136(3 rows) 137 138SELECT b[1:1][2][2], 139 d[1:1][2] 140 FROM arrtest; 141 b | d 142-----------------------+--------------- 143 {{{113,142},{1,147}}} | {} 144 {} | {} 145 {} | {{elt1,elt2}} 146(3 rows) 147 148INSERT INTO arrtest(a) VALUES('{1,null,3}'); 149SELECT a FROM arrtest; 150 a 151--------------- 152 {16,25,3,4,5} 153 {} 154 {16,25,23} 155 {1,NULL,3} 156(4 rows) 157 158UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL; 159SELECT a FROM arrtest WHERE a[2] IS NULL; 160 a 161----------------- 162 [4:4]={NULL} 163 {1,NULL,3,NULL} 164(2 rows) 165 166DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL; 167SELECT a,b,c FROM arrtest; 168 a | b | c 169---------------+-----------------------+------------------- 170 {16,25,3,4,5} | {{{113,142},{1,147}}} | {} 171 {16,25,23} | {{3,4},{4,5}} | {foobar,new_word} 172 [4:4]={NULL} | {3,4} | {foo,new_word} 173(3 rows) 174 175-- test mixed slice/scalar subscripting 176select '{{1,2,3},{4,5,6},{7,8,9}}'::int[]; 177 int4 178--------------------------- 179 {{1,2,3},{4,5,6},{7,8,9}} 180(1 row) 181 182select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; 183 int4 184--------------- 185 {{1,2},{4,5}} 186(1 row) 187 188select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[]; 189 int4 190-------------------------------------- 191 [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} 192(1 row) 193 194select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; 195 int4 196--------------- 197 {{5,6},{8,9}} 198(1 row) 199 200-- 201-- check subscription corner cases 202-- 203-- More subscripts than MAXDIM (6) 204SELECT ('{}'::int[])[1][2][3][4][5][6][7]; 205ERROR: number of array dimensions (7) exceeds the maximum allowed (6) 206-- NULL index yields NULL when selecting 207SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1]; 208 int4 209------ 210 211(1 row) 212 213SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1]; 214 int4 215------ 216 217(1 row) 218 219SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1]; 220 int4 221------ 222 223(1 row) 224 225-- NULL index in assignment is an error 226UPDATE arrtest 227 SET c[NULL] = '{"can''t assign"}' 228 WHERE array_dims(c) is not null; 229ERROR: array subscript in assignment must not be null 230UPDATE arrtest 231 SET c[NULL:1] = '{"can''t assign"}' 232 WHERE array_dims(c) is not null; 233ERROR: array subscript in assignment must not be null 234UPDATE arrtest 235 SET c[1:NULL] = '{"can''t assign"}' 236 WHERE array_dims(c) is not null; 237ERROR: array subscript in assignment must not be null 238-- Un-subscriptable type 239SELECT (now())[1]; 240ERROR: cannot subscript type timestamp with time zone because it does not support subscripting 241LINE 1: SELECT (now())[1]; 242 ^ 243-- test slices with empty lower and/or upper index 244CREATE TEMP TABLE arrtest_s ( 245 a int2[], 246 b int2[][] 247); 248INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}'); 249INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}'); 250SELECT * FROM arrtest_s; 251 a | b 252-------------------+-------------------------------------- 253 {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} 254 [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} 255(2 rows) 256 257SELECT a[:3], b[:2][:2] FROM arrtest_s; 258 a | b 259-----------+--------------------------- 260 {1,2,3} | {{1,2},{4,5}} 261 {1,2,3,4} | {{1,2,3},{4,5,6},{7,8,9}} 262(2 rows) 263 264SELECT a[2:], b[2:][2:] FROM arrtest_s; 265 a | b 266-----------+--------------- 267 {2,3,4,5} | {{5,6},{8,9}} 268 {3,4,5} | {{9}} 269(2 rows) 270 271SELECT a[:], b[:] FROM arrtest_s; 272 a | b 273-------------+--------------------------- 274 {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} 275 {1,2,3,4,5} | {{1,2,3},{4,5,6},{7,8,9}} 276(2 rows) 277 278-- updates 279UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}' 280 WHERE array_lower(a,1) = 1; 281SELECT * FROM arrtest_s; 282 a | b 283-------------------+-------------------------------------- 284 [0:4]={1,2,3,4,5} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}} 285 {11,12,13,4,5} | {{11,12,3},{14,15,6},{7,8,9}} 286(2 rows) 287 288UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}'; 289SELECT * FROM arrtest_s; 290 a | b 291---------------------+--------------------------------------- 292 [0:4]={1,2,3,23,24} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}} 293 {11,12,23,24,25} | {{11,12,3},{14,25,26},{7,28,29}} 294(2 rows) 295 296UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; 297SELECT * FROM arrtest_s; 298 a | b 299------------------------+--------------------------------------- 300 [0:4]={11,12,13,14,15} | [0:2][0:2]={{1,2,3},{4,5,6},{7,8,25}} 301 {11,12,13,14,15} | {{11,12,3},{14,25,26},{7,28,29}} 302(2 rows) 303 304UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small 305ERROR: source array too small 306INSERT INTO arrtest_s VALUES(NULL, NULL); 307UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null 308ERROR: array slice subscript must provide both boundaries 309DETAIL: When assigning to a slice of an empty array value, slice boundaries must be fully specified. 310-- check with fixed-length-array type, such as point 311SELECT f1[0:1] FROM POINT_TBL; 312ERROR: slices of fixed-length arrays not implemented 313SELECT f1[0:] FROM POINT_TBL; 314ERROR: slices of fixed-length arrays not implemented 315SELECT f1[:1] FROM POINT_TBL; 316ERROR: slices of fixed-length arrays not implemented 317SELECT f1[:] FROM POINT_TBL; 318ERROR: slices of fixed-length arrays not implemented 319-- subscript assignments to fixed-width result in NULL if previous value is NULL 320UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *; 321 f1 322---- 323 324(1 row) 325 326INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *; 327 f1 328---- 329 330(1 row) 331 332-- NULL assignments get ignored 333UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *; 334 f1 335--------- 336 (10,10) 337(1 row) 338 339-- but non-NULL subscript assignments work 340UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *; 341 f1 342----------- 343 (-10,-10) 344(1 row) 345 346-- but not to expand the range 347UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *; 348ERROR: array subscript out of range 349-- 350-- test array extension 351-- 352CREATE TEMP TABLE arrtest1 (i int[], t text[]); 353insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); 354select * from arrtest1; 355 i | t 356--------------+--------------------- 357 {1,2,NULL,4} | {one,two,NULL,four} 358(1 row) 359 360update arrtest1 set i[2] = 22, t[2] = 'twenty-two'; 361select * from arrtest1; 362 i | t 363---------------+---------------------------- 364 {1,22,NULL,4} | {one,twenty-two,NULL,four} 365(1 row) 366 367update arrtest1 set i[5] = 5, t[5] = 'five'; 368select * from arrtest1; 369 i | t 370-----------------+--------------------------------- 371 {1,22,NULL,4,5} | {one,twenty-two,NULL,four,five} 372(1 row) 373 374update arrtest1 set i[8] = 8, t[8] = 'eight'; 375select * from arrtest1; 376 i | t 377-----------------------------+------------------------------------------------- 378 {1,22,NULL,4,5,NULL,NULL,8} | {one,twenty-two,NULL,four,five,NULL,NULL,eight} 379(1 row) 380 381update arrtest1 set i[0] = 0, t[0] = 'zero'; 382select * from arrtest1; 383 i | t 384-------------------------------------+------------------------------------------------------------ 385 [0:8]={0,1,22,NULL,4,5,NULL,NULL,8} | [0:8]={zero,one,twenty-two,NULL,four,five,NULL,NULL,eight} 386(1 row) 387 388update arrtest1 set i[-3] = -3, t[-3] = 'minus-three'; 389select * from arrtest1; 390 i | t 391---------------------------------------------------+----------------------------------------------------------------------------------- 392 [-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} 393(1 row) 394 395update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve']; 396select * from arrtest1; 397 i | t 398-----------------------------------------------------+--------------------------------------------------------------------------------- 399 [-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} 400(1 row) 401 402update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20']; 403select * from arrtest1; 404 i | t 405---------------------------------------------------------------+----------------------------------------------------------------------------------------- 406 [-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} 407(1 row) 408 409update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22']; 410select * from arrtest1; 411 i | t 412-----------------------------------------------------------------------+-------------------------------------------------------------------------------------------------- 413 [-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} 414(1 row) 415 416update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26']; 417select * from arrtest1; 418 i | t 419-----------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------- 420 [-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} 421(1 row) 422 423update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13']; 424select * from arrtest1; 425 i | t 426--------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------- 427 [-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} 428(1 row) 429 430update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null]; 431select * from arrtest1; 432 i | t 433-----------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------ 434 [-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} 435(1 row) 436 437update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20']; 438select * from arrtest1; 439 i | t 440-----------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------ 441 [-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} 442(1 row) 443 444delete from arrtest1; 445insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); 446select * from arrtest1; 447 i | t 448--------------+--------------------- 449 {1,2,NULL,4} | {one,two,NULL,four} 450(1 row) 451 452update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5']; 453select * from arrtest1; 454 i | t 455------------------------+---------------------------- 456 [0:5]={0,1,2,NULL,4,5} | [0:5]={z,p1,p2,NULL,p4,p5} 457(1 row) 458 459-- 460-- array expressions and operators 461-- 462-- table creation and INSERTs 463CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]); 464INSERT INTO arrtest2 VALUES( 465 ARRAY[[[113,142],[1,147]]], 466 ARRAY[1.1,1.2,1.3]::float8[], 467 ARRAY[1.1,1.2,1.3], 468 ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]], 469 ARRAY['19620326','19931223','19970117']::timestamp[] 470); 471-- some more test data 472CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8); 473insert into arrtest_f values(1,'cat1',1.21); 474insert into arrtest_f values(2,'cat1',1.24); 475insert into arrtest_f values(3,'cat1',1.18); 476insert into arrtest_f values(4,'cat1',1.26); 477insert into arrtest_f values(5,'cat1',1.15); 478insert into arrtest_f values(6,'cat2',1.15); 479insert into arrtest_f values(7,'cat2',1.26); 480insert into arrtest_f values(8,'cat2',1.32); 481insert into arrtest_f values(9,'cat2',1.30); 482CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int); 483insert into arrtest_i values(1,'cat1',21); 484insert into arrtest_i values(2,'cat1',24); 485insert into arrtest_i values(3,'cat1',18); 486insert into arrtest_i values(4,'cat1',26); 487insert into arrtest_i values(5,'cat1',15); 488insert into arrtest_i values(6,'cat2',15); 489insert into arrtest_i values(7,'cat2',26); 490insert into arrtest_i values(8,'cat2',32); 491insert into arrtest_i values(9,'cat2',30); 492-- expressions 493SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM ( 494 SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f 495) AS t; 496 131 | 221 497-----+----- 498 131 | 221 499(1 row) 500 501SELECT ARRAY[[[[[['hello'],['world']]]]]]; 502 array 503--------------------------- 504 {{{{{{hello},{world}}}}}} 505(1 row) 506 507SELECT ARRAY[ARRAY['hello'],ARRAY['world']]; 508 array 509------------------- 510 {{hello},{world}} 511(1 row) 512 513SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY"; 514 ARRAY 515----------------------------------------------- 516 {1.15,1.15,1.18,1.21,1.24,1.26,1.26,1.3,1.32} 517(1 row) 518 519-- with nulls 520SELECT '{1,null,3}'::int[]; 521 int4 522------------ 523 {1,NULL,3} 524(1 row) 525 526SELECT ARRAY[1,NULL,3]; 527 array 528------------ 529 {1,NULL,3} 530(1 row) 531 532-- functions 533SELECT array_append(array[42], 6) AS "{42,6}"; 534 {42,6} 535-------- 536 {42,6} 537(1 row) 538 539SELECT array_prepend(6, array[42]) AS "{6,42}"; 540 {6,42} 541-------- 542 {6,42} 543(1 row) 544 545SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; 546 {1,2,3,4} 547----------- 548 {1,2,3,4} 549(1 row) 550 551SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}"; 552 {{1,2},{3,4},{5,6}} 553--------------------- 554 {{1,2},{3,4},{5,6}} 555(1 row) 556 557SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}"; 558 {{3,4},{5,6},{1,2}} 559--------------------- 560 {{3,4},{5,6},{1,2}} 561(1 row) 562 563SELECT array_position(ARRAY[1,2,3,4,5], 4); 564 array_position 565---------------- 566 4 567(1 row) 568 569SELECT array_position(ARRAY[5,3,4,2,1], 4); 570 array_position 571---------------- 572 3 573(1 row) 574 575SELECT array_position(ARRAY[[1,2],[3,4]], 3); 576ERROR: searching for elements in multidimensional arrays is not supported 577SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); 578 array_position 579---------------- 580 2 581(1 row) 582 583SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat'); 584 array_position 585---------------- 586 7 587(1 row) 588 589SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL); 590 array_position 591---------------- 592 593(1 row) 594 595SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL); 596 array_position 597---------------- 598 6 599(1 row) 600 601SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat'); 602 array_position 603---------------- 604 8 605(1 row) 606 607SELECT array_positions(NULL, 10); 608 array_positions 609----------------- 610 611(1 row) 612 613SELECT array_positions(NULL, NULL::int); 614 array_positions 615----------------- 616 617(1 row) 618 619SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4); 620 array_positions 621----------------- 622 {4,10} 623(1 row) 624 625SELECT array_positions(ARRAY[[1,2],[3,4]], 4); 626ERROR: searching for elements in multidimensional arrays is not supported 627SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL); 628 array_positions 629----------------- 630 {} 631(1 row) 632 633SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL); 634 array_positions 635----------------- 636 {4,10} 637(1 row) 638 639SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10 640 FROM generate_series(1,100) g(i)), 641 'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1); 642 array_length 643-------------- 644 10 645(1 row) 646 647DO $$ 648DECLARE 649 o int; 650 a int[] := ARRAY[1,2,3,2,3,1,2]; 651BEGIN 652 o := array_position(a, 2); 653 WHILE o IS NOT NULL 654 LOOP 655 RAISE NOTICE '%', o; 656 o := array_position(a, 2, o + 1); 657 END LOOP; 658END 659$$ LANGUAGE plpgsql; 660NOTICE: 2 661NOTICE: 4 662NOTICE: 7 663SELECT array_position('[2:4]={1,2,3}'::int[], 1); 664 array_position 665---------------- 666 2 667(1 row) 668 669SELECT array_positions('[2:4]={1,2,3}'::int[], 1); 670 array_positions 671----------------- 672 {2} 673(1 row) 674 675SELECT 676 array_position(ids, (1, 1)), 677 array_positions(ids, (1, 1)) 678 FROM 679(VALUES 680 (ARRAY[(0, 0), (1, 1)]), 681 (ARRAY[(1, 1)]) 682) AS f (ids); 683 array_position | array_positions 684----------------+----------------- 685 2 | {2} 686 1 | {1} 687(2 rows) 688 689-- operators 690SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]]; 691 a 692--------------- 693 {16,25,3,4,5} 694(1 row) 695 696SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE"; 697 FALSE 698------- 699 f 700(1 row) 701 702SELECT ARRAY[1,2] || 3 AS "{1,2,3}"; 703 {1,2,3} 704--------- 705 {1,2,3} 706(1 row) 707 708SELECT 0 || ARRAY[1,2] AS "{0,1,2}"; 709 {0,1,2} 710--------- 711 {0,1,2} 712(1 row) 713 714SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}"; 715 {1,2,3,4} 716----------- 717 {1,2,3,4} 718(1 row) 719 720SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY"; 721 ARRAY 722-------------------------------------- 723 {{{hello,world}},{{happy,birthday}}} 724(1 row) 725 726SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}"; 727 {{1,2},{3,4},{5,6}} 728--------------------- 729 {{1,2},{3,4},{5,6}} 730(1 row) 731 732SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; 733 {0,0,1,1,2,2} 734--------------- 735 {0,0,1,1,2,2} 736(1 row) 737 738SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; 739 {0,1,2,3} 740----------- 741 {0,1,2,3} 742(1 row) 743 744SELECT ARRAY[1.1] || ARRAY[2,3,4]; 745 ?column? 746------------- 747 {1.1,2,3,4} 748(1 row) 749 750SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; 751 seqno | i | t 752-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 753 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 754 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 755 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 756 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 757 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 758 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 759(6 rows) 760 761SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno; 762 seqno | i | t 763-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 764 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 765 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 766 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 767 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 768 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 769 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 770(6 rows) 771 772SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno; 773 seqno | i | t 774-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 775 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 776 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 777 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 778 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 779 53 | {38,17} | {AAAAAAAAAAA21658} 780 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 781 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 782 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 783(8 rows) 784 785SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno; 786 seqno | i | t 787-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 788 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 789 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 790 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 791 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 792 53 | {38,17} | {AAAAAAAAAAA21658} 793 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 794 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 795 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 796(8 rows) 797 798SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno; 799 seqno | i | t 800-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 801 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 802 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 803 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 804(3 rows) 805 806SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno; 807 seqno | i | t 808-------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------ 809 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 810 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 811 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 812 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 813 53 | {38,17} | {AAAAAAAAAAA21658} 814 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 815 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 816 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 817 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 818 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 819 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 820(11 rows) 821 822SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 823 seqno | i | t 824-------+---------------+---------------------------------------------------------------------------------------------------------------------------- 825 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 826 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 827 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 828 101 | {} | {} 829(4 rows) 830 831SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno; 832 seqno | i | t 833-------+----+---- 834 101 | {} | {} 835(1 row) 836 837SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno; 838 seqno | i | t 839-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 840 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} 841 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} 842 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} 843 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} 844 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} 845 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 846 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} 847 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} 848 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} 849 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} 850 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} 851 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 852 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} 853 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} 854 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 855 16 | {14,63,85,11} | {AAAAAA66777} 856 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} 857 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} 858 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 859 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} 860 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} 861 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 862 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} 863 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} 864 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} 865 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} 866 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} 867 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} 868 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} 869 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 870 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} 871 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} 872 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} 873 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} 874 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} 875 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} 876 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} 877 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} 878 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} 879 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 880 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} 881 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} 882 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} 883 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} 884 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 885 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} 886 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} 887 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} 888 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} 889 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} 890 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} 891 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} 892 53 | {38,17} | {AAAAAAAAAAA21658} 893 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} 894 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} 895 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} 896 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} 897 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} 898 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} 899 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} 900 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} 901 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} 902 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} 903 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 904 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 905 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} 906 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} 907 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} 908 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} 909 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} 910 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} 911 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 912 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} 913 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 914 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} 915 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} 916 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 917 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} 918 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 919 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} 920 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} 921 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 922 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} 923 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} 924 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} 925 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} 926 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} 927 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 928 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 929 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} 930 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} 931 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} 932 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} 933 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} 934 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} 935 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 936 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 937 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 938 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} 939 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 940 101 | {} | {} 941 102 | {NULL} | {NULL} 942(102 rows) 943 944SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno; 945 seqno | i | t 946-------+---+--- 947(0 rows) 948 949SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno; 950 seqno | i | t 951-------+----+---- 952 101 | {} | {} 953(1 row) 954 955SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 956 seqno | i | t 957-------+--------+-------- 958 102 | {NULL} | {NULL} 959(1 row) 960 961SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 962 seqno | i | t 963-------+---+--- 964(0 rows) 965 966SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 967 seqno | i | t 968-------+---+--- 969(0 rows) 970 971SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 972 seqno | i | t 973-------+----+---- 974 101 | {} | {} 975(1 row) 976 977SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 978 seqno | i | t 979-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 980 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 981 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 982 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 983 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 984(4 rows) 985 986SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 987 seqno | i | t 988-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 989 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 990 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 991 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 992 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 993(4 rows) 994 995SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 996 seqno | i | t 997-------+------------------+-------------------------------------------------------------------- 998 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 999 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 1000 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 1001(3 rows) 1002 1003SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 1004 seqno | i | t 1005-------+------------------+-------------------------------------------------------------------- 1006 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1007 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 1008 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 1009(3 rows) 1010 1011SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 1012 seqno | i | t 1013-------+------+-------------------------------------------------------------------- 1014 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 1015(1 row) 1016 1017SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 1018 seqno | i | t 1019-------+-----------------------+-------------------------------------------------------------------------------------------------------------------------------------------- 1020 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1021 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 1022 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 1023 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 1024 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 1025 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 1026(6 rows) 1027 1028SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 1029 seqno | i | t 1030-------+--------------------+----------------------------------------------------------------------------------------------------------- 1031 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 1032 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 1033 101 | {} | {} 1034(3 rows) 1035 1036SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno; 1037 seqno | i | t 1038-------+----+---- 1039 101 | {} | {} 1040(1 row) 1041 1042SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno; 1043 seqno | i | t 1044-------+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1045 1 | {92,75,71,52,64,83} | {AAAAAAAA44066,AAAAAA1059,AAAAAAAAAAA176,AAAAAAA48038} 1046 2 | {3,6} | {AAAAAA98232,AAAAAAAA79710,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAAAAAAA55798,AAAAAAAAA12793} 1047 3 | {37,64,95,43,3,41,13,30,11,43} | {AAAAAAAAAA48845,AAAAA75968,AAAAA95309,AAA54451,AAAAAAAAAA22292,AAAAAAA99836,A96617,AA17009,AAAAAAAAAAAAAA95246} 1048 4 | {71,39,99,55,33,75,45} | {AAAAAAAAA53663,AAAAAAAAAAAAAAA67062,AAAAAAAAAA64777,AAA99043,AAAAAAAAAAAAAAAAAAA91804,39557} 1049 5 | {50,42,77,50,4} | {AAAAAAAAAAAAAAAAA26540,AAAAAAA79710,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA176,AAAAA95309,AAAAAAAAAAA46154,AAAAAA66777,AAAAAAAAA27249,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA70104} 1050 6 | {39,35,5,94,17,92,60,32} | {AAAAAAAAAAAAAAA35875,AAAAAAAAAAAAAAAA23657} 1051 7 | {12,51,88,64,8} | {AAAAAAAAAAAAAAAAAA12591,AAAAAAAAAAAAAAAAA50407,AAAAAAAAAAAA67946} 1052 8 | {60,84} | {AAAAAAA81898,AAAAAA1059,AAAAAAAAAAAA81511,AAAAA961,AAAAAAAAAAAAAAAA31334,AAAAA64741,AA6416,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAA50407} 1053 9 | {56,52,35,27,80,44,81,22} | {AAAAAAAAAAAAAAA73034,AAAAAAAAAAAAA7929,AAAAAAA66161,AA88409,39557,A27153,AAAAAAAA9523,AAAAAAAAAAA99000} 1054 10 | {71,5,45} | {AAAAAAAAAAA21658,AAAAAAAAAAAA21089,AAA54451,AAAAAAAAAAAAAAAAAA54141,AAAAAAAAAAAAAA28620,AAAAAAAAAAA21658,AAAAAAAAAAA74076,AAAAAAAAA27249} 1055 11 | {41,86,74,48,22,74,47,50} | {AAAAAAAA9523,AAAAAAAAAAAA37562,AAAAAAAAAAAAAAAA14047,AAAAAAAAAAA46154,AAAA41702,AAAAAAAAAAAAAAAAA764,AAAAA62737,39557} 1056 12 | {17,99,18,52,91,72,0,43,96,23} | {AAAAA33250,AAAAAAAAAAAAAAAAAAA85420,AAAAAAAAAAA33576} 1057 13 | {3,52,34,23} | {AAAAAA98232,AAAA49534,AAAAAAAAAAA21658} 1058 14 | {78,57,19} | {AAAA8857,AAAAAAAAAAAAAAA73034,AAAAAAAA81587,AAAAAAAAAAAAAAA68526,AAAAA75968,AAAAAAAAAAAAAA65909,AAAAAAAAA10012,AAAAAAAAAAAAAA65909} 1059 15 | {17,14,16,63,67} | {AA6416,AAAAAAAAAA646,AAAAA95309} 1060 16 | {14,63,85,11} | {AAAAAA66777} 1061 17 | {7,10,81,85} | {AAAAAA43678,AAAAAAA12144,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAAAAA15356} 1062 18 | {1} | {AAAAAAAAAAA33576,AAAAA95309,64261,AAA59323,AAAAAAAAAAAAAA95246,55847,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAAAA64374} 1063 19 | {52,82,17,74,23,46,69,51,75} | {AAAAAAAAAAAAA73084,AAAAA75968,AAAAAAAAAAAAAAAA14047,AAAAAAA80240,AAAAAAAAAAAAAAAAAAA1205,A68938} 1064 20 | {72,89,70,51,54,37,8,49,79} | {AAAAAA58494} 1065 21 | {2,8,65,10,5,79,43} | {AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAAAAA91804,AAAAA64669,AAAAAAAAAAAAAAAA1443,AAAAAAAAAAAAAAAA23657,AAAAA12179,AAAAAAAAAAAAAAAAA88852,AAAAAAAAAAAAAAAA31334,AAAAAAAAAAAAAAAA41303,AAAAAAAAAAAAAAAAAAA85420} 1066 22 | {11,6,56,62,53,30} | {AAAAAAAA72908} 1067 23 | {40,90,5,38,72,40,30,10,43,55} | {A6053,AAAAAAAAAAA6119,AA44673,AAAAAAAAAAAAAAAAA764,AA17009,AAAAA17383,AAAAA70514,AAAAA33250,AAAAA95309,AAAAAAAAAAAA37562} 1068 24 | {94,61,99,35,48} | {AAAAAAAAAAA50956,AAAAAAAAAAA15165,AAAA85070,AAAAAAAAAAAAAAA36627,AAAAA961,AAAAAAAAAA55219} 1069 25 | {31,1,10,11,27,79,38} | {AAAAAAAAAAAAAAAAAA59334,45449} 1070 26 | {71,10,9,69,75} | {47735,AAAAAAA21462,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA91804,AAAAAAAAA72121,AAAAAAAAAAAAAAAAAAA1205,AAAAA41597,AAAA8857,AAAAAAAAAAAAAAAAAAA15356,AA17009} 1071 27 | {94} | {AA6416,A6053,AAAAAAA21462,AAAAAAA57334,AAAAAAAAAAAAAAAAAA12591,AA88409,AAAAAAAAAAAAA70254} 1072 28 | {14,33,6,34,14} | {AAAAAAAAAAAAAAA13198,AAAAAAAA69452,AAAAAAAAAAA82945,AAAAAAA12144,AAAAAAAAA72121,AAAAAAAAAA18601} 1073 29 | {39,21} | {AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAAAAA38885,AAAA85070,AAAAAAAAAAAAAAAAAAA70104,AAAAA66674,AAAAAAAAAAAAA62007,AAAAAAAA69452,AAAAAAA1242,AAAAAAAAAAAAAAAA1729,AAAA35194} 1074 30 | {26,81,47,91,34} | {AAAAAAAAAAAAAAAAAAA70104,AAAAAAA80240} 1075 31 | {80,24,18,21,54} | {AAAAAAAAAAAAAAA13198,AAAAAAAAAAAAAAAAAAA70415,A27153,AAAAAAAAA53663,AAAAAAAAAAAAAAAAA50407,A68938} 1076 32 | {58,79,82,80,67,75,98,10,41} | {AAAAAAAAAAAAAAAAAA61286,AAA54451,AAAAAAAAAAAAAAAAAAA87527,A96617,51533} 1077 33 | {74,73} | {A85417,AAAAAAA56483,AAAAA17383,AAAAAAAAAAAAA62159,AAAAAAAAAAAA52814,AAAAAAAAAAAAA85723,AAAAAAAAAAAAAAAAAA55796} 1078 34 | {70,45} | {AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAA28620,AAAAAAAAAA55219,AAAAAAAA23648,AAAAAAAAAA22292,AAAAAAA1242} 1079 35 | {23,40} | {AAAAAAAAAAAA52814,AAAA48949,AAAAAAAAA34727,AAAA8857,AAAAAAAAAAAAAAAAAAA62179,AAAAAAAAAAAAAAA68526,AAAAAAA99836,AAAAAAAA50094,AAAA91194,AAAAAAAAAAAAA73084} 1080 36 | {79,82,14,52,30,5,79} | {AAAAAAAAA53663,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA89194,AA88409,AAAAAAAAAAAAAAA81326,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAA33598} 1081 37 | {53,11,81,39,3,78,58,64,74} | {AAAAAAAAAAAAAAAAAAA17075,AAAAAAA66161,AAAAAAAA23648,AAAAAAAAAAAAAA10611} 1082 38 | {59,5,4,95,28} | {AAAAAAAAAAA82945,A96617,47735,AAAAA12179,AAAAA64669,AAAAAA99807,AA74433,AAAAAAAAAAAAAAAAA59387} 1083 39 | {82,43,99,16,74} | {AAAAAAAAAAAAAAA67062,AAAAAAA57334,AAAAAAAAAAAAAA65909,A27153,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAA64777,AAAAAAAAAAAA81511,AAAAAAAAAAAAAA65909,AAAAAAAAAAAAAA28620} 1084 40 | {34} | {AAAAAAAAAAAAAA10611,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAA50956,AAAAAAAAAAAAAAAA31334,AAAAA70466,AAAAAAAA81587,AAAAAAA74623} 1085 41 | {19,26,63,12,93,73,27,94} | {AAAAAAA79710,AAAAAAAAAA55219,AAAA41702,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA71621,AAAAAAAAAAAAAAAAA63050,AAAAAAA99836,AAAAAAAAAAAAAA8666} 1086 42 | {15,76,82,75,8,91} | {AAAAAAAAAAA176,AAAAAA38063,45449,AAAAAA54032,AAAAAAA81898,AA6416,AAAAAAAAAAAAAAAAAAA62179,45449,AAAAA60038,AAAAAAAA81587} 1087 43 | {39,87,91,97,79,28} | {AAAAAAAAAAA74076,A96617,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAAAAA55796,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAA67946} 1088 44 | {40,58,68,29,54} | {AAAAAAA81898,AAAAAA66777,AAAAAA98232} 1089 45 | {99,45} | {AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611} 1090 46 | {53,24} | {AAAAAAAAAAA53908,AAAAAA54032,AAAAA17383,AAAA48949,AAAAAAAAAA18601,AAAAA64669,45449,AAAAAAAAAAA98051,AAAAAAAAAAAAAAAAAA71621} 1091 47 | {98,23,64,12,75,61} | {AAA59323,AAAAA95309,AAAAAAAAAAAAAAAA31334,AAAAAAAAA27249,AAAAA17383,AAAAAAAAAAAA37562,AAAAAA1059,A84822,55847,AAAAA70466} 1092 48 | {76,14} | {AAAAAAAAAAAAA59671,AAAAAAAAAAAAAAAAAAA91804,AAAAAA66777,AAAAAAAAAAAAAAAAAAA89194,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAA73084,AAAAAAA79710,AAAAAAAAAAAAAAA40402,AAAAAAAAAAAAAAAAAAA65037} 1093 49 | {56,5,54,37,49} | {AA21643,AAAAAAAAAAA92631,AAAAAAAA81587} 1094 50 | {20,12,37,64,93} | {AAAAAAAAAA5483,AAAAAAAAAAAAAAAAAAA1205,AA6416,AAAAAAAAAAAAAAAAA63050,AAAAAAAAAAAAAAAAAA47955} 1095 51 | {47} | {AAAAAAAAAAAAAA96505,AAAAAAAAAAAAAAAAAA36842,AAAAA95309,AAAAAAAA81587,AA6416,AAAA91194,AAAAAA58494,AAAAAA1059,AAAAAAAA69452} 1096 52 | {89,0} | {AAAAAAAAAAAAAAAAAA47955,AAAAAAA48038,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAA73084,AAAAA70466,AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA46154,AA66862} 1097 53 | {38,17} | {AAAAAAAAAAA21658} 1098 54 | {70,47} | {AAAAAAAAAAAAAAAAAA54141,AAAAA40681,AAAAAAA48038,AAAAAAAAAAAAAAAA29150,AAAAA41597,AAAAAAAAAAAAAAAAAA59334,AA15322} 1099 55 | {47,79,47,64,72,25,71,24,93} | {AAAAAAAAAAAAAAAAAA55796,AAAAA62737} 1100 56 | {33,7,60,54,93,90,77,85,39} | {AAAAAAAAAAAAAAAAAA32918,AA42406} 1101 57 | {23,45,10,42,36,21,9,96} | {AAAAAAAAAAAAAAAAAAA70415} 1102 58 | {92} | {AAAAAAAAAAAAAAAA98414,AAAAAAAA23648,AAAAAAAAAAAAAAAAAA55796,AA25381,AAAAAAAAAAA6119} 1103 59 | {9,69,46,77} | {39557,AAAAAAA89932,AAAAAAAAAAAAAAAAA43052,AAAAAAAAAAAAAAAAA26540,AAA20874,AA6416,AAAAAAAAAAAAAAAAAA47955} 1104 60 | {62,2,59,38,89} | {AAAAAAA89932,AAAAAAAAAAAAAAAAAAA15356,AA99927,AA17009,AAAAAAAAAAAAAAA35875} 1105 61 | {72,2,44,95,54,54,13} | {AAAAAAAAAAAAAAAAAAA91804} 1106 62 | {83,72,29,73} | {AAAAAAAAAAAAA15097,AAAA8857,AAAAAAAAAAAA35809,AAAAAAAAAAAA52814,AAAAAAAAAAAAAAAAAAA38885,AAAAAAAAAAAAAAAAAA24183,AAAAAA43678,A96617} 1107 63 | {11,4,61,87} | {AAAAAAAAA27249,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAA13198,AAA20874,39557,51533,AAAAAAAAAAA53908,AAAAAAAAAAAAAA96505,AAAAAAAA78938} 1108 64 | {26,19,34,24,81,78} | {A96617,AAAAAAAAAAAAAAAAAAA70104,A68938,AAAAAAAAAAA53908,AAAAAAAAAAAAAAA453,AA17009,AAAAAAA80240} 1109 65 | {61,5,76,59,17} | {AAAAAA99807,AAAAA64741,AAAAAAAAAAA53908,AA21643,AAAAAAAAA10012} 1110 66 | {31,23,70,52,4,33,48,25} | {AAAAAAAAAAAAAAAAA69675,AAAAAAAA50094,AAAAAAAAAAA92631,AAAA35194,39557,AAAAAAA99836} 1111 67 | {31,94,7,10} | {AAAAAA38063,A96617,AAAA35194,AAAAAAAAAAAA67946} 1112 68 | {90,43,38} | {AA75092,AAAAAAAAAAAAAAAAA69675,AAAAAAAAAAA92631,AAAAAAAAA10012,AAAAAAAAAAAAA7929,AA21643} 1113 69 | {67,35,99,85,72,86,44} | {AAAAAAAAAAAAAAAAAAA1205,AAAAAAAA50094,AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAAAAAAA47955} 1114 70 | {56,70,83} | {AAAA41702,AAAAAAAAAAA82945,AA21643,AAAAAAAAAAA99000,A27153,AA25381,AAAAAAAAAAAAAA96505,AAAAAAA1242} 1115 71 | {74,26} | {AAAAAAAAAAA50956,AA74433,AAAAAAA21462,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAA36627,AAAAAAAAAAAAA70254,AAAAAAAAAA43419,39557} 1116 72 | {22,1,16,78,20,91,83} | {47735,AAAAAAA56483,AAAAAAAAAAAAA93788,AA42406,AAAAAAAAAAAAA73084,AAAAAAAA72908,AAAAAAAAAAAAAAAAAA61286,AAAAA66674,AAAAAAAAAAAAAAAAA50407} 1117 73 | {88,25,96,78,65,15,29,19} | {AAA54451,AAAAAAAAA27249,AAAAAAA9228,AAAAAAAAAAAAAAA67062,AAAAAAAAAAAAAAAAAAA70415,AAAAA17383,AAAAAAAAAAAAAAAA33598} 1118 74 | {32} | {AAAAAAAAAAAAAAAA1729,AAAAAAAAAAAAA22860,AAAAAA99807,AAAAA17383,AAAAAAAAAAAAAAA67062,AAAAAAAAAAA15165,AAAAAAAAAAA50956} 1119 75 | {12,96,83,24,71,89,55} | {AAAA48949,AAAAAAAA29716,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA67946,AAAAAAAAAAAAAAAA29150,AAA28075,AAAAAAAAAAAAAAAAA43052} 1120 76 | {92,55,10,7} | {AAAAAAAAAAAAAAA67062} 1121 77 | {97,15,32,17,55,59,18,37,50,39} | {AAAAAAAAAAAA67946,AAAAAA54032,AAAAAAAA81587,55847,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAAAAA43052,AAAAAA75463,AAAA49534,AAAAAAAA44066} 1122 78 | {55,89,44,84,34} | {AAAAAAAAAAA6119,AAAAAAAAAAAAAA8666,AA99927,AA42406,AAAAAAA81898,AAAAAAA9228,AAAAAAAAAAA92631,AA21643,AAAAAAAAAAAAAA28620} 1123 79 | {45} | {AAAAAAAAAA646,AAAAAAAAAAAAAAAAAAA70415,AAAAAA43678,AAAAAAAA72908} 1124 80 | {74,89,44,80,0} | {AAAA35194,AAAAAAAA79710,AAA20874,AAAAAAAAAAAAAAAAAAA70104,AAAAAAAAAAAAA73084,AAAAAAA57334,AAAAAAA9228,AAAAAAAAAAAAA62007} 1125 81 | {63,77,54,48,61,53,97} | {AAAAAAAAAAAAAAA81326,AAAAAAAAAA22292,AA25381,AAAAAAAAAAA74076,AAAAAAA81898,AAAAAAAAA72121} 1126 82 | {34,60,4,79,78,16,86,89,42,50} | {AAAAA40681,AAAAAAAAAAAAAAAAAA12591,AAAAAAA80240,AAAAAAAAAAAAAAAA55798,AAAAAAAAAAAAAAAAAAA70104} 1127 83 | {14,10} | {AAAAAAAAAA22292,AAAAAAAAAAAAA70254,AAAAAAAAAAA6119} 1128 84 | {11,83,35,13,96,94} | {AAAAA95309,AAAAAAAAAAAAAAAAAA32918,AAAAAAAAAAAAAAAAAA24183} 1129 85 | {39,60} | {AAAAAAAAAAAAAAAA55798,AAAAAAAAAA22292,AAAAAAA66161,AAAAAAA21462,AAAAAAAAAAAAAAAAAA12591,55847,AAAAAA98232,AAAAAAAAAAA46154} 1130 86 | {33,81,72,74,45,36,82} | {AAAAAAAA81587,AAAAAAAAAAAAAA96505,45449,AAAA80176} 1131 87 | {57,27,50,12,97,68} | {AAAAAAAAAAAAAAAAA26540,AAAAAAAAA10012,AAAAAAAAAAAA35809,AAAAAAAAAAAAAAAA29150,AAAAAAAAAAA82945,AAAAAA66777,31228,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAA28620,AAAAAAAAAAAAAA96505} 1132 88 | {41,90,77,24,6,24} | {AAAA35194,AAAA35194,AAAAAAA80240,AAAAAAAAAAA46154,AAAAAA58494,AAAAAAAAAAAAAAAAAAA17075,AAAAAAAAAAAAAAAAAA59334,AAAAAAAAAAAAAAAAAAA91804,AA74433} 1133 89 | {40,32,17,6,30,88} | {AA44673,AAAAAAAAAAA6119,AAAAAAAAAAAAAAAA23657,AAAAAAAAAAAAAAAAAA47955,AAAAAAAAAAAAAAAA33598,AAAAAAAAAAA33576,AA44673} 1134 90 | {88,75} | {AAAAA60038,AAAAAAAA23648,AAAAAAAAAAA99000,AAAA41702,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAA68526} 1135 91 | {78} | {AAAAAAAAAAAAA62007,AAA99043} 1136 92 | {85,63,49,45} | {AAAAAAA89932,AAAAAAAAAAAAA22860,AAAAAAAAAAAAAAAAAAA1205,AAAAAAAAAAAA21089} 1137 93 | {11} | {AAAAAAAAAAA176,AAAAAAAAAAAAAA8666,AAAAAAAAAAAAAAA453,AAAAAAAAAAAAA85723,A68938,AAAAAAAAAAAAA9821,AAAAAAA48038,AAAAAAAAAAAAAAAAA59387,AA99927,AAAAA17383} 1138 94 | {98,9,85,62,88,91,60,61,38,86} | {AAAAAAAA81587,AAAAA17383,AAAAAAAA81587} 1139 95 | {47,77} | {AAAAAAAAAAAAAAAAA764,AAAAAAAAAAA74076,AAAAAAAAAA18107,AAAAA40681,AAAAAAAAAAAAAAA35875,AAAAA60038,AAAAAAA56483} 1140 96 | {23,97,43} | {AAAAAAAAAA646,A87088} 1141 97 | {54,2,86,65} | {47735,AAAAAAA99836,AAAAAAAAAAAAAAAAA6897,AAAAAAAAAAAAAAAA29150,AAAAAAA80240,AAAAAAAAAAAAAAAA98414,AAAAAAA56483,AAAAAAAAAAAAAAAA29150,AAAAAAA39692,AA21643} 1142 98 | {38,34,32,89} | {AAAAAAAAAAAAAAAAAA71621,AAAA8857,AAAAAAAAAAAAAAAAAAA65037,AAAAAAAAAAAAAAAA31334,AAAAAAAAAA48845} 1143 99 | {37,86} | {AAAAAAAAAAAAAAAAAA32918,AAAAA70514,AAAAAAAAA10012,AAAAAAAAAAAAAAAAA59387,AAAAAAAAAA64777,AAAAAAAAAAAAAAAAAAA15356} 1144 100 | {85,32,57,39,49,84,32,3,30} | {AAAAAAA80240,AAAAAAAAAAAAAAAA1729,AAAAA60038,AAAAAAAAAAA92631,AAAAAAAA9523} 1145 101 | {} | {} 1146 102 | {NULL} | {NULL} 1147(102 rows) 1148 1149SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno; 1150 seqno | i | t 1151-------+---+--- 1152(0 rows) 1153 1154SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno; 1155 seqno | i | t 1156-------+----+---- 1157 101 | {} | {} 1158(1 row) 1159 1160-- array casts 1161SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}"; 1162 {1,2,3} 1163--------- 1164 {1,2,3} 1165(1 row) 1166 1167SELECT pg_typeof(ARRAY[1,2,3]::text[]::int[]::float8[]) AS "double precision[]"; 1168 double precision[] 1169-------------------- 1170 double precision[] 1171(1 row) 1172 1173SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}"; 1174 {{a,bc},{def,hijk}} 1175--------------------- 1176 {{a,bc},{def,hijk}} 1177(1 row) 1178 1179SELECT pg_typeof(ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[]) AS "character varying[]"; 1180 character varying[] 1181--------------------- 1182 character varying[] 1183(1 row) 1184 1185SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}"; 1186 {{{{{{a,bb,ccc}}}}}} 1187---------------------- 1188 {{{{{{a,bb,ccc}}}}}} 1189(1 row) 1190 1191SELECT NULL::text[]::int[] AS "NULL"; 1192 NULL 1193------ 1194 1195(1 row) 1196 1197-- scalar op any/all (array) 1198select 33 = any ('{1,2,3}'); 1199 ?column? 1200---------- 1201 f 1202(1 row) 1203 1204select 33 = any ('{1,2,33}'); 1205 ?column? 1206---------- 1207 t 1208(1 row) 1209 1210select 33 = all ('{1,2,33}'); 1211 ?column? 1212---------- 1213 f 1214(1 row) 1215 1216select 33 >= all ('{1,2,33}'); 1217 ?column? 1218---------- 1219 t 1220(1 row) 1221 1222-- boundary cases 1223select null::int >= all ('{1,2,33}'); 1224 ?column? 1225---------- 1226 1227(1 row) 1228 1229select null::int >= all ('{}'); 1230 ?column? 1231---------- 1232 t 1233(1 row) 1234 1235select null::int >= any ('{}'); 1236 ?column? 1237---------- 1238 f 1239(1 row) 1240 1241-- cross-datatype 1242select 33.4 = any (array[1,2,3]); 1243 ?column? 1244---------- 1245 f 1246(1 row) 1247 1248select 33.4 > all (array[1,2,3]); 1249 ?column? 1250---------- 1251 t 1252(1 row) 1253 1254-- errors 1255select 33 * any ('{1,2,3}'); 1256ERROR: op ANY/ALL (array) requires operator to yield boolean 1257LINE 1: select 33 * any ('{1,2,3}'); 1258 ^ 1259select 33 * any (44); 1260ERROR: op ANY/ALL (array) requires array on right side 1261LINE 1: select 33 * any (44); 1262 ^ 1263-- nulls 1264select 33 = any (null::int[]); 1265 ?column? 1266---------- 1267 1268(1 row) 1269 1270select null::int = any ('{1,2,3}'); 1271 ?column? 1272---------- 1273 1274(1 row) 1275 1276select 33 = any ('{1,null,3}'); 1277 ?column? 1278---------- 1279 1280(1 row) 1281 1282select 33 = any ('{1,null,33}'); 1283 ?column? 1284---------- 1285 t 1286(1 row) 1287 1288select 33 = all (null::int[]); 1289 ?column? 1290---------- 1291 1292(1 row) 1293 1294select null::int = all ('{1,2,3}'); 1295 ?column? 1296---------- 1297 1298(1 row) 1299 1300select 33 = all ('{1,null,3}'); 1301 ?column? 1302---------- 1303 f 1304(1 row) 1305 1306select 33 = all ('{33,null,33}'); 1307 ?column? 1308---------- 1309 1310(1 row) 1311 1312-- nulls later in the bitmap 1313SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i))); 1314 ?column? 1315---------- 1316 1317(1 row) 1318 1319-- test indexes on arrays 1320create temp table arr_tbl (f1 int[] unique); 1321insert into arr_tbl values ('{1,2,3}'); 1322insert into arr_tbl values ('{1,2}'); 1323-- failure expected: 1324insert into arr_tbl values ('{1,2,3}'); 1325ERROR: duplicate key value violates unique constraint "arr_tbl_f1_key" 1326DETAIL: Key (f1)=({1,2,3}) already exists. 1327insert into arr_tbl values ('{2,3,4}'); 1328insert into arr_tbl values ('{1,5,3}'); 1329insert into arr_tbl values ('{1,2,10}'); 1330set enable_seqscan to off; 1331set enable_bitmapscan to off; 1332select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}'; 1333 f1 1334---------- 1335 {1,2,10} 1336 {1,5,3} 1337(2 rows) 1338 1339select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}'; 1340 f1 1341---------- 1342 {1,2,3} 1343 {1,2,10} 1344(2 rows) 1345 1346-- test ON CONFLICT DO UPDATE with arrays 1347create temp table arr_pk_tbl (pk int4 primary key, f1 int[]); 1348insert into arr_pk_tbl values (1, '{1,2,3}'); 1349insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk) 1350 do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3] 1351 returning pk, f1; 1352 pk | f1 1353----+--------- 1354 1 | {3,2,5} 1355(1 row) 1356 1357insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk) 1358 do update set f1[1] = excluded.f1[1], 1359 f1[2] = excluded.f1[2], 1360 f1[3] = excluded.f1[3] 1361 returning pk, f1; 1362 pk | f1 1363----+------------ 1364 1 | {6,7,NULL} 1365(1 row) 1366 1367-- note: if above selects don't produce the expected tuple order, 1368-- then you didn't get an indexscan plan, and something is busted. 1369reset enable_seqscan; 1370reset enable_bitmapscan; 1371-- test [not] (like|ilike) (any|all) (...) 1372select 'foo' like any (array['%a', '%o']); -- t 1373 ?column? 1374---------- 1375 t 1376(1 row) 1377 1378select 'foo' like any (array['%a', '%b']); -- f 1379 ?column? 1380---------- 1381 f 1382(1 row) 1383 1384select 'foo' like all (array['f%', '%o']); -- t 1385 ?column? 1386---------- 1387 t 1388(1 row) 1389 1390select 'foo' like all (array['f%', '%b']); -- f 1391 ?column? 1392---------- 1393 f 1394(1 row) 1395 1396select 'foo' not like any (array['%a', '%b']); -- t 1397 ?column? 1398---------- 1399 t 1400(1 row) 1401 1402select 'foo' not like all (array['%a', '%o']); -- f 1403 ?column? 1404---------- 1405 f 1406(1 row) 1407 1408select 'foo' ilike any (array['%A', '%O']); -- t 1409 ?column? 1410---------- 1411 t 1412(1 row) 1413 1414select 'foo' ilike all (array['F%', '%O']); -- t 1415 ?column? 1416---------- 1417 t 1418(1 row) 1419 1420-- 1421-- General array parser tests 1422-- 1423-- none of the following should be accepted 1424select '{{1,{2}},{2,3}}'::text[]; 1425ERROR: malformed array literal: "{{1,{2}},{2,3}}" 1426LINE 1: select '{{1,{2}},{2,3}}'::text[]; 1427 ^ 1428DETAIL: Unexpected "{" character. 1429select '{{},{}}'::text[]; 1430ERROR: malformed array literal: "{{},{}}" 1431LINE 1: select '{{},{}}'::text[]; 1432 ^ 1433DETAIL: Unexpected "}" character. 1434select E'{{1,2},\\{2,3}}'::text[]; 1435ERROR: malformed array literal: "{{1,2},\{2,3}}" 1436LINE 1: select E'{{1,2},\\{2,3}}'::text[]; 1437 ^ 1438DETAIL: Unexpected "\" character. 1439select '{{"1 2" x},{3}}'::text[]; 1440ERROR: malformed array literal: "{{"1 2" x},{3}}" 1441LINE 1: select '{{"1 2" x},{3}}'::text[]; 1442 ^ 1443DETAIL: Unexpected array element. 1444select '{}}'::text[]; 1445ERROR: malformed array literal: "{}}" 1446LINE 1: select '{}}'::text[]; 1447 ^ 1448DETAIL: Junk after closing right brace. 1449select '{ }}'::text[]; 1450ERROR: malformed array literal: "{ }}" 1451LINE 1: select '{ }}'::text[]; 1452 ^ 1453DETAIL: Junk after closing right brace. 1454select array[]; 1455ERROR: cannot determine type of empty array 1456LINE 1: select array[]; 1457 ^ 1458HINT: Explicitly cast to the desired type, for example ARRAY[]::integer[]. 1459-- none of the above should be accepted 1460-- all of the following should be accepted 1461select '{}'::text[]; 1462 text 1463------ 1464 {} 1465(1 row) 1466 1467select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[]; 1468 text 1469----------------------------------------------- 1470 {{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}} 1471(1 row) 1472 1473select '{0 second ,0 second}'::interval[]; 1474 interval 1475--------------- 1476 {"@ 0","@ 0"} 1477(1 row) 1478 1479select '{ { "," } , { 3 } }'::text[]; 1480 text 1481------------- 1482 {{","},{3}} 1483(1 row) 1484 1485select ' { { " 0 second " , 0 second } }'::text[]; 1486 text 1487------------------------------- 1488 {{" 0 second ","0 second"}} 1489(1 row) 1490 1491select '{ 1492 0 second, 1493 @ 1 hour @ 42 minutes @ 20 seconds 1494 }'::interval[]; 1495 interval 1496------------------------------------ 1497 {"@ 0","@ 1 hour 42 mins 20 secs"} 1498(1 row) 1499 1500select array[]::text[]; 1501 array 1502------- 1503 {} 1504(1 row) 1505 1506select '[0:1]={1.1,2.2}'::float8[]; 1507 float8 1508----------------- 1509 [0:1]={1.1,2.2} 1510(1 row) 1511 1512-- all of the above should be accepted 1513-- tests for array aggregates 1514CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]); 1515INSERT INTO arraggtest (f1, f2, f3) VALUES 1516('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}'); 1517INSERT INTO arraggtest (f1, f2, f3) VALUES 1518('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}'); 1519SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 1520 max | min | max | min | max | min 1521-----------+---------+--------------------------+--------------------------+---------+------- 1522 {1,2,3,4} | {1,2,3} | {{grey,red},{grey,blue}} | {{grey,red},{blue,blue}} | {1.6,0} | {1.6} 1523(1 row) 1524 1525INSERT INTO arraggtest (f1, f2, f3) VALUES 1526('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}'); 1527SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 1528 max | min | max | min | max | min 1529---------------+---------+--------------------------------+--------------------------+-----------------------+------- 1530 {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} 1531(1 row) 1532 1533INSERT INTO arraggtest (f1, f2, f3) VALUES 1534('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}'); 1535SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 1536 max | min | max | min | max | min 1537---------------+---------+--------------------------------+------------------------------+-----------------------+------- 1538 {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} 1539(1 row) 1540 1541INSERT INTO arraggtest (f1, f2, f3) VALUES 1542('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL); 1543SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 1544 max | min | max | min | max | min 1545---------------+---------+--------------------------------+------------------------------+-----------------------+------- 1546 {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} 1547(1 row) 1548 1549INSERT INTO arraggtest (f1, f2, f3) VALUES 1550('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}'); 1551SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 1552 max | min | max | min | max | min 1553---------------+-----+--------------------------------+------------------------------+-----------------------+------- 1554 {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} 1555(1 row) 1556 1557-- A few simple tests for arrays of composite types 1558create type comptype as (f1 int, f2 text); 1559create table comptable (c1 comptype, c2 comptype[]); 1560-- XXX would like to not have to specify row() construct types here ... 1561insert into comptable 1562 values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]); 1563-- check that implicitly named array type _comptype isn't a problem 1564create type _comptype as enum('fooey'); 1565select * from comptable; 1566 c1 | c2 1567---------+----------------------- 1568 (1,foo) | {"(2,bar)","(3,baz)"} 1569(1 row) 1570 1571select c2[2].f2 from comptable; 1572 f2 1573----- 1574 baz 1575(1 row) 1576 1577drop type _comptype; 1578drop table comptable; 1579drop type comptype; 1580create or replace function unnest1(anyarray) 1581returns setof anyelement as $$ 1582select $1[s] from generate_subscripts($1,1) g(s); 1583$$ language sql immutable; 1584create or replace function unnest2(anyarray) 1585returns setof anyelement as $$ 1586select $1[s1][s2] from generate_subscripts($1,1) g1(s1), 1587 generate_subscripts($1,2) g2(s2); 1588$$ language sql immutable; 1589select * from unnest1(array[1,2,3]); 1590 unnest1 1591--------- 1592 1 1593 2 1594 3 1595(3 rows) 1596 1597select * from unnest2(array[[1,2,3],[4,5,6]]); 1598 unnest2 1599--------- 1600 1 1601 2 1602 3 1603 4 1604 5 1605 6 1606(6 rows) 1607 1608drop function unnest1(anyarray); 1609drop function unnest2(anyarray); 1610select array_fill(null::integer, array[3,3],array[2,2]); 1611 array_fill 1612----------------------------------------------------------------- 1613 [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} 1614(1 row) 1615 1616select array_fill(null::integer, array[3,3]); 1617 array_fill 1618------------------------------------------------------ 1619 {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} 1620(1 row) 1621 1622select array_fill(null::text, array[3,3],array[2,2]); 1623 array_fill 1624----------------------------------------------------------------- 1625 [2:4][2:4]={{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} 1626(1 row) 1627 1628select array_fill(null::text, array[3,3]); 1629 array_fill 1630------------------------------------------------------ 1631 {{NULL,NULL,NULL},{NULL,NULL,NULL},{NULL,NULL,NULL}} 1632(1 row) 1633 1634select array_fill(7, array[3,3],array[2,2]); 1635 array_fill 1636-------------------------------------- 1637 [2:4][2:4]={{7,7,7},{7,7,7},{7,7,7}} 1638(1 row) 1639 1640select array_fill(7, array[3,3]); 1641 array_fill 1642--------------------------- 1643 {{7,7,7},{7,7,7},{7,7,7}} 1644(1 row) 1645 1646select array_fill('juhu'::text, array[3,3],array[2,2]); 1647 array_fill 1648----------------------------------------------------------------- 1649 [2:4][2:4]={{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}} 1650(1 row) 1651 1652select array_fill('juhu'::text, array[3,3]); 1653 array_fill 1654------------------------------------------------------ 1655 {{juhu,juhu,juhu},{juhu,juhu,juhu},{juhu,juhu,juhu}} 1656(1 row) 1657 1658select a, a = '{}' as is_eq, array_dims(a) 1659 from (select array_fill(42, array[0]) as a) ss; 1660 a | is_eq | array_dims 1661----+-------+------------ 1662 {} | t | 1663(1 row) 1664 1665select a, a = '{}' as is_eq, array_dims(a) 1666 from (select array_fill(42, '{}') as a) ss; 1667 a | is_eq | array_dims 1668----+-------+------------ 1669 {} | t | 1670(1 row) 1671 1672select a, a = '{}' as is_eq, array_dims(a) 1673 from (select array_fill(42, '{}', '{}') as a) ss; 1674 a | is_eq | array_dims 1675----+-------+------------ 1676 {} | t | 1677(1 row) 1678 1679-- raise exception 1680select array_fill(1, null, array[2,2]); 1681ERROR: dimension array or low bound array cannot be null 1682select array_fill(1, array[2,2], null); 1683ERROR: dimension array or low bound array cannot be null 1684select array_fill(1, array[2,2], '{}'); 1685ERROR: wrong number of array subscripts 1686DETAIL: Low bound array has different size than dimensions array. 1687select array_fill(1, array[3,3], array[1,1,1]); 1688ERROR: wrong number of array subscripts 1689DETAIL: Low bound array has different size than dimensions array. 1690select array_fill(1, array[1,2,null]); 1691ERROR: dimension values cannot be null 1692select array_fill(1, array[[1,2],[3,4]]); 1693ERROR: wrong number of array subscripts 1694DETAIL: Dimension array must be one dimensional. 1695select string_to_array('1|2|3', '|'); 1696 string_to_array 1697----------------- 1698 {1,2,3} 1699(1 row) 1700 1701select string_to_array('1|2|3|', '|'); 1702 string_to_array 1703----------------- 1704 {1,2,3,""} 1705(1 row) 1706 1707select string_to_array('1||2|3||', '||'); 1708 string_to_array 1709----------------- 1710 {1,2|3,""} 1711(1 row) 1712 1713select string_to_array('1|2|3', ''); 1714 string_to_array 1715----------------- 1716 {1|2|3} 1717(1 row) 1718 1719select string_to_array('', '|'); 1720 string_to_array 1721----------------- 1722 {} 1723(1 row) 1724 1725select string_to_array('1|2|3', NULL); 1726 string_to_array 1727----------------- 1728 {1,|,2,|,3} 1729(1 row) 1730 1731select string_to_array(NULL, '|') IS NULL; 1732 ?column? 1733---------- 1734 t 1735(1 row) 1736 1737select string_to_array('abc', ''); 1738 string_to_array 1739----------------- 1740 {abc} 1741(1 row) 1742 1743select string_to_array('abc', '', 'abc'); 1744 string_to_array 1745----------------- 1746 {NULL} 1747(1 row) 1748 1749select string_to_array('abc', ','); 1750 string_to_array 1751----------------- 1752 {abc} 1753(1 row) 1754 1755select string_to_array('abc', ',', 'abc'); 1756 string_to_array 1757----------------- 1758 {NULL} 1759(1 row) 1760 1761select string_to_array('1,2,3,4,,6', ','); 1762 string_to_array 1763----------------- 1764 {1,2,3,4,"",6} 1765(1 row) 1766 1767select string_to_array('1,2,3,4,,6', ',', ''); 1768 string_to_array 1769------------------ 1770 {1,2,3,4,NULL,6} 1771(1 row) 1772 1773select string_to_array('1,2,3,4,*,6', ',', '*'); 1774 string_to_array 1775------------------ 1776 {1,2,3,4,NULL,6} 1777(1 row) 1778 1779select v, v is null as "is null" from string_to_table('1|2|3', '|') g(v); 1780 v | is null 1781---+--------- 1782 1 | f 1783 2 | f 1784 3 | f 1785(3 rows) 1786 1787select v, v is null as "is null" from string_to_table('1|2|3|', '|') g(v); 1788 v | is null 1789---+--------- 1790 1 | f 1791 2 | f 1792 3 | f 1793 | f 1794(4 rows) 1795 1796select v, v is null as "is null" from string_to_table('1||2|3||', '||') g(v); 1797 v | is null 1798-----+--------- 1799 1 | f 1800 2|3 | f 1801 | f 1802(3 rows) 1803 1804select v, v is null as "is null" from string_to_table('1|2|3', '') g(v); 1805 v | is null 1806-------+--------- 1807 1|2|3 | f 1808(1 row) 1809 1810select v, v is null as "is null" from string_to_table('', '|') g(v); 1811 v | is null 1812---+--------- 1813(0 rows) 1814 1815select v, v is null as "is null" from string_to_table('1|2|3', NULL) g(v); 1816 v | is null 1817---+--------- 1818 1 | f 1819 | | f 1820 2 | f 1821 | | f 1822 3 | f 1823(5 rows) 1824 1825select v, v is null as "is null" from string_to_table(NULL, '|') g(v); 1826 v | is null 1827---+--------- 1828(0 rows) 1829 1830select v, v is null as "is null" from string_to_table('abc', '') g(v); 1831 v | is null 1832-----+--------- 1833 abc | f 1834(1 row) 1835 1836select v, v is null as "is null" from string_to_table('abc', '', 'abc') g(v); 1837 v | is null 1838---+--------- 1839 | t 1840(1 row) 1841 1842select v, v is null as "is null" from string_to_table('abc', ',') g(v); 1843 v | is null 1844-----+--------- 1845 abc | f 1846(1 row) 1847 1848select v, v is null as "is null" from string_to_table('abc', ',', 'abc') g(v); 1849 v | is null 1850---+--------- 1851 | t 1852(1 row) 1853 1854select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',') g(v); 1855 v | is null 1856---+--------- 1857 1 | f 1858 2 | f 1859 3 | f 1860 4 | f 1861 | f 1862 6 | f 1863(6 rows) 1864 1865select v, v is null as "is null" from string_to_table('1,2,3,4,,6', ',', '') g(v); 1866 v | is null 1867---+--------- 1868 1 | f 1869 2 | f 1870 3 | f 1871 4 | f 1872 | t 1873 6 | f 1874(6 rows) 1875 1876select v, v is null as "is null" from string_to_table('1,2,3,4,*,6', ',', '*') g(v); 1877 v | is null 1878---+--------- 1879 1 | f 1880 2 | f 1881 3 | f 1882 4 | f 1883 | t 1884 6 | f 1885(6 rows) 1886 1887select array_to_string(NULL::int4[], ',') IS NULL; 1888 ?column? 1889---------- 1890 t 1891(1 row) 1892 1893select array_to_string('{}'::int4[], ','); 1894 array_to_string 1895----------------- 1896 1897(1 row) 1898 1899select array_to_string(array[1,2,3,4,NULL,6], ','); 1900 array_to_string 1901----------------- 1902 1,2,3,4,6 1903(1 row) 1904 1905select array_to_string(array[1,2,3,4,NULL,6], ',', '*'); 1906 array_to_string 1907----------------- 1908 1,2,3,4,*,6 1909(1 row) 1910 1911select array_to_string(array[1,2,3,4,NULL,6], NULL); 1912 array_to_string 1913----------------- 1914 1915(1 row) 1916 1917select array_to_string(array[1,2,3,4,NULL,6], ',', NULL); 1918 array_to_string 1919----------------- 1920 1,2,3,4,6 1921(1 row) 1922 1923select array_to_string(string_to_array('1|2|3', '|'), '|'); 1924 array_to_string 1925----------------- 1926 1|2|3 1927(1 row) 1928 1929select array_length(array[1,2,3], 1); 1930 array_length 1931-------------- 1932 3 1933(1 row) 1934 1935select array_length(array[[1,2,3], [4,5,6]], 0); 1936 array_length 1937-------------- 1938 1939(1 row) 1940 1941select array_length(array[[1,2,3], [4,5,6]], 1); 1942 array_length 1943-------------- 1944 2 1945(1 row) 1946 1947select array_length(array[[1,2,3], [4,5,6]], 2); 1948 array_length 1949-------------- 1950 3 1951(1 row) 1952 1953select array_length(array[[1,2,3], [4,5,6]], 3); 1954 array_length 1955-------------- 1956 1957(1 row) 1958 1959select cardinality(NULL::int[]); 1960 cardinality 1961------------- 1962 1963(1 row) 1964 1965select cardinality('{}'::int[]); 1966 cardinality 1967------------- 1968 0 1969(1 row) 1970 1971select cardinality(array[1,2,3]); 1972 cardinality 1973------------- 1974 3 1975(1 row) 1976 1977select cardinality('[2:4]={5,6,7}'::int[]); 1978 cardinality 1979------------- 1980 3 1981(1 row) 1982 1983select cardinality('{{1,2}}'::int[]); 1984 cardinality 1985------------- 1986 2 1987(1 row) 1988 1989select cardinality('{{1,2},{3,4},{5,6}}'::int[]); 1990 cardinality 1991------------- 1992 6 1993(1 row) 1994 1995select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]); 1996 cardinality 1997------------- 1998 8 1999(1 row) 2000 2001-- array_agg(anynonarray) 2002select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss; 2003 array_agg 2004-------------------------------------- 2005 {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14} 2006(1 row) 2007 2008select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; 2009 array_agg 2010--------------------------------- 2011 {0,1,2,3,4,5,6,7,8,9,0,1,2,3,4} 2012(1 row) 2013 2014select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; 2015 array_agg 2016--------------------------------------- 2017 {0,1,2,3,NULL,5,6,7,8,9,0,1,2,3,NULL} 2018(1 row) 2019 2020select array_agg(unique1) from tenk1 where unique1 < -15; 2021 array_agg 2022----------- 2023 2024(1 row) 2025 2026-- array_agg(anyarray) 2027select array_agg(ar) 2028 from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar); 2029 array_agg 2030--------------- 2031 {{1,2},{3,4}} 2032(1 row) 2033 2034select array_agg(distinct ar order by ar desc) 2035 from (select array[i / 2] from generate_series(1,10) a(i)) b(ar); 2036 array_agg 2037--------------------------- 2038 {{5},{4},{3},{2},{1},{0}} 2039(1 row) 2040 2041select array_agg(ar) 2042 from (select array_agg(array[i, i+1, i-1]) 2043 from generate_series(1,2) a(i)) b(ar); 2044 array_agg 2045--------------------- 2046 {{{1,2,0},{2,3,1}}} 2047(1 row) 2048 2049select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i); 2050 array_agg 2051--------------------------------------------- 2052 {{2.2,2.3,2.4},{3.2,3.3,3.4},{4.2,4.3,4.4}} 2053(1 row) 2054 2055select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i); 2056 array_agg 2057----------------------------------- 2058 {{Hello,9},{Hello,10},{Hello,11}} 2059(1 row) 2060 2061select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i); 2062 array_agg 2063-------------------------------------- 2064 {{1,1,2},{2,2,3},{3,NULL,4},{4,4,5}} 2065(1 row) 2066 2067-- errors 2068select array_agg('{}'::int[]) from generate_series(1,2); 2069ERROR: cannot accumulate empty arrays 2070select array_agg(null::int[]) from generate_series(1,2); 2071ERROR: cannot accumulate null arrays 2072select array_agg(ar) 2073 from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar); 2074ERROR: cannot accumulate arrays of different dimensionality 2075select unnest(array[1,2,3]); 2076 unnest 2077-------- 2078 1 2079 2 2080 3 2081(3 rows) 2082 2083select * from unnest(array[1,2,3]); 2084 unnest 2085-------- 2086 1 2087 2 2088 3 2089(3 rows) 2090 2091select unnest(array[1,2,3,4.5]::float8[]); 2092 unnest 2093-------- 2094 1 2095 2 2096 3 2097 4.5 2098(4 rows) 2099 2100select unnest(array[1,2,3,4.5]::numeric[]); 2101 unnest 2102-------- 2103 1 2104 2 2105 3 2106 4.5 2107(4 rows) 2108 2109select unnest(array[1,2,3,null,4,null,null,5,6]); 2110 unnest 2111-------- 2112 1 2113 2 2114 3 2115 2116 4 2117 2118 2119 5 2120 6 2121(9 rows) 2122 2123select unnest(array[1,2,3,null,4,null,null,5,6]::text[]); 2124 unnest 2125-------- 2126 1 2127 2 2128 3 2129 2130 4 2131 2132 2133 5 2134 6 2135(9 rows) 2136 2137select abs(unnest(array[1,2,null,-3])); 2138 abs 2139----- 2140 1 2141 2 2142 2143 3 2144(4 rows) 2145 2146select array_remove(array[1,2,2,3], 2); 2147 array_remove 2148-------------- 2149 {1,3} 2150(1 row) 2151 2152select array_remove(array[1,2,2,3], 5); 2153 array_remove 2154-------------- 2155 {1,2,2,3} 2156(1 row) 2157 2158select array_remove(array[1,NULL,NULL,3], NULL); 2159 array_remove 2160-------------- 2161 {1,3} 2162(1 row) 2163 2164select array_remove(array['A','CC','D','C','RR'], 'RR'); 2165 array_remove 2166-------------- 2167 {A,CC,D,C} 2168(1 row) 2169 2170select array_remove(array[1.0, 2.1, 3.3], 1); 2171 array_remove 2172-------------- 2173 {2.1,3.3} 2174(1 row) 2175 2176select array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed 2177ERROR: removing elements from multidimensional arrays is not supported 2178select array_remove(array['X','X','X'], 'X') = '{}'; 2179 ?column? 2180---------- 2181 t 2182(1 row) 2183 2184select array_replace(array[1,2,5,4],5,3); 2185 array_replace 2186--------------- 2187 {1,2,3,4} 2188(1 row) 2189 2190select array_replace(array[1,2,5,4],5,NULL); 2191 array_replace 2192--------------- 2193 {1,2,NULL,4} 2194(1 row) 2195 2196select array_replace(array[1,2,NULL,4,NULL],NULL,5); 2197 array_replace 2198--------------- 2199 {1,2,5,4,5} 2200(1 row) 2201 2202select array_replace(array['A','B','DD','B'],'B','CC'); 2203 array_replace 2204--------------- 2205 {A,CC,DD,CC} 2206(1 row) 2207 2208select array_replace(array[1,NULL,3],NULL,NULL); 2209 array_replace 2210--------------- 2211 {1,NULL,3} 2212(1 row) 2213 2214select array_replace(array['AB',NULL,'CDE'],NULL,'12'); 2215 array_replace 2216--------------- 2217 {AB,12,CDE} 2218(1 row) 2219 2220-- array(select array-value ...) 2221select array(select array[i,i/2] from generate_series(1,5) i); 2222 array 2223--------------------------------- 2224 {{1,0},{2,1},{3,1},{4,2},{5,2}} 2225(1 row) 2226 2227select array(select array['Hello', i::text] from generate_series(9,11) i); 2228 array 2229----------------------------------- 2230 {{Hello,9},{Hello,10},{Hello,11}} 2231(1 row) 2232 2233-- Insert/update on a column that is array of composite 2234create temp table t1 (f1 int8_tbl[]); 2235insert into t1 (f1[5].q1) values(42); 2236select * from t1; 2237 f1 2238----------------- 2239 [5:5]={"(42,)"} 2240(1 row) 2241 2242update t1 set f1[5].q2 = 43; 2243select * from t1; 2244 f1 2245------------------- 2246 [5:5]={"(42,43)"} 2247(1 row) 2248 2249-- Check that arrays of composites are safely detoasted when needed 2250create temp table src (f1 text); 2251insert into src 2252 select string_agg(random()::text,'') from generate_series(1,10000); 2253create type textandtext as (c1 text, c2 text); 2254create temp table dest (f1 textandtext[]); 2255insert into dest select array[row(f1,f1)::textandtext] from src; 2256select length(md5((f1[1]).c2)) from dest; 2257 length 2258-------- 2259 32 2260(1 row) 2261 2262delete from src; 2263select length(md5((f1[1]).c2)) from dest; 2264 length 2265-------- 2266 32 2267(1 row) 2268 2269truncate table src; 2270drop table src; 2271select length(md5((f1[1]).c2)) from dest; 2272 length 2273-------- 2274 32 2275(1 row) 2276 2277drop table dest; 2278drop type textandtext; 2279-- Tests for polymorphic-array form of width_bucket() 2280-- this exercises the varwidth and float8 code paths 2281SELECT 2282 op, 2283 width_bucket(op::numeric, ARRAY[1, 3, 5, 10.0]::numeric[]) AS wb_n1, 2284 width_bucket(op::numeric, ARRAY[0, 5.5, 9.99]::numeric[]) AS wb_n2, 2285 width_bucket(op::numeric, ARRAY[-6, -5, 2.0]::numeric[]) AS wb_n3, 2286 width_bucket(op::float8, ARRAY[1, 3, 5, 10.0]::float8[]) AS wb_f1, 2287 width_bucket(op::float8, ARRAY[0, 5.5, 9.99]::float8[]) AS wb_f2, 2288 width_bucket(op::float8, ARRAY[-6, -5, 2.0]::float8[]) AS wb_f3 2289FROM (VALUES 2290 (-5.2), 2291 (-0.0000000001), 2292 (0.000000000001), 2293 (1), 2294 (1.99999999999999), 2295 (2), 2296 (2.00000000000001), 2297 (3), 2298 (4), 2299 (4.5), 2300 (5), 2301 (5.5), 2302 (6), 2303 (7), 2304 (8), 2305 (9), 2306 (9.99999999999999), 2307 (10), 2308 (10.0000000000001) 2309) v(op); 2310 op | wb_n1 | wb_n2 | wb_n3 | wb_f1 | wb_f2 | wb_f3 2311------------------+-------+-------+-------+-------+-------+------- 2312 -5.2 | 0 | 0 | 1 | 0 | 0 | 1 2313 -0.0000000001 | 0 | 0 | 2 | 0 | 0 | 2 2314 0.000000000001 | 0 | 1 | 2 | 0 | 1 | 2 2315 1 | 1 | 1 | 2 | 1 | 1 | 2 2316 1.99999999999999 | 1 | 1 | 2 | 1 | 1 | 2 2317 2 | 1 | 1 | 3 | 1 | 1 | 3 2318 2.00000000000001 | 1 | 1 | 3 | 1 | 1 | 3 2319 3 | 2 | 1 | 3 | 2 | 1 | 3 2320 4 | 2 | 1 | 3 | 2 | 1 | 3 2321 4.5 | 2 | 1 | 3 | 2 | 1 | 3 2322 5 | 3 | 1 | 3 | 3 | 1 | 3 2323 5.5 | 3 | 2 | 3 | 3 | 2 | 3 2324 6 | 3 | 2 | 3 | 3 | 2 | 3 2325 7 | 3 | 2 | 3 | 3 | 2 | 3 2326 8 | 3 | 2 | 3 | 3 | 2 | 3 2327 9 | 3 | 2 | 3 | 3 | 2 | 3 2328 9.99999999999999 | 3 | 3 | 3 | 3 | 3 | 3 2329 10 | 4 | 3 | 3 | 4 | 3 | 3 2330 10.0000000000001 | 4 | 3 | 3 | 4 | 3 | 3 2331(19 rows) 2332 2333-- ensure float8 path handles NaN properly 2334SELECT 2335 op, 2336 width_bucket(op, ARRAY[1, 3, 9, 'NaN', 'NaN']::float8[]) AS wb 2337FROM (VALUES 2338 (-5.2::float8), 2339 (4::float8), 2340 (77::float8), 2341 ('NaN'::float8) 2342) v(op); 2343 op | wb 2344------+---- 2345 -5.2 | 0 2346 4 | 2 2347 77 | 3 2348 NaN | 5 2349(4 rows) 2350 2351-- these exercise the generic fixed-width code path 2352SELECT 2353 op, 2354 width_bucket(op, ARRAY[1, 3, 5, 10]) AS wb_1 2355FROM generate_series(0,11) as op; 2356 op | wb_1 2357----+------ 2358 0 | 0 2359 1 | 1 2360 2 | 1 2361 3 | 2 2362 4 | 2 2363 5 | 3 2364 6 | 3 2365 7 | 3 2366 8 | 3 2367 9 | 3 2368 10 | 4 2369 11 | 4 2370(12 rows) 2371 2372SELECT width_bucket(now(), 2373 array['yesterday', 'today', 'tomorrow']::timestamptz[]); 2374 width_bucket 2375-------------- 2376 2 2377(1 row) 2378 2379-- corner cases 2380SELECT width_bucket(5, ARRAY[3]); 2381 width_bucket 2382-------------- 2383 1 2384(1 row) 2385 2386SELECT width_bucket(5, '{}'); 2387 width_bucket 2388-------------- 2389 0 2390(1 row) 2391 2392-- error cases 2393SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); 2394ERROR: function width_bucket(text, integer[]) does not exist 2395LINE 1: SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); 2396 ^ 2397HINT: No function matches the given name and argument types. You might need to add explicit type casts. 2398SELECT width_bucket(5, ARRAY[3, 4, NULL]); 2399ERROR: thresholds array must not contain NULLs 2400SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); 2401ERROR: thresholds must be one-dimensional array 2402-- trim_array 2403SELECT arr, trim_array(arr, 2) 2404FROM 2405(VALUES ('{1,2,3,4,5,6}'::bigint[]), 2406 ('{1,2}'), 2407 ('[10:16]={1,2,3,4,5,6,7}'), 2408 ('[-15:-10]={1,2,3,4,5,6}'), 2409 ('{{1,10},{2,20},{3,30},{4,40}}')) v(arr); 2410 arr | trim_array 2411-------------------------------+----------------- 2412 {1,2,3,4,5,6} | {1,2,3,4} 2413 {1,2} | {} 2414 [10:16]={1,2,3,4,5,6,7} | {1,2,3,4,5} 2415 [-15:-10]={1,2,3,4,5,6} | {1,2,3,4} 2416 {{1,10},{2,20},{3,30},{4,40}} | {{1,10},{2,20}} 2417(5 rows) 2418 2419SELECT trim_array(ARRAY[1, 2, 3], -1); -- fail 2420ERROR: number of elements to trim must be between 0 and 3 2421SELECT trim_array(ARRAY[1, 2, 3], 10); -- fail 2422ERROR: number of elements to trim must be between 0 and 3 2423