Question 257582 in PostgreSQL Question posted in
The official documentation can be found here.

How exactly does inclusion in postgresql ranges work?

I am reading the documentation for ranges here and there it states:

-- includes only the single point 4
SELECT '[4,4]'::int4range;

Okay but when I execute this, I get

SELECT '[4,4]'::int4range;
 int4range
-----------
 [4,5)
(1 row)

Why is 5 included in the result?

Also getting the upper value, it gives

SELECT upper('[4,4]'::int4range);
 upper
-------
     5
(1 row)

Then the lower bound, it gives

SELECT lower('[4,4]'::int4range);
 lower
-------
     4
(1 row)

So obviously this does not include a single point? This includes 4 and 5. That is not a single value.

Also I thought when I create a range with [] I am saying lower and upper bound should be inclusive in the range, so why does running SELECT '[4,4]'::int4range; gives [4,5) where the range is enclosed in [) instead of [] that I was expecting.

I also tried with dataranges. I did

SELECT generate_series(lower('[2023-10-18,2023-10-18]'::daterange), upper('[2023-10-18,2023-10-18]'::daterange), interval '1 day')::date AS individual_date;
 individual_date
-----------------
 2023-10-18
 2023-10-19

I was expecting only 2023-10-18. Why is there two entries in the result?

It seems I really don’t understand how the ranges are supposed to work, as results conflicts with my expectations.

Tags:

2

Answers


  1. 0

    an angle bracket ([) means that that end of the interval is included, while a parenthesis (() means that the end is excluded.

    So '[4,4]'::int4range is a range that consists only of the number 4 (both ends are included), and '[4,5)'::int4range also consists only of the number 4 (the upper end 5 is excluded). PostgreSQL normalizes values range types so that the lower bound is included and the upper bound is excluded, whenever possible. This works (and confuses people) when the underlying data type is discrete, like integer or date. With a continuous data type, that normalization does not happen: you cannot rewrite '[1,2]'::numrange to an equivalent range with the upper end excluded.

    In short: there is nothing to worry, everything is working as it should.

    Login or Signup to reply.
  2. 0

    Take a look into 8.17.7. Discrete Range Types in PostgreSQL documentation. It states:

    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, [). (…)

    When you define a range like '[4,4]'::int4range it is converted to canonical form of [4,5). That is why the upper limit becomes 5.

    If it was not the case then the same range defined using different textual representations would have different properties (limits).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search