1

I have a current constraint like this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH &&);

This works fine, but I need to add one more thing to the constraint, it must only be enforced for rows where a boolean column is true (let's call it active). I tried this:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint EXCLUDE USING gist((<uuidcol>::text) WITH =, numrange(<start>, <end>) WITH && active = TRUE);

This throws syntax error:

ERROR: syntax error at or near "active"
SQL state: 42601

Is it possible to have the constraint include the active column as well?

2

You can add a WHERE clause to make it a partial EXCLUSION constraint (creating a partial index to implement it):

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist ((uuidcol::text) WITH =, numrange(start, end) WITH &&)
  WHERE (active);

The parentheses are not optional. The manual:

The predicate allows you to specify an exclusion constraint on a subset of the table; internally this creates a partial index. Note that parentheses are required around the predicate.

BTW, in Postgres 10 or later you can install the additional module btree_gist to add (among others) the necessary operator class for type uuid in GiST indexes. So you can simplify:

ALTER TABLE myschema.my_table ADD CONSTRAINT my_constraint
  EXCLUDE USING gist (uuidcol WITH =, numrange(start, end) WITH &&)
  WHERE (active);

Considerably more efficient than casting to text. Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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