rangetypes.out

-- Tests for range data types.
--
-- test input parser
-- (type textrange was already made in test_setup.sql)
--
-- negative tests; should fail
select ''::textrange;
ERROR:  malformed range literal: ""
LINE 1: select ''::textrange;
               ^
DETAIL:  Missing left parenthesis or bracket.
select '-[a,z)'::textrange;
ERROR:  malformed range literal: "-[a,z)"
LINE 1: select '-[a,z)'::textrange;
               ^
DETAIL:  Missing left parenthesis or bracket.
select '[a,z) - '::textrange;
ERROR:  malformed range literal: "[a,z) - "
LINE 1: select '[a,z) - '::textrange;
               ^
DETAIL:  Junk after right parenthesis or bracket.
select '(",a)'::textrange;
ERROR:  malformed range literal: "(",a)"
LINE 1: select '(",a)'::textrange;
               ^
DETAIL:  Unexpected end of input.
select '(,,a)'::textrange;
ERROR:  malformed range literal: "(,,a)"
LINE 1: select '(,,a)'::textrange;
               ^
DETAIL:  Too many commas.
select '(),a)'::textrange;
ERROR:  malformed range literal: "(),a)"
LINE 1: select '(),a)'::textrange;
               ^
DETAIL:  Missing comma after lower bound.
select '(a,))'::textrange;
ERROR:  malformed range literal: "(a,))"
LINE 1: select '(a,))'::textrange;
               ^
DETAIL:  Junk after right parenthesis or bracket.
select '(],a)'::textrange;
ERROR:  malformed range literal: "(],a)"
LINE 1: select '(],a)'::textrange;
               ^
DETAIL:  Missing comma after lower bound.
select '(a,])'::textrange;
ERROR:  malformed range literal: "(a,])"
LINE 1: select '(a,])'::textrange;
               ^
DETAIL:  Junk after right parenthesis or bracket.
select '[z,a]'::textrange;
ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: select '[z,a]'::textrange;
               ^
-- should succeed
select '  empty  '::textrange;
 textrange 
-----------
 empty
(1 row)

select ' ( empty, empty )  '::textrange;
      textrange       
----------------------
 (" empty"," empty ")
(1 row)

select ' ( " a " " a ", " z " " z " )  '::textrange;
        textrange         
--------------------------
 ("  a   a ","  z   z  ")
(1 row)

select '(a,)'::textrange;
 textrange 
-----------
 (a,)
(1 row)

select '[,z]'::textrange;
 textrange 
-----------
 (,z]
(1 row)

select '[a,]'::textrange;
 textrange 
-----------
 [a,)
(1 row)

select '(,)'::textrange;
 textrange 
-----------
 (,)
(1 row)

select '[ , ]'::textrange;
 textrange 
-----------
 [" "," "]
(1 row)

select '["",""]'::textrange;
 textrange 
-----------
 ["",""]
(1 row)

select '[",",","]'::textrange;
 textrange 
-----------
 [",",","]
(1 row)

select '["\\","\\"]'::textrange;
  textrange  
-------------
 ["\\","\\"]
(1 row)

select '(\\,a)'::textrange;
 textrange 
-----------
 ("\\",a)
(1 row)

select '((,z)'::textrange;
 textrange 
-----------
 ("(",z)
(1 row)

select '([,z)'::textrange;
 textrange 
-----------
 ("[",z)
(1 row)

select '(!,()'::textrange;
 textrange 
-----------
 (!,"(")
(1 row)

select '(!,[)'::textrange;
 textrange 
-----------
 (!,"[")
(1 row)

select '[a,a]'::textrange;
 textrange 
-----------
 [a,a]
(1 row)

-- these are allowed but normalize to empty:
select '[a,a)'::textrange;
 textrange 
-----------
 empty
(1 row)

select '(a,a]'::textrange;
 textrange 
-----------
 empty
(1 row)

select '(a,a)'::textrange;
 textrange 
-----------
 empty
(1 row)

-- Also try it with non-error-throwing API
select pg_input_is_valid('(1,4)', 'int4range');
 pg_input_is_valid 
-------------------
 t
(1 row)

select pg_input_is_valid('(1,4', 'int4range');
 pg_input_is_valid 
-------------------
 f
(1 row)

select pg_input_error_message('(1,4', 'int4range');
     pg_input_error_message      
---------------------------------
 malformed range literal: "(1,4"
(1 row)

select pg_input_is_valid('(4,1)', 'int4range');
 pg_input_is_valid 
-------------------
 f
(1 row)

select pg_input_error_message('(4,1)', 'int4range');
                      pg_input_error_message                       
-------------------------------------------------------------------
 range lower bound must be less than or equal to range upper bound
(1 row)

select pg_input_is_valid('(4,zed)', 'int4range');
 pg_input_is_valid 
-------------------
 f
(1 row)

select pg_input_error_message('(4,zed)', 'int4range');
            pg_input_error_message            
----------------------------------------------
 invalid input syntax for type integer: "zed"
(1 row)

select pg_input_is_valid('[1,2147483647]', 'int4range');
 pg_input_is_valid 
-------------------
 f
(1 row)

select pg_input_error_message('[1,2147483647]', 'int4range');
 pg_input_error_message 
------------------------
 integer out of range
(1 row)

select pg_input_is_valid('[2000-01-01,5874897-12-31]', 'daterange');
 pg_input_is_valid 
-------------------
 f
(1 row)

select pg_input_error_message('[2000-01-01,5874897-12-31]', 'daterange');
 pg_input_error_message 
------------------------
 date out of range
(1 row)

--
-- create some test data and test the operators
--
CREATE TABLE numrange_test (nr NUMRANGE);
create index numrange_test_btree on numrange_test(nr);
INSERT INTO numrange_test VALUES('[,)');
INSERT INTO numrange_test VALUES('[3,]');
INSERT INTO numrange_test VALUES('[, 5)');
INSERT INTO numrange_test VALUES(numrange(1.1, 2.2));
INSERT INTO numrange_test VALUES('empty');
INSERT INTO numrange_test VALUES(numrange(1.7, 1.7, '[]'));
SELECT nr, isempty(nr), lower(nr), upper(nr) FROM numrange_test;
    nr     | isempty | lower | upper 
-----------+---------+-------+-------
 (,)       | f       |       |      
 [3,)      | f       |     3 |      
 (,5)      | f       |       |     5
 [1.1,2.2) | f       |   1.1 |   2.2
 empty     | t       |       |      
 [1.7,1.7] | f       |   1.7 |   1.7
(6 rows)

SELECT nr, lower_inc(nr), lower_inf(nr), upper_inc(nr), upper_inf(nr) FROM numrange_test;
    nr     | lower_inc | lower_inf | upper_inc | upper_inf 
-----------+-----------+-----------+-----------+-----------
 (,)       | f         | t         | f         | t
 [3,)      | t         | f         | f         | t
 (,5)      | f         | t         | f         | f
 [1.1,2.2) | t         | f         | f         | f
 empty     | f         | f         | f         | f
 [1.7,1.7] | t         | f         | t         | f
(6 rows)

SELECT * FROM numrange_test WHERE range_contains(nr, numrange(1.9,1.91));
    nr     
-----------
 (,)
 (,5)
 [1.1,2.2)
(3 rows)

SELECT * FROM numrange_test WHERE nr @> numrange(1.0,10000.1);
 nr  
-----
 (,)
(1 row)

SELECT * FROM numrange_test WHERE range_contained_by(numrange(-1e7,-10000.1), nr);
  nr  
------
 (,)
 (,5)
(2 rows)

SELECT * FROM numrange_test WHERE 1.9 <@ nr;
    nr     
-----------
 (,)
 (,5)
 [1.1,2.2)
(3 rows)

select * from numrange_test where nr = 'empty';
  nr   
-------
 empty
(1 row)

select * from numrange_test where nr = '(1.1, 2.2)';
 nr 
----
(0 rows)

select * from numrange_test where nr = '[1.1, 2.2)';
    nr     
-----------
 [1.1,2.2)
(1 row)

select * from numrange_test where nr < 'empty';
 nr 
----
(0 rows)

select * from numrange_test where nr < numrange(-1000.0, -1000.0,'[]');
  nr   
-------
 (,)
 (,5)
 empty
(3 rows)

select * from numrange_test where nr < numrange(0.0, 1.0,'[]');
  nr   
-------
 (,)
 (,5)
 empty
(3 rows)

select * from numrange_test where nr < numrange(1000.0, 1001.0,'[]');
    nr     
-----------
 (,)
 [3,)
 (,5)
 [1.1,2.2)
 empty
 [1.7,1.7]
(6 rows)

select * from numrange_test where nr <= 'empty';
  nr   
-------
 empty
(1 row)

select * from numrange_test where nr >= 'empty';
    nr     
-----------
 (,)
 [3,)
 (,5)
 [1.1,2.2)
 empty
 [1.7,1.7]
(6 rows)

select * from numrange_test where nr > 'empty';
    nr     
-----------
 (,)
 [3,)
 (,5)
 [1.1,2.2)
 [1.7,1.7]
(5 rows)

select * from numrange_test where nr > numrange(-1001.0, -1000.0,'[]');
    nr     
-----------
 [3,)
 [1.1,2.2)
 [1.7,1.7]
(3 rows)

select * from numrange_test where nr > numrange(0.0, 1.0,'[]');
    nr     
-----------
 [3,)
 [1.1,2.2)
 [1.7,1.7]
(3 rows)

select * from numrange_test where nr > numrange(1000.0, 1000.0,'[]');
 nr 
----
(0 rows)

select numrange(2.0, 1.0);
ERROR:  range lower bound must be less than or equal to range upper bound
select numrange(2.0, 3.0) -|- numrange(3.0, 4.0);
 ?column? 
