如何使用CREATE TYPE创建范围类型？

PostgreSQL 自 9.2 版本起提供了范围类型，用于表示一段连续的区间，比如订单金额区间、设备运行温度区间或会议时间段。系统内置的 int4range、int8range、numrange、tsrange、tstzrange、daterange 已经能覆盖不少基础场景，但当业务需要特定比较规则或希望规范化边界表示时，使用 CREATE TYPE 的 AS RANGE 子句可以创建自己的范围类型。通过指定子类型、操作符类以及规范化函数，这个自定义范围类型可以像内置类型一样参与列定义、约束检查和 GiST 索引。

CREATE TYPE AS RANGE 的语法结构与参数说明

创建范围类型的基本语法并不复杂，核心是 AS RANGE 子句及其中的参数。下面是最完整的语法形式：

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype,
    SUBTYPE_OPCLASS = subtype_operator_class,
    COLLATION = collation,
    CANONICAL = canonical_function,
    SUBTYPE_DIFF = subtype_diff_function,
    MULTIRANGE_TYPE_NAME = multirange_type_name
);

其中 SUBTYPE 是必填项，它指定范围中每一个元素的类型，也就是子类型。子类型可以是任意带排序和等值语义的标量类型，例如 integer、numeric、timestamp 等。SUBTYPE_OPCLASS 指定用于子类型比较的操作符类，默认使用子类型的默认 B-tree 操作符类。对于自定义操作符类或者需要特殊排序规则的场景，这个参数必须显式给出。

COLLATION 只在子类型为可排序类型时有用，用来指定范围边界排序所采用的排序规则；如果子类型不支持排序规则，设置该参数会报错。CANONICAL 和 SUBTYPE_DIFF 是可选的函数参数，分别用于规范化范围值和计算两个子类型值之间的差异。它们对索引效率和数据一致性影响很大，后面会单独展开。最后 MULTIRANGE_TYPE_NAME 用于指定关联的多范围类型名称，PostgreSQL 14 及以上版本支持多范围类型，可以表示不连续的范围集合。

创建命令执行成功后，这个新建的类型会出现在 pg_type 系统目录中，并且系统会自动生成一组范围操作符和函数，例如包含判断、重叠判断、合并、交集等。这意味着我们可以直接使用 @> 运算符来判断一个值是否落在范围内，而不需要手动比较上下界。

自定义 canonical 函数与 subtype_diff 函数

canonical 函数接收一个范围值作为参数，返回一个等价的、规范化的范围值。它最常见的用途是处理离散类型中的开区间边界。以整数范围为例，开区间 (1,5) 和闭区间 [2,4] 表示完全相同的整数集合。如果不进行规范化，数据库可能把这两个范围当成不同的值，导致索引膨胀和比较结果不一致。通过 canonical 函数，我们可以把 (1,5) 转换为 [2,4]，保证相同集合始终用同一种形式存储。

写 canonical 函数时需要注意，它必须返回一个合法的范围值，不能改变原始范围的实际集合。PostgreSQL 会在写入和更新范围值时调用它，如果函数返回了错误的结果，查询结果可能出错。下面这个函数演示了离散整数范围的规范化逻辑，它接收内置的 int4range 并返回规范化后的 int4range：

CREATE FUNCTION int4range_canonical(r int4range)
RETURNS int4range
AS $$
DECLARE
    lower_val integer;
    upper_val integer;
    lower_inc boolean;
    upper_inc boolean;
BEGIN
    IF lower(r) IS NULL OR upper(r) IS NULL THEN
        RETURN r;
    END IF;

    lower_val := lower(r)::integer;
    upper_val := upper(r)::integer;
    lower_inc := lower_inc(r);
    upper_inc := upper_inc(r);

    IF NOT lower_inc THEN
        lower_val := lower_val + 1;
        lower_inc := true;
    END IF;

    IF NOT upper_inc THEN
        upper_val := upper_val - 1;
        upper_inc := true;
    END IF;

    RETURN int4range(lower_val, upper_val, '[)');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

这个函数里使用了 lower、upper、lower_inc 和 upper_inc 几个系统函数来拆解范围边界。注意 lower_inc 返回布尔值，表示下界是否为闭区间。处理时先把开区间转换为闭区间，再用 int4range 构造标准半开区间。虽然这里最后统一成了 [) 形式，但它仍然保持了原始整数集合不变。实际创建自定义范围类型时，如果子类型是离散的，也可以参考这段逻辑编写自己的规范化函数。

