Обсуждение: Why does the range type's upper function behave inconsistently?
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
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
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, On 05/07/2015 19:13, 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 > > #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. > Because for discrete range types, the canonical form is used, which is [). Check http://www.postgresql.org/docs/current/static/rangetypes.html and the discrete range types paragraph. Regards. > Regards, > > Dane - -- Julien Rouhaud http://dalibo.com - http://dalibo.org -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.17 (GNU/Linux) iQEcBAEBAgAGBQJVmWhvAAoJELGaJ8vfEpOqRa0H/1+QaaZm3JrGOks2FN/24j3/ US4+Zc8AJWarOtd9Nxe4FGkUeVN1kEitJVOXLn1f6tyWRTJZ1A6v8ZaJzykqj3Bj 6cifqmq+c+NNXFyOS9vou7gzIiDxrIYmDTLBc7LqT8eWUmkQKGQT4no4Cre3uD4F kAp/CvFBpyVLCGMsBP4fW7ShnyVlwk2r1KEDn8rgpVW5rPBV7KPrneoEPJ9EBHt0 jlnYpsxgnsu6OkbmTE3gA0a9Mx/pfJlN9r2TaVjH0oOVvgFDWYX6uLVJDtFJYQrf zOEjVBaGJQ1CT+2M2GEWQj7X4Px/o6tXbEx9sZikp/xD//+rH5LAuKf3NhPGE1w= =caV1 -----END PGP SIGNATURE-----
Dane Foster <studdugie@gmail.com> writes: > I don't understand the inconsistent behavior of the range types' upper > function in regard to inclusive ranges. The behavior is different for discrete vs. continuous ranges. For example, regression=# select int4range(1, 4, '[]'); int4range ----------- [1,5) (1 row) regression=# select numrange(1, 4, '[]'); numrange ---------- [1,4] (1 row) In the discrete case we normalize the bounds to '[)' style so that ranges that contain the same sets of values will compare as equal even when they were written differently. But there's no practical way to do that for continuous types. See http://www.postgresql.org/docs/9.4/static/rangetypes.html#RANGETYPES-DISCRETE regards, tom lane
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@aklaver.com
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,
Thanks again,
Dane
On Sun, Jul 5, 2015 at 1:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> 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@aklaver.com