PostgreSQL中如何用排除约束强制里程与日期同步递增？

我有一个名为rides的表，包含car_id、date字段和int4range类型的mileage字段。目前已通过btree_gist排除约束确保同一car_id的里程范围不重叠，现在需要额外强制：里程更高的行程，其日期不得早于里程更低的行程（即若mileage1 > mileage2，则date1 >= date2）。希望用排除约束替代效率较低的行级触发器实现。

现有表结构：

CREATE TABLE rides (
  car_id   INTEGER,
  mileage  INT4RANGE,
  date     DATE,
  EXCLUDE USING GIST (car_id WITH =, mileage WITH &&)
);

示例数据（同一car_id）：

• ride_1：里程[0, 50)，日期2024-07-10（合法）
• ride_2：里程[50, 100)，日期2024-07-10（合法：里程更高，日期相同）
• ride_3：里程[100, 150)，日期2024-07-11（合法：里程更高，日期更晚）
• ride_4：里程[150, 200)，日期2024-07-09（非法：里程更高但日期更早）

解决方案 #

1. 安装btree_gist扩展 #

因为需要对普通date类型使用GIST索引的比较操作符，首先执行以下语句安装扩展：

CREATE EXTENSION IF NOT EXISTS btree_gist;

2. 添加新的排除约束 #

在现有表结构基础上，新增一个排除约束，禁止同一car_id下里程范围完全在另一行程之后（即里程更高）且日期更早的情况：

ALTER TABLE rides
ADD CONSTRAINT rides_mileage_date_order_exclude
EXCLUDE USING GIST (
  car_id WITH =,
  mileage WITH >>,  -- 当前里程范围完全在另一行程的里程范围之后
  date WITH <       -- 当前日期早于另一行程的日期
);

也可以直接在创建表时合并两个约束：

CREATE TABLE rides (
  car_id   INTEGER,
  mileage  INT4RANGE,
  date     DATE,
  -- 禁止同一car_id的里程范围重叠
  EXCLUDE USING GIST (car_id WITH =, mileage WITH &&),
  -- 禁止同一car_id下，里程更高的行程日期更早
  EXCLUDE USING GIST (car_id WITH =, mileage WITH >>, date WITH <)
);

3. 约束逻辑说明 #

• mileage WITH >>：匹配当前行程的里程范围完全在另一行程里程范围之后的情况（即当前行程里程更高）
• date WITH <：匹配当前行程日期早于另一行程日期的情况
• 两个条件结合，再加上car_id WITH =，就会排除同一车辆下，里程更高但日期更早的非法行程。

验证效果 #

插入示例中的非法行程ride_4时，PostgreSQL会抛出约束冲突错误：

INSERT INTO rides (car_id, mileage, date) VALUES (1, '[150,200)', '2024-07-09');

错误信息类似：

ERROR:  conflicting key value violates exclusion constraint "rides_mileage_date_order_exclude"
DETAIL:  Key (car_id, mileage, date)=(1, [150,200), 2024-07-09) conflicts with existing key (car_id, mileage, date)=(1, [0,50), 2024-07-10).

而合法的ride_2、ride_3都能正常插入，不会触发约束错误。

内容的提问来源于stack exchange，提问作者Juul