Composite constraint of numrange and boolean

Question

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?

Erwin Brandstetter · Accepted Answer · 2017-12-21 03:42:51Z

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:

What is the optimal data type for an MD5 field?

Stack Exchange Network

Composite constraint of numrange and boolean

1 Answer 1

Your Answer

Linked

Hot Network Questions

Composite constraint of numrange and boolean

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions