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