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