2

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?

jennykwan
  • 2,631
  • 1
  • 22
  • 33

1 Answers1

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.

BillRobertson42
  • 12,602
  • 4
  • 40
  • 57
  • OK. Pretty much what I thought. I'll do some filtered indexes on upper_inf(bar) (selectivity of 2 does nothing) and plan accordingly. – jennykwan Aug 10 '15 at 21:55
  • Yes, filtered probably better. – BillRobertson42 Aug 11 '15 at 15:51
  • @alyssackwan Updated the answer per your suggestion. – BillRobertson42 Aug 11 '15 at 18:26
  • 1
    Note that `upper_inf` and `lower_inf` behave kind of counterintuitively for `daterange`. Specifically, `upper_inf(daterange('2021-05-01',NULL,'[)'))` returns true while `upper_inf(daterange('2021-05-01','infinity','[)'))` returns false. This is because the functions check whether a range is *unbounded with NULL*, as opposed to *technically unbounded with *. And since `'infinity'` is an actual, specific `date` value in PostgreSQL (even if it doesn't map to any real-world date), a range bounded by `'infinity'` is considered bounded by PostgreSQL. – Nick Muise May 20 '21 at 18:07