2

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 > 1).

Putting this in two constraints fails, because the gist only covers the range overlap and join id.

e.g.

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

Any help/insight would be greatly appreciated

1 Answer 1

2

It sounds like you need a partial constraint.

alter table a_table add constraint foobar exclude using gist 
    (tstzrange("start_time", "end_time") WITH &&) where (active);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.