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?

pozs · Accepted Answer · 2015-01-15 19:10:18Z

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, 2015 at 19:10

pozs

36.6k5 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

damisan Over a year ago

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.

pozs Over a year ago

@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

Mike Sherrill 'Cat Recall' · Accepted Answer · 2020-09-01 11:21:42Z

1

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

edited Sep 1, 2020 at 11:21

answered Jan 15, 2015 at 19:37

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Collectives™ on Stack Overflow

Postgres int4range upper bound unexpected value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related