postgresql - 如何检查某个day_id是否包含在给定时间间隔day_from到day_to内？

Question

我是一个相当缺乏经验的 SQL 开发人员，但我正在尽最大努力拥有一个一致的数据库。我寻求解决的问题可以在我系统的上层完成，但我知道最好在它们所属的地方处理类似的事情:在数据库层。

所以我拥有的是一张表，我在其中存储可能会或可能不会在指定日期重复的报价:

day_of_week
---------------------------------------------
id  day_name
1   SUNDAY
2   MONDAY
3   TUESDAY
4   WEDNESDAY
5   THURSDAY
6   FRIDAY
7   SATURDAY

offer
---------------------------------------------
id  day_id   valid_from_day  valid_until_day
1   3        2016-03-01      2016-03-01
2   6        2016-03-01      9999-03-01

这是我的问题:

offer
---------------------------------------------
id  day_id   valid_from_day  valid_until_day
1   2        2016-03-01      2016-03-01
2   6        2016-03-01      9999-03-01

day_id 2 对于 2016-03-01 到 2016-03-01 没有意义，因为 2016 年 1 日或 3 月是TUESDAY = 3，而不是 MONDAY = 2。

我希望能够将任意间隔存储到此表中，但我必须确保它们有意义。我正在寻找的是一种检查给定间隔 valid_from_day 到 valid_until_day 是否包含给定日期 day_id 的方法。这实际上应该是一个简单的检查，但我不确定我是否可以为此使用 CONSTRAINT 或者我是否必须为此使用 TRIGGER 以及如何使用看起来像。

Answer 1

我认为在这种情况下最好使用触发器。编写它以便仅当 day_id 在 extract(dow from valid_from_day::date) 和 extract(dow from valid_until_day::date) 之间时才执行 UPDATE 或 INSERT )。这应该很自然地适合触发器函数的工作方式。

您还可以使用其他变体，查看 EXTRACT功能。