----------
 t
(1 row)

select range_adjacent(numrange(2.0, 3.0), numrange(3.1, 4.0));
 range_adjacent 
----------------
 f
(1 row)

select range_adjacent(numrange(2.0, 3.0), numrange(3.1, null));
 range_adjacent 
----------------
 f
(1 row)

select numrange(2.0, 3.0, '[]') -|- numrange(3.0, 4.0, '()');
 ?column? 
----------
 t
(1 row)

select numrange(1.0, 2.0) -|- numrange(2.0, 3.0,'[]');
 ?column? 
----------
 t
(1 row)

select range_adjacent(numrange(2.0, 3.0, '(]'), numrange(1.0, 2.0, '(]'));
 range_adjacent 
----------------
 t
(1 row)

select numrange(1.1, 3.3) <@ numrange(0.1,10.1);
 ?column? 
----------
 t
(1 row)

select numrange(0.1, 10.1) <@ numrange(1.1,3.3);
 ?column? 
----------
 f
(1 row)

select numrange(1.1, 2.2) - numrange(2.0, 3.0);
 ?column?  
-----------
 [1.1,2.0)
(1 row)

select numrange(1.1, 2.2) - numrange(2.2, 3.0);
 ?column?  
-----------
 [1.1,2.2)
(1 row)

select numrange(1.1, 2.2,'[]') - numrange(2.0, 3.0);
 ?column?  
-----------
 [1.1,2.0)
(1 row)

select range_minus(numrange(10.1,12.2,'[]'), numrange(110.0,120.2,'(]'));
 range_minus 
-------------
 [10.1,12.2]
(1 row)

select range_minus(numrange(10.1,12.2,'[]'), numrange(0.0,120.2,'(]'));
 range_minus 
-------------
 empty
(1 row)

select numrange(4.5, 5.5, '[]') && numrange(5.5, 6.5);
 ?column? 
----------
 t
(1 row)

select numrange(1.0, 2.0) << numrange(3.0, 4.0);
 ?column? 
----------
 t
(1 row)

select numrange(1.0, 3.0,'[]') << numrange(3.0, 4.0,'[]');
 ?column? 
----------
 f
(1 row)

select numrange(1.0, 3.0,'()') << numrange(3.0, 4.0,'()');
 ?column? 
----------
 t
(1 row)

select numrange(1.0, 2.0) >> numrange(3.0, 4.0);
 ?column? 
----------
 f
(1 row)

select numrange(3.0, 70.0) &< numrange(6.6, 100.0);
 ?column? 
----------
 t
(1 row)

select numrange(1.1, 2.2) < numrange(1.0, 200.2);
 ?column? 
----------
 f
(1 row)

select numrange(1.1, 2.2) < numrange(1.1, 1.2);
 ?column? 
----------
 f
(1 row)

select numrange(1.0, 2.0) + numrange(2.0, 3.0);
 ?column?  
-----------
 [1.0,3.0)
(1 row)

select numrange(1.0, 2.0) + numrange(1.5, 3.0);
 ?column?  
-----------
 [1.0,3.0)
(1 row)

select numrange(1.0, 2.0) + numrange(2.5, 3.0); -- should fail
ERROR:  result of range union would not be contiguous
select range_merge(numrange(1.0, 2.0), numrange(2.0, 3.0));
 range_merge 
-------------
 [1.0,3.0)
(1 row)

select range_merge(numrange(1.0, 2.0), numrange(1.5, 3.0));
 range_merge 
-------------
 [1.0,3.0)
(1 row)

select range_merge(numrange(1.0, 2.0), numrange(2.5, 3.0)); -- shouldn't fail
 range_merge 
-------------
 [1.0,3.0)
(1 row)

select numrange(1.0, 2.0) * numrange(2.0, 3.0);
 ?column? 
----------
 empty
(1 row)

select numrange(1.0, 2.0) * numrange(1.5, 3.0);
 ?column?  
-----------
 [1.5,2.0)
(1 row)

select numrange(1.0, 2.0) * numrange(2.5, 3.0);
 ?column? 
----------
 empty
(1 row)

select range_intersect_agg(nr) from numrange_test;
 range_intersect_agg 
---------------------
 empty
(1 row)

select range_intersect_agg(nr) from numrange_test where false;
 range_intersect_agg 
---------------------
 
(1 row)

select range_intersect_agg(nr) from numrange_test where nr @> 4.0;
 range_intersect_agg 
---------------------
 [3,5)
(1 row)

analyze numrange_test;
create table numrange_test2(nr numrange);
create index numrange_test2_hash_idx on numrange_test2 using hash (nr);
INSERT INTO numrange_test2 VALUES('[, 5)');
INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2));
INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2));
INSERT INTO numrange_test2 VALUES(numrange(1.1, 2.2,'()'));
INSERT INTO numrange_test2 VALUES('empty');
select * from numrange_test2 where nr = 'empty'::numrange;
  nr   
