数值范围（INT4RANGE / NUMRANGE）

数值范围是范围类型家族里最"反直觉"的一类，因为离散整数范围会被自动规范化，输入的 [1,5] 输出却变成 [1,6)。理解规范化规则是正确使用它们的前提。本节讲清 int4range、int8range、numrange 的构造、边界处理与适用场景。

前置知识

阅读本节前，建议先了解：范围类型概述

基础概念

数值范围类型一览

类型	子类型	是否规范化	存储	典型用途
`int4range`	`integer`（4 字节）	是	变长	年龄段、分数段、ID 段
`int8range`	`bigint`（8 字节）	是	变长	大 ID 段、时间戳毫秒区间
`numrange`	`numeric`	否	变长	金额区间、价格带、税率区间

规范化（Canonicalization）

离散类型（int4range / int8range）会自动把范围转换成标准的 [下界, 上界) 形式。所谓离散，是指子类型有明确的"下一个值"。

sql

SELECT '[1,5]'::int4range  AS r1,   -- [1,6)  上界 +1
       '(1,5]'::int4range  AS r2,   -- [2,6)  下界 +1，上界 +1
       '(1,5)'::int4range  AS r3;   -- [2,5)  下界 +1

-- 结果：
--   r1   |   r2   |   r3
-- -------+--------+--------
--  [1,6) | [2,6)  | [2,5)

转换规则：

输入边界写法	规范化后的下界	规范化后的上界
`[a, b]`	`a`	`b + 1`
`[a, b)`	`a`	`b`
`(a, b]`	`a + 1`	`b + 1`
`(a, b)`	`a + 1`	`b`

连续类型（numrange）无法做这种转换，因为实数没有"下一个值"。

sql

SELECT '[1,5]'::numrange AS n1,   -- [1,5]  保持原样
       '(1,5)'::numrange AS n2;   -- (1,5)  保持原样

下界包含、上界排除（`[)`）

规范化之后的整数范围永远是 [) 形式，这带来一个非常好的性质：相邻区间绝无重叠，也绝无空隙。

sql

SELECT '[1,5)'::int4range && '[5,10)'::int4range AS overlap;   -- false（相邻不重叠）
SELECT '[1,5)'::int4range -|- '[5,10)'::int4range AS adjacent; -- true（相邻）

术语提示

数值范围（Numeric Range）、规范化（Canonicalization）、子类型（Subtype）、相邻（Adjacent）、无界范围（Unbounded Range）。本节频繁使用这些术语。

语法与用法

构造函数

int4range 有三个参数形式：

text

int4range(lower, upper)              -- 默认 [)
int4range(lower, upper, bounds)      -- bounds ∈ '[)' '(]' '[]' '()'
int4range(lower, NULL)               -- 上界无穷
int4range(NULL, upper)               -- 下界无穷

sql

SELECT int4range(1, 10)          AS a,   -- [1,10)
       int4range(1, 10, '[)')    AS b,   -- [1,10)
       int4range(1, 10, '[]')    AS c,   -- [1,11)
       int4range(1, NULL)        AS d,   -- [1,)
       int4range(NULL, 10)       AS e;   -- (,10)

int8range 用法完全一致：

sql

SELECT int8range(1000000000000, 2000000000000) AS r;   -- [1000000000000,2000000000000)

numrange 可以直接用数值字面量：

sql

SELECT numrange(0.0, 100.0)          AS a,   -- [0.0,100.0)
       numrange(0.0, 100.0, '[]')    AS b,   -- [0.0,100.0]
       numrange(99.99, NULL)         AS c;   -- [99.99,)

字面量写法

text

'[1,10)'::int4range
'[1,10]'::int4range          -- 会被规范化
'(,10)'::int4range           -- 下界无穷
'[1,)'::int4range            -- 上界无穷
'(,)'::int4range             -- 全范围
'empty'::int4range           -- 空

建表

sql

DROP TABLE IF EXISTS price_bands;

CREATE TABLE price_bands (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT      NOT NULL,
  band     NUMRANGE   NOT NULL,     -- 价格带（连续，不规范化）
  tier     INT4RANGE  NOT NULL,     -- 会员等级段（离散）
  CONSTRAINT chk_band_not_empty CHECK (NOT isempty(band)),
  CONSTRAINT chk_tier_not_empty CHECK (NOT isempty(tier))
);

INSERT INTO price_bands (name, band, tier) VALUES
  ('入门款', '[0,100)'::numrange,   '[0,1000)'::int4range),
  ('标准款', '[100,500)'::numrange, '[1000,5000)'::int4range),
  ('旗舰款', '[500,)'::numrange,    '[5000,)'::int4range);

SELECT id, name, band, tier FROM price_bands ORDER BY id;

-- 结果：
--  id |  name  |    band     |    tier
-- ----+--------+-------------+--------------
--   1 | 入门款  | [0,100)     | [0,1000)
--   2 | 标准款  | [100,500)   | [1000,5000)
--   3 | 旗舰款  | [500,)      | [5000,)

详细说明

规范化行为详解

规范化会改变数值，这是初学者最容易困惑的地方。看几个具体例子：

sql

-- 整数：上界包含会被 +1
SELECT '[1,5]'::int4range AS r;      -- [1,6)
SELECT '(1,5]'::int4range AS r2;     -- [2,6)
SELECT '[1,5)'::int4range AS r3;     -- [1,5)
SELECT 'empty'::int4range AS r4;     -- empty

-- 验证规范化后的包含关系
SELECT '[1,5]'::int4range @> 5 AS contains_five;    -- true（因为变成了 [1,6)）
SELECT '[1,5]'::int4range @> 6 AS contains_six;     -- false

再看"是否包含上界"的直觉陷阱：

sql

-- 直觉上 '[1,5]' 应该包含 5，规范化后确实包含 5
SELECT '[1,5]'::int4range @> 5 AS a;   -- true

-- 但 '[1,5)' 不包含 5
SELECT '[1,5)'::int4range @> 5 AS b;   -- false

-- 有趣的是，两者规范化后完全相同
SELECT '[1,5]'::int4range = '[1,6)'::int4range AS same;   -- true
SELECT '[1,5]'::int4range = '[1,5)'::int4range AS same2;  -- false

相等判断也是基于规范化后的值：

sql

SELECT '[1,3]'::int4range = '[1,4)'::int4range AS eq1;   -- true
SELECT '(0,4)'::int4range = '[1,4)'::int4range AS eq2;   -- true
SELECT '[1,3]'::int4range = '[1,3)'::int4range AS eq3;   -- false

`int8range` 与 `int4range` 的选择

int8range 除了子类型范围更大，行为与 int4range 完全一致。

sql

SELECT int8range(0, 10000000000) @> 9999999999 AS contains;   -- true
SELECT '[1,1000000]'::int8range AS normalized;                -- [1,1000001)

选择依据：看子类型的数据范围。IDs、时间戳毫秒值这类可能超过 21 亿的数值，用 int8range。

sql

-- 用 int4range 存超大值会报错
SELECT int4range(1, 3000000000);
-- ERROR:  integer out of range

-- 用 int8range 正常
SELECT int8range(1, 3000000000);
-- 结果：[1,3000000000)

空范围（Empty Range）

空范围不包含任何值，它的下界与上界都是 NULL。

sql

SELECT 'empty'::int4range AS e,
       isempty('empty'::int4range) AS is_e,
       lower('empty'::int4range)   AS lo,
       upper('empty'::int4range)   AS up;

-- 结果：
--   e   | is_e | lo | up
-- ------+------+----+----
--  empty | t    |    |

空范围的产生方式：当规范化后下界 ≥ 上界时，范围自动变空。

sql

SELECT '[5,5)'::int4range AS a,   -- empty
       '[5,3)'::int4range AS b,   -- empty
       '(5,6)'::int4range AS c;   -- empty（规范化成 [6,6)）

-- 结果：
--   a   |   b   |   c
-- ------+-------+-------
--  empty | empty | empty

注意 numrange 因为没有规范化，[5,5] 不是空的：

sql

SELECT '[5,5]'::numrange AS a,    -- [5,5]（含单个值 5）
       '[5,5)'::numrange AS b;    -- empty

-- 结果：
--   a    |   b
-- -------+-------
--  [5,5] | empty

这一点很重要：离散类型的"单点区间"无法表示（[5,5] 会变成 empty），而连续类型可以。

