范围类型与生成列 · 让约束替你把关 · PostgreSQL 特性详解

范围类型把「一段区间」当成一个值，配上 EXCLUDE 排他约束，数据库能在写入时就拒绝「时段重叠」——会议室预订、资源调度不再靠应用层校验。生成列则让派生字段由数据库自动维护，永远和源列同步。

范围类型与生成列 · 让约束替你把关

前面几章讲的是「PG 能存什么」。这一章换个角度：PG 能替你保证什么。两个特性——范围类型 + EXCLUDE 约束、生成列——共同点是把本该写在应用层、还容易写错的逻辑，交给数据库在写入那一刻强制执行。数据库把住的关，应用层怎么绕都绕不过去。

范围类型：区间是一个值

int4range、numrange、tsrange、daterange……PostgreSQL 把「一段连续区间」做成了单个值，自带一套运算符：

SELECT '[1,10)'::int4range @> 5;              -- 包含? True
SELECT '[1,5)'::int4range && '[4,10)'::int4range;  -- 相交? True
SELECT upper('[1,10)'::int4range);            -- 上界: 10
SELECT isempty('[5,5)'::int4range);           -- 空区间? True

注意边界记法：[ 闭、) 开，所以 [1,10) 含 1 不含 10，[5,5) 是空区间。@>（包含）、&&（相交）、+ * -（并 / 交 / 差）、lower / upper（取界）构成完整的区间代数。做「这个时间点落在哪个档位」「两个预约冲不冲突」，不用再把区间拆成两个 start / end 列自己比大小。

EXCLUDE 约束：数据库强制「互不重叠」

范围类型真正的杀手锏，是配上 EXCLUDE 排他约束。它能让数据库保证「没有任何两行的区间相交」——这是 UNIQUE 约束的推广：UNIQUE 保证「没有两行相等」，EXCLUDE 保证「没有两行满足某个运算符」（这里是 && 相交）。

CREATE EXTENSION IF NOT EXISTS btree_gist;   -- 让 = 和 && 能同建一个 GiST 索引

CREATE TABLE bookings (
  room  text,
  during tsrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)  -- 同房间 且 时段相交 → 拒绝
);

实测里，同一房间的 9:00-10:00、10:00-11:00 相邻不重叠，插入成功；不同房间的 9:30-10:30 也成功。可一旦试图给 A 房插一个 9:30-10:30（和它的 9:00-10:00 重叠）：

试图插 A 房 9:30-10:30(跟 9:00-10:00 重叠) → 数据库直接拒绝
✓ 拒绝了: ExclusionViolation

为什么要 btree_gist 扩展？EXCLUDE 靠 GiST 索引在写入时检查冲突。GiST 天生会处理范围的 &&（相交），但不会处理普通标量的 =（等值）。btree_gist 把「等值」也教给 GiST，于是「同一个 room 且 during 相交」这种混合条件才能落在同一个索引里一次判完。这也解释了 EXCLUDE 的代价：它本质是一个索引 + 每次写入的一次冲突检查。

这一条是 PostgreSQL 的独门绝技。会议室预订、酒店排房、设备租借、排班——这类「区间不许撞车」的规则，在别的数据库里通常得靠应用层加锁 + 查重，还有并发漏判的风险；PG 直接在数据库层用一条约束焊死，并发再高也不会漏。

生成列：派生字段，数据库自己算

生成列(Generated Column，PG 12+)声明「这一列的值由别的列算出来」，由数据库自动维护，你永远改不脏它：

CREATE TABLE measurements (
  length_cm numeric,
  width_cm  numeric,
  area_cm2  numeric GENERATED ALWAYS AS (length_cm * width_cm) STORED,
  label     text,
  label_lower text GENERATED ALWAYS AS (lower(label)) STORED
);

它有三个硬规矩，实测都验证了：

• 试图 INSERT 直接写 area_cm2 → 数据库拒绝(GeneratedAlways)
• UPDATE length_cm = 20 后,area_cm2 自动变成 100(= 20 × 5)
• 给 label_lower 建索引 → 大小写不敏感查询直接走索引

不能手动写：GENERATED ALWAYS 的列只能由表达式产出，硬塞报错——保证它永远是源列的忠实函数。
源列变，它自动重算：改 length_cm，area_cm2 立刻跟着变，不存在「忘了同步」。
表达式必须 IMMUTABLE：不能调 now()、random() 这类每次结果不同的函数——否则「由源列决定」就不成立了。

STORED 还是 VIRTUAL？ STORED 把结果物理存下来，占空间，但能建索引（实测就给 label_lower 建了索引做大小写不敏感查询）。PostgreSQL 18 起新增了 VIRTUAL 生成列（且成为默认）：查询时才计算，不占存储、也不能索引，适合「偶尔读、不想占空间」的派生值。选择标准很直接：要索引它，就 STORED；只是省事不想占地方，就 VIRTUAL。

生成列最常见的用法之一，是给全文检索维护 tsvector 列——正文一改，检索向量自动更新，替代了过去要写触发器的做法（见全文检索）。

横向对比

能力	PostgreSQL	MySQL	Oracle	SQL Server
范围类型	`int4range` / `tsrange` 等原生	无（拆成两列）	无原生类型	无原生类型
「区间不重叠」约束	EXCLUDE + GiST，声明式	无，靠应用层	无，靠应用层	无，靠应用层
生成列 STORED	有	有(`STORED`)	有（虚拟列）	有（计算列 `PERSISTED`）
生成列 VIRTUAL	PG 18+	有（默认）	有（默认）	有（默认非 PERSISTED）

生成列大家都有；EXCLUDE 排他约束几乎是 PostgreSQL 独有——这是「把业务规则下沉到数据库」最有代表性的一个例子。

应用场景

资源调度防撞车：会议室 / 酒店房间 / 医生排班 / 设备租借，EXCLUDE USING gist (resource WITH =, period WITH &&)，并发预订也不会重叠。
价格 / 有效期档位：numrange、daterange 存价格区间或生效区间，@> 判「某值落在哪档」。
派生字段单一来源：面积、总价、全名、lower(x)——用生成列，杜绝「算好存进去后源数据变了却忘了更新」。
给函数结果建索引：lower(email)、(data->>'city') 做成 STORED 生成列再建索引，查询走索引又不必每次写函数。

小结与下一章

范围类型 + EXCLUDE 把「时段不许重叠」这类规则变成数据库的硬约束；生成列把派生字段的维护责任从应用层收回数据库。它们体现的是 PostgreSQL 一贯的哲学：能让数据库保证的一致性，就别赌应用层不出错。

类型系统这一站到此走完。下一站进入查询表达力——同样一份数据，PostgreSQL 能用一条 SQL 表达出别处要写一大段代码的逻辑。第一个就是能自我引用、能遍历树的 CTE。👉 CTE：让一条 SQL 学会「递归」。

参考文献

[1] PostgreSQL 官方文档.「8.17. Range Types.」https://www.postgresql.org/docs/current/rangetypes.html —— 范围类型、边界记法与运算符。
[2] PostgreSQL 官方文档.「5.4.1. Exclusion Constraints / 11.11. btree_gist.」https://www.postgresql.org/docs/current/ddl-constraints.html —— EXCLUDE 约束语义与 GiST 依赖。
[3] PostgreSQL 官方文档.「5.4. Generated Columns.」https://www.postgresql.org/docs/current/ddl-generated-columns.html —— STORED / VIRTUAL 生成列（PG 18 起支持 VIRTUAL）。

范围类型与生成列 · 让约束替你把关

本页目录