-------
 empty
(1 row)

select * from numrange_test2 where nr = numrange(1.1, 2.2);
    nr     
-----------
 [1.1,2.2)
 [1.1,2.2)
(2 rows)

select * from numrange_test2 where nr = numrange(1.1, 2.3);
 nr 
----
(0 rows)

set enable_nestloop=t;
set enable_hashjoin=f;
set enable_mergejoin=f;
select * from numrange_test natural join numrange_test2 order by nr;
    nr     
-----------
 empty
 (,5)
 [1.1,2.2)
 [1.1,2.2)
(4 rows)

set enable_nestloop=f;
set enable_hashjoin=t;
set enable_mergejoin=f;
select * from numrange_test natural join numrange_test2 order by nr;
    nr     
-----------
 empty
 (,5)
 [1.1,2.2)
 [1.1,2.2)
(4 rows)

set enable_nestloop=f;
set enable_hashjoin=f;
set enable_mergejoin=t;
select * from numrange_test natural join numrange_test2 order by nr;
    nr     
-----------
 empty
 (,5)
 [1.1,2.2)
 [1.1,2.2)
(4 rows)

set enable_nestloop to default;
set enable_hashjoin to default;
set enable_mergejoin to default;
-- keep numrange_test around to help exercise dump/reload
DROP TABLE numrange_test2;
--
-- Apply a subset of the above tests on a collatable type, too
--
CREATE TABLE textrange_test (tr textrange);
create index textrange_test_btree on textrange_test(tr);
INSERT INTO textrange_test VALUES('[,)');
INSERT INTO textrange_test VALUES('["a",]');
INSERT INTO textrange_test VALUES('[,"q")');
INSERT INTO textrange_test VALUES(textrange('b', 'g'));
INSERT INTO textrange_test VALUES('empty');
INSERT INTO textrange_test VALUES(textrange('d', 'd', '[]'));
SELECT tr, isempty(tr), lower(tr), upper(tr) FROM textrange_test;
  tr   | isempty | lower | upper 
-------+---------+-------+-------
 (,)   | f       |       | 
 [a,)  | f       | a     | 
 (,q)  | f       |       | q
 [b,g) | f       | b     | g
 empty | t       |       | 
 [d,d] | f       | d     | d
(6 rows)

SELECT tr, lower_inc(tr), lower_inf(tr), upper_inc(tr), upper_inf(tr) FROM textrange_test;
  tr   | lower_inc | lower_inf | upper_inc | upper_inf 
-------+-----------+-----------+-----------+-----------
 (,)   | f         | t         | f         | t
 [a,)  | t         | f         | f         | t
 (,q)  | f         | t         | f         | f
 [b,g) | t         | f         | f         | f
 empty | f         | f         | f         | f
 [d,d] | t         | f         | t         | f
(6 rows)

SELECT * FROM textrange_test WHERE range_contains(tr, textrange('f', 'fx'));
  tr   
-------
 (,)
 [a,)
 (,q)
 [b,g)
(4 rows)

SELECT * FROM textrange_test WHERE tr @> textrange('a', 'z');
  tr  
------
 (,)
 [a,)
(2 rows)

SELECT * FROM textrange_test WHERE range_contained_by(textrange('0','9'), tr);
  tr  
------
 (,)
 (,q)
(2 rows)

SELECT * FROM textrange_test WHERE 'e'::text <@ tr;
  tr   
-------
 (,)
 [a,)
 (,q)
 [b,g)
(4 rows)

select * from textrange_test where tr = 'empty';
  tr   
-------
 empty
(1 row)

select * from textrange_test where tr = '("b","g")';
 tr 
----
(0 rows)

select * from textrange_test where tr = '["b","g")';
  tr   
-------
 [b,g)
(1 row)

select * from textrange_test where tr < 'empty';
 tr 
----
(0 rows)

-- test canonical form for int4range
select int4range(1, 10, '[]');
 int4range 
-----------
 [1,11)
(1 row)

select int4range(1, 10, '[)');
 int4range 
-----------
 [1,10)
(1 row)

select int4range(1, 10, '(]');
 int4range 
-----------
 [2,11)
(1 row)

select int4range(1, 10, '()');
 int4range 
-----------
 [2,10)
(1 row)

select int4range(1, 2, '()');
 int4range 
-----------
 empty
(1 row)

-- test canonical form for daterange
select daterange('2000-01-10'::date, '2000-01-20'::date, '[]');
        daterange        
-------------------------
 [01-10-2000,01-21-2000)
(1 row)

select daterange('2000-01-10'::date, '2000-01-20'::date, '[)');
        daterange        
-------------------------
 [01-10-2000,01-20-2000)
(1 row)

select daterange('2000-01-10'::date, '2000-01-20'::date, '(]');
        daterange        
-------------------------
 [01-11-2000,01-21-2000)
(1 row)

select daterange('2000-01-10'::date, '2000-01-20'::date, '()');
        daterange        
