From: Pietro Pugni <pietro.pugni@gmail.com>
To: pgsql-docs@postgresql.org
Subject: Let's improve the documentation of upper()
Date: Tue, 15 Dec 2015 03:19:08 +0100
Message-ID: <EBB489A4-94D2-45C9-AD03-DC492392EAB8@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
Hi all,
I suggest to improve the documentation related to the upper() function for discrete sets.
It should be useful to report some examples using discrete type ranges like datarange or int8range.
I found this discussion on the bug mailing list: http://www.postgresql.org/message-id/20150116152713.2582.10294@wrigleys.postgresql.org <http://www.postgresql.org/message-id/20150116152713.2582.10294@wrigleys.postgresql.org;
It states that the mathematical definition used for upper bound is actually wrong for discrete sets when explicitly defining ranges as closed sets and I agree.
I was working a lot with dates when I first found this “bug”.
Users as me expect that upper(‘[1,2]’::int8range) returns 2 but it doesn’t. From the documentation it’s hard to find why PostgreSQL returns 3 instead of 2, so an example with a brief and mathematically clear explanation should be provided (the explanation provided is not clear at all).
Also, from the answers given in the link above I suspect that this function will be never modified to work as expected but I would like to ask if it could be done in the near future.
As for now the only way to return the actual mathematical upper bound of a discrete range is to change if from being right closed to right opened. This will return the expected value:
SELECT upper(‘[1,2)’::int8range)
but this is more a hack than a real solution because that range should contain only the number 1. Given integer numbers, the interval [1,2) translates to {1} but what the user really wants is {1,2}. Again, the upper bound of {1} is 1 while the upper bound of {1,2} is 2.
The definition described in the documentation also contrasts with the lower() function. Users like me expect a similar behavior for this function, but it doesn’t and instead is mathematically correct.
Regards
Pietro Pugni
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: pietro.pugni@gmail.com
Subject: Re: Let's improve the documentation of upper()
In-Reply-To: <EBB489A4-94D2-45C9-AD03-DC492392EAB8@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox