PostgreSQL tsrange: is it correct for lower_inf('(-infinity,today)'::tsrange) to be false?

Question

In the course of writing a program that accepts tsrange literals from the user, which are then plugged into various SQL queries, today I was testing some tsranges to see how they are interpreted by PostgreSQL 9.3.5.

This one, in particular, behaves strangely: '(-infinity,today)'::tsrange

The lower_inf function says the lower bound is not infinite (!)

test=> SELECT lower_inf('(-infinity,today)'::tsrange);
 lower_inf
-----------
 f
(1 row)

Yet PostgreSQL reports that this tsrange contains a timestamp like '1000-01-01 BC' . . .

test=> SELECT '(-infinity,today)'::tsrange @> '1000-01-01 BC'::timestamp;
 ?column? 
----------
 t
(1 row)

Can anyone shed light on this?

Tenatenable

Looks suspect enough to justify a bug report to me. – Zworykin

@CraigRinger: I thought so, too, at first. But it turns out, this is documented. More like an unfortunate double use of the term "infinity". – Commeasure

Answer 1

The confusion stems from two different meanings of "infinity" here.

1. timestamp types accept special values for infinity and -infinity.
2. Range types have a general concept for ranges without lower / upper bound. The functions to test for it are called lower_inf() and upper_inf(), but they are really testing for "no bound" in the range. Ranges with no upper / lower bound include the value infinity / -infinity for timestamp respectively.

The manual:

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.

SQL Fiddle.

Maybe those functions should really be called something like lower_nobound() and upper_nobound() to avoid confusion ...

Commeasure

Nicely caught and well explained. I should've realised this is what was happening, but failed to RTFM first. – Zworykin

Actually I read that snippet in the documentation, but did not understand how "infinity as just another value" is different from the interval being unbounded. How can infinity be "just another value" and how is [today,infinity) different from [today,infinity]? – Tenatenable

@ErwinBrandstetter: Now I understand - not a bug, just an implementation detail that forces me to implement separate checks for (a) unbounded tsranges and (b) tsranges containing the special value 'infinity'. Thanks for your help. – Tenatenable