-------------------------
 [01-11-2000,01-20-2000)
(1 row)

select daterange('2000-01-10'::date, '2000-01-11'::date, '()');
 daterange 
-----------
 empty
(1 row)

select daterange('2000-01-10'::date, '2000-01-11'::date, '(]');
        daterange        
-------------------------
 [01-11-2000,01-12-2000)
(1 row)

select daterange('-infinity'::date, '2000-01-01'::date, '()');
       daterange        
------------------------
 (-infinity,01-01-2000)
(1 row)

select daterange('-infinity'::date, '2000-01-01'::date, '[)');
       daterange        
------------------------
 [-infinity,01-01-2000)
(1 row)

select daterange('2000-01-01'::date, 'infinity'::date, '[)');
       daterange       
-----------------------
 [01-01-2000,infinity)
(1 row)

select daterange('2000-01-01'::date, 'infinity'::date, '[]');
       daterange       
-----------------------
 [01-01-2000,infinity]
(1 row)

-- test GiST index that's been built incrementally
create table test_range_gist(ir int4range);
create index test_range_gist_idx on test_range_gist using gist (ir);
insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g;
insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g;
insert into test_range_gist select int4range(g, g+10000) from generate_series(1,1000) g;
insert into test_range_gist select 'empty'::int4range from generate_series(1,500) g;
insert into test_range_gist select int4range(NULL,g*10,'(]') from generate_series(1,100) g;
insert into test_range_gist select int4range(g*10,NULL,'(]') from generate_series(1,100) g;
insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g;
-- test statistics and selectivity estimation as well
--
-- We don't check the accuracy of selectivity estimation, but at least check
-- it doesn't fall.
analyze test_range_gist;
-- first, verify non-indexed results
SET enable_seqscan    = t;
SET enable_indexscan  = f;
SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_gist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_gist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_gist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_gist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_gist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

select count(*) from test_range_gist where ir @> '{}'::int4multirange;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir @> int4multirange(int4range(10,20), int4range(30,40));
 count 
-------
   107
(1 row)

select count(*) from test_range_gist where ir && '{(10,20),(30,40),(50,60)}'::int4multirange;
 count 
-------
   271
(1 row)

select count(*) from test_range_gist where ir <@ '{(10,30),(40,60),(70,90)}'::int4multirange;
 count 
-------
  1060
(1 row)

select count(*) from test_range_gist where ir << int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
     5
(1 row)

-- now check same queries using index
SET enable_seqscan    = f;
SET enable_indexscan  = t;
SET enable_bitmapscan = f;
select count(*) from test_range_gist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_gist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_gist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_gist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_gist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_gist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

select count(*) from test_range_gist where ir @> '{}'::int4multirange;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir @> int4multirange(int4range(10,20), int4range(30,40));
 count 
-------
   107
(1 row)

select count(*) from test_range_gist where ir && '{(10,20),(30,40),(50,60)}'::int4multirange;
 count 
-------
   271
(1 row)

select count(*) from test_range_gist where ir <@ '{(10,30),(40,60),(70,90)}'::int4multirange;
 count 
-------
  1060
(1 row)

select count(*) from test_range_gist where ir << int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
     5
(1 row)

-- now check same queries using a bulk-loaded index
drop index test_range_gist_idx;
create index test_range_gist_idx on test_range_gist using gist (ir);
select count(*) from test_range_gist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_gist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_gist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_gist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_gist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_gist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

select count(*) from test_range_gist where ir @> '{}'::int4multirange;
 count 
-------
  6200
(1 row)

select count(*) from test_range_gist where ir @> int4multirange(int4range(10,20), int4range(30,40));
 count 
-------
   107
(1 row)

select count(*) from test_range_gist where ir && '{(10,20),(30,40),(50,60)}'::int4multirange;
 count 
-------
   271
(1 row)

select count(*) from test_range_gist where ir <@ '{(10,30),(40,60),(70,90)}'::int4multirange;
 count 
-------
  1060
(1 row)

select count(*) from test_range_gist where ir << int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
   189
(1 row)

select count(*) from test_range_gist where ir >> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  3554
(1 row)

select count(*) from test_range_gist where ir &< int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  1029
(1 row)

select count(*) from test_range_gist where ir &> int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
  4794
(1 row)

select count(*) from test_range_gist where ir -|- int4multirange(int4range(100,200), int4range(400,500));
 count 
-------
     5
(1 row)

-- test SP-GiST index that's been built incrementally
create table test_range_spgist(ir int4range);
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g;
insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g;
insert into test_range_spgist select int4range(g, g+10000) from generate_series(1,1000) g;
insert into test_range_spgist select 'empty'::int4range from generate_series(1,500) g;
insert into test_range_spgist select int4range(NULL,g*10,'(]') from generate_series(1,100) g;
insert into test_range_spgist select int4range(g*10,NULL,'(]') from generate_series(1,100) g;
insert into test_range_spgist select int4range(g, g+10) from generate_series(1,2000) g;
-- first, verify non-indexed results
SET enable_seqscan    = t;
SET enable_indexscan  = f;
SET enable_bitmapscan = f;
select count(*) from test_range_spgist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_spgist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_spgist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_spgist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_spgist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_spgist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_spgist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_spgist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_spgist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_spgist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_spgist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

