Sample Header Ad - 728x90

How to find out what operator class and access method should be used for an exclusion constraint?

3 votes
1 answer
4427 views
Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table. From reading the docs carefully, I could get as far as this: create table subnets ( subnet cidr, exclude (subnet with &&) ); But this doesn't work. It produces the inscrutable message: ERROR: operator &&(inet,inet) is not a member of operator family "network_ops" DETAIL: The exclusion operator must be related to the index operator class for the constraint. Despite reading up on the doc sections about operator classes and indexes and index types, I retained the feeling that I was missing an entire explanatory section that is taken for granted. I still don't know what IS an operator class, really, nor an operator family. I did find [posted on a mailing list](https://groups.google.com/forum/#!topic/mailing.database.pgsql-admin/9ldVTdZGrp8) a code snippet that led to the following working code: create table subnets ( subnet cidr, exclude using gist (subnet inet_ops with &&) ); But I can't truly understand what the "using gist" is for, nor the "inet_ops." I know that "using gist" relates to types of indexes. I know an index is automatically created for a "unique" constraint, and I *guess* that an index may also be automatically created for an "exclusion" constraint. [The only documentation about "operater classes"](https://www.postgresql.org/docs/9.6/static/indexes-opclass.html) all relates to *indexes,* not constraints. **For this or future queries where I want an exclusion constraint, how can I determine what operator class and access method should be specified for the constraint to work?** Note that even with the working code in hand I'm unable to find *why* it's "gist" and not something else, and *why* it's "inet_ops" and not "network_ops" or nothing. \doS and the queries listed in the operator class documentation were unenlightening. Another error I produced was also unenlightening: vagrant=# create table subnets ( subnet cidr, exclude using gist (subnet with &&) ); ERROR: data type cidr has no default operator class for access method "gist" HINT: You must specify an operator class for the index or define a default operator class for the data type. (This question is based on the premise that I should not *need* to resort to copying and pasting incantations; a thorough review of the documentation plus carefully reading error messages *should* let me resolve problems. With Postgres, this has always been true before now.)
Asked by Wildcard (587 rep)
May 4, 2018, 02:24 AM
Last activity: May 4, 2018, 02:33 PM