CODEWITHSUNDEEP
postgresqlpostgresql-10
Francesco Belladonna
Francesco Belladonna

Reputation: 11689

How to add an exclude constraint with an existing index with specified operator?

Assuming the following index:

CREATE INDEX CONCURRENTLY blocked_date_idx_on_since_until_range
  ON blocked_date
  USING gist(
    tstzrange(
      "blocked_since",
      "blocked_until",
      '[]'
    )
  );

Which is a range on 2 dates, I'd like to:

ALTER TABLE blocked_date
  ADD CONSTRAINT blocked_date_since_until_overlap
  EXCLUDE
    USING INDEX blocked_date_idx_on_since_until_range WITH &&;

This syntax is not valid (I get an error), but I can't find a valid one where I can specify which operator to use on the index. What is the right syntax for the ALTER TABLE part?

Upvotes: 3

Views: 450

Answers (1)

Francesco Belladonna
Francesco Belladonna

Reputation: 11689

Currently there is no syntax to achieve this. You are required to create the constraint directly and can't create the index upfront.

Upvotes: 4

Related Questions