结合PostgreSQL EXCLUDE范围约束和UNIQUE约束

问题描述

在PostgreSQL中，如何将范围列的排除约束与其他标量列的唯一约束结合在一起。或换一种说法，如何确保范围重叠检查仅与其他某些列的唯一检查结合使用？

In PostgreSQL, how do you combine an exclusion constraint on a range column with a unique constraint on other, scalar columns. Or to put it another way, how do I ensure that the range overlap check is only done in combination with a unique check on some other columns?

例如，说我有：

CREATE TABLE reservation (
    restaurant_id int,
    time_range tsrange
);

我想确保每个 restaurant_id ，没有重叠的 time_range s。

I want to make sure that for each restaurant_id, there are no overlapping time_ranges.

我知道我可以创建像这样的互斥范围约束：

I know that I can create an exclusive range constraint like this:

CREATE TABLE reservation (
    restaurant_id int,
    time_range tsrange EXCLUDE USING gist (time_range WITH &&)
);

但是如何确保 time_range 支票的范围由 restaurant_id ？

But how do I make sure that the time_range check is scoped by restaurant_id?

推荐答案

CREATE TABLE reservation
(
    restaurant_id int,
    time_range tsrange,
    EXCLUDE USING gist (restaurant_id with =, time_range WITH &&)
);

请注意，您需要安装扩展名 btree_gist ，因为默认情况下GIST索引没有相等运算符：

Note that you need to install the extension btree_gist because the GIST index does not have an equality operator by default:

这篇关于结合PostgreSQL EXCLUDE范围约束和UNIQUE约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！