Why `infinity` is not contained by range?

Question

I have range which upper bound is infinity.

Why infinity is not contained by this range?

I expect that this returns true:

=> select '[2019-01-02, infinity]'::daterange @> 'infinity'::date;
 ?column? 
----------
 f
(1 row)

because:

=> select 'infinity'::date = 'infinity'::date;
 ?column? 
----------
 t
(1 row)

Related: https://stackoverflow.com/questions/27103608 and https://stackoverflow.com/questions/15578050 — , Apr 29 '19 at 14:23

score 1 · Answer 1 · 2019-04-29T14:31:20.733

1

select '[2019-01-02, infinity]'::daterange;
+-------------------------------------------+
| daterange                                 |
|-------------------------------------------|
| DateRange('2019-01-02', 'infinity', '[)') |
+-------------------------------------------+

[) means that the right border is excluded.

But note that these infinite values are never values of the range's element type, and can never be part of the range.

https://www.postgresql.org/docs/current/rangetypes.html

Edit

Also note this:

select '[2019-01-01, 2019-01-31]'::daterange;         
+---------------------------------------------+
| daterange                                   |
|---------------------------------------------|
| DateRange('2019-01-01', '2019-02-01', '[)') |
+---------------------------------------------+

The inclusive range is translated to an exclusive range one day later. How should this be done for infinity?

edited Apr 29 '19 at 14:31

answered Apr 29 '19 at 14:23

That seems at odds from the next paragraph, but I'm probably missing something here: *"But [today,infinity] means something different from [today,infinity) — the latter excludes the special timestamp value infinity."* In essense `[today, infinity]` noted here in the documentation is not possible because the previous paragraph quoted in this answer states as much. – JNevill Apr 29 '19 at 14:25
Maybe an inconsistent wording in the documentation. – Apr 29 '19 at 14:29
@LutzHorn: To my mind the `infinity` despite on it is inclusive or exclusive should be part of the range. eg: [7,infinity) @> infinity should be TRUE, [7,infinity] @> infinity should be TRUE. `infinity` is special thus should be handled specially – Eugen Konkov Apr 29 '19 at 16:28
@LutzHorn: look into [this](https://stackoverflow.com/a/55907513/4632019) solution – Eugen Konkov Apr 29 '19 at 16:37

Laurenz Albe · Accepted Answer · 2019-07-18T22:09:00.783

The documentation says:

Also, some element types have a notion of “infinity”, but that is just another value so far as the range type mechanisms are concerned. For example, in timestamp ranges, [today,] means the same thing as [today,). But [today,infinity] means something different from [today,infinity) — the latter excludes the special timestamp value infinity.

That seems to flatly contradict your observations, because

SELECT 'infinity'::date;
   date   
----------
 infinity
(1 row)

The explanation for this behavior is that PostgreSQL converts ranges of “discrete” types (date and the integer types) into their “canonical” representation:

SELECT '[2010-01-01,2010-02-28]'::daterange;
        daterange        
-------------------------
 [2010-01-01,2010-03-01)
(1 row)

This is what happened in your example: PostgreSQL adds 1 to infinity (which doesn't change the value) and converts the inclusive upper bound to an exclusive upper bound.

Now this is arguably not the correct behavior, because now infinity is not part of the interval any more. I have sent in a patch for that, and it got fixed with commit e6feef571a.

score 1 · Answer 3 · answered Apr 29 '19 at 16:36

Thanks to ilmari.

For those who is looking the working solution for select '[2019-01-02, infinity]'::daterange @> 'infinity'::date; that is: do not use infinity as a bound, use an unbounded range:

> select '[2019-01-02,)'::daterange @> 'infinity'::date;
 ?column? 
----------
 t
(1 row)

> select '[2019-01-02,]'::daterange @> 'infinity'::date;
 ?column? 
----------
 t
(1 row)

Why `infinity` is not contained by range?

3 Answers3