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
Last activity: May 4, 2018, 02:33 PM