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 <[email protected]> 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 > [email protected] >
