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 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.
The functions lower_inf and upper_inf test for infinite lower and upper bounds of a range, respectively."
Regards
Ralph
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 type was implemented and checked for
WHERE valid_until IS NULL
that's where we now use the "upper_inf()" function.
Regards
Ralph
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general