-- now check same queries using index
SET enable_seqscan    = f;
SET enable_indexscan  = t;
SET enable_bitmapscan = f;
select count(*) from test_range_spgist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_spgist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_spgist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_spgist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_spgist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_spgist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_spgist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_spgist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_spgist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_spgist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_spgist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

-- now check same queries using a bulk-loaded index
drop index test_range_spgist_idx;
create index test_range_spgist_idx on test_range_spgist using spgist (ir);
select count(*) from test_range_spgist where ir @> 'empty'::int4range;
 count 
-------
  6200
(1 row)

select count(*) from test_range_spgist where ir = int4range(10,20);
 count 
-------
     2
(1 row)

select count(*) from test_range_spgist where ir @> 10;
 count 
-------
   130
(1 row)

select count(*) from test_range_spgist where ir @> int4range(10,20);
 count 
-------
   111
(1 row)

select count(*) from test_range_spgist where ir && int4range(10,20);
 count 
-------
   158
(1 row)

select count(*) from test_range_spgist where ir <@ int4range(10,50);
 count 
-------
  1062
(1 row)

select count(*) from test_range_spgist where ir << int4range(100,500);
 count 
-------
   189
(1 row)

select count(*) from test_range_spgist where ir >> int4range(100,500);
 count 
-------
  3554
(1 row)

select count(*) from test_range_spgist where ir &< int4range(100,500);
 count 
-------
  1029
(1 row)

select count(*) from test_range_spgist where ir &> int4range(100,500);
 count 
-------
  4794
(1 row)

select count(*) from test_range_spgist where ir -|- int4range(100,500);
 count 
-------
     5
(1 row)

-- test index-only scans
explain (costs off)
select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Sort
   Sort Key: ir
   ->  Index Only Scan using test_range_spgist_idx on test_range_spgist
         Index Cond: (ir -|- '[10,20)'::int4range)
(4 rows)

select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
     ir     
------------
 [20,30)
 [20,30)
 [20,10020)
(3 rows)

RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
-- test elem <@ range operator
create table test_range_elem(i int4);
create index test_range_elem_idx on test_range_elem (i);
insert into test_range_elem select i from generate_series(1,100) i;
SET enable_seqscan    = f;
select count(*) from test_range_elem where i <@ int4range(10,50);
 count 
-------
    40
(1 row)

-- also test spgist index on anyrange expression
create index on test_range_elem using spgist(int4range(i,i+10));
explain (costs off)
select count(*) from test_range_elem where int4range(i,i+10) <@ int4range(10,30);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Aggregate
   ->  Index Scan using test_range_elem_int4range_idx on test_range_elem
         Index Cond: (int4range(i, (i + 10)) <@ '[10,30)'::int4range)
(3 rows)

select count(*) from test_range_elem where int4range(i,i+10) <@ int4range(10,30);
 count 
-------
    11
(1 row)

