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?

Puke

Answer 1

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.

Alimentation

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

@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 – Alimentation

Answer 2

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

Grussing