From: | Julien Rouhaud <julien(dot)rouhaud(at)dalibo(dot)com> |
---|---|
To: | Dane Foster <studdugie(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Why does the range type's upper function behave inconsistently? |
Date: | 2015-07-05 17:25:03 |
Message-ID: | 5599686F.5080100@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
-----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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-07-05 17:28:07 | Re: Why does the range type's upper function behave inconsistently? |
Previous Message | Dane Foster | 2015-07-05 17:13:43 | Why does the range type's upper function behave inconsistently? |