postgresql - Postgres int4range 上限意外值

Question

使用 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

让我们进一步检查:

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

来自 pg 文档:

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

虽然 3 在技术上是整数范围 [1,3) ∩ ℕ = {1, 2} 的上限，但所有 ≥ 2 的自然数也是如此。我希望 upper 函数返回范围的 supremum(最小上限)。

我错过了什么吗？

Answer 1

发生这种情况是因为 int4range 是 discrete range .这样的范围总是自动转换成它们的规范表示，以便能够测试等价性，例如:

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.