1-- Tests for range data types. 2 3create type textrange as range (subtype=text, collation="C"); 4 5-- 6-- test input parser 7-- 8 9-- negative tests; should fail 10select ''::textrange; 11select '-[a,z)'::textrange; 12select '[a,z) - '::textrange; 13select '(",a)'::textrange; 14select '(,,a)'::textrange; 15select '(),a)'::textrange; 16select '(a,))'::textrange; 17select '(],a)'::textrange; 18select '(a,])'::textrange; 19select '[z,a]'::textrange; 20 21-- should succeed 22select ' empty '::textrange; 23select ' ( empty, empty ) '::textrange; 24select ' ( " a " " a ", " z " " z " ) '::textrange; 25select '(,z)'::textrange; 26select '(a,)'::textrange; 27select '[,z]'::textrange; 28select '[a,]'::textrange; 29select '(,)'::textrange; 30select '[ , ]'::textrange; 31select '["",""]'::textrange; 32select '[",",","]'::textrange; 33select '["\\","\\"]'::textrange; 34select '(\\,a)'::textrange; 35select '((,z)'::textrange; 36select '([,z)'::textrange; 37select '(!,()'::textrange; 38select '(!,[)'::textrange; 39select '[a,a]'::textrange; 40-- these are allowed but normalize to empty: 41select '[a,a)'::textrange; 42select '(a,a]'::textrange; 43select '(a,a)'::textrange; 44 45-- 46-- create some test data and test the operators 47-- 48 49CREATE TABLE numrange_test (nr NUMRANGE); 50create index numrange_test_btree on numrange_test(nr); 51 52INSERT INTO numrange_test VALUES('[,)'); 53INSERT INTO numrange_test VALUES('[3,]'); 54INSERT INTO numrange_test VALUES('[, 5)'); 55INSERT INTO numrange_test VALUES(numrange(1.1, 2.2)); 56INSERT INTO numrange_test VALUES('empty'); 57INSERT INTO numrange_test VALUES(numrange(1.7, 1.7, '[]')); 58 59SELECT nr, isempty(nr), lower(nr), upper(nr) FROM numrange_test; 60SELECT nr, lower_inc(nr), lower_inf(nr), upper_inc(nr), upper_inf(nr) FROM numrange_test; 61 62SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91)); 63SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1); 64SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr); 65SELECT * FROM numrange_test WHERE 1.9 <@ nr; 66 67select * from numrange_test where nr = 'empty'; 68select * from numrange_test where nr = '(1.1, 2.2)'; 69select * from numrange_test where nr = '[1.1, 2.2)'; 70select * from numrange_test where nr < 'empty'; 71select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]'); 72select * from numrange_test where nr < numrange(0.0, 1.0,'[]'); 73select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]'); 74select * from numrange_test where nr <= 'empty'; 75select * from numrange_test where nr >= 'empty'; 76select * from numrange_test where nr > 'empty'; 77select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]'); 78select * from numrange_test where nr > numrange(0.0, 1.0,'[]'); 79select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]'); 80 81select numrange(2.0, 1.0); 82 83select numrange(2.0, 3.0) -|- numrange(3.0, 4.0); 84select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0)); 85select range_adjacent(numrange(2.0, 3.0), numrange(3.1, null)); 86select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()'); 87select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]'); 88select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]')); 89 90select numrange(1.1, 3.3) <@ numrange(0.1,10.1); 91select numrange(0.1, 10.1) <@ numrange(1.1,3.3); 92 93select numrange(1.1, 2.2) - numrange(2.0, 3.0); 94select numrange(1.1, 2.2) - numrange(2.2, 3.0); 95select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0); 96select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]')); 97select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]')); 98 99select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5); 100select numrange(1.0, 2.0) << numrange(3.0, 4.0); 101select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]'); 102select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()'); 103select numrange(1.0, 2.0) >> numrange(3.0, 4.0); 104select numrange(3.0, 70.0) &< numrange(6.6, 100.0); 105 106select numrange(1.1, 2.2) < numrange(1.0, 200.2); 107select numrange(1.1, 2.2) < numrange(1.1, 1.2); 108 109select numrange(1.0, 2.0) + numrange(2.0, 3.0); 110select numrange(1.0, 2.0) + numrange(1.5, 3.0); 111select numrange(1.0, 2.0) + numrange(2.5, 3.0); -- should fail 112 113select range_merge(numrange(1.0, 2.0), numrange(2.0, 3.0)); 114select range_merge(numrange(1.0, 2.0), numrange(1.5, 3.0)); 115select range_merge(numrange(1.0, 2.0), numrange(2.5, 3.0)); -- shouldn't fail 116 117select numrange(1.0, 2.0) * numrange(2.0, 3.0); 118select numrange(1.0, 2.0) * numrange(1.5, 3.0); 119select numrange(1.0, 2.0) * numrange(2.5, 3.0); 120 121create table numrange_test2(nr numrange); 122create index numrange_test2_hash_idx on numrange_test2 using hash (nr); 123 124INSERT INTO numrange_test2 VALUES('[, 5)'); 125INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); 126INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2)); 127INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2,'()')); 128INSERT INTO numrange_test2 VALUES('empty'); 129 130select * from numrange_test2 where nr = 'empty'::numrange; 131select * from numrange_test2 where nr = numrange(1.1, 2.2); 132select * from numrange_test2 where nr = numrange(1.1, 2.3); 133 134set enable_nestloop=t; 135set enable_hashjoin=f; 136set enable_mergejoin=f; 137select * from numrange_test natural join numrange_test2 order by nr; 138set enable_nestloop=f; 139set enable_hashjoin=t; 140set enable_mergejoin=f; 141select * from numrange_test natural join numrange_test2 order by nr; 142set enable_nestloop=f; 143set enable_hashjoin=f; 144set enable_mergejoin=t; 145select * from numrange_test natural join numrange_test2 order by nr; 146 147set enable_nestloop to default; 148set enable_hashjoin to default; 149set enable_mergejoin to default; 150 151-- keep numrange_test around to help exercise dump/reload 152DROP TABLE numrange_test2; 153 154-- 155-- Apply a subset of the above tests on a collatable type, too 156-- 157 158CREATE TABLE textrange_test (tr textrange); 159create index textrange_test_btree on textrange_test(tr); 160 161INSERT INTO textrange_test VALUES('[,)'); 162INSERT INTO textrange_test VALUES('["a",]'); 163INSERT INTO textrange_test VALUES('[,"q")'); 164INSERT INTO textrange_test VALUES(textrange('b', 'g')); 165INSERT INTO textrange_test VALUES('empty'); 166INSERT INTO textrange_test VALUES(textrange('d', 'd', '[]')); 167 168SELECT tr, isempty(tr), lower(tr), upper(tr) FROM textrange_test; 169SELECT tr, lower_inc(tr), lower_inf(tr), upper_inc(tr), upper_inf(tr) FROM textrange_test; 170 171SELECT * FROM textrange_test WHERE range_contains(tr, textrange('f', 'fx')); 172SELECT * FROM textrange_test WHERE tr @> textrange('a', 'z'); 173SELECT * FROM textrange_test WHERE range_contained_by(textrange('0','9'), tr); 174SELECT * FROM textrange_test WHERE 'e'::text <@ tr; 175 176select * from textrange_test where tr = 'empty'; 177select * from textrange_test where tr = '("b","g")'; 178select * from textrange_test where tr = '["b","g")'; 179select * from textrange_test where tr < 'empty'; 180 181 182-- test canonical form for int4range 183select int4range(1, 10, '[]'); 184select int4range(1, 10, '[)'); 185select int4range(1, 10, '(]'); 186select int4range(1, 10, '()'); 187select int4range(1, 2, '()'); 188 189-- test canonical form for daterange 190select daterange('2000-01-10'::date, '2000-01-20'::date, '[]'); 191select daterange('2000-01-10'::date, '2000-01-20'::date, '[)'); 192select daterange('2000-01-10'::date, '2000-01-20'::date, '(]'); 193select daterange('2000-01-10'::date, '2000-01-20'::date, '()'); 194select daterange('2000-01-10'::date, '2000-01-11'::date, '()'); 195select daterange('2000-01-10'::date, '2000-01-11'::date, '(]'); 196select daterange('-infinity'::date, '2000-01-01'::date, '()'); 197select daterange('-infinity'::date, '2000-01-01'::date, '[)'); 198select daterange('2000-01-01'::date, 'infinity'::date, '[)'); 199select daterange('2000-01-01'::date, 'infinity'::date, '[]'); 200 201-- test GiST index that's been built incrementally 202create table test_range_gist(ir int4range); 203create index test_range_gist_idx on test_range_gist using gist (ir); 204 205insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; 206insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g; 207insert into test_range_gist select int4range(g, g+10000) from generate_series(1,1000) g; 208insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g; 209insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_series(1,100) g; 210insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; 211insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; 212 213-- first, verify non-indexed results 214SET enable_seqscan = t; 215SET enable_indexscan = f; 216SET enable_bitmapscan = f; 217 218select count(*) from test_range_gist where ir @> 'empty'::int4range; 219select count(*) from test_range_gist where ir = int4range(10,20); 220select count(*) from test_range_gist where ir @> 10; 221select count(*) from test_range_gist where ir @> int4range(10,20); 222select count(*) from test_range_gist where ir && int4range(10,20); 223select count(*) from test_range_gist where ir <@ int4range(10,50); 224select count(*) from test_range_gist where ir << int4range(100,500); 225select count(*) from test_range_gist where ir >> int4range(100,500); 226select count(*) from test_range_gist where ir &< int4range(100,500); 227select count(*) from test_range_gist where ir &> int4range(100,500); 228select count(*) from test_range_gist where ir -|- int4range(100,500); 229 230-- now check same queries using index 231SET enable_seqscan = f; 232SET enable_indexscan = t; 233SET enable_bitmapscan = f; 234 235select count(*) from test_range_gist where ir @> 'empty'::int4range; 236select count(*) from test_range_gist where ir = int4range(10,20); 237select count(*) from test_range_gist where ir @> 10; 238select count(*) from test_range_gist where ir @> int4range(10,20); 239select count(*) from test_range_gist where ir && int4range(10,20); 240select count(*) from test_range_gist where ir <@ int4range(10,50); 241select count(*) from test_range_gist where ir << int4range(100,500); 242select count(*) from test_range_gist where ir >> int4range(100,500); 243select count(*) from test_range_gist where ir &< int4range(100,500); 244select count(*) from test_range_gist where ir &> int4range(100,500); 245select count(*) from test_range_gist where ir -|- int4range(100,500); 246 247-- now check same queries using a bulk-loaded index 248drop index test_range_gist_idx; 249create index test_range_gist_idx on test_range_gist using gist (ir); 250 251select count(*) from test_range_gist where ir @> 'empty'::int4range; 252select count(*) from test_range_gist where ir = int4range(10,20); 253select count(*) from test_range_gist where ir @> 10; 254select count(*) from test_range_gist where ir @> int4range(10,20); 255select count(*) from test_range_gist where ir && int4range(10,20); 256select count(*) from test_range_gist where ir <@ int4range(10,50); 257select count(*) from test_range_gist where ir << int4range(100,500); 258select count(*) from test_range_gist where ir >> int4range(100,500); 259select count(*) from test_range_gist where ir &< int4range(100,500); 260select count(*) from test_range_gist where ir &> int4range(100,500); 261select count(*) from test_range_gist where ir -|- int4range(100,500); 262 263-- test SP-GiST index that's been built incrementally 264create table test_range_spgist(ir int4range); 265create index test_range_spgist_idx on test_range_spgist using spgist (ir); 266 267insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g; 268insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g; 269insert into test_range_spgist select int4range(g, g+10000) from generate_series(1,1000) g; 270insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g; 271insert into test_range_spgist select int4range(NULL,g*10,'(]') from generate_series(1,100) g; 272insert into test_range_spgist select int4range(g*10,NULL,'(]') from generate_series(1,100) g; 273insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g; 274 275-- first, verify non-indexed results 276SET enable_seqscan = t; 277SET enable_indexscan = f; 278SET enable_bitmapscan = f; 279 280select count(*) from test_range_spgist where ir @> 'empty'::int4range; 281select count(*) from test_range_spgist where ir = int4range(10,20); 282select count(*) from test_range_spgist where ir @> 10; 283select count(*) from test_range_spgist where ir @> int4range(10,20); 284select count(*) from test_range_spgist where ir && int4range(10,20); 285select count(*) from test_range_spgist where ir <@ int4range(10,50); 286select count(*) from test_range_spgist where ir << int4range(100,500); 287select count(*) from test_range_spgist where ir >> int4range(100,500); 288select count(*) from test_range_spgist where ir &< int4range(100,500); 289select count(*) from test_range_spgist where ir &> int4range(100,500); 290select count(*) from test_range_spgist where ir -|- int4range(100,500); 291 292-- now check same queries using index 293SET enable_seqscan = f; 294SET enable_indexscan = t; 295SET enable_bitmapscan = f; 296 297select count(*) from test_range_spgist where ir @> 'empty'::int4range; 298select count(*) from test_range_spgist where ir = int4range(10,20); 299select count(*) from test_range_spgist where ir @> 10; 300select count(*) from test_range_spgist where ir @> int4range(10,20); 301select count(*) from test_range_spgist where ir && int4range(10,20); 302select count(*) from test_range_spgist where ir <@ int4range(10,50); 303select count(*) from test_range_spgist where ir << int4range(100,500); 304select count(*) from test_range_spgist where ir >> int4range(100,500); 305select count(*) from test_range_spgist where ir &< int4range(100,500); 306select count(*) from test_range_spgist where ir &> int4range(100,500); 307select count(*) from test_range_spgist where ir -|- int4range(100,500); 308 309-- now check same queries using a bulk-loaded index 310drop index test_range_spgist_idx; 311create index test_range_spgist_idx on test_range_spgist using spgist (ir); 312 313select count(*) from test_range_spgist where ir @> 'empty'::int4range; 314select count(*) from test_range_spgist where ir = int4range(10,20); 315select count(*) from test_range_spgist where ir @> 10; 316select count(*) from test_range_spgist where ir @> int4range(10,20); 317select count(*) from test_range_spgist where ir && int4range(10,20); 318select count(*) from test_range_spgist where ir <@ int4range(10,50); 319select count(*) from test_range_spgist where ir << int4range(100,500); 320select count(*) from test_range_spgist where ir >> int4range(100,500); 321select count(*) from test_range_spgist where ir &< int4range(100,500); 322select count(*) from test_range_spgist where ir &> int4range(100,500); 323select count(*) from test_range_spgist where ir -|- int4range(100,500); 324 325-- test index-only scans 326explain (costs off) 327select ir from test_range_spgist where ir -|- int4range(10,20) order by ir; 328select ir from test_range_spgist where ir -|- int4range(10,20) order by ir; 329 330RESET enable_seqscan; 331RESET enable_indexscan; 332RESET enable_bitmapscan; 333 334-- test elem <@ range operator 335create table test_range_elem(i int4); 336create index test_range_elem_idx on test_range_elem (i); 337insert into test_range_elem select i from generate_series(1,100) i; 338 339select count(*) from test_range_elem where i <@ int4range(10,50); 340 341drop table test_range_elem; 342 343-- 344-- Btree_gist is not included by default, so to test exclusion 345-- constraints with range types, use singleton int ranges for the "=" 346-- portion of the constraint. 347-- 348 349create table test_range_excl( 350 room int4range, 351 speaker int4range, 352 during tsrange, 353 exclude using gist (room with =, during with &&), 354 exclude using gist (speaker with =, during with &&) 355); 356 357insert into test_range_excl 358 values(int4range(123, 123, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:00, 2010-01-02 11:00)'); 359insert into test_range_excl 360 values(int4range(123, 123, '[]'), int4range(2, 2, '[]'), '[2010-01-02 11:00, 2010-01-02 12:00)'); 361insert into test_range_excl 362 values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); 363insert into test_range_excl 364 values(int4range(124, 124, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:10)'); 365insert into test_range_excl 366 values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)'); 367 368-- test bigint ranges 369select int8range(10000000000::int8, 20000000000::int8,'(]'); 370-- test tstz ranges 371set timezone to '-08'; 372select '[2010-01-01 01:00:00 -05, 2010-01-01 02:00:00 -08)'::tstzrange; 373-- should fail 374select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)'::tstzrange; 375set timezone to default; 376 377-- 378-- Test user-defined range of floats 379-- 380 381--should fail 382create type float8range as range (subtype=float8, subtype_diff=float4mi); 383 384--should succeed 385create type float8range as range (subtype=float8, subtype_diff=float8mi); 386select '[123.001, 5.e9)'::float8range @> 888.882::float8; 387create table float8range_test(f8r float8range, i int); 388insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42); 389select * from float8range_test; 390drop table float8range_test; 391 392-- 393-- Test range types over domains 394-- 395 396create domain mydomain as int4; 397create type mydomainrange as range(subtype=mydomain); 398select '[4,50)'::mydomainrange @> 7::mydomain; 399drop domain mydomain; -- fail 400drop domain mydomain cascade; 401 402-- 403-- Test domains over range types 404-- 405 406create domain restrictedrange as int4range check (upper(value) < 10); 407select '[4,5)'::restrictedrange @> 7; 408select '[4,50)'::restrictedrange @> 7; -- should fail 409drop domain restrictedrange; 410 411-- 412-- Test multiple range types over the same subtype 413-- 414 415create type textrange1 as range(subtype=text, collation="C"); 416create type textrange2 as range(subtype=text, collation="C"); 417 418select textrange1('a','Z') @> 'b'::text; 419select textrange2('a','z') @> 'b'::text; 420 421drop type textrange1; 422drop type textrange2; 423 424-- 425-- Test polymorphic type system 426-- 427 428create function anyarray_anyrange_func(a anyarray, r anyrange) 429 returns anyelement as 'select $1[1] + lower($2);' language sql; 430 431select anyarray_anyrange_func(ARRAY[1,2], int4range(10,20)); 432 433-- should fail 434select anyarray_anyrange_func(ARRAY[1,2], numrange(10,20)); 435 436drop function anyarray_anyrange_func(anyarray, anyrange); 437 438-- should fail 439create function bogus_func(anyelement) 440 returns anyrange as 'select int4range(1,10)' language sql; 441 442-- should fail 443create function bogus_func(int) 444 returns anyrange as 'select int4range(1,10)' language sql; 445 446create function range_add_bounds(anyrange) 447 returns anyelement as 'select lower($1) + upper($1)' language sql; 448 449select range_add_bounds(int4range(1, 17)); 450select range_add_bounds(numrange(1.0001, 123.123)); 451 452create function rangetypes_sql(q anyrange, b anyarray, out c anyelement) 453 as $$ select upper($1) + $2[1] $$ 454 language sql; 455 456select rangetypes_sql(int4range(1,10), ARRAY[2,20]); 457select rangetypes_sql(numrange(1,10), ARRAY[2,20]); -- match failure 458 459-- 460-- Arrays of ranges 461-- 462 463select ARRAY[numrange(1.1, 1.2), numrange(12.3, 155.5)]; 464 465create table i8r_array (f1 int, f2 int8range[]); 466insert into i8r_array values (42, array[int8range(1,10), int8range(2,20)]); 467select * from i8r_array; 468drop table i8r_array; 469 470-- 471-- Ranges of arrays 472-- 473 474create type arrayrange as range (subtype=int4[]); 475 476select arrayrange(ARRAY[1,2], ARRAY[2,1]); 477select arrayrange(ARRAY[2,1], ARRAY[1,2]); -- fail 478 479select array[1,1] <@ arrayrange(array[1,2], array[2,1]); 480select array[1,3] <@ arrayrange(array[1,2], array[2,1]); 481 482-- 483-- Ranges of composites 484-- 485 486create type two_ints as (a int, b int); 487create type two_ints_range as range (subtype = two_ints); 488 489-- with force_parallel_mode on, this exercises tqueue.c's range remapping 490select *, row_to_json(upper(t)) as u from 491 (values (two_ints_range(row(1,2), row(3,4))), 492 (two_ints_range(row(5,6), row(7,8)))) v(t); 493 494-- this must be rejected to avoid self-inclusion issues: 495alter type two_ints add attribute c two_ints_range; 496 497drop type two_ints cascade; 498 499-- 500-- Check behavior when subtype lacks a hash function 501-- 502 503create type cashrange as range (subtype = money); 504 505set enable_sort = off; -- try to make it pick a hash setop implementation 506 507select '(2,5)'::cashrange except select '(5,6)'::cashrange; 508 509reset enable_sort; 510 511-- 512-- OUT/INOUT/TABLE functions 513-- 514 515create function outparam_succeed(i anyrange, out r anyrange, out t text) 516 as $$ select $1, 'foo'::text $$ language sql; 517 518select * from outparam_succeed(int4range(1,2)); 519 520create function outparam2_succeed(r anyrange, out lu anyarray, out ul anyarray) 521 as $$ select array[lower($1), upper($1)], array[upper($1), lower($1)] $$ 522 language sql; 523 524select * from outparam2_succeed(int4range(1,11)); 525 526create function inoutparam_succeed(out i anyelement, inout r anyrange) 527 as $$ select upper($1), $1 $$ language sql; 528 529select * from inoutparam_succeed(int4range(1,2)); 530 531create function table_succeed(r anyrange) 532 returns table(l anyelement, u anyelement) 533 as $$ select lower($1), upper($1) $$ 534 language sql; 535 536select * from table_succeed(int4range(1,11)); 537 538-- should fail 539create function outparam_fail(i anyelement, out r anyrange, out t text) 540 as $$ select '[1,10]', 'foo' $$ language sql; 541 542--should fail 543create function inoutparam_fail(inout i anyelement, out r anyrange) 544 as $$ select $1, '[1,10]' $$ language sql; 545 546--should fail 547create function table_fail(i anyelement) returns table(i anyelement, r anyrange) 548 as $$ select $1, '[1,10]' $$ language sql; 549