postgresql - 如何处理具有排除约束的表的更新?
问题描述
我有一张表跟踪带有排除约束的酒店预订,如下所示。
目前,我允许客人更新他们的预订。因此,如果 guest_id 1 将他的预订从 3 天预订更改为从 2010-01-03、2010-01-03 的单日预订,如果我运行此更新语句,postgres 将由于重叠约束而阻止更新:
update reservation set from_ts = '2021-01-03 00:00:00', to_ts='2021-01-10 23:59:00', during = '[2021-01-03 00:00:00, 2021-01-10 23:59:00]' where id = 1
那你如何允许这个更新呢?您是否必须保持预订 id 相同,并删除其他人?
** 注意:我实际上是每行存储一天,因为我还有其他属性可以每天跟踪**
Table: reservation
id | room | from_ts | to_ts | guest_id
----+------+---------------------+---------------------+------------
1 | 101 | 2010-01-01 00:00:00 | 2010-01-01 23:59:00 | 1
2 | 101 | 2010-01-02 00:00:00 | 2010-01-02 23:59:00 | 1
3 | 101 | 2010-01-03 00:00:00 | 2010-01-03 23:59:00 | 1
CREATE TABLE reservation (
id int,
guest_id int,
room int,
from_ts timestamp without time zone,
to_ts timestamp without time zone,
during tsrange,
EXCLUDE USING GIST (room WITH =, during WITH &&)
);
-- bootstrap to test the problem
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 1, 1, 101, '2021-01-01 00:00:00', '2021-01-01 23:59:00', '[2021-01-01 00:00:00, 2021-01-01 23:59:00]');
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 2, 1, 101, '2021-01-02 00:00:00', '2021-01-02 23:59:00', '[2021-01-02 00:00:00, 2021-01-02 23:59:00]' );
INSERT INTO reservation ( id, guest_id, room, from_ts, to_ts, during ) VALUES ( 3, 1, 101, '2021-01-03 00:00:00', '2021-01-03 23:59:00', '[2021-01-03 00:00:00, 2021-01-03 23:59:00]' );
-- update statement will fail after you run the insert statements
update reservation set from_ts = '2021-01-03 00:00:00', to_ts='2021-01-10 23:59:00', during = '[2021-01-03 00:00:00, 2021-01-10 23:59:00]' where id = 1
解决方案
您可以通过使用不同的排除约束而不是您创建的排除约束来解决该问题:
ALTER TABLE reservation ADD EXCLUDE USING gist (
room WITH =,
guest_id WITH <>,
tsrange(from_ts, to_ts, '[]') WITH &&
);
这将排除房间相等且时间戳范围重叠且预订针对不同客人的两个条目。
请注意,我使用了表达式而不是during
. 避免数据库设计中的冗余是一个好主意。当然,您也可以保留duration
and 取消from_ts
and to_ts
。
另请注意to_ts
,在 23:59:00 结束时有多尴尬。您可以改为选择在上端开放的区间:
SELECT tsrange('2021-01-02 00:00:00', '2021-01-03 00:00:00');
tsrange
═══════════════════════════════════════════════
["2021-01-02 00:00:00","2021-01-03 00:00:00")
(1 row)
这不会与以 2021-01-03 00:00:00 开头的范围重叠。
推荐阅读
- image - Slick carousel 的图片现在仅在从成功的 Facebook Connect 回来后才会显示
- node.js - 错误:找不到模块“body-Parser”部署 HEROKU
- gulp - Gulp 任务创建页面特定的 js 文件,将常见的 js 文件与页面特定的文件连接起来
- c# - 在不使用磁盘的情况下即时压缩流
- powershell - 在 ForEach-Object 循环中编辑文本文件内容
- google-cloud-platform - 如何将 Google 表格数据加载到 Google Cloud MySQL?
- reactjs - 使用 sinon 存根测试辅助函数
- grails - 如何在 Grails 3 中启用 Tomcat 访问日志?
- python - 使用 LSTM 层在 Keras 中进行预测
- django - 突出显示未翻译的 Django 字符串