1set enable_seqscan=off; 2set enable_sort=off; 3/* 4 * Complete checks for int2[]. 5 */ 6CREATE TABLE test_array ( 7 i int2[] 8); 9INSERT INTO test_array VALUES ('{}'), ('{0}'), ('{1,2,3,4}'), ('{1,2,3}'), ('{1,2}'), ('{1}'); 10CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 11SELECT NULL::int[] = '{1}'; 12 ?column? 13---------- 14 15(1 row) 16 17SELECT NULL::int[] && '{1}'; 18 ?column? 19---------- 20 21(1 row) 22 23SELECT NULL::int[] @> '{1}'; 24 ?column? 25---------- 26 27(1 row) 28 29SELECT NULL::int[] <@ '{1}'; 30 ?column? 31---------- 32 33(1 row) 34 35SELECT NULL::int[] % '{1}'; 36 ?column? 37---------- 38 39(1 row) 40 41SELECT NULL::int[] <=> '{1}'; 42 ?column? 43---------- 44 45(1 row) 46 47INSERT INTO test_array VALUES (NULL); 48SELECT * FROM test_array WHERE i = '{1}'; 49 i 50----- 51 {1} 52(1 row) 53 54DELETE FROM test_array WHERE i IS NULL; 55SELECT * FROM test_array WHERE i = '{NULL}'; 56ERROR: array must not contain nulls 57SELECT * FROM test_array WHERE i = '{1,2,3,NULL}'; 58ERROR: array must not contain nulls 59SELECT * FROM test_array WHERE i = '{{1,2},{3,4}}'; 60ERROR: array must have 1 dimension 61EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 62 QUERY PLAN 63------------------------------------------ 64 Index Scan using idx_array on test_array 65 Index Cond: (i = '{}'::smallint[]) 66(2 rows) 67 68SELECT * FROM test_array WHERE i = '{}'; 69 i 70---- 71 {} 72(1 row) 73 74SELECT * FROM test_array WHERE i = '{0}'; 75 i 76----- 77 {0} 78(1 row) 79 80SELECT * FROM test_array WHERE i = '{1}'; 81 i 82----- 83 {1} 84(1 row) 85 86SELECT * FROM test_array WHERE i = '{1,2}'; 87 i 88------- 89 {1,2} 90(1 row) 91 92SELECT * FROM test_array WHERE i = '{2,1}'; 93 i 94--- 95(0 rows) 96 97SELECT * FROM test_array WHERE i = '{1,2,3,3}'; 98 i 99--- 100(0 rows) 101 102SELECT * FROM test_array WHERE i = '{0,0}'; 103 i 104--- 105(0 rows) 106 107SELECT * FROM test_array WHERE i = '{100}'; 108 i 109--- 110(0 rows) 111 112EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 113 QUERY PLAN 114------------------------------------------ 115 Index Scan using idx_array on test_array 116 Index Cond: (i && '{}'::smallint[]) 117(2 rows) 118 119SELECT * FROM test_array WHERE i && '{}'; 120 i 121--- 122(0 rows) 123 124SELECT * FROM test_array WHERE i && '{1}'; 125 i 126----------- 127 {1,2,3,4} 128 {1,2,3} 129 {1,2} 130 {1} 131(4 rows) 132 133SELECT * FROM test_array WHERE i && '{2}'; 134 i 135----------- 136 {1,2,3,4} 137 {1,2,3} 138 {1,2} 139(3 rows) 140 141SELECT * FROM test_array WHERE i && '{3}'; 142 i 143----------- 144 {1,2,3,4} 145 {1,2,3} 146(2 rows) 147 148SELECT * FROM test_array WHERE i && '{4}'; 149 i 150----------- 151 {1,2,3,4} 152(1 row) 153 154SELECT * FROM test_array WHERE i && '{1,2}'; 155 i 156----------- 157 {1,2,3,4} 158 {1,2,3} 159 {1,2} 160 {1} 161(4 rows) 162 163SELECT * FROM test_array WHERE i && '{1,2,3}'; 164 i 165----------- 166 {1,2,3,4} 167 {1,2,3} 168 {1,2} 169 {1} 170(4 rows) 171 172SELECT * FROM test_array WHERE i && '{1,2,3,4}'; 173 i 174----------- 175 {1,2,3,4} 176 {1,2,3} 177 {1,2} 178 {1} 179(4 rows) 180 181SELECT * FROM test_array WHERE i && '{4,3,2,1}'; 182 i 183----------- 184 {1,2,3,4} 185 {1,2,3} 186 {1,2} 187 {1} 188(4 rows) 189 190SELECT * FROM test_array WHERE i && '{0,0}'; 191 i 192----- 193 {0} 194(1 row) 195 196SELECT * FROM test_array WHERE i && '{100}'; 197 i 198--- 199(0 rows) 200 201EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 202 QUERY PLAN 203------------------------------------------ 204 Index Scan using idx_array on test_array 205 Index Cond: (i @> '{}'::smallint[]) 206(2 rows) 207 208SELECT * FROM test_array WHERE i @> '{}'; 209 i 210----------- 211 {} 212 {0} 213 {1,2,3,4} 214 {1,2,3} 215 {1,2} 216 {1} 217(6 rows) 218 219SELECT * FROM test_array WHERE i @> '{1}'; 220 i 221----------- 222 {1,2,3,4} 223 {1,2,3} 224 {1,2} 225 {1} 226(4 rows) 227 228SELECT * FROM test_array WHERE i @> '{2}'; 229 i 230----------- 231 {1,2,3,4} 232 {1,2,3} 233 {1,2} 234(3 rows) 235 236SELECT * FROM test_array WHERE i @> '{3}'; 237 i 238----------- 239 {1,2,3,4} 240 {1,2,3} 241(2 rows) 242 243SELECT * FROM test_array WHERE i @> '{4}'; 244 i 245----------- 246 {1,2,3,4} 247(1 row) 248 249SELECT * FROM test_array WHERE i @> '{1,2,4}'; 250 i 251----------- 252 {1,2,3,4} 253(1 row) 254 255SELECT * FROM test_array WHERE i @> '{1,2,3,4}'; 256 i 257----------- 258 {1,2,3,4} 259(1 row) 260 261SELECT * FROM test_array WHERE i @> '{4,3,2,1}'; 262 i 263----------- 264 {1,2,3,4} 265(1 row) 266 267SELECT * FROM test_array WHERE i @> '{0,0}'; 268 i 269----- 270 {0} 271(1 row) 272 273SELECT * FROM test_array WHERE i @> '{100}'; 274 i 275--- 276(0 rows) 277 278EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 279 QUERY PLAN 280------------------------------------------ 281 Index Scan using idx_array on test_array 282 Index Cond: (i <@ '{}'::smallint[]) 283(2 rows) 284 285SELECT * FROM test_array WHERE i <@ '{}'; 286 i 287---- 288 {} 289(1 row) 290 291SELECT * FROM test_array WHERE i <@ '{1}'; 292 i 293----- 294 {} 295 {1} 296(2 rows) 297 298SELECT * FROM test_array WHERE i <@ '{2}'; 299 i 300---- 301 {} 302(1 row) 303 304SELECT * FROM test_array WHERE i <@ '{1,2,4}'; 305 i 306------- 307 {} 308 {1,2} 309 {1} 310(3 rows) 311 312SELECT * FROM test_array WHERE i <@ '{1,2,3,4}'; 313 i 314----------- 315 {} 316 {1,2,3,4} 317 {1,2,3} 318 {1,2} 319 {1} 320(5 rows) 321 322SELECT * FROM test_array WHERE i <@ '{4,3,2,1}'; 323 i 324----------- 325 {} 326 {1,2,3,4} 327 {1,2,3} 328 {1,2} 329 {1} 330(5 rows) 331 332SELECT * FROM test_array WHERE i <@ '{0,0}'; 333 i 334----- 335 {} 336 {0} 337(2 rows) 338 339SELECT * FROM test_array WHERE i <@ '{100}'; 340 i 341---- 342 {} 343(1 row) 344 345EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 346 QUERY PLAN 347------------------------------------------ 348 Index Scan using idx_array on test_array 349 Index Cond: (i % '{}'::smallint[]) 350(2 rows) 351 352SELECT * FROM test_array WHERE i % '{}'; 353 i 354--- 355(0 rows) 356 357SELECT * FROM test_array WHERE i % '{1}'; 358 i 359----------- 360 {1,2,3,4} 361 {1,2,3} 362 {1,2} 363 {1} 364(4 rows) 365 366SELECT * FROM test_array WHERE i % '{2}'; 367 i 368----------- 369 {1,2,3,4} 370 {1,2,3} 371 {1,2} 372(3 rows) 373 374SELECT * FROM test_array WHERE i % '{1,2}'; 375 i 376----------- 377 {1,2,3,4} 378 {1,2,3} 379 {1,2} 380 {1} 381(4 rows) 382 383SELECT * FROM test_array WHERE i % '{1,2,4}'; 384 i 385----------- 386 {1,2,3,4} 387 {1,2,3} 388 {1,2} 389 {1} 390(4 rows) 391 392SELECT * FROM test_array WHERE i % '{1,2,3,4}'; 393 i 394----------- 395 {1,2,3,4} 396 {1,2,3} 397 {1,2} 398 {1} 399(4 rows) 400 401SELECT * FROM test_array WHERE i % '{4,3,2,1}'; 402 i 403----------- 404 {1,2,3,4} 405 {1,2,3} 406 {1,2} 407 {1} 408(4 rows) 409 410SELECT * FROM test_array WHERE i % '{1,2,3,4,5}'; 411 i 412----------- 413 {1,2,3,4} 414 {1,2,3} 415 {1,2} 416(3 rows) 417 418SELECT * FROM test_array WHERE i % '{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}'; 419 i 420----------- 421 {1,2,3,4} 422(1 row) 423 424SELECT * FROM test_array WHERE i % '{1,10,20,30,40,50}'; 425 i 426--- 427(0 rows) 428 429SELECT * FROM test_array WHERE i % '{1,10,20,30}'; 430 i 431----- 432 {1} 433(1 row) 434 435SELECT * FROM test_array WHERE i % '{1,1,1,1,1}'; 436 i 437----------- 438 {1,2,3,4} 439 {1,2,3} 440 {1,2} 441 {1} 442(4 rows) 443 444SELECT * FROM test_array WHERE i % '{0,0}'; 445 i 446----- 447 {0} 448(1 row) 449 450SELECT * FROM test_array WHERE i % '{100}'; 451 i 452--- 453(0 rows) 454 455EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC; 456 QUERY PLAN 457------------------------------------------ 458 Index Scan using idx_array on test_array 459 Index Cond: (i && '{1}'::smallint[]) 460 Order By: (i <=> '{1}'::smallint[]) 461(3 rows) 462 463SELECT * FROM test_array WHERE i && '{1}' ORDER BY i <=> '{1}' ASC; 464 i 465----------- 466 {1} 467 {1,2} 468 {1,2,3} 469 {1,2,3,4} 470(4 rows) 471 472DROP INDEX idx_array; 473ALTER TABLE test_array ADD COLUMN add_info timestamp; 474CREATE INDEX idx_array ON test_array 475USING rum (i rum_anyarray_addon_ops, add_info) 476WITH (attach = 'add_info', to = 'i'); 477WITH q as ( 478 SELECT row_number() OVER (ORDER BY i) idx, ctid FROM test_array 479) 480UPDATE test_array SET add_info = '2016-05-16 14:21:25'::timestamp + 481 format('%s days', q.idx)::interval 482FROM q WHERE test_array.ctid = q.ctid; 483EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 484 QUERY PLAN 485------------------------------------------ 486 Index Scan using idx_array on test_array 487 Index Cond: (i = '{}'::smallint[]) 488(2 rows) 489 490EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 491 QUERY PLAN 492------------------------------------------ 493 Index Scan using idx_array on test_array 494 Index Cond: (i && '{}'::smallint[]) 495(2 rows) 496 497EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 498 QUERY PLAN 499------------------------------------------ 500 Index Scan using idx_array on test_array 501 Index Cond: (i @> '{}'::smallint[]) 502(2 rows) 503 504EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 505 QUERY PLAN 506------------------------------------------ 507 Index Scan using idx_array on test_array 508 Index Cond: (i <@ '{}'::smallint[]) 509(2 rows) 510 511EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 512 QUERY PLAN 513---------------------------------- 514 Seq Scan on test_array 515 Filter: (i % '{}'::smallint[]) 516(2 rows) 517 518EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{1}' ORDER BY add_info <=> '2016-05-16 14:21:25' LIMIT 10; 519 QUERY PLAN 520------------------------------------------------------------------------------------------ 521 Limit 522 -> Index Scan using idx_array on test_array 523 Index Cond: (i && '{1}'::smallint[]) 524 Order By: (add_info <=> 'Mon May 16 14:21:25 2016'::timestamp without time zone) 525(4 rows) 526 527SELECT * FROM test_array WHERE i && '{1}' ORDER BY add_info <=> '2016-05-16 14:21:25' LIMIT 10; 528ERROR: doesn't support order by over pass-by-reference column 529DROP INDEX idx_array; 530/* 531 * Sanity checks for popular array types. 532 */ 533ALTER TABLE test_array ALTER COLUMN i TYPE int4[]; 534CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 535EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 536 QUERY PLAN 537------------------------------------------ 538 Index Scan using idx_array on test_array 539 Index Cond: (i = '{}'::integer[]) 540(2 rows) 541 542EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 543 QUERY PLAN 544------------------------------------------ 545 Index Scan using idx_array on test_array 546 Index Cond: (i && '{}'::integer[]) 547(2 rows) 548 549EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 550 QUERY PLAN 551------------------------------------------ 552 Index Scan using idx_array on test_array 553 Index Cond: (i @> '{}'::integer[]) 554(2 rows) 555 556EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 557 QUERY PLAN 558------------------------------------------ 559 Index Scan using idx_array on test_array 560 Index Cond: (i <@ '{}'::integer[]) 561(2 rows) 562 563EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 564 QUERY PLAN 565------------------------------------------ 566 Index Scan using idx_array on test_array 567 Index Cond: (i % '{}'::integer[]) 568(2 rows) 569 570DROP INDEX idx_array; 571ALTER TABLE test_array ALTER COLUMN i TYPE int8[]; 572CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 573EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 574 QUERY PLAN 575------------------------------------------ 576 Index Scan using idx_array on test_array 577 Index Cond: (i = '{}'::bigint[]) 578(2 rows) 579 580EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 581 QUERY PLAN 582------------------------------------------ 583 Index Scan using idx_array on test_array 584 Index Cond: (i && '{}'::bigint[]) 585(2 rows) 586 587EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 588 QUERY PLAN 589------------------------------------------ 590 Index Scan using idx_array on test_array 591 Index Cond: (i @> '{}'::bigint[]) 592(2 rows) 593 594EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 595 QUERY PLAN 596------------------------------------------ 597 Index Scan using idx_array on test_array 598 Index Cond: (i <@ '{}'::bigint[]) 599(2 rows) 600 601EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 602 QUERY PLAN 603------------------------------------------ 604 Index Scan using idx_array on test_array 605 Index Cond: (i % '{}'::bigint[]) 606(2 rows) 607 608DROP INDEX idx_array; 609ALTER TABLE test_array ALTER COLUMN i TYPE text[]; 610CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 611EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 612 QUERY PLAN 613------------------------------------------ 614 Index Scan using idx_array on test_array 615 Index Cond: (i = '{}'::text[]) 616(2 rows) 617 618EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 619 QUERY PLAN 620------------------------------------------ 621 Index Scan using idx_array on test_array 622 Index Cond: (i && '{}'::text[]) 623(2 rows) 624 625EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 626 QUERY PLAN 627------------------------------------------ 628 Index Scan using idx_array on test_array 629 Index Cond: (i @> '{}'::text[]) 630(2 rows) 631 632EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 633 QUERY PLAN 634------------------------------------------ 635 Index Scan using idx_array on test_array 636 Index Cond: (i <@ '{}'::text[]) 637(2 rows) 638 639EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 640 QUERY PLAN 641------------------------------------------ 642 Index Scan using idx_array on test_array 643 Index Cond: (i % '{}'::text[]) 644(2 rows) 645 646DROP INDEX idx_array; 647ALTER TABLE test_array ALTER COLUMN i TYPE varchar[]; 648CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 649EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 650 QUERY PLAN 651----------------------------------------------- 652 Index Scan using idx_array on test_array 653 Index Cond: (i = '{}'::character varying[]) 654(2 rows) 655 656EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 657 QUERY PLAN 658------------------------------------------------ 659 Index Scan using idx_array on test_array 660 Index Cond: (i && '{}'::character varying[]) 661(2 rows) 662 663EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 664 QUERY PLAN 665------------------------------------------------ 666 Index Scan using idx_array on test_array 667 Index Cond: (i @> '{}'::character varying[]) 668(2 rows) 669 670EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 671 QUERY PLAN 672------------------------------------------------ 673 Index Scan using idx_array on test_array 674 Index Cond: (i <@ '{}'::character varying[]) 675(2 rows) 676 677EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 678 QUERY PLAN 679----------------------------------------------- 680 Index Scan using idx_array on test_array 681 Index Cond: (i % '{}'::character varying[]) 682(2 rows) 683 684DROP INDEX idx_array; 685ALTER TABLE test_array ALTER COLUMN i TYPE char[]; 686CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 687EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 688 QUERY PLAN 689------------------------------------------ 690 Index Scan using idx_array on test_array 691 Index Cond: (i = '{}'::bpchar[]) 692(2 rows) 693 694EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 695 QUERY PLAN 696------------------------------------------ 697 Index Scan using idx_array on test_array 698 Index Cond: (i && '{}'::bpchar[]) 699(2 rows) 700 701EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 702 QUERY PLAN 703------------------------------------------ 704 Index Scan using idx_array on test_array 705 Index Cond: (i @> '{}'::bpchar[]) 706(2 rows) 707 708EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 709 QUERY PLAN 710------------------------------------------ 711 Index Scan using idx_array on test_array 712 Index Cond: (i <@ '{}'::bpchar[]) 713(2 rows) 714 715EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 716 QUERY PLAN 717------------------------------------------ 718 Index Scan using idx_array on test_array 719 Index Cond: (i % '{}'::bpchar[]) 720(2 rows) 721 722DROP INDEX idx_array; 723ALTER TABLE test_array ALTER COLUMN i TYPE numeric[] USING i::numeric[]; 724CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 725EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 726 QUERY PLAN 727------------------------------------------ 728 Index Scan using idx_array on test_array 729 Index Cond: (i = '{}'::numeric[]) 730(2 rows) 731 732EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 733 QUERY PLAN 734------------------------------------------ 735 Index Scan using idx_array on test_array 736 Index Cond: (i && '{}'::numeric[]) 737(2 rows) 738 739EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 740 QUERY PLAN 741------------------------------------------ 742 Index Scan using idx_array on test_array 743 Index Cond: (i @> '{}'::numeric[]) 744(2 rows) 745 746EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 747 QUERY PLAN 748------------------------------------------ 749 Index Scan using idx_array on test_array 750 Index Cond: (i <@ '{}'::numeric[]) 751(2 rows) 752 753EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 754 QUERY PLAN 755------------------------------------------ 756 Index Scan using idx_array on test_array 757 Index Cond: (i % '{}'::numeric[]) 758(2 rows) 759 760DROP INDEX idx_array; 761ALTER TABLE test_array ALTER COLUMN i TYPE float4[] USING i::float4[]; 762CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 763EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 764 QUERY PLAN 765------------------------------------------ 766 Index Scan using idx_array on test_array 767 Index Cond: (i = '{}'::real[]) 768(2 rows) 769 770EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 771 QUERY PLAN 772------------------------------------------ 773 Index Scan using idx_array on test_array 774 Index Cond: (i && '{}'::real[]) 775(2 rows) 776 777EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 778 QUERY PLAN 779------------------------------------------ 780 Index Scan using idx_array on test_array 781 Index Cond: (i @> '{}'::real[]) 782(2 rows) 783 784EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 785 QUERY PLAN 786------------------------------------------ 787 Index Scan using idx_array on test_array 788 Index Cond: (i <@ '{}'::real[]) 789(2 rows) 790 791EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 792 QUERY PLAN 793------------------------------------------ 794 Index Scan using idx_array on test_array 795 Index Cond: (i % '{}'::real[]) 796(2 rows) 797 798DROP INDEX idx_array; 799ALTER TABLE test_array ALTER COLUMN i TYPE float8[] USING i::float8[]; 800CREATE INDEX idx_array ON test_array USING rum (i rum_anyarray_ops); 801EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i = '{}'; 802 QUERY PLAN 803---------------------------------------------- 804 Index Scan using idx_array on test_array 805 Index Cond: (i = '{}'::double precision[]) 806(2 rows) 807 808EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i && '{}'; 809 QUERY PLAN 810----------------------------------------------- 811 Index Scan using idx_array on test_array 812 Index Cond: (i && '{}'::double precision[]) 813(2 rows) 814 815EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i @> '{}'; 816 QUERY PLAN 817----------------------------------------------- 818 Index Scan using idx_array on test_array 819 Index Cond: (i @> '{}'::double precision[]) 820(2 rows) 821 822EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i <@ '{}'; 823 QUERY PLAN 824----------------------------------------------- 825 Index Scan using idx_array on test_array 826 Index Cond: (i <@ '{}'::double precision[]) 827(2 rows) 828 829EXPLAIN (COSTS OFF) SELECT * FROM test_array WHERE i % '{}'; 830 QUERY PLAN 831---------------------------------------------- 832 Index Scan using idx_array on test_array 833 Index Cond: (i % '{}'::double precision[]) 834(2 rows) 835 836DROP INDEX idx_array; 837/* 838 * Check ordering using distance operator 839 */ 840CREATE TABLE test_array_order ( 841 i int2[] 842); 843\copy test_array_order(i) from 'data/rum_array.data'; 844CREATE INDEX idx_array_order ON test_array_order USING rum (i rum_anyarray_ops); 845EXPLAIN (COSTS OFF) 846SELECT *, i <=> '{51}' from test_array_order WHERE i @> '{23,20}' order by i <=> '{51}'; 847 QUERY PLAN 848------------------------------------------------------ 849 Index Scan using idx_array_order on test_array_order 850 Index Cond: (i @> '{23,20}'::smallint[]) 851 Order By: (i <=> '{51}'::smallint[]) 852(3 rows) 853 854SELECT i, 855 CASE WHEN distance = 'Infinity' THEN -1 856 ELSE distance::numeric(18,14) 857 END distance 858 FROM 859 (SELECT *, (i <=> '{51}') AS distance 860 FROM test_array_order WHERE i @> '{23,20}' ORDER BY i <=> '{51}') t; 861 i | distance 862---------------------+------------------ 863 {20,23,51} | 1.73205080756888 864 {33,51,20,77,23,65} | 2.44948974278318 865 {23,76,34,23,2,20} | -1 866 {20,60,45,23,29} | -1 867 {23,89,38,20,40,95} | -1 868 {23,20,72} | -1 869 {73,23,20} | -1 870 {6,97,20,89,23} | -1 871 {20,98,30,23,1,66} | -1 872 {57,23,39,46,50,20} | -1 873 {81,20,26,22,23} | -1 874 {18,23,10,90,15,20} | -1 875(12 rows) 876 877