Postgres int4range upper bound unexpected value

Question

Using PostgreSQL 9.4:

SELECT x, lower(x), upper(x) FROM (SELECT '[1,2]'::numrange x) q;
> [1,2] | 1 | 2      -- looks OK

SELECT x, lower(x), upper(x) FROM (SELECT '[1,2]'::int4range x) q;
> [1,3) | 1 | >>3<<  -- this is unexpected

Let's check further:

SELECT x, lower(x), upper(x) FROM (SELECT '[1,3)'::numrange x) q1;
> [1,3) | 1 | 3      -- looks OK

SELECT x, lower(x), upper(x) FROM (SELECT '[1,3]'::numrange x) q1;
> [1,3] | 1 | 3      -- looks OK

From pg documentation:

upper(anyrange) | range's element type | upper bound of range | upper(numrange(1.1,2.2)) | 2.2

While 3 technically is an upper bound of the integer range [1,3) ∩ ℕ = {1, 2}, so are all natural numbers ≥ 2. I would expect the upper function returns the supremum (least upper bound) of the range.

Am I missing something?

score 3 · Answer 1 · answered Jan 15 '15 at 19:10

3

This happens because int4range is a discrete range. Such ranges always automatically converted to their canonical representation in order to be able to test equivalence, f.ex.:

SELECT '[4,8]'::int4range = '(3,9)'::int4range

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, [). User-defined range types can use other conventions, however.

answered Jan 15 '15 at 19:10

pozs

34,608
5
57
63

Yes, they are converted to the canonical form. And the range [1,2] is exactly the same a [1,3) in N. But 3 in the sup. of neither. – damisan Jan 15 '15 at 19:13
@FireBiker yes, 3 is not the supremum, but it is the upper bound (by PostgreSQL's definition) of `[1,3)`. You can use the `upper_inc()` to detect, if it's inclusive or not. I think it's because this way the functions are consistent (i.e. both `upper('[1,3)'::int4range)` and `upper('[1,3)'::numrange)` will give `3`) -- note that supremum can be referred as the *least upper bound*, while PostgreSQL only uses the term *upper bound* – pozs Jan 15 '15 at 21:15

Mike Sherrill 'Cat Recall' · Answer 2 · 2020-09-01T11:21:42.587

The canonical form of the closed range [1,2] is the half-open (or half-closed) range [1,3). The function upper() returns the upper bound of the canonical form.

select upper(int4range(1, 2, '[]'));  -- Canonical form is '[1,3)'
--
3

That range doesn't contain the value 3.

select int4range(1, 2, '[]') @> 3;
--
f

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, [). User-defined range types can use other conventions, however.

Source: PostgreSQL discrete range types

There's a different function to call if you need to know whether the value returned by upper() is inclusive.

select upper_inc(int4range(1, 2, '[]'));
--
f

Postgres int4range upper bound unexpected value

2 Answers2