subtype_diff 函数的参数是两个子类型值，返回值是一个双精度浮点数，表示它们之间的差值。这个函数对 GiST 索引的精度很重要。如果没有提供 subtype_diff，范围索引仍然可以工作，但无法准确估计某些范围谓词的选择性，可能导致查询计划偏向全表扫描。对于 numeric 类型，可以直接使用内置的 numeric_sub 函数；对于 timestamp 类型，可以用 timestamp_sub。自定义离散类型如果没有合适的差异函数，也可以返回一个粗略的距离值。

完整示例：创建温度范围类型并进行查询

接下来创建一个基于 numeric 的温度范围类型，用于存储设备允许运行的温度区间。温度是连续量，不需要像整数那样做离散开闭区间的步长转换，因此 canonical 函数可以省略。但为了优化范围索引，我们提供 numeric_sub 作为 SUBTYPE_DIFF。

CREATE TYPE temprange AS RANGE (
    SUBTYPE = numeric,
    SUBTYPE_OPCLASS = numeric_ops,
    SUBTYPE_DIFF = numeric_sub
);

创建完成后，可以在建表时直接使用这个类型。下面建立一个传感器读数表，其中 reading_range 列存放温度范围，并创建一个 GiST 索引来加速范围查询：

CREATE TABLE temp_readings (
    id bigserial PRIMARY KEY,
    device_id integer NOT NULL,
    reading_range temprange NOT NULL,
    recorded_at timestamptz DEFAULT now()
);

CREATE INDEX idx_temp_readings_range
ON temp_readings
USING GiST (reading_range);

插入数据时，范围值可以用字符串形式表示，再通过 ::temprange 转换。边界使用方括号表示包含，圆括号表示不包含，例如 [18.5,24.0) 表示大于等于 18.5 且小于 24.0。查询某个温度是否落在范围内，使用包含运算符 @>；查询两个范围是否重叠，使用重叠运算符 &&。

INSERT INTO temp_readings (device_id, reading_range)
VALUES
    (1, '[18.5,24.0)'::temprange),
    (2, '[15.0,20.0)'::temprange),
    (3, '[22.0,30.0)'::temprange);

SELECT * FROM temp_readings
WHERE reading_range @> 22.5::numeric;

SELECT device_id FROM temp_readings
WHERE reading_range && '[19.0,23.0)'::temprange;

第一条查询返回设备 3，因为 22.5 落在 [22.0,30.0) 内；第二条查询返回设备 1 和 3，因为它们的范围与 [19.0,23.0) 相交。GiST 索引能够高效处理这类范围谓词，即使表数据量很大，也能避免顺序扫描。

如果希望保证范围不为空，可以在表约束中使用范围函数 isempty。例如添加检查约束：

ALTER TABLE temp_readings
ADD CONSTRAINT temp_range_not_empty
CHECK (NOT isempty(reading_range));

还可以使用 lower 和 upper 函数抽取边界值，或者使用 lower_inc 和 upper_inc 判断边界是否包含。这些函数让自定义范围类型在应用层处理起来非常灵活。

与内置范围类型的对比及使用建议

内置范围类型和自定义范围类型并不是对立关系。如果业务区间只是普通的整数、日期或时间戳区间，直接使用内置类型即可，它们已经配置了合适的 canonical 函数和 subtype_diff 函数，开箱即用。自定义范围类型的价值在于当子类型是自定义枚举、自定义复合类型或者需要特殊排序规则时，可以通过 AS RANGE 精确控制比较逻辑。

创建自定义范围类型后，建议至少考虑两个问题。第一，子类型的等值语义是否清晰。范围类型的很多操作依赖子类型等值判断，如果子类型没有稳定的等值规则，范围类型的行为可能出现意外。第二，是否提供 canonical 函数和 subtype_diff 函数。前者避免同一个集合出现多种表示，后者提升索引查询的统计信息质量，对性能敏感的系统尤为重要。

另外，范围类型虽然支持 B-tree 索引，但真正发挥区间查询优势的是 GiST 和 SP-GiST 索引。建表时如果经常执行包含、重叠、左/右相邻等范围谓词，应该优先创建 GiST 索引。对于不连续的范围集合，还可以考虑使用多范围类型，但多范围类型同样需要在创建时通过 MULTIRANGE_TYPE_NAME 指定关联名称。

总的来说，CREATE TYPE 的 AS RANGE 子句让 PostgreSQL 的范围抽象可以延伸到任意标量类型。理解它的参数含义，尤其是 canonical 和 subtype_diff，能够帮助你在数据一致性和查询性能之间找到合适的平衡点。

PostgreSQLCREATE TYPE范围类型修改时间：2026-10-01 23:57:23