GIST exclude constraint with a specific value Is there a way to represent a constraint, where I prevent date range overlaps, but only when a column equals a specific value? So I can have zero or many inactive, but only ever one active?
I'm running into a problem where i need a gist constraint to check date range overlaps but only if a column equals a specific value ('active' in this case).
We have a table which has dates and there can be overlap, if a column equals a specific value (active can only ever have one, but inactive can be zero or many).
GIST seems to be able to say prevent any duplicate (2 active, but also excludes 1 active, 1 inactive, or inactive &gt; 1).
Putting this in two constraints fails, because the gist only covers the range overlap and join id.
e.g.
ALTER TABLE &quot;a_table&quot; 
  ADD CONSTRAINT &quot;CHK_some_name&quot; 
  EXCLUDE USING gist (&quot;a_join_id&quot; WITH =, tstzrange(&quot;start_time&quot;, &quot;end_time&quot;) WITH &amp;&amp;)

ALTER TABLE &quot;a_table&quot; 
  ADD CONSTRAINT &quot;CHK_some_name&quot; 
  EXCLUDE USING gist (&quot;an_enum&quot; with =, &quot;a_join_id&quot; WITH =, tstzrange(&quot;start_time&quot;, &quot;end_time&quot;) WITH &amp;&amp;)

Any help/insight would be greatly appreciated
It sounds like you need a partial constraint.
alter table a_table add constraint foobar exclude using gist 
    (tstzrange(&quot;start_time&quot;, &quot;end_time&quot;) WITH &amp;&amp;) where (active);