Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks everyone. I understand now. The funny thing is I read the documentation many weeks before actually using range types for the first time but it didn't click that the documentation was describing the behavior I was observing, until now.

Thanks again,


Dane

On Sun, Jul 5, 2015 at 1:33 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 07/05/2015 10:13 AM, Dane Foster wrote:
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

To follow up on Julien Rouhaud post, if you do:

production=# select daterange('2015-01-01', current_date, '[]');
        daterange
-------------------------
 [2015-01-01,2015-07-06)
(1 row)

see that the '[]] has been changed to '[)' with tomorrows date as the upper bound.



#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


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux