Logical operators in a Postgres EXCLUDE constraint

Question

Consider a following schema with a date range that must not overlap between two arbitrary rows:

CREATE TABLE dummy (
     duration tsrange,
     active   bool,
     EXCLUDE USING gist (duration WITH &&)
);

How would I limit this constraint to rows where active is true? Meaning that no two rows with both active = true can overlap in date. Rows with active = false would be allowed to overlap with any other row.

According to this post, the above exclude constraint checks that for every two rows A.duration && B.duration is false or null. The conclusion would be to use a logical operator to check if both actives are true.

What I tried is the following:

CREATE TABLE dummy (
     duration tsrange,
     active   bool,
     EXCLUDE USING gist (duration WITH &&, active WITH AND)
);

But this operator does not seem to be supported by the exclude syntax:

ERROR:  syntax error at or near "AND"

The bit-wise and & does also not work:

CREATE TABLE dummy (
     duration tsrange,
     active   bool,
     EXCLUDE USING gist (duration WITH &&, active WITH &)
);
ERROR:  data type boolean has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

Is there any possibility to express my constraint?

score 3 · Accepted Answer · answered Aug 07 '23 at 17:05

3

It is not entirely clear what you want to allow or disallow.

If you want to allow overlap as long as at most one member of each overlap is 'active', then you need a simple EXCLUDE with a WHERE clause, not a compounded EXCLUDE.

CREATE TABLE dummy (
     duration tsrange,
     active   bool,
     EXCLUDE USING gist (duration WITH &&) where (active is true)
);

answered Aug 07 '23 at 17:05

jjanes

37,812
5
27
34

I want to check for overlap where both rows are 'active'. I have edited my post to hopefully be more clear about it. – Heap Underflow Aug 07 '23 at 23:11
@HeapUnderflow I think we are describing the same thing, so my answer should work for you. – jjanes Aug 08 '23 at 03:08
Yes, you are right. For some reason, I was under the assumption I would not be able to insert active rows where an inactive is. This was wrong. – Heap Underflow Aug 08 '23 at 08:25

Logical operators in a Postgres EXCLUDE constraint

1 Answers1