自定义范围类型
内置的六种范围类型覆盖了数值与时间两大类，但现实业务里还有别的东西需要区间语义：IP 网段、版本号范围、货币区间、自定义的"金额档位"。PostgreSQL 允许用 CREATE TYPE ... AS RANGE 定义自己的范围类型（Range Type），本节讲清它的四个关键参数与常见坑。
前置知识
阅读本节前，建议先了解：范围函数
基础概念
自定义范围类型的组成
CREATE TYPE ... AS RANGE 并不需要你实现范围运算逻辑——那些由 PostgreSQL 的通用范围框架提供。你只需要告诉它四件事：
| 参数 | 必需性 | 作用 |
|---|---|---|
subtype | 必需 | 子类型（Subtype），即区间的刻度类型 |
subtype_opclass | 可选 | 子类型在索引中使用的操作符类 |
canonical | 可选 | 规范化函数，把范围转成标准形式 |
subtype_diff | 可选 | 计算两个子类型值之差的函数，供 GiST 索引使用 |
multirange_type_name | 可选 | 配套多范围类型的名字（PG14+） |
子类型必须可比较
范围框架依赖子类型的 < 与 =。任何有 B-tree 操作符类的类型都能当子类型，包括：
sql
-- 常见的可用子类型
integer, bigint, numeric, real, double precision
timestamp, timestamptz, date
text, varchar -- 字符串也能做范围（字典序）
inet, cidr -- 网络地址sql
-- 字符串范围示例：内置没有，但可以现造
CREATE TYPE textrange AS RANGE (subtype = text);
SELECT '[apple,orange)'::textrange @> 'banana'::text AS has_banana; -- true规范化函数是可选的，但很关键
内置的 int4range 有规范化（[1,5] → [1,6)），是因为它指定了 canonical 函数。自定义范围类型如果不提供 canonical，就不会规范化，边界写法会原样保留。
sql
-- 不提供 canonical：边界原样保留
CREATE TYPE intrange_raw AS RANGE (subtype = integer);
SELECT '[1,5]'::intrange_raw AS r; -- [1,5]
-- 提供 canonical 后才会转换（内置 int4range 就是这么做的）
SELECT '[1,5]'::int4range AS r; -- [1,6)没有 canonical 时的后果
不规范化意味着 '[1,5]'::intrange_raw 与 '[1,6)'::intrange_raw 被当作两个不同的范围，即使它们在数学上表示同一组整数。这会导致：
- 唯一约束/排除约束无法识别"等价"的范围；
=比较结果与直觉不符；- 相邻判定
-|-失效（[1,5]与[5,10]在 5 处重叠而非相邻）。
差函数与 GiST 索引
subtype_diff 的作用是让 GiST 索引能估算"两个边界之间有多远"，从而构建更合理的索引树。以整数为例：
sql
-- 内置 int4range 的差函数等价于
CREATE FUNCTION int4range_diff(a integer, b integer) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT (a - b)::double precision $$;不提供 subtype_diff 时 GiST 索引仍能工作，但构建质量较差，查询性能可能下降。
术语提示
子类型（Subtype）、子类型操作符类（Subtype Operator Class）、规范化函数（Canonical Function）、差函数（Subtype Diff Function）、多范围类型名（Multirange Type Name）。这四个参数是本节的核心。
语法与用法
创建语法
text
CREATE TYPE name AS RANGE (
subtype = subtype_name,
[ subtype_opclass = opclass_name, ]
[ collation = collation_name, ]
[ canonical = canonical_function, ]
[ subtype_diff = subtype_diff_function, ]
[ multirange_type_name = multirange_name ]
);最小写法
sql
-- 只要 subtype，其余全省略
CREATE TYPE floatrange AS RANGE (subtype = double precision);
SELECT '[1.5,3.7)'::floatrange AS r,
lower('[1.5,3.7)'::floatrange) AS lo,
upper('[1.5,3.7)'::floatrange) AS hi;text
r | lo | hi
----------+------+------
[1.5,3.7) | 1.5 | 3.7带规范化与差函数的完整写法
sql
-- 差函数：供 GiST 索引使用
CREATE FUNCTION intrange_my_diff(a integer, b integer) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT (a - b)::double precision $$;
-- 规范化函数：把 [a,b] 转成 [a,b+1)
CREATE FUNCTION intrange_my_canonical(r intrange_raw)
RETURNS intrange_raw
LANGUAGE sql IMMUTABLE STRICT
AS $$
SELECT intrange_raw(
CASE WHEN lower_inc(r) THEN lower(r) ELSE lower(r) + 1 END,
CASE WHEN upper_inc(r) THEN upper(r) + 1 ELSE upper(r) END,
'[)'
)
$$;注意规范化函数的输入输出类型都是待规范化的那个范围类型自身，这带来一个先有鸡还是先有蛋的问题：需要先创建类型，再创建函数，最后 ALTER TYPE 挂上函数。
sql
-- 三步走：建类型 → 建函数 → 挂函数
CREATE TYPE intrange_canon AS RANGE (subtype = integer, subtype_diff = intrange_my_diff);
CREATE FUNCTION intrange_canon_canonical(r intrange_canon)
RETURNS intrange_canon
LANGUAGE sql IMMUTABLE STRICT
AS $$
SELECT intrange_canon(
CASE WHEN lower_inc(r) THEN lower(r) ELSE lower(r) + 1 END,
CASE WHEN upper_inc(r) THEN upper(r) + 1 ELSE upper(r) END,
'[)'
)
$$;
ALTER TYPE intrange_canon SET (canonical = intrange_canon_canonical);
SELECT '[1,5]'::intrange_canon AS canonicalized; -- [1,6)规范化函数必须是 IMMUTABLE
canonical 与 subtype_diff 都必须是 IMMUTABLE 且 STRICT 的函数。写成 STABLE 会直接报错，因为这会让"同一个范围字面量在不同时刻有不同规范化结果"，索引将不可靠。
示例表
sql
DROP TABLE IF EXISTS ip_blocks;
CREATE TABLE ip_blocks (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
net_block cidrrange NOT NULL, -- 自定义网络范围类型
owner TEXT NOT NULL,
CONSTRAINT no_net_overlap EXCLUDE USING GIST (net_block WITH &&)
);详细说明
为 inet / cidr 造一个网络范围类型
网络地址是典型需要区间语义的场景：网段 10.0.0.0/8 与 10.1.0.0/16 是包含关系，10.0.0.0/24 与 10.0.1.0/24 之间可以比较先后。
sql
-- 先造差函数：把两个 inet 的距离量化，供 GiST 使用
CREATE FUNCTION inet_diff(a inet, b inet) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT
AS $$
SELECT (host(a)::inet - host(b)::inet)::double precision -- 依赖 inet 的减法运算符
$$;inet 类型原生支持 - 运算符（返回两个地址间的差值），可以直接作为 subtype_diff 的基础。
sql
CREATE TYPE cidrrange AS RANGE (
subtype = inet,
subtype_diff = inet_diff
);
SELECT '[10.0.0.0,10.1.0.0)'::cidrrange @> '10.0.5.1'::inet AS contained; -- true
SELECT '[10.0.0.0,10.1.0.0)'::cidrrange && '[10.0.128.0,10.0.192.0)'::cidrrange AS overlaps; -- truetext
contained
-----------
t字符串范围与排序规则
字符串子类型依赖排序规则（Collation）。若不指定 collation，使用数据库默认排序规则：
sql
CREATE TYPE textrange AS RANGE (subtype = text);
-- 默认 C 排序下，大写字母排在小写字母之前
SELECT '[A,z)'::textrange @> 'a'::text AS has_lower_a; -- true
SELECT '[a,z)'::textrange @> 'B'::text AS has_upper_b; -- false（B 的码点小于 a）text
has_lower_a | has_upper_b
-------------+-------------
t | f同一个字符串范围在不同排序规则下语义不同，这是字符串范围最容易出错的地方。若业务要求"按拼音/按笔画排序"，需要指定对应的 collation：
sql
CREATE TYPE textrange_zh AS RANGE (
subtype = text,
collation = "zh_CN.utf8" -- 需操作系统支持该 locale
);配套多范围类型
PostgreSQL 14 起，创建范围类型时可以顺带指定多范围类型的名字：
sql
-- PostgreSQL 14+
CREATE TYPE floatrange AS RANGE (
subtype = double precision,
multirange_type_name = floatmultirange
);
SELECT '{[1.5,3.7),[5.0,8.2)}'::floatmultirange AS mr,
lower('{[5.0,8.2)}'::floatmultirange) AS first_seg_lo;不指定名字时，PostgreSQL 会自动生成一个（通常是"范围类型名去掉 range 再加 multirange"）。
sql
-- 查看自动生成的多范围类型
SELECT t.typname AS range_type,
m.typname AS multirange_type
FROM pg_range r
JOIN pg_type t ON t.oid = r.rngtypid
JOIN pg_type m ON m.oid = r.rngmultitypid
WHERE t.typname IN ('cidrrange', 'floatrange')
ORDER BY t.typname;text
range_type | multirange_type
------------+-----------------
cidrrange | cidrmultirange
floatrange | floatmultirange自定义范围类型的系统目录
sql
-- 查看自定义范围类型的全部配置
SELECT t.typname AS range_name,
st.typname AS subtype,
opc.opcname AS opclass,
r.rngcanonical::regproc AS canonical_fn,
r.rngsubdiff::regproc AS diff_fn
FROM pg_range r
JOIN pg_type t ON t.oid = r.rngtypid
JOIN pg_type st ON st.oid = r.rngsubtype
LEFT JOIN pg_opclass opc ON opc.oid = r.rngsubopc
WHERE t.typname = 'cidrrange';text
range_name | subtype | opclass | canonical_fn | diff_fn
------------+---------+-----------+--------------+---------------
cidrrange | inet | inet_ops | - | inet_diffcanonical_fn 显示 - 表示未设置。
实际用途：IP 黑名单区间
sql
DROP TABLE IF EXISTS ip_blacklist;
CREATE TABLE ip_blacklist (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
net_block cidrrange NOT NULL,
reason TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_ip_blacklist_gist ON ip_blacklist USING GIST (net_block);
INSERT INTO ip_blacklist (net_block, reason) VALUES
('[10.0.0.0,10.0.1.0)'::cidrrange, '恶意扫描'),
('[192.168.100.0,192.168.101.0)'::cidrrange, '暴力破解'),
('[172.16.0.0,172.17.0.0)'::cidrrange, '爬虫');
-- INSERT 0 3
-- 判断某 IP 是否命中黑名单（走 GiST 索引）
SELECT id, reason
FROM ip_blacklist
WHERE net_block @> '10.0.0.128'::inet;text
id | reason
----+----------
1 | 恶意扫描何时该自定义范围类型
| 场景 | 建议 |
|---|---|
| 整数、数值、日期、时间 | 直接用内置的 int4range / numrange / datemultirange 等，不要自造 |
| 网络地址区间 | 自定义（内置没有） |
| 版本号区间 | 自定义，子类型建议用数组或自定义比较函数 |
| 字符串字典序区间 | 自定义，务必明确 collation |
| 只在应用层判断区间关系 | 不要建类型，存两个独立字段更简单 |
版本提示
CREATE TYPE ... AS RANGE 自 PostgreSQL 9.2 引入。multirange_type_name 参数以及自动创建配套多范围类型的行为是 PostgreSQL 14 新增的；在 PostgreSQL 13 及更早版本中创建范围类型不会附带多范围类型。
实战示例
用 cidrrange 搭建一套 IP 段管理系统。
第 1 步：创建差函数与范围类型
sql
-- inet 的减法运算符返回两个地址间的整数差值
CREATE FUNCTION inet_diff(a inet, b inet) RETURNS double precision
LANGUAGE sql IMMUTABLE STRICT
AS $$ SELECT (host(a)::inet - host(b)::inet)::double precision $$;
CREATE TYPE cidrrange AS RANGE (
subtype = inet,
subtype_diff = inet_diff,
multirange_type_name = cidrmultirange
);第 2 步：确认类型创建成功
sql
SELECT t.typname AS range_name,
st.typname AS subtype,
m.typname AS multirange_name
FROM pg_range r
JOIN pg_type t ON t.oid = r.rngtypid
JOIN pg_type st ON st.oid = r.rngsubtype
JOIN pg_type m ON m.oid = r.rngmultitypid
WHERE t.typname = 'cidrrange';text
range_name | subtype | multirange_name
------------+---------+-----------------
cidrrange | inet | cidrmultirange第 3 步：建表并验证基本操作
sql
DROP TABLE IF EXISTS ip_blocks;
CREATE TABLE ip_blocks (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
net_block cidrrange NOT NULL,
owner TEXT NOT NULL,
CONSTRAINT no_net_overlap EXCLUDE USING GIST (net_block WITH &&)
);
CREATE INDEX idx_ip_blocks_gist ON ip_blocks USING GIST (net_block);
INSERT INTO ip_blocks (name, net_block, owner) VALUES
('办公网', '[10.0.0.0,10.0.16.0)'::cidrrange, 'IT 部'),
('测试网', '[10.1.0.0,10.1.8.0)'::cidrrange, 'QA 组'),
('生产网', '[10.2.0.0,10.2.64.0)'::cidrrange, '运维组');
-- INSERT 0 3第 4 步：地址归属查询
sql
SELECT name, owner
FROM ip_blocks
WHERE net_block @> '10.2.3.4'::inet;text
name | owner
--------+--------
生产网 | 运维组第 5 步：时间范围扩展与重叠检测
sql
-- 某网段是否与已有网段冲突（会被排除约束拒绝）
INSERT INTO ip_blocks (name, net_block, owner) VALUES
('临时网', '[10.0.8.0,10.0.24.0)'::cidrrange, '外包');text
ERROR: conflicting key value violates exclusion constraint "no_net_overlap"
DETAIL: Key (net_block)=(10.0.8.0-10.0.24.0) conflicts with existing key (net_block)=(10.0.0.0-10.0.16.0).第 6 步：合法扩展（不与现有网段重叠）
sql
INSERT INTO ip_blocks (name, net_block, owner) VALUES
('预留网', '[10.3.0.0,10.3.8.0)'::cidrrange, 'IT 部');
-- INSERT 0 1第 7 步：合并所有网段为多范围
sql
-- PostgreSQL 14+：range_agg 支持 anyrange，包含自定义范围类型
SELECT range_agg(net_block) AS all_blocks
FROM ip_blocks;text
all_blocks
--------------------------------------------------------------------------------------------
{[10.0.0.0,10.0.16.0),[10.1.0.0,10.1.8.0),[10.2.0.0,10.2.64.0),[10.3.0.0,10.3.8.0)}第 8 步：清理
sql
DROP TABLE IF EXISTS ip_blocks;
DROP TYPE IF EXISTS cidrrange CASCADE;
DROP FUNCTION IF EXISTS inet_diff(inet, inet);DROP TYPE 必须带 CASCADE，否则会因为多范围类型 cidrmultirange 仍依赖它而失败。
注意事项
- 子类型必须有 B-tree 操作符类。 自定义的复合类型或没有排序规则的枚举（Enum）不能直接当子类型。
canonical与subtype_diff必须IMMUTABLE STRICT。 违反会被直接拒绝，或造成索引与实际数据不一致。- 创建带
canonical的类型需要三步。 先CREATE TYPE（不带 canonical）→ 建规范化函数 →ALTER TYPE ... SET (canonical = ...)。 - 不规范化会让等值判断"看起来不对"。
'[1,5]'与'[1,6)'不相等，但它们覆盖同一组整数。 - 字符串范围的语义依赖 collation。 不显式指定就用数据库默认值，跨环境迁移时可能行为不同。
DROP TYPE需要CASCADE。 多范围类型、使用该类型的表列与索引都会一并被删除。- 自定义范围类型也能用排除约束。 前提是它的子类型操作符类可用于 GiST（内置的
inet就有）。 subtype_diff返回double precision。 返回类型固定，即使子类型是整数，也要显式转换。- 范围类型不可继承、不能作为域的基础。
CREATE DOMAIN myrange AS int4range可以，但范围类型本身不能有子类型继承。 - 自定义范围类型不能有默认值中的隐式转换。 字面量必须显式转换：
'[10.0.0.0,10.1.0.0)'::cidrrange，写成字符串会被当成未知类型。 pg_range视图是排查配置的入口。rngcanonical为 0 表示没有规范化函数，rngsubdiff为 0 表示没有差函数。multirange_type_name是 PostgreSQL 14+ 参数。 低版本写了这个参数会报语法错误，但范围类型本身仍可创建。
最佳实践
- 优先使用内置范围类型。 整数的用
int4range，时间的用tstzrange，只有内置确实没有对应子类型时才自造。 - 离散子类型一定提供
canonical。 让[a,b]自动变成[a,b+1)，才能保证相邻不重叠的优良性质。 - 提供
subtype_diff让 GiST 索引更高效。 尤其是数值类子类型，差函数能让索引树的分裂更均衡。 subtype_diff的返回值要能"量化"距离。(a - b)::double precision是最常见的写法，直接借用于子类型的减法运算符。- 字符串范围显式写
collation。 生产环境不要依赖数据库默认排序规则。 - 给自定义范围类型配一个明确命名的多范围类型。 用
multirange_type_name指定，避免自动生成的名字难以记忆。 - 类型创建脚本要包含差函数与规范化函数。 把它们放在同一个迁移文件里，避免出现"类型建了但函数忘了"的半成品。
- 用
pg_range定期核对类型配置。 特别是迁移到新环境后，确认rngsubdiff与rngcanonical都指向预期的函数。 EXCLUDE USING GIST与自定义范围类型天然契合。 只要子类型有 GiST 操作符类，就能立刻获得"区间不重叠"的约束能力。
下一节
继续学习：几何类型概述
参考链接
- PostgreSQL 官方文档：CREATE TYPE
- PostgreSQL 官方文档：Range Types
- PostgreSQL 官方文档：Range Functions and Operators
- PostgreSQL 官方文档：Multirange Types
- PostgreSQL 官方文档：GiST Indexes