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?
Best Answer
You can add a
WHERE
clause to make it a partialEXCLUSION
constraint (creating a partial index to implement it):The parentheses are not optional. The manual:
BTW, in Postgres 10 or later you can install the additional module
btree_gist
to add (among others) the necessary operator class for typeuuid
in GiST indexes. So you can simplify:Considerably more efficient than casting to
text
. Related: