postgresql - 防止开始时间、结束时间列中的时间重叠

Question

我们有一个用例，我们将 start_week 和 end_week 存储在一行中，并希望确保这些跨行的值永远不会重叠，即它们是互斥的。下面的例子

Ideal
code - startw - endw
T1 - 201401 - 201404
T2 - 201405 - 201408
T3 - 201409 - 201416

Not Ideal
code - startw - endw
T1 - 201401 - 201404
T2 - 201403 - 201408
T3 - 201407 - 201408
T3 - 201406 - 201410

能否在 Postgres 中添加这样的约束，以便在插入期间捕获错误？解决此类问题并避免插入的最佳方法是什么，而不是在插入数据后运行检查。

Answer 1

PostgreSQL 有一个专门为此设计的特性 - exclusion constraints .

参见 CREATE TABLE ... EXCLUDE ... .

为了您的目的，您需要一个 constraint on a range .您的案例在手册中有所介绍。

您对日期使用了一种奇怪的格式，这有点复杂；您需要将它们转换为排除约束的 tstzrange。

create table exclude(code text, startw integer, endw integer);

insert into exclude(code, startw, endw) values
('T1', 201401, 201404),
('T2', 201405, 201408),
('T3', 201409, 201416);

你不能只是:

alter table exclude 
  add constraint no_overlapping_timestamps 
  EXCLUDE USING gist( tstzrange(startw, endw) WITH && );

因为您没有使用真正的时间戳；您必须将周数转换为上面表达式中的时间戳。我不能为你做那件事，因为我不知道你如何定义“一周”。如果是标准方式，则:

alter table exclude 
  add constraint no_overlapping_timestamps 
  EXCLUDE USING gist( tstzrange(to_date(startw, 'YYYYWW'), to_date(endw, 'YYYYWW')) WITH && );