RESET enable_seqscan;
drop table test_range_elem;
--
-- Btree_gist is not included by default, so to test exclusion
-- constraints with range types, use singleton int ranges for the "="
-- portion of the constraint.
--
create table test_range_excl(
  room int4range,
  speaker int4range,
  during tsrange,
  exclude using gist (room with =, during with &&),
  exclude using gist (speaker with =, during with &&)
);
insert into test_range_excl
  values(int4range(123, 123, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:00, 2010-01-02 11:00)');
insert into test_range_excl
  values(int4range(123, 123, '[]'), int4range(2, 2, '[]'), '[2010-01-02 11:00, 2010-01-02 12:00)');
insert into test_range_excl
  values(int4range(123, 123, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
ERROR:  conflicting key value violates exclusion constraint "test_range_excl_room_during_excl"
DETAIL:  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")).
insert into test_range_excl
  values(int4range(124, 124, '[]'), int4range(3, 3, '[]'), '[2010-01-02 10:10, 2010-01-02 11:10)');
insert into test_range_excl
  values(int4range(125, 125, '[]'), int4range(1, 1, '[]'), '[2010-01-02 10:10, 2010-01-02 11:00)');
ERROR:  conflicting key value violates exclusion constraint "test_range_excl_speaker_during_excl"
DETAIL:  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")).
-- test bigint ranges
select int8range(10000000000::int8, 20000000000::int8,'(]');
         int8range         
---------------------------
 [10000000001,20000000001)
(1 row)

-- test tstz ranges
set timezone to '-08';
select '[2010-01-01 01:00:00 -05, 2010-01-01 02:00:00 -08)'::tstzrange;
                            tstzrange                            
-----------------------------------------------------------------
 ["Thu Dec 31 22:00:00 2009 -08","Fri Jan 01 02:00:00 2010 -08")
(1 row)

-- should fail
select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)'::tstzrange;
ERROR:  range lower bound must be less than or equal to range upper bound
LINE 1: select '[2010-01-01 01:00:00 -08, 2010-01-01 02:00:00 -05)':...
               ^
set timezone to default;
--
-- Test user-defined range of floats
-- (type float8range was already made in test_setup.sql)
--
--should fail
create type bogus_float8range as range (subtype=float8, subtype_diff=float4mi);
ERROR:  function float4mi(double precision, double precision) does not exist
select '[123.001, 5.e9)'::float8range @> 888.882::float8;
 ?column? 
----------
 t
(1 row)

create table float8range_test(f8r float8range, i int);
insert into float8range_test values(float8range(-100.00007, '1.111113e9'), 42);
select * from float8range_test;
           f8r           | i  
-------------------------+----
 [-100.00007,1111113000) | 42
(1 row)

drop table float8range_test;
--
-- Test range types over domains
--
create domain mydomain as int4;
create type mydomainrange as range(subtype=mydomain);
select '[4,50)'::mydomainrange @> 7::mydomain;
 ?column? 
----------
 t
(1 row)

drop domain mydomain;  -- fail
ERROR:  cannot drop type mydomain because other objects depend on it
DETAIL:  type mydomainrange depends on type mydomain
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
drop domain mydomain cascade;
NOTICE:  drop cascades to type mydomainrange
--
-- Test domains over range types
--
create domain restrictedrange as int4range check (upper(value) < 10);
select '[4,5)'::restrictedrange @> 7;
 ?column? 
----------
 f
(1 row)

select '[4,50)'::restrictedrange @> 7; -- should fail
ERROR:  value for domain restrictedrange violates check constraint "restrictedrange_check"
drop domain restrictedrange;
--
-- Test multiple range types over the same subtype
--
create type textrange1 as range(subtype=text, collation="C");
create type textrange2 as range(subtype=text, collation="C");
select textrange1('a','Z') @> 'b'::text;
ERROR:  range lower bound must be less than or equal to range upper bound
select textrange2('a','z') @> 'b'::text;
 ?column? 
----------
 t
(1 row)

drop type textrange1;
drop type textrange2;
--
-- Test polymorphic type system
--
create function anyarray_anyrange_func(a anyarray, r anyrange)
  returns anyelement as 'select $1[1] + lower($2);' language sql;
select anyarray_anyrange_func(ARRAY[1,2], int4range(10,20));
 anyarray_anyrange_func 
------------------------
                     11
(1 row)

-- should fail
select anyarray_anyrange_func(ARRAY[1,2], numrange(10,20));
ERROR:  function anyarray_anyrange_func(integer[], numrange) does not exist
LINE 1: select anyarray_anyrange_func(ARRAY[1,2], numrange(10,20));
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
drop function anyarray_anyrange_func(anyarray, anyrange);
-- should fail
create function bogus_func(anyelement)
  returns anyrange as 'select int4range(1,10)' language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyrange requires at least one input of type anyrange or anymultirange.
-- should fail
create function bogus_func(int)
  returns anyrange as 'select int4range(1,10)' language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyrange requires at least one input of type anyrange or anymultirange.
create function range_add_bounds(anyrange)
  returns anyelement as 'select lower($1) + upper($1)' language sql;
select range_add_bounds(int4range(1, 17));
 range_add_bounds 
------------------
               18
(1 row)

select range_add_bounds(numrange(1.0001, 123.123));
 range_add_bounds 
------------------
         124.1231
(1 row)

create function rangetypes_sql(q anyrange, b anyarray, out c anyelement)
  as $$ select upper($1) + $2[1] $$
  language sql;
select rangetypes_sql(int4range(1,10), ARRAY[2,20]);
 rangetypes_sql 
----------------
             12
(1 row)

select rangetypes_sql(numrange(1,10), ARRAY[2,20]);  -- match failure
ERROR:  function rangetypes_sql(numrange, integer[]) does not exist
LINE 1: select rangetypes_sql(numrange(1,10), ARRAY[2,20]);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
create function anycompatiblearray_anycompatiblerange_func(a anycompatiblearray, r anycompatiblerange)
  returns anycompatible as 'select $1[1] + lower($2);' language sql;
select anycompatiblearray_anycompatiblerange_func(ARRAY[1,2], int4range(10,20));
 anycompatiblearray_anycompatiblerange_func 
--------------------------------------------
                                         11
(1 row)

select anycompatiblearray_anycompatiblerange_func(ARRAY[1,2], numrange(10,20));
 anycompatiblearray_anycompatiblerange_func 
--------------------------------------------
                                         11
(1 row)

-- should fail
select anycompatiblearray_anycompatiblerange_func(ARRAY[1.1,2], int4range(10,20));
ERROR:  function anycompatiblearray_anycompatiblerange_func(numeric[], int4range) does not exist
LINE 1: select anycompatiblearray_anycompatiblerange_func(ARRAY[1.1,...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
drop function anycompatiblearray_anycompatiblerange_func(anycompatiblearray, anycompatiblerange);
-- should fail
create function bogus_func(anycompatible)
  returns anycompatiblerange as 'select int4range(1,10)' language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anycompatiblerange requires at least one input of type anycompatiblerange or anycompatiblemultirange.
--
-- Arrays of ranges
--
select ARRAY[numrange(1.1, 1.2), numrange(12.3, 155.5)];
            array             
------------------------------
 {"[1.1,1.2)","[12.3,155.5)"}
(1 row)

create table i8r_array (f1 int, f2 int8range[]);
insert into i8r_array values (42, array[int8range(1,10), int8range(2,20)]);
select * from i8r_array;
 f1 |         f2          
----+---------------------
 42 | {"[1,10)","[2,20)"}
(1 row)

drop table i8r_array;
--
-- Ranges of arrays
--
create type arrayrange as range (subtype=int4[]);
select arrayrange(ARRAY[1,2], ARRAY[2,1]);
    arrayrange     
-------------------
 ["{1,2}","{2,1}")
(1 row)

select arrayrange(ARRAY[2,1], ARRAY[1,2]);  -- fail
ERROR:  range lower bound must be less than or equal to range upper bound
select array[1,1] <@ arrayrange(array[1,2], array[2,1]);
 ?column? 
----------
 f
(1 row)

select array[1,3] <@ arrayrange(array[1,2], array[2,1]);
 ?column? 
----------
 t
(1 row)

--
-- Ranges of composites
--
create type two_ints as (a int, b int);
create type two_ints_range as range (subtype = two_ints);
-- with debug_parallel_query on, this exercises tqueue.c's range remapping
select *, row_to_json(upper(t)) as u from
  (values (two_ints_range(row(1,2), row(3,4))),
          (two_ints_range(row(5,6), row(7,8)))) v(t);
         t         |       u       
-------------------+---------------
 ["(1,2)","(3,4)") | {"a":3,"b":4}
 ["(5,6)","(7,8)") | {"a":7,"b":8}
(2 rows)

-- this must be rejected to avoid self-inclusion issues:
alter type two_ints add attribute c two_ints_range;
ERROR:  composite type two_ints cannot be made a member of itself
drop type two_ints cascade;
NOTICE:  drop cascades to type two_ints_range
--
-- Check behavior when subtype lacks a hash function
--
create type cashrange as range (subtype = money);
set enable_sort = off;  -- try to make it pick a hash setop implementation
select '(2,5)'::cashrange except select '(5,6)'::cashrange;
   cashrange   
---------------
 ($2.00,$5.00)
(1 row)

reset enable_sort;
--
-- OUT/INOUT/TABLE functions
--
-- infer anyrange from anyrange
create function outparam_succeed(i anyrange, out r anyrange, out t text)
  as $$ select $1, 'foo'::text $$ language sql;
select * from outparam_succeed(int4range(1,2));
   r   |  t  
-------+-----
 [1,2) | foo
(1 row)

create function outparam2_succeed(r anyrange, out lu anyarray, out ul anyarray)
  as $$ select array[lower($1), upper($1)], array[upper($1), lower($1)] $$
  language sql;
select * from outparam2_succeed(int4range(1,11));
   lu   |   ul   
--------+--------
 {1,11} | {11,1}
(1 row)

-- infer anyarray from anyrange
create function outparam_succeed2(i anyrange, out r anyarray, out t text)
  as $$ select ARRAY[upper($1)], 'foo'::text $$ language sql;
select * from outparam_succeed2(int4range(int4range(1,2)));
  r  |  t  
-----+-----
 {2} | foo
(1 row)

-- infer anyelement from anyrange
create function inoutparam_succeed(out i anyelement, inout r anyrange)
  as $$ select upper($1), $1 $$ language sql;
select * from inoutparam_succeed(int4range(1,2));
 i |   r   
---+-------
 2 | [1,2)
(1 row)

create function table_succeed(r anyrange)
  returns table(l anyelement, u anyelement)
  as $$ select lower($1), upper($1) $$
  language sql;
select * from table_succeed(int4range(1,11));
 l | u  
---+----
 1 | 11
(1 row)

-- should fail
create function outparam_fail(i anyelement, out r anyrange, out t text)
  as $$ select '[1,10]', 'foo' $$ language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyrange requires at least one input of type anyrange or anymultirange.
--should fail
create function inoutparam_fail(inout i anyelement, out r anyrange)
  as $$ select $1, '[1,10]' $$ language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyrange requires at least one input of type anyrange or anymultirange.
--should fail
create function table_fail(i anyelement) returns table(i anyelement, r anyrange)
  as $$ select $1, '[1,10]' $$ language sql;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyrange requires at least one input of type anyrange or anymultirange.