将 PostgreSQL EXCLUDE 范围约束与 UNIQUE 约束相结合 [英] Combine a PostgreSQL EXCLUDE range constraint with a UNIQUE constraint

问题描述

在 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_ranges.

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:

http://www.postgresql.org/docs/current/静态/btree-gist.html

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