1

I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query:

select  upper(('[2005-12-01,2005-12-04]')::daterange)

It returns 2005-12-05 instead of 2005-12-04.

The following query excludes the upper bound by specifying ) instead of ]:

select  upper(('[2005-12-01,2005-12-04)')::daterange)

It returns 2005-12-04.

Isn't the upper() function supposed to return the upper bound limit of a given range? If so, by specifying [2005-12-01,2005-12-04] as the range, it should return the upper bound which is 2005-12-04. It returns 2005-12-05 instead, which is very odd to me.

Any explanation on why this happens? I only know that '[2005-12-01,2005-12-04]'::daterange is translated by PostgreSQL to '[2005-12-01,2005-12-05)'::daterange, but this doesn't explain why 2005-12-05 should be the upper bound when I have explicitly specified 2005-12-04 as the upper bound.

Improve this question

1 Answer 1

Reset to default
2

'[2005-11-01,2005-12-04]' is just another string literal for a date range to represent the canonical form '[2005-11-01,2005-12-05)'. The documentation:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

Bold emphasis mine.

Else you would have two equal values and upper() would still return a different result, which wouldn't be acceptable.

Improve this answer
4
  • Hi Erwin, I still don't get it. [•,•) and [•,•] should have different upper bounds but PostgreSQL returns the same. I didn't use the daterange(•,•) notation because I knew it is translated into a range of type [•,•) so I explicitly specified the [•,•]::daterange notation. By specifying [•,•] I'm forcing PostgreSQL to use the 2005-12-01≤x≤2005-12-04 range, right? From my math knowledge, the upper bound of 2005-12-01≤x≤2005-12-04 is 2005-12-04 while the upper bound of 2005-12-01≤x<2005-12-05 is 2005-12-05. May be PostgreSQL implements a different definition of upper bound?
    – pietrop
    Dec 15, 2015 at 1:08
  • @pietrop: The range types on discrete values mentioned in my quote are always transformed to canonical form internally. I.e.: [•,°]::daterange is exactly the same as [•,°+1)::daterange, and upper() always returns °+1 as (exclusive) upper bound. Follow my link and read the manual for more details.
    – Erwin Brandstetter
    Dec 15, 2015 at 1:14
  • The problem is given by the conversion from closed to opened intervals. They are both the same because they are discrete but the true upper bound really differs. PostgreSQL doesn't account for the actual definition of the interval given in the query and this leads to a wrong result from upper() relatively to how the interval was defined prior to the conversion. In mathematics the upper bound of [•,°] is ° while the upper bound of [•,°+1) is °+1. As an example, the upper bound of {1,2,3} is 3, not 4. I'm just saying the term "upper bound" is used improperly and is misleading.
    – pietrop
    Dec 15, 2015 at 1:37
  • 1
    I've just found a similar discussion on the psql mailing list: postgresql.org/message-id/… Mathematically speaking the upper() function returns a wrong result with discrete sets. Informatically speaking it returns a correct result that responds to a naive definition of upper bound. The official documentation lacks of mathematical clearness in my opinion. I'll ask for it to be improved. Thank you for your help
    – pietrop
    Dec 15, 2015 at 1:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.