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?

Answer 1

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)
);