1-- 2-- ARRAYS 3-- 4 5CREATE TABLE arrtest ( 6 a int2[], 7 b int4[][][], 8 c name[], 9 d text[][], 10 e float8[], 11 f char(5)[], 12 g varchar(5)[] 13); 14 15-- 16-- only the 'e' array is 0-based, the others are 1-based. 17-- 18 19INSERT INTO arrtest (a[1:5], b[1:1][1:2][1:2], c, d, f, g) 20 VALUES ('{1,2,3,4,5}', '{{{0,0},{1,2}}}', '{}', '{}', '{}', '{}'); 21 22UPDATE arrtest SET e[0] = '1.1'; 23 24UPDATE arrtest SET e[1] = '2.2'; 25 26INSERT INTO arrtest (f) 27 VALUES ('{"too long"}'); 28 29INSERT INTO arrtest (a, b[1:2][1:2], c, d, e, f, g) 30 VALUES ('{11,12,23}', '{{3,4},{4,5}}', '{"foobar"}', 31 '{{"elt1", "elt2"}}', '{"3.4", "6.7"}', 32 '{"abc","abcde"}', '{"abc","abcde"}'); 33 34INSERT INTO arrtest (a, b[1:2], c, d[1:2]) 35 VALUES ('{}', '{3,4}', '{foo,bar}', '{bar,foo}'); 36 37 38SELECT * FROM arrtest; 39 40SELECT arrtest.a[1], 41 arrtest.b[1][1][1], 42 arrtest.c[1], 43 arrtest.d[1][1], 44 arrtest.e[0] 45 FROM arrtest; 46 47SELECT a[1], b[1][1][1], c[1], d[1][1], e[0] 48 FROM arrtest; 49 50SELECT a[1:3], 51 b[1:1][1:2][1:2], 52 c[1:2], 53 d[1:1][1:2] 54 FROM arrtest; 55 56SELECT array_ndims(a) AS a,array_ndims(b) AS b,array_ndims(c) AS c 57 FROM arrtest; 58 59SELECT array_dims(a) AS a,array_dims(b) AS b,array_dims(c) AS c 60 FROM arrtest; 61 62-- returns nothing 63SELECT * 64 FROM arrtest 65 WHERE a[1] < 5 and 66 c = '{"foobar"}'::_name; 67 68UPDATE arrtest 69 SET a[1:2] = '{16,25}' 70 WHERE NOT a = '{}'::_int2; 71 72UPDATE arrtest 73 SET b[1:1][1:1][1:2] = '{113, 117}', 74 b[1:1][1:2][2:2] = '{142, 147}' 75 WHERE array_dims(b) = '[1:1][1:2][1:2]'; 76 77UPDATE arrtest 78 SET c[2:2] = '{"new_word"}' 79 WHERE array_dims(c) is not null; 80 81SELECT a,b,c FROM arrtest; 82 83SELECT a[1:3], 84 b[1:1][1:2][1:2], 85 c[1:2], 86 d[1:1][2:2] 87 FROM arrtest; 88 89SELECT b[1:1][2][2], 90 d[1:1][2] 91 FROM arrtest; 92 93INSERT INTO arrtest(a) VALUES('{1,null,3}'); 94SELECT a FROM arrtest; 95UPDATE arrtest SET a[4] = NULL WHERE a[2] IS NULL; 96SELECT a FROM arrtest WHERE a[2] IS NULL; 97DELETE FROM arrtest WHERE a[2] IS NULL AND b IS NULL; 98SELECT a,b,c FROM arrtest; 99 100-- test mixed slice/scalar subscripting 101select '{{1,2,3},{4,5,6},{7,8,9}}'::int[]; 102select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; 103select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[]; 104select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; 105 106-- 107-- check subscription corner cases 108-- 109-- More subscripts than MAXDIMS(6) 110SELECT ('{}'::int[])[1][2][3][4][5][6][7]; 111-- NULL index yields NULL when selecting 112SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1]; 113SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1]; 114SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1]; 115-- NULL index in assignment is an error 116UPDATE arrtest 117 SET c[NULL] = '{"can''t assign"}' 118 WHERE array_dims(c) is not null; 119UPDATE arrtest 120 SET c[NULL:1] = '{"can''t assign"}' 121 WHERE array_dims(c) is not null; 122UPDATE arrtest 123 SET c[1:NULL] = '{"can''t assign"}' 124 WHERE array_dims(c) is not null; 125 126-- test slices with empty lower and/or upper index 127CREATE TEMP TABLE arrtest_s ( 128 a int2[], 129 b int2[][] 130); 131INSERT INTO arrtest_s VALUES ('{1,2,3,4,5}', '{{1,2,3}, {4,5,6}, {7,8,9}}'); 132INSERT INTO arrtest_s VALUES ('[0:4]={1,2,3,4,5}', '[0:2][0:2]={{1,2,3}, {4,5,6}, {7,8,9}}'); 133 134SELECT * FROM arrtest_s; 135SELECT a[:3], b[:2][:2] FROM arrtest_s; 136SELECT a[2:], b[2:][2:] FROM arrtest_s; 137SELECT a[:], b[:] FROM arrtest_s; 138 139-- updates 140UPDATE arrtest_s SET a[:3] = '{11, 12, 13}', b[:2][:2] = '{{11,12}, {14,15}}' 141 WHERE array_lower(a,1) = 1; 142SELECT * FROM arrtest_s; 143UPDATE arrtest_s SET a[3:] = '{23, 24, 25}', b[2:][2:] = '{{25,26}, {28,29}}'; 144SELECT * FROM arrtest_s; 145UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; 146SELECT * FROM arrtest_s; 147UPDATE arrtest_s SET a[:] = '{23, 24, 25}'; -- fail, too small 148INSERT INTO arrtest_s VALUES(NULL, NULL); 149UPDATE arrtest_s SET a[:] = '{11, 12, 13, 14, 15}'; -- fail, no good with null 150 151-- check with fixed-length-array type, such as point 152SELECT f1[0:1] FROM POINT_TBL; 153SELECT f1[0:] FROM POINT_TBL; 154SELECT f1[:1] FROM POINT_TBL; 155SELECT f1[:] FROM POINT_TBL; 156 157-- subscript assignments to fixed-width result in NULL if previous value is NULL 158UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *; 159INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *; 160-- NULL assignments get ignored 161UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *; 162-- but non-NULL subscript assignments work 163UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *; 164-- but not to expand the range 165UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *; 166 167-- 168-- test array extension 169-- 170CREATE TEMP TABLE arrtest1 (i int[], t text[]); 171insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); 172select * from arrtest1; 173update arrtest1 set i[2] = 22, t[2] = 'twenty-two'; 174select * from arrtest1; 175update arrtest1 set i[5] = 5, t[5] = 'five'; 176select * from arrtest1; 177update arrtest1 set i[8] = 8, t[8] = 'eight'; 178select * from arrtest1; 179update arrtest1 set i[0] = 0, t[0] = 'zero'; 180select * from arrtest1; 181update arrtest1 set i[-3] = -3, t[-3] = 'minus-three'; 182select * from arrtest1; 183update arrtest1 set i[0:2] = array[10,11,12], t[0:2] = array['ten','eleven','twelve']; 184select * from arrtest1; 185update arrtest1 set i[8:10] = array[18,null,20], t[8:10] = array['p18',null,'p20']; 186select * from arrtest1; 187update arrtest1 set i[11:12] = array[null,22], t[11:12] = array[null,'p22']; 188select * from arrtest1; 189update arrtest1 set i[15:16] = array[null,26], t[15:16] = array[null,'p26']; 190select * from arrtest1; 191update arrtest1 set i[-5:-3] = array[-15,-14,-13], t[-5:-3] = array['m15','m14','m13']; 192select * from arrtest1; 193update arrtest1 set i[-7:-6] = array[-17,null], t[-7:-6] = array['m17',null]; 194select * from arrtest1; 195update arrtest1 set i[-12:-10] = array[-22,null,-20], t[-12:-10] = array['m22',null,'m20']; 196select * from arrtest1; 197delete from arrtest1; 198insert into arrtest1 values(array[1,2,null,4], array['one','two',null,'four']); 199select * from arrtest1; 200update arrtest1 set i[0:5] = array[0,1,2,null,4,5], t[0:5] = array['z','p1','p2',null,'p4','p5']; 201select * from arrtest1; 202 203-- 204-- array expressions and operators 205-- 206 207-- table creation and INSERTs 208CREATE TEMP TABLE arrtest2 (i integer ARRAY[4], f float8[], n numeric[], t text[], d timestamp[]); 209INSERT INTO arrtest2 VALUES( 210 ARRAY[[[113,142],[1,147]]], 211 ARRAY[1.1,1.2,1.3]::float8[], 212 ARRAY[1.1,1.2,1.3], 213 ARRAY[[['aaa','aab'],['aba','abb'],['aca','acb']],[['baa','bab'],['bba','bbb'],['bca','bcb']]], 214 ARRAY['19620326','19931223','19970117']::timestamp[] 215); 216 217-- some more test data 218CREATE TEMP TABLE arrtest_f (f0 int, f1 text, f2 float8); 219insert into arrtest_f values(1,'cat1',1.21); 220insert into arrtest_f values(2,'cat1',1.24); 221insert into arrtest_f values(3,'cat1',1.18); 222insert into arrtest_f values(4,'cat1',1.26); 223insert into arrtest_f values(5,'cat1',1.15); 224insert into arrtest_f values(6,'cat2',1.15); 225insert into arrtest_f values(7,'cat2',1.26); 226insert into arrtest_f values(8,'cat2',1.32); 227insert into arrtest_f values(9,'cat2',1.30); 228 229CREATE TEMP TABLE arrtest_i (f0 int, f1 text, f2 int); 230insert into arrtest_i values(1,'cat1',21); 231insert into arrtest_i values(2,'cat1',24); 232insert into arrtest_i values(3,'cat1',18); 233insert into arrtest_i values(4,'cat1',26); 234insert into arrtest_i values(5,'cat1',15); 235insert into arrtest_i values(6,'cat2',15); 236insert into arrtest_i values(7,'cat2',26); 237insert into arrtest_i values(8,'cat2',32); 238insert into arrtest_i values(9,'cat2',30); 239 240-- expressions 241SELECT t.f[1][3][1] AS "131", t.f[2][2][1] AS "221" FROM ( 242 SELECT ARRAY[[[111,112],[121,122],[131,132]],[[211,212],[221,122],[231,232]]] AS f 243) AS t; 244SELECT ARRAY[[[[[['hello'],['world']]]]]]; 245SELECT ARRAY[ARRAY['hello'],ARRAY['world']]; 246SELECT ARRAY(select f2 from arrtest_f order by f2) AS "ARRAY"; 247 248-- with nulls 249SELECT '{1,null,3}'::int[]; 250SELECT ARRAY[1,NULL,3]; 251 252-- functions 253SELECT array_append(array[42], 6) AS "{42,6}"; 254SELECT array_prepend(6, array[42]) AS "{6,42}"; 255SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}"; 256SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}"; 257SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}"; 258 259SELECT array_position(ARRAY[1,2,3,4,5], 4); 260SELECT array_position(ARRAY[5,3,4,2,1], 4); 261SELECT array_position(ARRAY[[1,2],[3,4]], 3); 262SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon'); 263SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'sat'); 264SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], NULL); 265SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], NULL); 266SELECT array_position(ARRAY['sun','mon','tue','wed','thu',NULL,'fri','sat'], 'sat'); 267 268SELECT array_positions(NULL, 10); 269SELECT array_positions(NULL, NULL::int); 270SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4); 271SELECT array_positions(ARRAY[[1,2],[3,4]], 4); 272SELECT array_positions(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL); 273SELECT array_positions(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL); 274SELECT array_length(array_positions(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10 275 FROM generate_series(1,100) g(i)), 276 'AAAAAAAAAAAAAAAAAAAAAAAAA5'), 1); 277 278DO $$ 279DECLARE 280 o int; 281 a int[] := ARRAY[1,2,3,2,3,1,2]; 282BEGIN 283 o := array_position(a, 2); 284 WHILE o IS NOT NULL 285 LOOP 286 RAISE NOTICE '%', o; 287 o := array_position(a, 2, o + 1); 288 END LOOP; 289END 290$$ LANGUAGE plpgsql; 291 292SELECT array_position('[2:4]={1,2,3}'::int[], 1); 293SELECT array_positions('[2:4]={1,2,3}'::int[], 1); 294 295SELECT 296 array_position(ids, (1, 1)), 297 array_positions(ids, (1, 1)) 298 FROM 299(VALUES 300 (ARRAY[(0, 0), (1, 1)]), 301 (ARRAY[(1, 1)]) 302) AS f (ids); 303 304-- operators 305SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]]; 306SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE"; 307SELECT ARRAY[1,2] || 3 AS "{1,2,3}"; 308SELECT 0 || ARRAY[1,2] AS "{0,1,2}"; 309SELECT ARRAY[1,2] || ARRAY[3,4] AS "{1,2,3,4}"; 310SELECT ARRAY[[['hello','world']]] || ARRAY[[['happy','birthday']]] AS "ARRAY"; 311SELECT ARRAY[[1,2],[3,4]] || ARRAY[5,6] AS "{{1,2},{3,4},{5,6}}"; 312SELECT ARRAY[0,0] || ARRAY[1,1] || ARRAY[2,2] AS "{0,0,1,1,2,2}"; 313SELECT 0 || ARRAY[1,2] || 3 AS "{0,1,2,3}"; 314 315SELECT * FROM array_op_test WHERE i @> '{32}' ORDER BY seqno; 316SELECT * FROM array_op_test WHERE i && '{32}' ORDER BY seqno; 317SELECT * FROM array_op_test WHERE i @> '{17}' ORDER BY seqno; 318SELECT * FROM array_op_test WHERE i && '{17}' ORDER BY seqno; 319SELECT * FROM array_op_test WHERE i @> '{32,17}' ORDER BY seqno; 320SELECT * FROM array_op_test WHERE i && '{32,17}' ORDER BY seqno; 321SELECT * FROM array_op_test WHERE i <@ '{38,34,32,89}' ORDER BY seqno; 322SELECT * FROM array_op_test WHERE i = '{}' ORDER BY seqno; 323SELECT * FROM array_op_test WHERE i @> '{}' ORDER BY seqno; 324SELECT * FROM array_op_test WHERE i && '{}' ORDER BY seqno; 325SELECT * FROM array_op_test WHERE i <@ '{}' ORDER BY seqno; 326SELECT * FROM array_op_test WHERE i = '{NULL}' ORDER BY seqno; 327SELECT * FROM array_op_test WHERE i @> '{NULL}' ORDER BY seqno; 328SELECT * FROM array_op_test WHERE i && '{NULL}' ORDER BY seqno; 329SELECT * FROM array_op_test WHERE i <@ '{NULL}' ORDER BY seqno; 330 331SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908}' ORDER BY seqno; 332SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908}' ORDER BY seqno; 333SELECT * FROM array_op_test WHERE t @> '{AAAAAAAAAA646}' ORDER BY seqno; 334SELECT * FROM array_op_test WHERE t && '{AAAAAAAAAA646}' ORDER BY seqno; 335SELECT * FROM array_op_test WHERE t @> '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 336SELECT * FROM array_op_test WHERE t && '{AAAAAAAA72908,AAAAAAAAAA646}' ORDER BY seqno; 337SELECT * FROM array_op_test WHERE t <@ '{AAAAAAAA72908,AAAAAAAAAAAAAAAAAAA17075,AA88409,AAAAAAAAAAAAAAAAAA36842,AAAAAAA48038,AAAAAAAAAAAAAA10611}' ORDER BY seqno; 338SELECT * FROM array_op_test WHERE t = '{}' ORDER BY seqno; 339SELECT * FROM array_op_test WHERE t @> '{}' ORDER BY seqno; 340SELECT * FROM array_op_test WHERE t && '{}' ORDER BY seqno; 341SELECT * FROM array_op_test WHERE t <@ '{}' ORDER BY seqno; 342 343-- array casts 344SELECT ARRAY[1,2,3]::text[]::int[]::float8[] AS "{1,2,3}"; 345SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE"; 346SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}"; 347SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE"; 348SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}"; 349SELECT NULL::text[]::int[] AS "NULL"; 350 351-- scalar op any/all (array) 352select 33 = any ('{1,2,3}'); 353select 33 = any ('{1,2,33}'); 354select 33 = all ('{1,2,33}'); 355select 33 >= all ('{1,2,33}'); 356-- boundary cases 357select null::int >= all ('{1,2,33}'); 358select null::int >= all ('{}'); 359select null::int >= any ('{}'); 360-- cross-datatype 361select 33.4 = any (array[1,2,3]); 362select 33.4 > all (array[1,2,3]); 363-- errors 364select 33 * any ('{1,2,3}'); 365select 33 * any (44); 366-- nulls 367select 33 = any (null::int[]); 368select null::int = any ('{1,2,3}'); 369select 33 = any ('{1,null,3}'); 370select 33 = any ('{1,null,33}'); 371select 33 = all (null::int[]); 372select null::int = all ('{1,2,3}'); 373select 33 = all ('{1,null,3}'); 374select 33 = all ('{33,null,33}'); 375-- nulls later in the bitmap 376SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i))); 377 378-- test indexes on arrays 379create temp table arr_tbl (f1 int[] unique); 380insert into arr_tbl values ('{1,2,3}'); 381insert into arr_tbl values ('{1,2}'); 382-- failure expected: 383insert into arr_tbl values ('{1,2,3}'); 384insert into arr_tbl values ('{2,3,4}'); 385insert into arr_tbl values ('{1,5,3}'); 386insert into arr_tbl values ('{1,2,10}'); 387 388set enable_seqscan to off; 389set enable_bitmapscan to off; 390select * from arr_tbl where f1 > '{1,2,3}' and f1 <= '{1,5,3}'; 391select * from arr_tbl where f1 >= '{1,2,3}' and f1 < '{1,5,3}'; 392 393-- test ON CONFLICT DO UPDATE with arrays 394create temp table arr_pk_tbl (pk int4 primary key, f1 int[]); 395insert into arr_pk_tbl values (1, '{1,2,3}'); 396insert into arr_pk_tbl values (1, '{3,4,5}') on conflict (pk) 397 do update set f1[1] = excluded.f1[1], f1[3] = excluded.f1[3] 398 returning pk, f1; 399insert into arr_pk_tbl(pk, f1[1:2]) values (1, '{6,7,8}') on conflict (pk) 400 do update set f1[1] = excluded.f1[1], 401 f1[2] = excluded.f1[2], 402 f1[3] = excluded.f1[3] 403 returning pk, f1; 404 405-- note: if above selects don't produce the expected tuple order, 406-- then you didn't get an indexscan plan, and something is busted. 407reset enable_seqscan; 408reset enable_bitmapscan; 409 410-- test [not] (like|ilike) (any|all) (...) 411select 'foo' like any (array['%a', '%o']); -- t 412select 'foo' like any (array['%a', '%b']); -- f 413select 'foo' like all (array['f%', '%o']); -- t 414select 'foo' like all (array['f%', '%b']); -- f 415select 'foo' not like any (array['%a', '%b']); -- t 416select 'foo' not like all (array['%a', '%o']); -- f 417select 'foo' ilike any (array['%A', '%O']); -- t 418select 'foo' ilike all (array['F%', '%O']); -- t 419 420-- 421-- General array parser tests 422-- 423 424-- none of the following should be accepted 425select '{{1,{2}},{2,3}}'::text[]; 426select '{{},{}}'::text[]; 427select E'{{1,2},\\{2,3}}'::text[]; 428select '{{"1 2" x},{3}}'::text[]; 429select '{}}'::text[]; 430select '{ }}'::text[]; 431select array[]; 432-- none of the above should be accepted 433 434-- all of the following should be accepted 435select '{}'::text[]; 436select '{{{1,2,3,4},{2,3,4,5}},{{3,4,5,6},{4,5,6,7}}}'::text[]; 437select '{0 second ,0 second}'::interval[]; 438select '{ { "," } , { 3 } }'::text[]; 439select ' { { " 0 second " , 0 second } }'::text[]; 440select '{ 441 0 second, 442 @ 1 hour @ 42 minutes @ 20 seconds 443 }'::interval[]; 444select array[]::text[]; 445select '[0:1]={1.1,2.2}'::float8[]; 446-- all of the above should be accepted 447 448-- tests for array aggregates 449CREATE TEMP TABLE arraggtest ( f1 INT[], f2 TEXT[][], f3 FLOAT[]); 450 451INSERT INTO arraggtest (f1, f2, f3) VALUES 452('{1,2,3,4}','{{grey,red},{blue,blue}}','{1.6, 0.0}'); 453INSERT INTO arraggtest (f1, f2, f3) VALUES 454('{1,2,3}','{{grey,red},{grey,blue}}','{1.6}'); 455SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 456 457INSERT INTO arraggtest (f1, f2, f3) VALUES 458('{3,3,2,4,5,6}','{{white,yellow},{pink,orange}}','{2.1,3.3,1.8,1.7,1.6}'); 459SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 460 461INSERT INTO arraggtest (f1, f2, f3) VALUES 462('{2}','{{black,red},{green,orange}}','{1.6,2.2,2.6,0.4}'); 463SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 464 465INSERT INTO arraggtest (f1, f2, f3) VALUES 466('{4,2,6,7,8,1}','{{red},{black},{purple},{blue},{blue}}',NULL); 467SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 468 469INSERT INTO arraggtest (f1, f2, f3) VALUES 470('{}','{{pink,white,blue,red,grey,orange}}','{2.1,1.87,1.4,2.2}'); 471SELECT max(f1), min(f1), max(f2), min(f2), max(f3), min(f3) FROM arraggtest; 472 473-- A few simple tests for arrays of composite types 474 475create type comptype as (f1 int, f2 text); 476 477create table comptable (c1 comptype, c2 comptype[]); 478 479-- XXX would like to not have to specify row() construct types here ... 480insert into comptable 481 values (row(1,'foo'), array[row(2,'bar')::comptype, row(3,'baz')::comptype]); 482 483-- check that implicitly named array type _comptype isn't a problem 484create type _comptype as enum('fooey'); 485 486select * from comptable; 487select c2[2].f2 from comptable; 488 489drop type _comptype; 490drop table comptable; 491drop type comptype; 492 493create or replace function unnest1(anyarray) 494returns setof anyelement as $$ 495select $1[s] from generate_subscripts($1,1) g(s); 496$$ language sql immutable; 497 498create or replace function unnest2(anyarray) 499returns setof anyelement as $$ 500select $1[s1][s2] from generate_subscripts($1,1) g1(s1), 501 generate_subscripts($1,2) g2(s2); 502$$ language sql immutable; 503 504select * from unnest1(array[1,2,3]); 505select * from unnest2(array[[1,2,3],[4,5,6]]); 506 507drop function unnest1(anyarray); 508drop function unnest2(anyarray); 509 510select array_fill(null::integer, array[3,3],array[2,2]); 511select array_fill(null::integer, array[3,3]); 512select array_fill(null::text, array[3,3],array[2,2]); 513select array_fill(null::text, array[3,3]); 514select array_fill(7, array[3,3],array[2,2]); 515select array_fill(7, array[3,3]); 516select array_fill('juhu'::text, array[3,3],array[2,2]); 517select array_fill('juhu'::text, array[3,3]); 518select a, a = '{}' as is_eq, array_dims(a) 519 from (select array_fill(42, array[0]) as a) ss; 520select a, a = '{}' as is_eq, array_dims(a) 521 from (select array_fill(42, '{}') as a) ss; 522select a, a = '{}' as is_eq, array_dims(a) 523 from (select array_fill(42, '{}', '{}') as a) ss; 524-- raise exception 525select array_fill(1, null, array[2,2]); 526select array_fill(1, array[2,2], null); 527select array_fill(1, array[2,2], '{}'); 528select array_fill(1, array[3,3], array[1,1,1]); 529select array_fill(1, array[1,2,null]); 530select array_fill(1, array[[1,2],[3,4]]); 531 532select string_to_array('1|2|3', '|'); 533select string_to_array('1|2|3|', '|'); 534select string_to_array('1||2|3||', '||'); 535select string_to_array('1|2|3', ''); 536select string_to_array('', '|'); 537select string_to_array('1|2|3', NULL); 538select string_to_array(NULL, '|') IS NULL; 539select string_to_array('abc', ''); 540select string_to_array('abc', '', 'abc'); 541select string_to_array('abc', ','); 542select string_to_array('abc', ',', 'abc'); 543select string_to_array('1,2,3,4,,6', ','); 544select string_to_array('1,2,3,4,,6', ',', ''); 545select string_to_array('1,2,3,4,*,6', ',', '*'); 546 547select array_to_string(NULL::int4[], ',') IS NULL; 548select array_to_string('{}'::int4[], ','); 549select array_to_string(array[1,2,3,4,NULL,6], ','); 550select array_to_string(array[1,2,3,4,NULL,6], ',', '*'); 551select array_to_string(array[1,2,3,4,NULL,6], NULL); 552select array_to_string(array[1,2,3,4,NULL,6], ',', NULL); 553 554select array_to_string(string_to_array('1|2|3', '|'), '|'); 555 556select array_length(array[1,2,3], 1); 557select array_length(array[[1,2,3], [4,5,6]], 0); 558select array_length(array[[1,2,3], [4,5,6]], 1); 559select array_length(array[[1,2,3], [4,5,6]], 2); 560select array_length(array[[1,2,3], [4,5,6]], 3); 561 562select cardinality(NULL::int[]); 563select cardinality('{}'::int[]); 564select cardinality(array[1,2,3]); 565select cardinality('[2:4]={5,6,7}'::int[]); 566select cardinality('{{1,2}}'::int[]); 567select cardinality('{{1,2},{3,4},{5,6}}'::int[]); 568select cardinality('{{{1,9},{5,6}},{{2,3},{3,4}}}'::int[]); 569 570-- array_agg(anynonarray) 571select array_agg(unique1) from (select unique1 from tenk1 where unique1 < 15 order by unique1) ss; 572select array_agg(ten) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; 573select array_agg(nullif(ten, 4)) from (select ten from tenk1 where unique1 < 15 order by unique1) ss; 574select array_agg(unique1) from tenk1 where unique1 < -15; 575 576-- array_agg(anyarray) 577select array_agg(ar) 578 from (values ('{1,2}'::int[]), ('{3,4}'::int[])) v(ar); 579select array_agg(distinct ar order by ar desc) 580 from (select array[i / 2] from generate_series(1,10) a(i)) b(ar); 581select array_agg(ar) 582 from (select array_agg(array[i, i+1, i-1]) 583 from generate_series(1,2) a(i)) b(ar); 584select array_agg(array[i+1.2, i+1.3, i+1.4]) from generate_series(1,3) g(i); 585select array_agg(array['Hello', i::text]) from generate_series(9,11) g(i); 586select array_agg(array[i, nullif(i, 3), i+1]) from generate_series(1,4) g(i); 587-- errors 588select array_agg('{}'::int[]) from generate_series(1,2); 589select array_agg(null::int[]) from generate_series(1,2); 590select array_agg(ar) 591 from (values ('{1,2}'::int[]), ('{3}'::int[])) v(ar); 592 593select unnest(array[1,2,3]); 594select * from unnest(array[1,2,3]); 595select unnest(array[1,2,3,4.5]::float8[]); 596select unnest(array[1,2,3,4.5]::numeric[]); 597select unnest(array[1,2,3,null,4,null,null,5,6]); 598select unnest(array[1,2,3,null,4,null,null,5,6]::text[]); 599select abs(unnest(array[1,2,null,-3])); 600select array_remove(array[1,2,2,3], 2); 601select array_remove(array[1,2,2,3], 5); 602select array_remove(array[1,NULL,NULL,3], NULL); 603select array_remove(array['A','CC','D','C','RR'], 'RR'); 604select array_remove('{{1,2,2},{1,4,3}}', 2); -- not allowed 605select array_remove(array['X','X','X'], 'X') = '{}'; 606select array_replace(array[1,2,5,4],5,3); 607select array_replace(array[1,2,5,4],5,NULL); 608select array_replace(array[1,2,NULL,4,NULL],NULL,5); 609select array_replace(array['A','B','DD','B'],'B','CC'); 610select array_replace(array[1,NULL,3],NULL,NULL); 611select array_replace(array['AB',NULL,'CDE'],NULL,'12'); 612 613-- array(select array-value ...) 614select array(select array[i,i/2] from generate_series(1,5) i); 615select array(select array['Hello', i::text] from generate_series(9,11) i); 616 617-- Insert/update on a column that is array of composite 618 619create temp table t1 (f1 int8_tbl[]); 620insert into t1 (f1[5].q1) values(42); 621select * from t1; 622update t1 set f1[5].q2 = 43; 623select * from t1; 624 625-- Check that arrays of composites are safely detoasted when needed 626 627create temp table src (f1 text); 628insert into src 629 select string_agg(random()::text,'') from generate_series(1,10000); 630create type textandtext as (c1 text, c2 text); 631create temp table dest (f1 textandtext[]); 632insert into dest select array[row(f1,f1)::textandtext] from src; 633select length(md5((f1[1]).c2)) from dest; 634delete from src; 635select length(md5((f1[1]).c2)) from dest; 636truncate table src; 637drop table src; 638select length(md5((f1[1]).c2)) from dest; 639drop table dest; 640drop type textandtext; 641 642-- Tests for polymorphic-array form of width_bucket() 643 644-- this exercises the varwidth and float8 code paths 645SELECT 646 op, 647 width_bucket(op::numeric, ARRAY[1, 3, 5, 10.0]::numeric[]) AS wb_n1, 648 width_bucket(op::numeric, ARRAY[0, 5.5, 9.99]::numeric[]) AS wb_n2, 649 width_bucket(op::numeric, ARRAY[-6, -5, 2.0]::numeric[]) AS wb_n3, 650 width_bucket(op::float8, ARRAY[1, 3, 5, 10.0]::float8[]) AS wb_f1, 651 width_bucket(op::float8, ARRAY[0, 5.5, 9.99]::float8[]) AS wb_f2, 652 width_bucket(op::float8, ARRAY[-6, -5, 2.0]::float8[]) AS wb_f3 653FROM (VALUES 654 (-5.2), 655 (-0.0000000001), 656 (0.000000000001), 657 (1), 658 (1.99999999999999), 659 (2), 660 (2.00000000000001), 661 (3), 662 (4), 663 (4.5), 664 (5), 665 (5.5), 666 (6), 667 (7), 668 (8), 669 (9), 670 (9.99999999999999), 671 (10), 672 (10.0000000000001) 673) v(op); 674 675-- ensure float8 path handles NaN properly 676SELECT 677 op, 678 width_bucket(op, ARRAY[1, 3, 9, 'NaN', 'NaN']::float8[]) AS wb 679FROM (VALUES 680 (-5.2::float8), 681 (4::float8), 682 (77::float8), 683 ('NaN'::float8) 684) v(op); 685 686-- these exercise the generic fixed-width code path 687SELECT 688 op, 689 width_bucket(op, ARRAY[1, 3, 5, 10]) AS wb_1 690FROM generate_series(0,11) as op; 691 692SELECT width_bucket(now(), 693 array['yesterday', 'today', 'tomorrow']::timestamptz[]); 694 695-- corner cases 696SELECT width_bucket(5, ARRAY[3]); 697SELECT width_bucket(5, '{}'); 698 699-- error cases 700SELECT width_bucket('5'::text, ARRAY[3, 4]::integer[]); 701SELECT width_bucket(5, ARRAY[3, 4, NULL]); 702SELECT width_bucket(5, ARRAY[ARRAY[1, 2], ARRAY[3, 4]]); 703