Thread: tstzrange with ... 'infinity'] + upper_inf() using 9.2.4

Hi,

> select upper_inf(tsrange('2013-05-01'::timestamp, 'infinity'::timestamp, '[]'));
 upper_inf
-----------
 f
(1 row)

As far as I understood the docs, this query should return true, as the upper bound includes 'infinity'?

[http://www.postgresql.org/docs/9.2/interactive/functions-range.html]
"The lower and upper functions return null if the range is empty or the requested bound is infinite. The lower_inc,
upper_inc,lower_inf, and upper_inf functions all return false for an empty range." 

[http://www.postgresql.org/docs/9.2/interactive/rangetypes.html]
"Also, some element types have a notion of "infinity", but that is just another value so far as the range type
mechanismsare 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.
The functions lower_inf and upper_inf test for infinite lower and upper bounds of a range, respectively."


Regards
Ralph

Ralph Graulich <maillist@shauny.de> writes:
> select upper_inf(tsrange('2013-05-01'::timestamp, 'infinity'::timestamp, '[]'));
>  upper_inf
> -----------
>  f
> (1 row)

> As far as I understood the docs, this query should return true, as the upper bound includes 'infinity'?

No, that's the point of the sentence about the range mechanisms not
understanding special values of the underlying type.  upper_inf tests
for a range with no upper bound, period.  It would need some
type-specific special knowledge to do what you're hoping for, and it
has not got that.

            regards, tom lane

> No, that's the point of the sentence about the range mechanisms not
> understanding special values of the underlying type.  upper_inf tests
> for a range with no upper bound, period.  It would need some
> type-specific special knowledge to do what you're hoping for, and it
> has not got that.

Got that, thanks Tom!

So I can simply use

select tstzrange('2013-05-01'::timestamp, 'infinity'::timestamp, '[]') @> 'infinity'::timestamptz as validity;
 validity
----------
 t
(1 row)

to test for the case where the upper bound is given (= not null) and infinite.

And when one formerly used two timestamptz fields (valid_from, valid_until) to simulate the range type before the range
typewas implemented and checked for 

WHERE valid_until IS NULL

that's where we now use the "upper_inf()" function.

Regards
Ralph