Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops"

How come when I try to create an exclusion constraint with GIST,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE (a WITH &&)
);
ERROR:  operator &&(anyrange,anyrange) is not a member of operator family "range_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

An EXCLUSION constraint with && (Overlaps) needs to be GIST or SP-GIST because the default index is btree and its operator class does not support && (Overlaps). From the docs on Interfacing Extensions To Indexes


GiST indexes are more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” support routine of each particular GiST operator class interprets the strategy numbers however it likes. As an example, several of the built-in GiST index operator classes index two-dimensional geometric objects, providing the “R-tree” strategies shown in Table 37.4. Four of these are true two-dimensional tests (overlaps, same, contains, contained by); four of them consider only the X direction; and the other four provide the same tests in the Y direction.

You can use the GIST index on an exclusion constraint with,

CREATE TABLE foo (
  a tsrange,
  EXCLUDE USING GIST (a WITH &&)
);

The operator class

• btree supports only `, >=






• gist and sp-gist support far more. And, gin may also one day support being used in an EXCLUSION constraint.




So long as the default GIST operator class is properly defaulted for the type you're using it should work: it's not for inet and cidr`

See also,

• Why does a CIDR or INET index require me to specify the index's Operator Class?

StackExchange Database Administrators Q#201552, answer score: 2