sql

-- 整数：想表示"只有 5"这个单点，必须用 [5,6)
SELECT '[5,6)'::int4range @> 5 AS only_five;   -- true
SELECT '[5,6)'::int4range @> 6 AS not_six;     -- false

无界范围与边界值处理

无界范围（Unbounded Range）用省略边界表示，下界或上界为无穷。

sql

SELECT '[100,)'::numrange  AS a,   -- 100 及以上
       '(,100)'::numrange  AS b,   -- 100 以下（不含 100）
       '(,)'::numrange     AS c;   -- 全部

-- 判断是否有界
SELECT lower_inf('(,100)'::numrange)  AS lo_inf,   -- true
       upper_inf('(,100)'::numrange)  AS up_inf,   -- false
       lower_inf('[100,)'::numrange)  AS lo_inf2;  -- false

无界范围的 lower / upper 返回 NULL：

sql

SELECT lower('(,100)'::numrange) AS lo,   -- NULL
       upper('(,100)'::numrange) AS up;   -- 100

用 COALESCE 处理展示层：

sql

SELECT name,
       COALESCE(lower(band)::text, '-∞') AS min_price,
       COALESCE(upper(band)::text, '+∞') AS max_price
FROM price_bands
ORDER BY id;

-- 结果：
--   name  | min_price | max_price
-- --------+-----------+-----------
--  入门款  | 0         | 100
--  标准款  | 100       | 500
--  旗舰款  | 500       | +∞

`numrange` 的精度问题

numrange 的子类型是 numeric，保留精确的小数表示，不会出现浮点误差。

sql

SELECT numrange(0.1, 0.3) @> 0.2 AS r1;   -- true
SELECT numrange(0.1, 0.3) @> 0.3 AS r2;   -- false（上界排除）

但要注意 numeric 的相同数值可能有不同表示：

sql

SELECT 1.0 = 1.00 AS same_value;   -- true（数值比较按值）
SELECT numrange(1.0, 2.0) = numrange(1.00, 2.00) AS same_range;  -- true

适用场景

场景一：价格带划分。用 numrange 存价格区间，判断商品属于哪一档。

sql

SELECT p.name AS product, b.name AS band
FROM products p
JOIN price_bands b
  ON p.price::numeric <@ b.band
ORDER BY p.name;

场景二：分数段/年龄段。用 int4range 存等级区间。

sql

DROP TABLE IF EXISTS score_levels;

CREATE TABLE score_levels (
  level_name TEXT      NOT NULL,
  score_rng  INT4RANGE NOT NULL
);

INSERT INTO score_levels (level_name, score_rng) VALUES
  ('优秀', '[90,100]'::int4range),   -- 规范化成 [90,101)
  ('良好', '[80,90)'::int4range),
  ('及格', '[60,80)'::int4range),
  ('不及格', '[0,60)'::int4range);

SELECT level_name, score_rng FROM score_levels ORDER BY lower(score_rng) DESC;

-- 结果：
--  level_name | score_rng
-- ------------+-----------
--  优秀        | [90,101)
--  良好        | [80,90)
--  及格        | [60,80)
--  不及格      | [0,60)

注意 [90,100] 变成了 [90,101)，这是为了能包含满分 100。

场景三：ID 段分配。用 int8range 管理分片 ID 范围。

sql

DROP TABLE IF EXISTS id_shards;

CREATE TABLE id_shards (
  shard_no  INT       NOT NULL PRIMARY KEY,
  id_range  INT8RANGE NOT NULL,
  enabled   BOOLEAN   NOT NULL DEFAULT true
);

INSERT INTO id_shards (shard_no, id_range) VALUES
  (0, int8range(1,          1000000000)),
  (1, int8range(1000000000, 2000000000)),
  (2, int8range(2000000000, 3000000000));

-- 查某个 ID 属于哪个分片
SELECT shard_no
FROM id_shards
WHERE id_range @> 1500000000;
-- 结果：1

场景四：版本兼容区间。

sql

SELECT '[2,5)'::int4range @> 3 AS supports_v3;    -- true
SELECT '[2,5)'::int4range @> 5 AS supports_v5;    -- false

数值范围 vs 两个数值列

