具有重叠时间戳范围的排除约束

Question

我希望能有一个与这个等价的内容：

我需要一个相当于此内容的等价物：

create table t (
    fromts timestamptz, 
    tots timestamptz,
    exclude using gist ((fromts, tots) with overlaps)
);
ERROR:  syntax error at or near ","
LINE 4:         exclude using gist ((fromts, tots) with overlaps)

我猜除了语法错误之外，还存在不可交换运算符的问题。

最简单的方法是什么？

- Clodoaldo Neto

---

timestampz？你是指 timestamptz 吗？ - Erwin Brandstetter

Answer 1

起初这可能看起来有些牵强，但它可以直接使用（没有双关语）：

正如@Quassnoi帮助我发现的那样，这仅适用于timestamp，而不适用于timestamptz：

CREATE TABLE t (
    fromts timestamp, 
    tots   timestamp,
    exclude using GIST (box(point(EXTRACT(EPOCH FROM fromts), 0)
                          , point(EXTRACT(EPOCH FROM tots),   0)) WITH &&)
);

我的测试是使用timestamp类型的，在您澄清类型之前。EXTRACT在timestamp上是IMMUTABLE，但不适用于timestamptz。@Quassnoi在下面的评论中发表了一个假设。

因此，对于带有时区的timestamp，您必须使用辅助函数使转换为IMMUTABLE，就像@Quassnoi在他的答案中演示的那样。

在Postgres 9.2或更高版本中

... 您可以使用新引入的范围类型，tstzrange来处理这种特殊情况。
手册提供了一个具有排除约束的完整代码示例。

- Erwin Brandstetter

---

ERROR: functions in index expression must be marked IMMUTABLE - Clodoaldo Neto

@Quassnoi：没错。在发布之前，我已经使用Postgres 9.1测试了这个解决方案。我也有一个可行的实现，稍微简化了一下。 - Erwin Brandstetter

@ErwinBrandstetter：在我的安装和sqlfiddle上都无法工作。 - Quassnoi

@ErwinBrandstetter：TZ 函数取决于会话区域参数，这就是我认为的原因。我原以为 EXTRACT 可能会对 EPOCH 进行一些魔法使其不可变，但看起来并非如此。 - Quassnoi

考虑这个 sqlfiddle 上带有时间戳的演示。如果你改成 timestamptz，它会失败。让我感到疑惑。但是现在我没有时间了... - Erwin Brandstetter

Answer 2

PostgreSQL需要一个命名类型和一个操作符类来实现排除约束。在9.1版本中，RANGE尚未实现。

您可以定义自己的类型，但这需要超级权限（即无法在托管数据库等上工作）。

您可以尝试将时间戳转换为框并使用交集运算符（&&）定义约束：

CREATE FUNCTION ts_to_box(TIMESTAMPTZ, TIMESTAMPTZ)
RETURNS BOX
AS
$$
    SELECT  BOX(POINT(DATE_PART('epoch', $1), -1), POINT(DATE_PART('epoch', $2), 1))
$$
LANGUAGE 'sql'
IMMUTABLE;

CREATE TABLE t (
    fromts timestamptz, 
    tots timestamptz,
    EXCLUDE USING GIST (ts_to_box(fromts, tots) WITH &&)
);

- Quassnoi

---

这个方案非常好，但是对于无限或空时间戳来说就不适用了。如果你必须处理这种情况，请使用时间扩展：https://dev59.com/I2kv5IYBdhLWcg3waAJT。你可以在这里看到为什么它不起作用：http://stackoverflow.com/questions/10642646/overlapping-box-with-infinite-edge-coordinate。 - yokoloko