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]

 



-----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-----


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[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