partial constraint EXCLUDE USING gist

Question

I have the following constraint:

ALTER TABLE my_table
  ADD CONSTRAINT no_overlap EXCLUDE USING gist (
    product_id WITH =,
    applicable_period WITH &&
  )

Now I realized, that this should only be checked conditionally, e.g.

ALTER TABLE my_table
  ADD CONSTRAINT no_overlap EXCLUDE USING gist (
    product_id WITH =,
    applicable_period WITH &&
  )
  WHERE my_table.user_id IS NULL

Is this possible and otherwise how can I achieve this in an efficient/advised way? (I could add a trigger, but I hope there is more elegant/efficient approaches).

Should I resort to table partition? Thank you

Exclusion constraints prevent 2 rows from having overlapping values - you declare a list of comparisons and at least one of them should evaluate to FALSE or NULL. So you could simply add user_id WITH = to your constraint definition. — IVO GELOV
– IVO GELOV, Commented Apr 29, 2020 at 13:54
but wouldn't it be "broken" because NULL is not equal to NULL? — Andrey Deineko
– Andrey Deineko, Commented Apr 29, 2020 at 14:10
@IVOGELOV that would indeed allow overlapping ranges for rows without a user_id. But it would also allow overlapping ranges for different users - which the filtered version wouldn't. — user330315
– user330315, Commented Apr 29, 2020 at 14:16

user330315 · Accepted Answer · 2020-04-29 13:55:49Z

6

According to the manual, you simply need parentheses:

ALTER TABLE my_table
  ADD CONSTRAINT no_overlap 
  EXCLUDE USING gist (product_id WITH =, applicable_period WITH &&)
WHERE (user_id is null);

answered Apr 29, 2020 at 13:55

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

partial constraint EXCLUDE USING gist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related