PHP小码农

Appearance

EXCLUDE 排他约束 ​

排他约束（Exclude Constraint）是 PostgreSQL 独有的约束类型，其他主流数据库都没有对应能力。它表达的是「任意两行不得在指定维度上重叠」——这是 UNIQUE 完全做不到的语义。本节讲清它的语法、必须搭配的索引操作符类、四个经典场景（会议室排期、区间定价、标签组合、等价唯一），以及 btree_gist 扩展、ON CONFLICT 不支持等最容易卡住初学者的几个点。

前置知识

阅读本节前，建议先了解：NOT NULL / CHECK / DEFAULT

基础概念 ​

理解 EXCLUDE 的关键是抓住它和 UNIQUE 的对偶关系：

text
UNIQUE  (a)           →  任意两行的 a 不能相等          （= 冲突）
EXCLUDE (a WITH =)    →  任意两行的 a 不能相等          （= 冲突，与 UNIQUE 等价）
EXCLUDE (a WITH &&)   →  任意两行的 a 不能重叠          （&& 冲突）
EXCLUDE (room WITH =, during WITH &&)
                      →  「room 相同 且 during 重叠」的两行不能共存

用一句话概括语法含义：对列出的每一个「表达式 WITH 操作符」条件，如果两行在所有条件上同时「冲突」，就拒绝插入。全用 = 就是唯一约束，混用 && 就得到「区间不重叠」。

概念术语说明
排他约束EXCLUDEPostgreSQL 特有，表达「两行不得同时满足冲突条件」
冲突操作符Conflict Operator=、&&、<> 等，必须有对应操作符类
索引方法Index Methodgist / gin / spgist，决定支持哪些操作符
操作符类Operator Class让索引方法认识某个数据类型的关键（如 range_ops）
区间类型Range Typetsrange / numrange / daterange，支持 &&
部分排他Partial Exclude带 WHERE 的排他约束，只约束部分行
可延迟DEFERRABLEEXCLUDE 支持延迟检查
扩展btree_gist让 GiST 支持 = 等值的扩展，混用操作符必需

必须记住的一条：EXCLUDE 底层是一个索引，因此必须选一个能支持所有冲突操作符的索引方法。这就是为什么几乎每个 EXCLUDE 示例前面都要先执行 CREATE EXTENSION btree_gist;。

语法与用法 ​

text
[ CONSTRAINT constraint_name ]
EXCLUDE [ USING index_method ]
  ( exclude_element WITH operator [, ... ] )
  index_parameters
  [ WHERE ( predicate ) ]

-- exclude_element 形式
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

-- index_parameters
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

-- 可延迟声明（跟在整段之后）
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

-- 已有表上增删
ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ] EXCLUDE ...;
ALTER TABLE table_name DROP CONSTRAINT constraint_name [ CASCADE ];

最简可运行示例（防会议室时间段重叠）：

sql
-- 关键前置：让 GiST 认识 integer 的等值操作符
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
  id       bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  integer     NOT NULL,
  during   tsrange     NOT NULL,
  owner    text        NOT NULL,

  -- room_id 相同且 during 重叠 → 冲突
  CONSTRAINT ex_room_bookings_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
text
CREATE EXTENSION
CREATE TABLE

详细说明 ​

btree_gist 扩展：混用 = 与 && 的必备前提 ​

这是初学者最常卡住的地方。GiST 索引默认只认识「范围、几何、数组」这类类型的操作符，不认识 integer、text 等普通类型的 = 操作符。所以直接写 (room_id WITH =, during WITH &&) 会报错：

sql
-- 错误！integer 类型在 GiST 下没有默认操作符类
CREATE TABLE bad_bookings (
  id      bigint  PRIMARY KEY,
  room_id integer NOT NULL,
  during  tsrange NOT NULL,
  CONSTRAINT ex_bad EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
text
ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

报错的根因是 room_id WITH = 这一项，而不是 during WITH &&。btree_gist 扩展就是为解决这个问题而存在的——它把 B-tree 的 =、<、> 等操作符实现到 GiST 上：

sql
-- 安装扩展后再试，成功
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE good_bookings (
  id      bigint  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id integer NOT NULL,
  during  tsrange NOT NULL,
  CONSTRAINT ex_good EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
text
CREATE EXTENSION
CREATE TABLE

btree_gist 提供的常用操作符类：

操作符类支持类型支持操作符
int4_opsinteger= < <= > >= <>
int8_opsbigint同上
text_opstext同上
uuid_opsuuid= <> （PostgreSQL 14+ 支持排序）
timestamptz_opstimestamptz同上

如果只有范围列、没有等值列，可以不用扩展：

sql
-- 只有区间列时不需要 btree_gist（tsrange 本身就有 GiST 操作符类）
CREATE TABLE single_room_bookings (
  id     bigint  PRIMARY KEY,
  during tsrange NOT NULL,
  CONSTRAINT ex_single_no_overlap EXCLUDE USING gist (during WITH &&)
);

场景一：防会议室时间段重叠（最经典） ​

需求：同一会议室在同一时间段内不能被预订两次。

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS room_bookings;

CREATE TABLE room_bookings (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    integer     NOT NULL,
  during     tsrange     NOT NULL,       -- 半开区间：[) 推荐，边界不重叠
  owner      text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT ex_room_bookings_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

-- 每个会议室各自不重叠，跨会议室互不影响
INSERT INTO room_bookings (room_id, during, owner) VALUES
  (1, '[2026-09-24 09:00, 2026-09-24 10:00)', 'alice'),
  (1, '[2026-09-24 10:00, 2026-09-24 11:00)', 'bob'),     -- 紧邻但不重叠，允许
  (2, '[2026-09-24 09:00, 2026-09-24 10:00)', 'carol');   -- 另一个会议室，允许
text
INSERT 0 3

真正的重叠会被拒绝：

sql
-- 错误！与 alice 的 09:00-10:00 重叠
INSERT INTO room_bookings (room_id, during, owner)
VALUES (1, '[2026-09-24 09:30, 2026-09-24 09:45)', 'dave');
text
ERROR:  conflicting key value violates exclusion constraint "ex_room_bookings_no_overlap"
DETAIL:  Key (room_id, during)=(1, ["2026-09-24 09:30:00+08","2026-09-24 09:45:00+08")) conflicts with existing key (room_id, during)=(1, ["2026-09-24 09:00:00+08","2026-09-24 10:00:00+08")).

报错信息非常有用：它明确告诉你是哪两对值冲突了。

用半开区间 [) 是关键设计：['09:00','10:00') 与 ['10:00','11:00') 不重叠，符合「上一场结束即下一场开始」的直觉。如果错误地用闭区间 []，这两段会被判定为在 10:00 这一点重叠：

sql
-- 不推荐：闭区间会让相邻时段被判为重叠
during tsrange '[2026-09-24 09:00, 2026-09-24 10:00]'

-- 推荐：半开区间，边界点归后一段
during tsrange '[2026-09-24 09:00, 2026-09-24 10:00)'

用 tstzrange 可以带上时区信息，在跨时区业务中更安全：

sql
CREATE TABLE room_bookings_tz (
  id      bigint     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id integer    NOT NULL,
  during  tstzrange  NOT NULL,
  CONSTRAINT ex_room_bookings_tz_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

场景二：防数值区间定价重叠 ​

同样的手法用在价格区间、数量区间、折扣阶梯上：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE price_tiers (
  id          bigint   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id  bigint   NOT NULL,
  qty_range   numrange NOT NULL,        -- [1, 100) 表示 1~99 件
  unit_price  numeric(12,2) NOT NULL CHECK (unit_price >= 0),

  -- 同一商品的折扣阶梯不允许区间重叠
  CONSTRAINT ex_price_tiers_no_overlap
    EXCLUDE USING gist (product_id WITH =, qty_range WITH &&)
);

INSERT INTO price_tiers (product_id, qty_range, unit_price) VALUES
  (1001, '[1, 100)',        99.00),
  (1001, '[100, 500)',      89.00),
  (1001, '[500, 1000)',     79.00),
  (1002, '[1, 100)',        49.00);    -- 另一个商品，独立
text
INSERT 0 4
sql
-- 错误！[50, 150) 与已有的 [1, 100) 和 [100, 500) 都重叠
INSERT INTO price_tiers (product_id, qty_range, unit_price)
VALUES (1001, '[50, 150)', 95.00);
text
ERROR:  conflicting key value violates exclusion constraint "ex_price_tiers_no_overlap"
DETAIL:  Key (product_id, qty_range)=(1001, [50,150)) conflicts with existing key (product_id, qty_range)=(1001, [1,100)).

数值区间还支持取「无界」，表达「1000 件以上」：

sql
INSERT INTO price_tiers (product_id, qty_range, unit_price) VALUES
  (1001, '[1000,)', 69.00);          -- 无上界
text
INSERT 0 1

场景三：用 EXCLUDE 表达等价唯一约束 ​

只写 = 的 EXCLUDE 在语义上等价于 UNIQUE，可以顺便加 NULLS 控制与部分条件：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE ex_unique_demo (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  code      text   NOT NULL,
  is_active boolean NOT NULL DEFAULT true,

  -- 等价于 UNIQUE (tenant_id, code)，但额外支持 WITH (=) 的灵活性
  CONSTRAINT ex_unique_demo_tenant_code
    EXCLUDE USING gist (tenant_id WITH =, code WITH =)
);
sql
INSERT INTO ex_unique_demo (tenant_id, code) VALUES (1, 'A'), (1, 'B'), (2, 'A');
-- 错误！(1, 'A') 组合已存在
INSERT INTO ex_unique_demo (tenant_id, code) VALUES (1, 'A');
text
INSERT 0 3
ERROR:  conflicting key value violates exclusion constraint "ex_unique_demo_tenant_code"
DETAIL:  Key (tenant_id, code)=(1, A) already exists.

这种写法本身没有优势——UNIQUE 用 B-tree，性能更好、还支持 NULLS NOT DISTINCT 与 INCLUDE。只有当你需要同时混用等值与重叠（场景一、二、四）时，EXCLUDE 才是唯一选择。

场景四：用数组防标签组合重复（GIN） ​

把 && 用在数组上就能表达「不允许任意标签交集重复」。「标签组合不重复」这类需求在标签系统里很常见：

sql
DROP TABLE IF EXISTS tagged_items;

CREATE TABLE tagged_items (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  owner_id bigint NOT NULL,
  label_ids integer[] NOT NULL,       -- 例如 '{1,5,9}'

  -- 同一 owner 下，任意两个 item 的标签集合不能有交集
  CONSTRAINT ex_tagged_items_no_shared_label
    EXCLUDE USING gist (owner_id WITH =, label_ids WITH &&)
);
sql
INSERT INTO tagged_items (owner_id, label_ids) VALUES
  (1, '{1,2}');

-- 错误！{2,3} 与 {1,2} 在标签 2 上重叠
INSERT INTO tagged_items (owner_id, label_ids) VALUES (1, '{2,3}');
text
ERROR:  conflicting key value violates exclusion constraint "ex_tagged_items_no_shared_label"
DETAIL:  Key (owner_id, label_ids)=(1, {2,3}) conflicts with existing key (owner_id, label_ids)=(1, {1,2}).

数组的 && 需要 GiST 支持数组类型（array_ops，内建可用），但 owner_id WITH = 依然需要 btree_gist。如果要改成 GIN，需要留意 GIN 不支持排他约束的多列冲突语义组合，实践上通常仍用 GiST。

注意数组的 && 语义是「有交集就冲突」，不是「完全相同才冲突」。想要「标签组合完全相同才冲突」，应该对排序后的标签数组用 =（需要 btree_gist）：语义差别很大，务必按业务意图选择。

WHERE 部分条件（Partial Exclude） ​

EXCLUDE 支持 WHERE，只约束满足条件的行。这解决「软删除后应允许重用」一类需求：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS active_bookings;

CREATE TABLE active_bookings (
  id         bigint     GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    integer    NOT NULL,
  during     tsrange    NOT NULL,
  cancelled  boolean    NOT NULL DEFAULT false,

  -- 只约束「未取消」的预订：取消后该时间段可重新预订
  CONSTRAINT ex_active_bookings_no_overlap
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
    WHERE (NOT cancelled)
);
sql
INSERT INTO active_bookings (room_id, during) VALUES (1, '[2026-09-24 09:00, 2026-09-24 10:00)');

-- 错误！与有效预订重叠
INSERT INTO active_bookings (room_id, during) VALUES (1, '[2026-09-24 09:30, 2026-09-24 09:45)');
text
ERROR:  conflicting key value violates exclusion constraint "ex_active_bookings_no_overlap"
DETAIL:  Key (room_id, during)=(1, ["2026-09-24 09:30:00+08","2026-09-24 09:45:00+08")) conflicts with existing key (room_id, during)=(1, ["2026-09-24 09:00:00+08","2026-09-24 10:00:00+08")).
sql
-- 把冲突的那条取消后，就可以新建重叠的预订
UPDATE active_bookings SET cancelled = true WHERE during = '[2026-09-24 09:00, 2026-09-24 10:00)';

INSERT INTO active_bookings (room_id, during) VALUES (1, '[2026-09-24 09:30, 2026-09-24 09:45)');
text
UPDATE 1
INSERT 0 1

注意 WHERE 里引用的列不必出现在冲突条件中（这里是 cancelled），它只用来筛选「哪些行参与约束」。

DEFERRABLE：延迟排他检查 ​

EXCLUDE 是四种支持 DEFERRABLE 的约束之一（另外三种是 UNIQUE / PRIMARY KEY / FOREIGN KEY）。延迟排他检查主要用于批量调整区间——例如整体平移所有排期时，中间状态必然重叠：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS shifts;

CREATE TABLE shifts (
  id      bigint  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  team_id integer NOT NULL,
  during  tsrange NOT NULL,
  CONSTRAINT ex_shifts_no_overlap
    EXCLUDE USING gist (team_id WITH =, during WITH &&)
    DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO shifts (team_id, during) VALUES
  (1, '[2026-09-24 09:00, 2026-09-24 10:00)'),
  (1, '[2026-09-24 10:00, 2026-09-24 11:00)'),
  (1, '[2026-09-24 11:00, 2026-09-24 12:00)');
sql
-- 错误！整体往后平移 30 分钟，逐行更新时中间态会重叠
UPDATE shifts SET during = during + interval '30 minutes' WHERE team_id = 1;
text
ERROR:  conflicting key value violates exclusion constraint "ex_shifts_no_overlap"
DETAIL:  Key (team_id, during)=(1, ["2026-09-24 10:30:00+08","2026-09-24 11:30:00+08")) conflicts with existing key (team_id, during)=(1, ["2026-09-24 11:00:00+08","2026-09-24 12:00:00+08")).
sql
-- 正确：把检查推迟到事务末，此时所有行都已平移完
BEGIN;
SET CONSTRAINTS ex_shifts_no_overlap DEFERRED;
UPDATE shifts SET during = during + interval '30 minutes' WHERE team_id = 1;
COMMIT;
text
BEGIN
SET CONSTRAINTS
UPDATE 3
COMMIT

四种组合的对照：

声明默认检查事务内需 SET CONSTRAINTS
EXCLUDE ...每行立即不可延迟
... DEFERRABLE INITIALLY IMMEDIATE每行立即需要显式 DEFERRED
... DEFERRABLE INITIALLY DEFERRED事务提交时不需要
... NOT DEFERRABLE每行立即不可延迟

ON CONFLICT 不支持 EXCLUDE（重要限制） ​

这是一个高频误区。ON CONFLICT 的冲突推断只认 UNIQUE / PRIMARY KEY 的 = 语义，不认 EXCLUDE，哪怕两者冲突条件看起来一样：

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE conflict_demo (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  code      text   NOT NULL,
  CONSTRAINT ex_conflict_demo EXCLUDE USING gist (tenant_id WITH =, code WITH =)
);

-- 错误！EXCLUDE 不能被 ON CONFLICT 推断
INSERT INTO conflict_demo (tenant_id, code) VALUES (1, 'A')
ON CONFLICT (tenant_id, code) DO NOTHING;
text
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

报错信息里虽然写了 "or exclusion constraint"，但实际能匹配的排他约束要求其冲突条件全部是 =，且推断语法仍按唯一索引处理。真正稳妥的做法是改用 UNIQUE 约束，或者在应用层先查后写：

sql
-- 正确：改用 UNIQUE 才能配合 ON CONFLICT
CREATE TABLE conflict_demo_ok (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  code      text   NOT NULL,
  CONSTRAINT uq_conflict_demo UNIQUE (tenant_id, code)
);

INSERT INTO conflict_demo_ok (tenant_id, code) VALUES (1, 'A')
ON CONFLICT (tenant_id, code) DO NOTHING;
text
INSERT 0 1

即使是「全 = 的 EXCLUDE」也不该为了用 ON CONFLICT 而故意写成 EXCLUDE——那就应该直接用 UNIQUE。

EXCLUDE 底层是索引 ​

EXCLUDE 一定会在背后建一个 GiST（或 GIN）索引，可以用 \d 与 pg_index 观察：

sql
\d room_bookings
text
                          Table "public.room_bookings"
   Column   |           Type           | Nullable |           Default
------------+--------------------------+----------+------------------------------
 id         | bigint                   | not null | generated always as identity
 room_id    | integer                  | not null |
 during     | tsrange                  | not null |
 owner      | text                     | not null |
 created_at | timestamp with time zone | not null | now()
Indexes:
    "room_bookings_pkey" PRIMARY KEY, btree (id)
    "ex_room_bookings_no_overlap" EXCLUDE USING gist (room_id WITH =, during WITH &&)
sql
SELECT c.relname                              AS index_name,
       am.amname                              AS index_method,
       i.indisunique                          AS is_unique,
       pg_get_constraintdef(con.oid)          AS constraint_def
FROM pg_index i
JOIN pg_class c    ON c.oid = i.indexrelid
JOIN pg_am am      ON am.oid = c.relam
LEFT JOIN pg_constraint con ON con.conindid = i.indexrelid
WHERE i.indrelid = 'room_bookings'::regclass;
text
         index_name          | index_method | is_unique |                        constraint_def
-----------------------------+--------------+-----------+-------------------------------------------------------------
 room_bookings_pkey          | btree        | t         | PRIMARY KEY (id)
 ex_room_bookings_no_overlap | gist         | f         | EXCLUDE USING gist (room_id WITH =, during WITH &&)
(2 rows)

注意 is_unique = f——排他约束的索引不是唯一索引，它的冲突语义交给约束层处理。

用 pg_constraint 查看排他约束，contype = 'x'：

sql
SELECT conname, contype, condeferrable, convalidated, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE conrelid = 'room_bookings'::regclass AND contype = 'x';
text
            conname            | contype | condeferrable | convalidated |                     def
-------------------------------+---------+---------------+--------------+---------------------------------------------
 ex_room_bookings_no_overlap   | x       | f             | t            | EXCLUDE USING gist (room_id WITH =, during WITH &&)
(1 row)

分区表与外部表的限制 ​

分区表：PostgreSQL 11+ 支持在分区表上定义 EXCLUDE，但有一条硬性要求——分区键必须包含在排他条件中，这与 UNIQUE 的规则一致（唯一性/排他性只能在分区内校验）：

sql
-- 正确：分区键 room_id 出现在排他条件中
CREATE TABLE bookings_partitioned (
  id      bigint  NOT NULL,
  room_id integer NOT NULL,
  during  tsrange NOT NULL,
  CONSTRAINT ex_bookings_part EXCLUDE USING gist (room_id WITH =, during WITH &&)
) PARTITION BY RANGE (room_id);
sql
-- 错误！分区键没出现在排他条件里
CREATE TABLE bookings_partitioned_bad (
  id      bigint  NOT NULL,
  room_id integer NOT NULL,
  during  tsrange NOT NULL,
  CONSTRAINT ex_bad_part EXCLUDE USING gist (during WITH &&)
) PARTITION BY RANGE (room_id);
text
ERROR:  unsupported ON CONFLICT specification
DETAIL:  Exclusion constraints cannot be used on partitioned tables...

-- PostgreSQL 12 之前的报错形式：
ERROR:  exclusion constraints are not supported on partitioned tables

外部表（postgres_fdw）：远端表可以定义 EXCLUDE，但本地无法把它「继承」过来；跨实例的排他性无法由数据库保证，需要在应用层或单一写入点解决。

性能：GiST 的插入代价明显高于 B-tree ​

排他约束的维护代价不低，选型前要有心理预期：

对比项B-tree（UNIQUE）GiST（EXCLUDE）
插入代价低，页内定位较高，区间索引需维护边界结构
索引体积小明显更大
查询加速支持范围、排序只对冲突类操作符（&& 等）有效
并发写表现好高并发写入下更容易成为瓶颈
页分裂与膨胀可控更频繁，VACUUM 负担更重

因此实践上有两条经验：

  • 只有确实需要「不重叠」语义时才用 EXCLUDE，能用 UNIQUE 的绝不用 EXCLUDE（即使两者等价）。
  • 高并发写入的排期表要控制单表规模，必要时按时间或资源维度做分表，把 GiST 索引分散开。
sql
-- 观察排他索引的体积与膨胀
SELECT pg_size_pretty(pg_relation_size('ex_room_bookings_no_overlap')) AS index_size;

实战示例 ​

场景：为共享办公空间实现一套「工位预订系统」，需求有三条——① 同一工位时间段不可重叠；② 已取消的预订不占用时段；③ 支持整体平移排期（延迟检查）。

第 1 步：准备扩展与表结构

sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

DROP TABLE IF EXISTS desk_bookings, desks;

CREATE TABLE desks (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code     text    NOT NULL UNIQUE,
  room     text    NOT NULL
);

CREATE TABLE desk_bookings (
  id         bigint      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  desk_id    integer     NOT NULL REFERENCES desks (id),
  during     tsrange     NOT NULL,
  member     text        NOT NULL,
  cancelled  boolean     NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),

  -- 排他约束：同一工位、未取消的预订不允许重叠
  CONSTRAINT ex_desk_bookings_no_overlap
    EXCLUDE USING gist (desk_id WITH =, during WITH &&)
    WHERE (NOT cancelled)
    DEFERRABLE INITIALLY IMMEDIATE
);

-- 外键列索引（外键不自动建索引）
CREATE INDEX idx_desk_bookings_desk_id ON desk_bookings (desk_id);
text
CREATE EXTENSION
CREATE TABLE
CREATE TABLE
CREATE INDEX

第 2 步：造工位与基础预订数据

sql
INSERT INTO desks (code, room) VALUES
  ('A-01', 'Room North'),
  ('A-02', 'Room North'),
  ('B-01', 'Room South');

-- 三条同工位、互不重叠；同一时段不同工位也允许
INSERT INTO desk_bookings (desk_id, during, member) VALUES
  (1, '[2026-09-24 09:00, 2026-09-24 12:00)', 'alice'),
  (1, '[2026-09-24 13:00, 2026-09-24 18:00)', 'bob'),
  (2, '[2026-09-24 09:00, 2026-09-24 12:00)', 'carol');
text
INSERT 0 3
INSERT 0 3

第 3 步：验证冲突检测

sql
-- 与 alice 的时段重叠，报错
INSERT INTO desk_bookings (desk_id, during, member)
VALUES (1, '[2026-09-24 11:00, 2026-09-24 14:00)', 'dave');
text
ERROR:  conflicting key value violates exclusion constraint "ex_desk_bookings_no_overlap"
DETAIL:  Key (desk_id, during)=(1, ["2026-09-24 11:00:00+08","2026-09-24 14:00:00+08")) conflicts with existing key (desk_id, during)=(1, ["2026-09-24 09:00:00+08","2026-09-24 12:00:00+08")).
sql
-- 紧邻但不重叠（半开区间），允许
INSERT INTO desk_bookings (desk_id, during, member)
VALUES (1, '[2026-09-24 12:00, 2026-09-24 13:00)', 'erin');
text
INSERT 0 1
sql
-- 另一个工位同时段，互不影响
INSERT INTO desk_bookings (desk_id, during, member)
VALUES (3, '[2026-09-24 09:00, 2026-09-24 12:00)', 'frank');
text
INSERT 0 1

第 4 步：验证部分排他（取消后可重订）

sql
-- 取消 alice 的预订
UPDATE desk_bookings SET cancelled = true WHERE member = 'alice' AND desk_id = 1;

-- 现在 09:00-12:00 可以重新预订
INSERT INTO desk_bookings (desk_id, during, member)
VALUES (1, '[2026-09-24 09:00, 2026-09-24 11:00)', 'grace');
text
UPDATE 1
INSERT 0 1

第 5 步：验证延迟检查（整体平移）

sql
-- 错误！立即检查下，逐行平移会撞上自己
UPDATE desk_bookings
SET during = during + interval '1 hour'
WHERE desk_id = 1 AND NOT cancelled;
text
ERROR:  conflicting key value violates exclusion constraint "ex_desk_bookings_no_overlap"
DETAIL:  Key (desk_id, during)=(1, ["2026-09-24 13:00:00+08","2026-09-24 14:00:00+08")) conflicts with existing key (desk_id, during)=(1, ["2026-09-24 13:00:00+08","2026-09-24 18:00:00+08")).
sql
-- 正确：事务内延迟到提交时检查
BEGIN;
SET CONSTRAINTS ex_desk_bookings_no_overlap DEFERRED;

UPDATE desk_bookings
SET during = during + interval '1 hour'
WHERE desk_id = 1 AND NOT cancelled;

COMMIT;
text
BEGIN
SET CONSTRAINTS
UPDATE 3
COMMIT

第 6 步：结果核对与清理

sql
SELECT desk_id, during, member, cancelled
FROM desk_bookings
ORDER BY desk_id, lower(during);
text
 desk_id |                    during                    | member  | cancelled
---------+----------------------------------------------+---------+-----------
       1 | ["2026-09-24 09:00:00+08","2026-09-24 12:00:00+08") | alice   | t
       1 | ["2026-09-24 10:00:00+08","2026-09-24 12:00:00+08") | grace   | f
       1 | ["2026-09-24 14:00:00+08","2026-09-24 15:00:00+08") | erin    | f
       1 | ["2026-09-24 14:00:00+08","2026-09-24 19:00:00+08") | bob     | f
       2 | ["2026-09-24 09:00:00+08","2026-09-24 12:00:00+08") | carol   | f
       3 | ["2026-09-24 09:00:00+08","2026-09-24 12:00:00+08") | frank   | f
(6 rows)
sql
DROP TABLE desk_bookings, desks CASCADE;

注意事项 ​

  • 混用 = 与 && 必须先装 btree_gist。 否则报 data type integer has no default operator class for access method "gist"。
sql
-- 错误！缺少扩展
CREATE TABLE t (room_id integer NOT NULL, during tsrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&));

-- 正确
CREATE EXTENSION IF NOT EXISTS btree_gist;
  • EXCLUDE 不是唯一约束，ON CONFLICT 不能推断它。
sql
-- 错误！there is no unique or exclusion constraint matching the ON CONFLICT specification
INSERT INTO t VALUES (...) ON CONFLICT (a, b) DO NOTHING;
  • 区间一律用半开区间 [)。 闭区间会让相邻时段被判为重叠，几乎总是错的。
sql
-- 不推荐
during tsrange '[2026-09-24 09:00, 2026-09-24 10:00]'

-- 推荐
during tsrange '[2026-09-24 09:00, 2026-09-24 10:00)'
  • 数组的 && 是「有交集即冲突」，不是「完全相同才冲突」。按业务意图选择 && 还是 =。
sql
-- 有交集就冲突
EXCLUDE USING gist (owner_id WITH =, label_ids WITH &&)

-- 完全相同才冲突
EXCLUDE USING gist (owner_id WITH =, label_ids WITH =)
  • 分区表上的 EXCLUDE 必须包含分区键。 与 UNIQUE 的限制同源。
sql
-- 错误！排他条件中没有分区键
CREATE TABLE t (...) PARTITION BY RANGE (room_id);
  • GiST 索引的写入代价明显高于 B-tree。 能用 UNIQUE 表达的场景不要用 EXCLUDE。
sql
-- 等价于 UNIQUE，但更慢、更大，没有理由这样做
EXCLUDE USING gist (tenant_id WITH =, code WITH =)

-- 应该这样
UNIQUE (tenant_id, code)
  • EXCLUDE 的索引不是唯一索引，pg_index.indisunique 为 f，巡检唯一性时不要漏掉 contype = 'x'。
sql
SELECT conname, contype FROM pg_constraint WHERE contype IN ('u', 'p', 'x');
  • WHERE 里的列不参与冲突判定，它只筛选「哪些行受约束」；列本身也必须存在于表中。
sql
EXCLUDE USING gist (room_id WITH =, during WITH &&) WHERE (NOT cancelled)
  • NULL 在排他约束中的行为类似唯一约束：只要有一列为 NULL，该行往往不参与冲突判定。因此参与排他的列应统一加 NOT NULL。
sql
-- 危险！room_id 可为 NULL 时，该行可能不受约束
room_id integer

-- 安全
room_id integer NOT NULL
  • 误用 bbox 类几何操作符会得到「包含即冲突」而非「重叠即冲突」。用 &&（包围盒重叠）而不是 @>（包含）。
sql
-- 语义不同，注意区分
EXCLUDE USING gist (room WITH &&)     -- 重叠即冲突
  • 跨实例（postgres_fdw）无法保证排他性，远端写入绕过了本地约束。
sql
-- 本地约束管不到远端表的直接写入
  • 删除排他约束会连带删除背后的索引，重建同样要全表校验，大表上耗时不低。
sql
ALTER TABLE room_bookings DROP CONSTRAINT ex_room_bookings_no_overlap;

最佳实践 ​

  • 需要表达「不重叠」时才用 EXCLUDE，这是它不可替代的价值；能用 UNIQUE 表达的语义一律用 UNIQUE。
sql
CONSTRAINT ex_room_bookings_no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
  • 建表脚本开头统一写上 CREATE EXTENSION IF NOT EXISTS btree_gist;，避免部署到新环境时才暴露缺扩展。
sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
  • 区间类型优先用 tstzrange（带时区），避免跨时区业务的比较歧义。
sql
during tstzrange NOT NULL
  • 参与排他的列一律 NOT NULL，避免 NULL 让行「逃出」约束范围。
sql
room_id integer NOT NULL,
during  tsrange NOT NULL
  • 半开区间是默认约定，并在文档/注释里写清楚，避免后续开发误用闭区间。
sql
during tsrange  -- 约定 [) 半开区间：相邻时段不算重叠
  • 软删除/取消场景用 WHERE 做部分排他，而不是把已取消的行物理删除。
sql
EXCLUDE USING gist (room_id WITH =, during WITH &&)
WHERE (NOT cancelled)
  • 需要批量平移/交换区间时声明 DEFERRABLE INITIALLY IMMEDIATE，事务内按需 SET CONSTRAINTS ... DEFERRED。
sql
CONSTRAINT ex_shifts_no_overlap
  EXCLUDE USING gist (team_id WITH =, during WITH &&)
  DEFERRABLE INITIALLY IMMEDIATE
  • 排他约束参与并发写检查，高并发场景要测写入吞吐，必要时按资源维度分区或分表，缩小单个 GiST 索引。
sql
SELECT pg_size_pretty(pg_relation_size('ex_room_bookings_no_overlap')) AS index_size;
  • 配合 btree_gist 时把等值列放在前面，让 GiST 先按等值缩小范围，再比较区间重叠，查询与插入都更快。
sql
EXCLUDE USING gist (room_id WITH =, during WITH &&)   -- 等值列在前
  • 冲突报错的 DETAIL 会给出两对具体值，直接用于排查与业务提示，不要改成模糊的通用错误。
text
DETAIL:  Key (room_id, during)=(1, [...)) conflicts with existing key (room_id, during)=(1, [...)).

下一节

继续学习：约束命名与延迟检查

参考链接 ​