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

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 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" 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 spe

PostgreSQL Indexes consist of two parts, from the docs on Interfacing Extensions To Indexes

• Index Methods (Access Method), for those you're talking about brin, btree, gin, gist, hash, sp-gist (you can see this with \dA+ in psql.)

• Operator Class

GIST is the Index Method. For why GIST or SP-GIST is required on EXCLUSION CONSTRAINTS see this post. The Operator Class you need is inet_ops. The problem you're encountering is for cidr and inet two GIST operator classes are provided, and the wrong one is the default,

• the operator class gist_cidr_ops is the default. It's part of the "Additional Supplied Modules" and most distros package it as -contrib. It is provided by the btree_gist extension (source)

• the operator class inet_ops is core, and not the default.

How would you know this? You probably wouldn't. As far as the access type, good luck. The docs themselves while distributed with PostgreSQL are sorely lacking here, though they do touch on this,


For historical reasons, the inet_ops operator class is not the default class for types inet and cidr. To use it, mention the class name in CREATE INDEX, for example

CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);

The best you could do is submit a doc patch to btree_gist where this should probably also be documented.

StackExchange Database Administrators Q#205773, answer score: 4