维度	数值范围	min_value + max_value
单值归属判断	`rng @> 42`	`42 >= min AND 42 < max`
区间重叠检测	`r1 && r2`	4 个条件
防止区间重叠	`EXCLUDE USING GIST`	不支持
取边界值	`lower()` / `upper()`	直接读列
数学运算	不直接支持	直接支持
开放边界	原生支持	需额外标记列

如果业务需要频繁对边界做数学运算（如"最小值加 10%"），用两个数值列更方便。如果核心需求是区间归属与重叠判断，范围类型明显更优。

实战示例

用"商品价格带与会员折扣"做完整演练。

第 1 步：建表

sql

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS price_bands;

CREATE TABLE products (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  TEXT     NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE price_bands (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  band_name TEXT     NOT NULL,
  band      NUMRANGE NOT NULL,
  discount  NUMERIC(4,2) NOT NULL DEFAULT 1.00,
  CONSTRAINT chk_band_not_empty CHECK (NOT isempty(band))
);

INSERT INTO products (name, price) VALUES
  ('鼠标',     59.00),
  ('键盘',    299.00),
  ('显示器',  1299.00),
  ('主机',    6999.00);

INSERT INTO price_bands (band_name, band, discount) VALUES
  ('低价位',  numrange(NULL, 100),      1.00),
  ('中价位',  numrange(100, 1000),      0.95),
  ('高价位',  numrange(1000, 5000),     0.90),
  ('超高价',  numrange(5000, NULL),     0.85);

SELECT id, band_name, band, discount FROM price_bands ORDER BY id;

-- 结果：
--  id | band_name |     band      | discount
-- ----+-----------+---------------+----------
--   1 | 低价位     | (,100)        |     1.00
--   2 | 中价位     | [100,1000)    |     0.95
--   3 | 高价位     | [1000,5000)   |     0.90
--   4 | 超高价     | [5000,)       |     0.85

第 2 步：单值归属查询

sql

SELECT p.name AS product, p.price, b.band_name, b.discount,
       round(p.price * b.discount, 2) AS final_price
FROM products p
JOIN price_bands b ON p.price <@ b.band
ORDER BY p.price;

-- 结果：
--  product |  price  | band_name | discount | final_price
-- ---------+---------+-----------+----------+-------------
--  鼠标     |   59.00 | 低价位     |     1.00 |       59.00
--  键盘     |  299.00 | 中价位     |     0.95 |      284.05
--  显示器   | 1299.00 | 高价位     |     0.90 |     1169.10
--  主机     | 6999.00 | 超高价     |     0.85 |     5949.15

第 3 步：边界值验证

sql

-- 100 属于哪一档？[100,1000) 含 100，所以是中价位
SELECT band_name FROM price_bands WHERE 100::numeric <@ band;
-- 结果：中价位

-- 1000 属于哪一档？[1000,5000) 含 1000
SELECT band_name FROM price_bands WHERE 1000::numeric <@ band;
-- 结果：高价位

-- 边界值不会同时命中两档（因为 [) 语义）
SELECT count(*) AS matched_bands
FROM price_bands WHERE 100::numeric <@ band;
-- 结果：1

第 4 步：区间重叠检测

sql

-- 找出与 [500,2000) 有重叠的价格带
SELECT band_name, band
FROM price_bands
WHERE band && numrange(500, 2000)
ORDER BY lower(band);

-- 结果：
--  band_name |     band
-- -----------+---------------
--  中价位     | [100,1000)
--  高价位     | [1000,5000)

-- 找出完全被 [0,2000) 包含的价格带
SELECT band_name, band
FROM price_bands
WHERE band <@ numrange(0, 2000)
ORDER BY lower(band);

-- 结果：
--  band_name |     band
-- -----------+-------------
--  中价位     | [100,1000)
--  高价位     | [1000,5000)

第 5 步：用排除约束防止价格带重叠

sql

DROP TABLE IF EXISTS price_bands_v2;

CREATE TABLE price_bands_v2 (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  band_name TEXT     NOT NULL,
  band      NUMRANGE NOT NULL,
  -- 价格带之间不允许重叠
  CONSTRAINT no_band_overlap EXCLUDE USING GIST (band WITH &&)
);

INSERT INTO price_bands_v2 (band_name, band) VALUES
  ('低价位', numrange(NULL, 100)),
  ('中价位', numrange(100, 1000));

-- 尝试插入重叠区间
INSERT INTO price_bands_v2 (band_name, band) VALUES
  ('冲突档', numrange(500, 1500));

-- 结果：
-- ERROR:  conflicting key value violates exclusion constraint "no_band_overlap"
-- DETAIL:  Key (band)=([500,1500)) conflicts with existing key (band)=([100,1000)).

第 6 步：区间合并与统计

sql

-- 把所有价格带合并成一个多范围（PG14+）
SELECT range_agg(band) AS merged_bands
FROM price_bands;

-- 结果：
--              merged_bands
-- ----------------------------------------
--  {[,100),[100,1000),[1000,5000),[5000,)}

注意 range_agg 是 PG14+ 新增函数，且它不会自动合并相邻区间（[,100) 与 [100,1000) 相邻但保留为两段）。如需合并，需要配合 range_merge 或自定义逻辑。

第 7 步：清理

sql

DROP TABLE price_bands_v2;
DROP TABLE price_bands;
DROP TABLE products;
DROP TABLE score_levels;
DROP TABLE id_shards;

注意事项

离散类型会被规范化：'[1,5]'::int4range 存成 [1,6)，输出与输入不同是正常的。
[5,5] 在整数范围里是 empty：规范化后变成 [5,6) 才对；想表示单点用 int4range(5,6)。
numrange 不规范化：'[5,5]'::numrange 是"只含 5"的合法区间，不是空。
相等比较基于规范化后的值：'[1,3]'::int4range = '[1,4)'::int4range 为 true。
int4range 子类型上限约 21 亿：超出范围会报 integer out of range。
无界范围的 lower / upper 返回 NULL：展示前必须用 COALESCE 或 lower_inf / upper_inf 处理。
空范围的 lower / upper 也是 NULL：和无穷边界一样返回 NULL，但 isempty 为 true。
numrange 的边界值不受"下一个值"影响：(,100) 与 [100,) 之间确实存在空隙。
排除约束需要 GiST/SP-GiST：B-tree 不支持 && 操作符。
range_agg 不自动合并相邻区间：需要合并时要显式处理。
不要用 float / real 做范围子类型：PostgreSQL 不提供内置的浮点范围类型，需要自定义。

最佳实践

整数区间用 int4range，大数用 int8range：按子类型上限选择，不要指望 int4range 溢出后自动升级。
优先写 [) 形式：虽然 [] 会被规范化，但直接写 [) 可读性更好、意图更明确。
金额区间用 numrange：numeric 的精确性对金额场景很重要。
给范围列加 NOT NULL + CHECK (NOT isempty(...))：排除无意义的空值。
重叠敏感的表加排除约束：EXCLUDE USING GIST (band WITH &&) 是硬保障。
查询单值归属用 <@：比 @> 更直观（"这个值在区间内"）。
展示层统一用 lower / upper + COALESCE：不要向前端暴露原始的 [) 格式。
给范围列建 GiST 索引：&&、<@、@> 查询都能受益。
避免用范围类型做算术累加：需要数值运算时拆成两列更合适。

继续学习：日期时间范围（TSRANGE / TSTZRANGE / DATERANGE）

参考链接

PostgreSQL 官方文档：Range Types
PostgreSQL 官方文档：Range Functions and Operators
PostgreSQL 官方文档：Constraints
PostgreSQL 官方文档：Numeric Types

数值范围（INT4RANGE / NUMRANGE） ​

基础概念 ​

数值范围类型一览 ​

规范化（Canonicalization） ​

下界包含、上界排除（[)） ​

语法与用法 ​

构造函数 ​

字面量写法 ​

建表 ​

详细说明 ​

规范化行为详解 ​

int8range 与 int4range 的选择 ​

空范围（Empty Range） ​

无界范围与边界值处理 ​

numrange 的精度问题 ​

适用场景 ​

数值范围 vs 两个数值列 ​

实战示例 ​

第 1 步：建表 ​

第 2 步：单值归属查询 ​

第 3 步：边界值验证 ​

第 4 步：区间重叠检测 ​

第 5 步：用排除约束防止价格带重叠 ​