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 (
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 a code snippet that led to the following working code:
create table subnets (
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" 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 (
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.)