How to check if an unbounded range is NULL on the right in PostgreSQL 9.3 - ensuring GIST indexes are used

Question

I'm using range datatypes in PG 9.3 (with btree_gist enabled, though I don't think it matters). I have GIST indexes that include these range columns. Some are int8range and some are tsrange.

I want to query with a WHERE expression essentially saying "range is NULL (unbounded) on the right side". How do I write that?

For tsrange, I can do "tsrange @> timestamp 'infinity'". But there's no equivalent for int8range. And I assume the way to do this properly for int8range should be the way for tsrange as well (not relying on timestamp-specific treatment of 'infinity').

The expression should be usable for GIST indexes (i.e. falls into the default operator class for these range types).

Help?

Answer 1

From the fine manual: http://www.postgresql.org/docs/9.4/static/functions-range.html

The upper_inf function will tell you that.

# select upper_inf(int8range(1, null));
 upper_inf 
-----------    
 t
(1 row)

# select upper_inf(int8range(1, 2));
 upper_inf 
-----------
 f
(1 row)

If you need to query on that, I don't think that indexes will help you. http://www.postgresql.org/docs/9.4/static/rangetypes.html

A GiST or SP-GiST index can accelerate queries involving these range
operators: =, &&, <@, @>, <<, >>, -|-, &<, and &> (see Table 9-47 
for more information).

You can create a partial index that will help with that query though. e.g.

# create table foo (id int primary key, bar int8range);  
CREATE TABLE
# create index on foo(bar) where upper_inf(bar) = true;
CREATE INDEX
# \d foo
      Table "freshop.foo"
 Column |   Type    | Modifiers 
--------+-----------+-----------
 id     | integer   | not null
 bar    | int8range | 
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)
    "foo_bar_idx" btree (bar) WHERE upper_inf(bar) = true

Then if you put upper_inf(bar) = true into a query, the optimizer should understand to use the foo_upper_inf_idx index.