1-- Tests for range data types. 2create type textrange as range (subtype=text, collation="C"); 3-- 4-- test input parser 5-- 6-- negative tests; should fail 7select ''::textrange; 8ERROR: malformed range literal: "" 9LINE 1: select ''::textrange; 10 ^ 11DETAIL: Missing left parenthesis or bracket. 12select '-[a,z)'::textrange; 13ERROR: malformed range literal: "-[a,z)" 14LINE 1: select '-[a,z)'::textrange; 15 ^ 16DETAIL: Missing left parenthesis or bracket. 17select '[a,z) - '::textrange; 18ERROR: malformed range literal: "[a,z) - " 19LINE 1: select '[a,z) - '::textrange; 20 ^ 21DETAIL: Junk after right parenthesis or bracket. 22select '(",a)'::textrange; 23ERROR: malformed range literal: "(",a)" 24LINE 1: select '(",a)'::textrange; 25 ^ 26DETAIL: Unexpected end of input. 27select '(,,a)'::textrange; 28ERROR: malformed range literal: "(,,a)" 29LINE 1: select '(,,a)'::textrange; 30 ^ 31DETAIL: Too many commas. 32select '(),a)'::textrange; 33ERROR: malformed range literal: "(),a)" 34LINE 1: select '(),a)'::textrange; 35 ^ 36DETAIL: Missing comma after lower bound. 37select '(a,))'::textrange; 38ERROR: malformed range literal: "(a,))" 39LINE 1: select '(a,))'::textrange; 40 ^ 41DETAIL: Junk after right parenthesis or bracket. 42select '(],a)'::textrange; 43ERROR: malformed range literal: "(],a)" 44LINE 1: select '(],a)'::textrange; 45 ^ 46DETAIL: Missing comma after lower bound. 47select '(a,])'::textrange; 48ERROR: malformed range literal: "(a,])" 49LINE 1: select '(a,])'::textrange; 50 ^ 51DETAIL: Junk after right parenthesis or bracket. 52select '[z,a]'::textrange; 53ERROR: range lower bound must be less than or equal to range upper bound 54LINE 1: select '[z,a]'::textrange; 55 ^ 56-- should succeed 57select ' empty '::textrange; 58 textrange 59----------- 60 empty 61(1 row) 62 63select ' ( empty, empty ) '::textrange; 64 textrange 65---------------------- 66 (" empty"," empty ") 67(1 row) 68 69select ' ( " a " " a ", " z " " z " ) '::textrange; 70 textrange 71-------------------------- 72 (" a a "," z z ") 73(1 row) 74 75select '(,z)'::textrange; 76 textrange 77----------- 78 (,z) 79(1 row) 80 81select '(a,)'::textrange; 82 textrange 83----------- 84 (a,) 85(1 row) 86 87select '[,z]'::textrange; 88 textrange 89----------- 90 (,z] 91(1 row) 92 93select '[a,]'::textrange; 94 textrange 95----------- 96 [a,) 97(1 row) 98 99select '(,)'::textrange; 100 textrange 101----------- 102 (,) 103(1 row) 104 105select '[ , ]'::textrange; 106 textrange 107----------- 108 [" "," "] 109(1 row) 110 111select '["",""]'::textrange; 112 textrange 113----------- 114 ["",""] 115(1 row) 116 117select '[",",","]'::textrange; 118 textrange 119----------- 120 [",",","] 121(1 row) 122 123select '["\\","\\"]'::textrange; 124 textrange 125------------- 126 ["\\","\\"] 127(1 row) 128 129select '(\\,a)'::textrange; 130 textrange 131----------- 132 ("\\",a) 133(1 row) 134 135select '((,z)'::textrange; 136 textrange 137----------- 138 ("(",z) 139(1 row) 140 141select '([,z)'::textrange; 142 textrange 143----------- 144 ("[",z) 145(1 row) 146 147select '(!,()'::textrange; 148 textrange 149----------- 150 (!,"(") 151(1 row) 152 153select '(!,[)'::textrange; 154 textrange 155----------- 156 (!,"[") 157(1 row) 158 159select '[a,a]'::textrange; 160 textrange 161----------- 162 [a,a] 163(1 row) 164 165-- these are allowed but normalize to empty: 166select '[a,a)'::textrange; 167 textrange 168----------- 169 empty 170(1 row) 171 172select '(a,a]'::textrange; 173 textrange 174----------- 175 empty 176(1 row) 177 178select '(a,a)'::textrange; 179 textrange 180----------- 181 empty 182(1 row) 183 184-- 185-- create some test data and test the operators 186-- 187CREATE TABLE numrange_test (nr NUMRANGE); 188create index numrange_test_btree on numrange_test(nr); 189INSERT INTO numrange_test VALUES('[,)'); 190INSERT INTO numrange_test VALUES('[3,]'); 191INSERT INTO numrange_test VALUES('[, 5)'); 192INSERT INTO numrange_test VALUES(numrange(1.1, 2.2)); 193INSERT INTO numrange_test VALUES('empty'); 194INSERT INTO numrange_test VALUES(numrange(1.7, 1.7, '[]')); 195SELECT nr, isempty(nr), lower(nr), upper(nr) FROM numrange_test; 196 nr | isempty | lower | upper 197-----------+---------+-------+------- 198 (,) | f | | 199 [3,) | f | 3 | 200 (,5) | f | | 5 201 [1.1,2.2) | f | 1.1 | 2.2 202 empty | t | | 203 [1.7,1.7] | f | 1.7 | 1.7 204(6 rows) 205 206SELECT nr, lower_inc(nr), lower_inf(nr), upper_inc(nr), upper_inf(nr) FROM numrange_test; 207 nr | lower_inc | lower_inf | upper_inc | upper_inf 208-----------+-----------+-----------+-----------+----------- 209 (,) | f | t | f | t 210 [3,) | t | f | f | t 211 (,5) | f | t | f | f 212 [1.1,2.2) | t | f | f | f 213 empty | f | f | f | f 214 [1.7,1.7] | t | f | t | f 215(6 rows) 216 217SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); 218 nr 219----------- 220 (,) 221 (,5) 222 [1.1,2.2) 223(3 rows) 224 225SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1); 226 nr 227----- 228 (,) 229(1 row) 230 231SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr); 232 nr 233------ 234 (,) 235 (,5) 236(2 rows) 237 238SELECT * FROM numrange_test WHERE 1.9 <@ nr; 239 nr 240----------- 241 (,) 242 (,5) 243 [1.1,2.2) 244(3 rows) 245 246select * from numrange_test where nr = 'empty'; 247 nr 248------- 249 empty 250(1 row) 251 252select * from numrange_test where nr = '(1.1, 2.2)'; 253 nr 254---- 255(0 rows) 256 257select * from numrange_test where nr = '[1.1, 2.2)'; 258 nr 259----------- 260 [1.1,2.2) 261(1 row) 262 263select * from numrange_test where nr < 'empty'; 264 nr 265---- 266(0 rows) 267 268select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); 269 nr 270------- 271 (,) 272 (,5) 273 empty 274(3 rows) 275 276select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); 277 nr 278------- 279 (,) 280 (,5) 281 empty 282(3 rows) 283 284select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); 285 nr 286----------- 287 (,) 288 [3,) 289 (,5) 290 [1.1,2.2) 291 empty 292 [1.7,1.7] 293(6 rows) 294 295select * from numrange_test where nr <= 'empty'; 296 nr 297------- 298 empty 299(1 row) 300 301select * from numrange_test where nr >= 'empty'; 302 nr 303----------- 304 (,) 305 [3,) 306 (,5) 307 [1.1,2.2) 308 empty 309 [1.7,1.7] 310(6 rows) 311 312select * from numrange_test where nr > 'empty'; 313 nr 314----------- 315 (,) 316 [3,) 317 (,5) 318 [1.1,2.2) 319 [1.7,1.7] 320(5 rows) 321 322select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); 323 nr 324----------- 325 [3,) 326 [1.1,2.2) 327 [1.7,1.7] 328(3 rows) 329 330select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); 331 nr 332----------- 333 [3,) 334 [1.1,2.2) 335 [1.7,1.7] 336(3 rows) 337 338select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); 339 nr 340---- 341(0 rows) 342 343select numrange(2.0, 1.0); 344ERROR: range lower bound must be less than or equal to range upper bound 345select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); 346 ?column? 347---------- 348 t 349(1 row) 350 351select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); 352 range_adjacent 353---------------- 354 f 355(1 row) 356 357select range_adjacent(numrange(2.0, 3.0), numrange(3.1, null)); 358 range_adjacent 359---------------- 360 f 361(1 row) 362 363select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()'); 364 ?column? 365---------- 366 t 367(1 row) 368 369select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); 370 ?column? 371---------- 372 t 373(1 row) 374 375select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); 376 range_adjacent 377---------------- 378 t 379(1 row) 380 381select numrange(1.1, 3.3) <@ numrange(0.1,10.1); 382 ?column? 383---------- 384 t 385(1 row) 386 387select numrange(0.1, 10.1) <@ numrange(1.1,3.3); 388 ?column? 389---------- 390 f 391(1 row) 392 393select numrange(1.1, 2.2) - numrange(2.0, 3.0); 394 ?column? 395----------- 396 [1.1,2.0) 397(1 row) 398 399select numrange(1.1, 2.2) - numrange(2.2, 3.0); 400 ?column? 401----------- 402 [1.1,2.2) 403(1 row) 404 405select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0); 406 ?column? 407----------- 408 [1.1,2.0) 409(1 row) 410 411select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); 412 range_minus 413------------- 414 [10.1,12.2] 415(1 row) 416 417select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); 418 range_minus 419------------- 420 empty 421(1 row) 422 423select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); 424 ?column? 425---------- 426 t 427(1 row) 428 429select numrange(1.0, 2.0) << numrange(3.0, 4.0); 430 ?column? 431---------- 432 t 433(1 row) 434 435select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]'); 436 ?column? 437---------- 438 f 439(1 row) 440 441select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()'); 442 ?column? 443---------- 444 t 445(1 row) 446 447select numrange(1.0, 2.0) >> numrange(3.0, 4.0); 448 ?column? 449---------- 450 f 451(1 row) 452 453select numrange(3.0, 70.0) &< numrange(6.6, 100.0); 454 ?column? 455---------- 456 t 457(1 row) 458 459select numrange(1.1, 2.2) < numrange(1.0, 200.2); 460 ?column? 461---------- 462 f 463(1 row) 464 465select numrange(1.1, 2.2) < numrange(1.1, 1.2); 466 ?column? 467---------- 468 f 469(1 row) 470 471select numrange(1.0, 2.0) + numrange(2.0, 3.0); 472 ?column? 473----------- 474 [1.0,3.0) 475(1 row) 476 477select numrange(1.0, 2.0) + numrange(1.5, 3.0); 478 ?column? 479----------- 480 [1.0,3.0) 481(1 row) 482 483select numrange(1.0, 2.0) + numrange(2.5, 3.0); -- should fail 484ERROR: result of range union would not be contiguous 485select range_merge(numrange(1.0, 2.0), numrange(2.0, 3.0)); 486 range_merge 487------------- 488 [1.0,3.0) 489(1 row) 490 491select range_merge(numrange(1.0, 2.0), numrange(1.5, 3.0)); 492 range_merge 493------------- 494 [1.0,3.0) 495(1 row) 496 497select range_merge(numrange(1.0, 2.0), numrange(2.5, 3.0)); -- shouldn't fail 498 range_merge 499------------- 500 [1.0,3.0) 501(1 row) 502 503select numrange(1.0, 2.0) * numrange(2.0, 3.0); 504 ?column? 505---------- 506 empty 507(1 row) 508 509select numrange(1.0, 2.0) * numrange(1.5, 3.0); 510 ?column? 511----------- 512 [1.5,2.0) 513(1 row) 514 515select numrange(1.0, 2.0) * numrange(2.5, 3.0); 516 ?column? 517---------- 518 empty 519(1 row) 520 521create table numrange_test2(nr numrange); 522create index numrange_test2_hash_idx on numrange_test2 using hash (nr); 523INSERT INTO numrange_test2 VALUES('[, 5)'); 524INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); 525INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); 526INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2,'()')); 527INSERT INTO numrange_test2 VALUES('empty'); 528select * from numrange_test2 where nr = 'empty'::numrange; 529 nr 530------- 531 empty 532(1 row) 533 534select * from numrange_test2 where nr = numrange(1.1, 2.2); 535 nr 536----------- 537 [1.1,2.2) 538 [1.1,2.2) 539(2 rows) 540 541select * from numrange_test2 where nr = numrange(1.1, 2.3); 542 nr 543---- 544(0 rows) 545 546set enable_nestloop=t; 547set enable_hashjoin=f; 548set enable_mergejoin=f; 549select * from numrange_test natural join numrange_test2 order by nr; 550 nr 551----------- 552 empty 553 (,5) 554 [1.1,2.2) 555 [1.1,2.2) 556(4 rows) 557 558set enable_nestloop=f; 559set enable_hashjoin=t; 560set enable_mergejoin=f; 561select * from numrange_test natural join numrange_test2 order by nr; 562 nr 563----------- 564 empty 565 (,5) 566 [1.1,2.2) 567 [1.1,2.2) 568(4 rows) 569 570set enable_nestloop=f; 571set enable_hashjoin=f; 572set enable_mergejoin=t; 573select * from numrange_test natural join numrange_test2 order by nr; 574 nr 575----------- 576 empty 577 (,5) 578 [1.1,2.2) 579 [1.1,2.2) 580(4 rows) 581 582set enable_nestloop to default; 583set enable_hashjoin to default; 584set enable_mergejoin to default; 585-- keep numrange_test around to help exercise dump/reload 586DROP TABLE numrange_test2; 587-- 588-- Apply a subset of the above tests on a collatable type, too 589-- 590CREATE TABLE textrange_test (tr textrange); 591create index textrange_test_btree on textrange_test(tr); 592INSERT INTO textrange_test VALUES('[,)'); 593INSERT INTO textrange_test VALUES('["a",]'); 594INSERT INTO textrange_test VALUES('[,"q")'); 595INSERT INTO textrange_test VALUES(textrange('b', 'g')); 596INSERT INTO textrange_test VALUES('empty'); 597INSERT INTO textrange_test VALUES(textrange('d', 'd', '[]')); 598SELECT tr, isempty(tr), lower(tr), upper(tr) FROM textrange_test; 599 tr | isempty | lower | upper 600-------+---------+-------+------- 601 (,) | f | | 602 [a,) | f | a | 603 (,q) | f | | q 604 [b,g) | f | b | g 605 empty | t | | 606 [d,d] | f | d | d 607(6 rows) 608 609SELECT tr, lower_inc(tr), lower_inf(tr), upper_inc(tr), upper_inf(tr) FROM textrange_test; 610 tr | lower_inc | lower_inf | upper_inc | upper_inf 611-------+-----------+-----------+-----------+----------- 612 (,) | f | t | f | t 613 [a,) | t | f | f | t 614 (,q) | f | t | f | f 615 [b,g) | t | f | f | f 616 empty | f | f | f | f 617 [d,d] | t | f | t | f 618(6 rows) 619 620SELECT * FROM textrange_test WHERE range_contains(tr, textrange('f', 'fx')); 621 tr 622------- 623 (,) 624 [a,) 625 (,q) 626 [b,g) 627(4 rows) 628 629SELECT * FROM textrange_test WHERE tr @> textrange('a', 'z'); 630 tr 631------ 632 (,) 633 [a,) 634(2 rows) 635 636SELECT * FROM textrange_test WHERE range_contained_by(textrange('0','9'), tr); 637 tr 638------ 639 (,) 640 (,q) 641(2 rows) 642 643SELECT * FROM textrange_test WHERE 'e'::text <@ tr; 644 tr 645------- 646 (,) 647 [a,) 648 (,q) 649 [b,g) 650(4 rows) 651 652select * from textrange_test where tr = 'empty'; 653 tr 654------- 655 empty 656(1 row) 657 658select * from textrange_test where tr = '("b","g")'; 659 tr 660---- 661(0 rows) 662 663select * from textrange_test where tr = '["b","g")'; 664 tr 665------- 666 [b,g) 667(1 row) 668 669select * from textrange_test where tr < 'empty'; 670 tr 671---- 672(0 rows) 673 674-- test canonical form for int4range 675select int4range(1, 10, '[]'); 676 int4range 677----------- 678 [1,11) 679(1 row) 680 681select int4range(1, 10, '[)'); 682 int4range 683----------- 684 [1,10) 685(1 row) 686 687select int4range(1, 10, '(]'); 688 int4range 689----------- 690 [2,11) 691(1 row) 692 693select int4range(1, 10, '()'); 694 int4range 695----------- 696 [2,10) 697(1 row) 698 699select int4range(1, 2, '()'); 700 int4range 701----------- 702 empty 703(1 row) 704 705-- test canonical form for daterange 706select daterange('2000-01-10'::date, '2000-01-20'::date, '[]'); 707 daterange 708------------------------- 709 [01-10-2000,01-21-2000) 710(1 row) 711 712select daterange('2000-01-10'::date, '2000-01-20'::date, '[)'); 713 daterange 714------------------------- 715 [01-10-2000,01-20-2000) 716(1 row) 717 718select daterange('2000-01-10'::date, '2000-01-20'::date, '(]'); 719 daterange 720------------------------- 721 [01-11-2000,01-21-2000) 722(1 row) 723 724select daterange('2000-01-10'::date, '2000-01-20'::date, '()'); 725 daterange 726------------------------- 727 [01-11-2000,01-20-2000) 728(1 row) 729 730select daterange('2000-01-10'::date, '2000-01-11'::date, '()'); 731 daterange 732----------- 733 empty 734(1 row) 735 736select daterange('2000-01-10'::date, '2000-01-11'::date, '(]'); 737 daterange 738------------------------- 739 [01-11-2000,01-12-2000) 740(1 row) 741 742select daterange('-infinity'::date, '2000-01-01'::date, '()'); 743 daterange 744------------------------ 745 (-infinity,01-01-2000) 746(1 row) 747 748select daterange('-infinity'::date, '2000-01-01'::date, '[)'); 749 daterange 750------------------------ 751 [-infinity,01-01-2000) 752(1 row) 753 754select daterange('2000-01-01'::date, 'infinity'::date, '[)'); 755 daterange 756----------------------- 757 [01-01-2000,infinity) 758(1 row) 759 760select daterange('2000-01-01'::date, 'infinity'::date, '[]'); 761 daterange 762----------------------- 763 [01-01-2000,infinity] 764(1 row) 765 766-- test GiST index that's been built incrementally 767create table test_range_gist(ir int4range); 768create index test_range_gist_idx on test_range_gist using gist (ir); 769insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; 770insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g; 771insert into test_range_gist select int4range(g, g+10000) from generate_series(1,1000) g; 772insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g; 773insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_series(1,100) g; 774insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; 775insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; 776-- first, verify non-indexed results 777SET enable_seqscan = t; 778SET enable_indexscan = f; 779SET enable_bitmapscan = f; 780select count(*) from test_range_gist where ir @> 'empty'::int4range; 781 count 782------- 783 6200 784(1 row) 785 786select count(*) from test_range_gist where ir = int4range(10,20); 787 count 788------- 789 2 790(1 row) 791 792select count(*) from test_range_gist where ir @> 10; 793 count 794------- 795 130 796(1 row) 797 798select count(*) from test_range_gist where ir @> int4range(10,20); 799 count 800------- 801 111 802(1 row) 803 804select count(*) from test_range_gist where ir && int4range(10,20); 805 count 806------- 807 158 808(1 row) 809 810select count(*) from test_range_gist where ir <@ int4range(10,50); 811 count 812------- 813 1062 814(1 row) 815 816select count(*) from test_range_gist where ir << int4range(100,500); 817 count 818------- 819 189 820(1 row) 821 822select count(*) from test_range_gist where ir >> int4range(100,500); 823 count 824------- 825 3554 826(1 row) 827 828select count(*) from test_range_gist where ir &< int4range(100,500); 829 count 830------- 831 1029 832(1 row) 833 834select count(*) from test_range_gist where ir &> int4range(100,500); 835 count 836------- 837 4794 838(1 row) 839 840select count(*) from test_range_gist where ir -|- int4range(100,500); 841 count 842------- 843 5 844(1 row) 845 846-- now check same queries using index 847SET enable_seqscan = f; 848SET enable_indexscan = t; 849SET enable_bitmapscan = f; 850select count(*) from test_range_gist where ir @> 'empty'::int4range; 851 count 852------- 853 6200 854(1 row) 855 856select count(*) from test_range_gist where ir = int4range(10,20); 857 count 858------- 859 2 860(1 row) 861 862select count(*) from test_range_gist where ir @> 10; 863 count 864------- 865 130 866(1 row) 867 868select count(*) from test_range_gist where ir @> int4range(10,20); 869 count 870------- 871 111 872(1 row) 873 874select count(*) from test_range_gist where ir && int4range(10,20); 875 count 876------- 877 158 878(1 row) 879 880select count(*) from test_range_gist where ir <@ int4range(10,50); 881 count 882------- 883 1062 884(1 row) 885 886select count(*) from test_range_gist where ir << int4range(100,500); 887 count 888------- 889 189 890(1 row) 891 892select count(*) from test_range_gist where ir >> int4range(100,500); 893 count 894------- 895 3554 896(1 row) 897 898select count(*) from test_range_gist where ir &< int4range(100,500); 899 count 900------- 901 1029 902(1 row) 903 904select count(*) from test_range_gist where ir &> int4range(100,500); 905 count 906------- 907 4794 908(1 row) 909 910select count(*) from test_range_gist where ir -|- int4range(100,500); 911 count 912------- 913 5 914(1 row) 915 916-- now check same queries using a bulk-loaded index 917drop index test_range_gist_idx; 918create index test_range_gist_idx on test_range_gist using gist (ir); 919select count(*) from test_range_gist where ir @> 'empty'::int4range; 920 count 921------- 922 6200 923(1 row) 924 925select count(*) from test_range_gist where ir = int4range(10,20); 926 count 927------- 928 2 929(1 row) 930 931select count(*) from test_range_gist where ir @> 10; 932 count 933------- 934 130 935(1 row) 936 937select count(*) from test_range_gist where ir @> int4range(10,20); 938 count 939------- 940 111 941(1 row) 942 943select count(*) from test_range_gist where ir && int4range(10,20); 944 count 945------- 946 158 947(1 row) 948 949select count(*) from test_range_gist where ir <@ int4range(10,50); 950 count 951------- 952 1062 953(1 row) 954 955select count(*) from test_range_gist where ir << int4range(100,500); 956 count 957------- 958 189 959(1 row) 960 961select count(*) from test_range_gist where ir >> int4range(100,500); 962 count 963------- 964 3554 965(1 row) 966 967select count(*) from test_range_gist where ir &< int4range(100,500); 968 count 969------- 970 1029 971(1 row) 972 973select count(*) from test_range_gist where ir &> int4range(100,500); 974 count 975------- 976 4794 977(1 row) 978 979select count(*) from test_range_gist where ir -|- int4range(100,500); 980 count 981------- 982 5 983(1 row) 984 985-- test SP-GiST index that's been built incrementally 986create table test_range_spgist(ir int4range); 987create index test_range_spgist_idx on test_range_spgist using spgist (ir); 988insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g; 989insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g; 990insert into test_range_spgist select int4range(g, g+10000) from generate_series(1,1000) g; 991insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g; 992insert into test_range_spgist select int4range(NULL,g*10,'(]') from generate_series(1,100) g; 993insert into test_range_spgist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; 994insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g; 995-- first, verify non-indexed results 996SET enable_seqscan = t; 997SET enable_indexscan = f; 998SET enable_bitmapscan = f; 999select count(*) from test_range_spgist where ir @> 'empty'::int4range; 1000 count 1001------- 1002 6200 1003(1 row) 1004 1005select count(*) from test_range_spgist where ir = int4range(10,20); 1006 count 1007------- 1008 2 1009(1 row) 1010 1011select count(*) from test_range_spgist where ir @> 10; 1012 count 1013------- 1014 130 1015(1 row) 1016 1017select count(*) from test_range_spgist where ir @> int4range(10,20); 1018 count 1019------- 1020 111 1021(1 row) 1022 1023select count(*) from test_range_spgist where ir && int4range(10,20); 1024 count 1025------- 1026 158 1027(1 row) 1028 1029select count(*) from test_range_spgist where ir <@ int4range(10,50); 1030 count 1031------- 1032 1062 1033(1 row) 1034 1035select count(*) from test_range_spgist where ir << int4range(100,500); 1036 count 1037------- 1038 189 1039(1 row) 1040 1041select count(*) from test_range_spgist where ir >> int4range(100,500); 1042 count 1043------- 1044 3554 1045(1 row) 1046 1047select count(*) from test_range_spgist where ir &< int4range(100,500); 1048 count 1049------- 1050 1029 1051(1 row) 1052 1053select count(*) from test_range_spgist where ir &> int4range(100,500); 1054 count 1055------- 1056 4794 1057(1 row) 1058 1059select count(*) from test_range_spgist where ir -|- int4range(100,500); 1060 count 1061------- 1062 5 1063(1 row) 1064 1065-- now check same queries using index 1066SET enable_seqscan = f; 1067SET enable_indexscan = t; 1068SET enable_bitmapscan = f; 1069select count(*) from test_range_spgist where ir @> 'empty'::int4range; 1070 count 1071------- 1072 6200 1073(1 row) 1074 1075select count(*) from test_range_spgist where ir = int4range(10,20); 1076 count 1077------- 1078 2 1079(1 row) 1080 1081select count(*) from test_range_spgist where ir @> 10; 1082 count 1083------- 1084 130 1085(1 row) 1086 1087select count(*) from test_range_spgist where ir @> int4range(10,20); 1088 count 1089------- 1090 111 1091(1 row) 1092 1093select count(*) from test_range_spgist where ir && int4range(10,20); 1094 count 1095------- 1096 158 1097(1 row) 1098 1099select count(*) from test_range_spgist where ir <@ int4range(10,50); 1100 count 1101------- 1102 1062 1103(1 row) 1104 1105select count(*) from test_range_spgist where ir << int4range(100,500); 1106 count 1107------- 1108 189 1109(1 row) 1110 1111select count(*) from test_range_spgist where ir >> int4range(100,500); 1112 count 1113------- 1114 3554 1115(1 row) 1116 1117select count(*) from test_range_spgist where ir &< int4range(100,500); 1118 count 1119------- 1120 1029 1121(1 row) 1122 1123select count(*) from test_range_spgist where ir &> int4range(100,500); 1124 count 1125------- 1126 4794 1127(1 row) 1128 1129select count(*) from test_range_spgist where ir -|- int4range(100,500); 1130 count 1131------- 1132 5 1133(1 row) 1134 1135-- now check same queries using a bulk-loaded index 1136drop index test_range_spgist_idx; 1137create index test_range_spgist_idx on test_range_spgist using spgist (ir); 1138select count(*) from test_range_spgist where ir @> 'empty'::int4range; 1139 count 1140------- 1141 6200 1142(1 row) 1143 1144select count(*) from test_range_spgist where ir = int4range(10,20); 1145 count 1146------- 1147 2 1148(1 row) 1149 1150select count(*) from test_range_spgist where ir @> 10; 1151 count 1152------- 1153 130 1154(1 row) 1155 1156select count(*) from test_range_spgist where ir @> int4range(10,20); 1157 count 1158------- 1159 111 1160(1 row) 1161 1162select count(*) from test_range_spgist where ir && int4range(10,20); 1163 count 1164------- 1165 158 1166(1 row) 1167 1168select count(*) from test_range_spgist where ir <@ int4range(10,50); 1169 count 1170------- 1171 1062 1172(1 row) 1173 1174select count(*) from test_range_spgist where ir << int4range(100,500); 1175 count 1176------- 1177 189 1178(1 row) 1179 1180select count(*) from test_range_spgist where ir >> int4range(100,500); 1181 count 1182------- 1183 3554 1184(1 row) 1185 1186select count(*) from test_range_spgist where ir &< int4range(100,500); 1187 count 1188------- 1189 1029 1190(1 row) 1191 1192select count(*) from test_range_spgist where ir &> int4range(100,500); 1193 count 1194------- 1195 4794 1196(1 row) 1197 1198select count(*) from test_range_spgist where ir -|- int4range(100,500); 1199 count 1200------- 1201 5 1202(1 row) 1203 1204-- test index-only scans 1205explain (costs off) 1206select ir from test_range_spgist where ir -|- int4range(10,20) order by ir; 1207 QUERY PLAN 1208------------------------------------------------------------------------ 1209 Sort 1210 Sort Key: ir 1211 -> Index Only Scan using test_range_spgist_idx on test_range_spgist 1212 Index Cond: (ir -|- '[10,20)'::int4range) 1213(4 rows) 1214 1215select ir from test_range_spgist where ir -|- int4range(10,20) order by ir; 1216 ir 1217------------ 1218 [20,30) 1219 [20,30) 1220 [20,10020) 1221(3 rows) 1222 1223RESET enable_seqscan; 1224RESET enable_indexscan; 1225RESET enable_bitmapscan; 1226-- test elem <@ range operator 1227create table test_range_elem(i int4); 1228create index test_range_elem_idx on test_range_elem (i); 1229insert into test_range_elem select i from generate_series(1,100) i; 1230select count(*) from test_range_elem where i <@ int4range(10,50); 1231 count 1232------- 1233 40 1234(1 row) 1235 1236drop table test_range_elem; 1237-- 1238-- Btree_gist is not included by default, so to test exclusion 1239-- constraints with range types, use singleton int ranges for the "=" 1240-- portion of the constraint. 1241-- 1242create table test_range_excl( 1243 room int4range, 1244 speaker int4range, 1245 during tsrange, 1246 exclude using gist (room with =, during with &&), 1247 exclude using gist (speaker with =, during with &&) 1248); 1249insert into test_range_excl 1250 values(int4range(123, 123, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:00, 2010-01-02 11:00)'); 1251insert into test_range_excl 1252 values(int4range(123, 123, '[]'), int4range(2, 2, '[]'), '[2010-01-02 11:00, 2010-01-02 12:00)'); 1253insert into test_range_excl 1254 values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); 1255ERROR: conflicting key value violates exclusion constraint "test_range_excl_room_during_excl" 1256DETAIL: Key (room, during)=([123,124), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (room, during)=([123,124), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). 1257insert into test_range_excl 1258 values(int4range(124, 124, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:10)'); 1259insert into test_range_excl 1260 values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); 1261ERROR: conflicting key value violates exclusion constraint "test_range_excl_speaker_during_excl" 1262DETAIL: Key (speaker, during)=([1,2), ["Sat Jan 02 10:10:00 2010","Sat Jan 02 11:00:00 2010")) conflicts with existing key (speaker, during)=([1,2), ["Sat Jan 02 10:00:00 2010","Sat Jan 02 11:00:00 2010")). 1263-- test bigint ranges 1264select int8range(10000000000::int8, 20000000000::int8,'(]'); 1265 int8range 1266--------------------------- 1267 [10000000001,20000000001) 1268(1 row) 1269 1270-- test tstz ranges 1271set timezone to '-08'; 1272select '[2010-01-01 01:00:00 -05, 2010-01-01 02:00:00 -08)'::tstzrange; 1273 tstzrange 1274----------------------------------------------------------------- 1275 ["Thu Dec 31 22:00:00 2009 -08","Fri Jan 01 02:00:00 2010 -08") 1276(1 row) 1277 1278-- should fail 1279select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)'::tstzrange; 1280ERROR: range lower bound must be less than or equal to range upper bound 1281LINE 1: select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)':... 1282 ^ 1283set timezone to default; 1284-- 1285-- Test user-defined range of floats 1286-- 1287--should fail 1288create type float8range as range (subtype=float8, subtype_diff=float4mi); 1289ERROR: function float4mi(double precision, double precision) does not exist 1290--should succeed 1291create type float8range as range (subtype=float8, subtype_diff=float8mi); 1292select '[123.001, 5.e9)'::float8range @> 888.882::float8; 1293 ?column? 1294---------- 1295 t 1296(1 row) 1297 1298create table float8range_test(f8r float8range, i int); 1299insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42); 1300select * from float8range_test; 1301 f8r | i 1302-------------------------+---- 1303 [-100.00007,1111113000) | 42 1304(1 row) 1305 1306drop table float8range_test; 1307-- 1308-- Test range types over domains 1309-- 1310create domain mydomain as int4; 1311create type mydomainrange as range(subtype=mydomain); 1312select '[4,50)'::mydomainrange @> 7::mydomain; 1313 ?column? 1314---------- 1315 t 1316(1 row) 1317 1318drop domain mydomain; -- fail 1319ERROR: cannot drop type mydomain because other objects depend on it 1320DETAIL: type mydomainrange depends on type mydomain 1321HINT: Use DROP ... CASCADE to drop the dependent objects too. 1322drop domain mydomain cascade; 1323NOTICE: drop cascades to type mydomainrange 1324-- 1325-- Test domains over range types 1326-- 1327create domain restrictedrange as int4range check (upper(value) < 10); 1328select '[4,5)'::restrictedrange @> 7; 1329 ?column? 1330---------- 1331 f 1332(1 row) 1333 1334select '[4,50)'::restrictedrange @> 7; -- should fail 1335ERROR: value for domain restrictedrange violates check constraint "restrictedrange_check" 1336drop domain restrictedrange; 1337-- 1338-- Test multiple range types over the same subtype 1339-- 1340create type textrange1 as range(subtype=text, collation="C"); 1341create type textrange2 as range(subtype=text, collation="C"); 1342select textrange1('a','Z') @> 'b'::text; 1343ERROR: range lower bound must be less than or equal to range upper bound 1344select textrange2('a','z') @> 'b'::text; 1345 ?column? 1346---------- 1347 t 1348(1 row) 1349 1350drop type textrange1; 1351drop type textrange2; 1352-- 1353-- Test polymorphic type system 1354-- 1355create function anyarray_anyrange_func(a anyarray, r anyrange) 1356 returns anyelement as 'select $1[1] + lower($2);' language sql; 1357select anyarray_anyrange_func(ARRAY[1,2], int4range(10,20)); 1358 anyarray_anyrange_func 1359------------------------ 1360 11 1361(1 row) 1362 1363-- should fail 1364select anyarray_anyrange_func(ARRAY[1,2], numrange(10,20)); 1365ERROR: function anyarray_anyrange_func(integer[], numrange) does not exist 1366LINE 1: select anyarray_anyrange_func(ARRAY[1,2], numrange(10,20)); 1367 ^ 1368HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1369drop function anyarray_anyrange_func(anyarray, anyrange); 1370-- should fail 1371create function bogus_func(anyelement) 1372 returns anyrange as 'select int4range(1,10)' language sql; 1373ERROR: cannot determine result data type 1374DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. 1375-- should fail 1376create function bogus_func(int) 1377 returns anyrange as 'select int4range(1,10)' language sql; 1378ERROR: cannot determine result data type 1379DETAIL: A function returning a polymorphic type must have at least one polymorphic argument. 1380create function range_add_bounds(anyrange) 1381 returns anyelement as 'select lower($1) + upper($1)' language sql; 1382select range_add_bounds(int4range(1, 17)); 1383 range_add_bounds 1384------------------ 1385 18 1386(1 row) 1387 1388select range_add_bounds(numrange(1.0001, 123.123)); 1389 range_add_bounds 1390------------------ 1391 124.1231 1392(1 row) 1393 1394create function rangetypes_sql(q anyrange, b anyarray, out c anyelement) 1395 as $$ select upper($1) + $2[1] $$ 1396 language sql; 1397select rangetypes_sql(int4range(1,10), ARRAY[2,20]); 1398 rangetypes_sql 1399---------------- 1400 12 1401(1 row) 1402 1403select rangetypes_sql(numrange(1,10), ARRAY[2,20]); -- match failure 1404ERROR: function rangetypes_sql(numrange, integer[]) does not exist 1405LINE 1: select rangetypes_sql(numrange(1,10), ARRAY[2,20]); 1406 ^ 1407HINT: No function matches the given name and argument types. You might need to add explicit type casts. 1408-- 1409-- Arrays of ranges 1410-- 1411select ARRAY[numrange(1.1, 1.2), numrange(12.3, 155.5)]; 1412 array 1413------------------------------ 1414 {"[1.1,1.2)","[12.3,155.5)"} 1415(1 row) 1416 1417create table i8r_array (f1 int, f2 int8range[]); 1418insert into i8r_array values (42, array[int8range(1,10), int8range(2,20)]); 1419select * from i8r_array; 1420 f1 | f2 1421----+--------------------- 1422 42 | {"[1,10)","[2,20)"} 1423(1 row) 1424 1425drop table i8r_array; 1426-- 1427-- Ranges of arrays 1428-- 1429create type arrayrange as range (subtype=int4[]); 1430select arrayrange(ARRAY[1,2], ARRAY[2,1]); 1431 arrayrange 1432------------------- 1433 ["{1,2}","{2,1}") 1434(1 row) 1435 1436select arrayrange(ARRAY[2,1], ARRAY[1,2]); -- fail 1437ERROR: range lower bound must be less than or equal to range upper bound 1438select array[1,1] <@ arrayrange(array[1,2], array[2,1]); 1439 ?column? 1440---------- 1441 f 1442(1 row) 1443 1444select array[1,3] <@ arrayrange(array[1,2], array[2,1]); 1445 ?column? 1446---------- 1447 t 1448(1 row) 1449 1450-- 1451-- Ranges of composites 1452-- 1453create type two_ints as (a int, b int); 1454create type two_ints_range as range (subtype = two_ints); 1455-- with force_parallel_mode on, this exercises tqueue.c's range remapping 1456select *, row_to_json(upper(t)) as u from 1457 (values (two_ints_range(row(1,2), row(3,4))), 1458 (two_ints_range(row(5,6), row(7,8)))) v(t); 1459 t | u 1460-------------------+--------------- 1461 ["(1,2)","(3,4)") | {"a":3,"b":4} 1462 ["(5,6)","(7,8)") | {"a":7,"b":8} 1463(2 rows) 1464 1465-- this must be rejected to avoid self-inclusion issues: 1466alter type two_ints add attribute c two_ints_range; 1467ERROR: composite type two_ints cannot be made a member of itself 1468drop type two_ints cascade; 1469NOTICE: drop cascades to type two_ints_range 1470-- 1471-- Check behavior when subtype lacks a hash function 1472-- 1473create type cashrange as range (subtype = money); 1474set enable_sort = off; -- try to make it pick a hash setop implementation 1475select '(2,5)'::cashrange except select '(5,6)'::cashrange; 1476 cashrange 1477--------------- 1478 ($2.00,$5.00) 1479(1 row) 1480 1481reset enable_sort; 1482-- 1483-- OUT/INOUT/TABLE functions 1484-- 1485create function outparam_succeed(i anyrange, out r anyrange, out t text) 1486 as $$ select $1, 'foo'::text $$ language sql; 1487select * from outparam_succeed(int4range(1,2)); 1488 r | t 1489-------+----- 1490 [1,2) | foo 1491(1 row) 1492 1493create function outparam2_succeed(r anyrange, out lu anyarray, out ul anyarray) 1494 as $$ select array[lower($1), upper($1)], array[upper($1), lower($1)] $$ 1495 language sql; 1496select * from outparam2_succeed(int4range(1,11)); 1497 lu | ul 1498--------+-------- 1499 {1,11} | {11,1} 1500(1 row) 1501 1502create function inoutparam_succeed(out i anyelement, inout r anyrange) 1503 as $$ select upper($1), $1 $$ language sql; 1504select * from inoutparam_succeed(int4range(1,2)); 1505 i | r 1506---+------- 1507 2 | [1,2) 1508(1 row) 1509 1510create function table_succeed(r anyrange) 1511 returns table(l anyelement, u anyelement) 1512 as $$ select lower($1), upper($1) $$ 1513 language sql; 1514select * from table_succeed(int4range(1,11)); 1515 l | u 1516---+---- 1517 1 | 11 1518(1 row) 1519 1520-- should fail 1521create function outparam_fail(i anyelement, out r anyrange, out t text) 1522 as $$ select '[1,10]', 'foo' $$ language sql; 1523ERROR: cannot determine result data type 1524DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. 1525--should fail 1526create function inoutparam_fail(inout i anyelement, out r anyrange) 1527 as $$ select $1, '[1,10]' $$ language sql; 1528ERROR: cannot determine result data type 1529DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. 1530--should fail 1531create function table_fail(i anyelement) returns table(i anyelement, r anyrange) 1532 as $$ select $1, '[1,10]' $$ language sql; 1533ERROR: cannot determine result data type 1534DETAIL: A function returning "anyrange" must have at least one "anyrange" argument. 1535