Why does the range type's upper function behave inconsistently?

From: Dane Foster <studdugie(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why does the range type's upper function behave inconsistently?
Date: 2015-07-05 17:13:43
Message-ID: CA+WxinKGxfMK7X7dw3Ni4FoVxbEhPNuePTCkEVR-nYX=4rWKjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I don't understand the inconsistent behavior of the range types' upper
function in regard to inclusive ranges.

For example(s):
1. SELECT upper(int4range(1, 4, '[]')) = 4; -- FALSE
2. SELECT upper(int8range(1, 4, '[]')) = 4; -- FALSE
3. SELECT upper(numrange(1, 4, '[]')) = 4; -- TRUE
4. SELECT upper(tstzrange('2015-07-01: 00:00:00', now(), '[]')) = now(); --
TRUE
5. SELECT upper(daterange('2015-01-01', current_date, '[]')) =
current_date; -- FALSE

#1 & #2 are FALSE because upper returns 5 instead of 4; and #5 is FALSE
because upper returns: current_date + interval '1 day'. I don't understand
the logic behind why it would return the inclusive upper bound value for
some ranges and not others. If anyone can shed some light on this behavior
it would be greatly appreciated.

One of things I originally tried to use upper for was CHECK constraints.
That was until I wrote some unit tests and realized that upper doesn't
consistently work the way I expected. Of course my assumptions are probably
wrong so that's why I'm asking for clarification.

Regards,

Dane

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Julien Rouhaud 2015-07-05 17:25:03 Re: Why does the range type's upper function behave inconsistently?
Previous Message Greg Sabino Mullane 2015-07-05 14:47:20 Re: pgbouncer issue