postgresqlsqlalchemypostgresql-9.4gist-index

ERROR: data type inet has no default operator class for access method "gist"


Using PostgreSQL version 9.4.5, I have a table that contains an INET column. In that column I have a gist index

CREATE TABLE ipv4_prefix (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    prefix inet NOT NULL,
    CONSTRAINT ck_ipv4_prefix_valid_ipv4 CHECK ((family(prefix) = 4))
);
ALTER TABLE ONLY ipv4_prefix ADD CONSTRAINT pk_ipv4_prefix PRIMARY KEY (id);
CREATE INDEX ipv4_prefix_gist_index ON ipv4_prefix USING gist (prefix inet_ops);

This all seems to work:

mimir=# \d ipv4_prefix
              Table "ipv4_prefix"
 Column | Type |              Modifiers              
--------+------+-------------------------------------
 id     | uuid | not null default uuid_generate_v4()
 prefix | inet | not null
Indexes:
    "pk_ipv4_prefix" PRIMARY KEY, btree (id)
    "ipv4_prefix_gist_index" gist (prefix inet_ops)
Check constraints:
    "ck_ipv4_prefix_valid_ipv4" CHECK (family(prefix) = 4)

However, when I try to add an EXCLUDE constraint:

ALTER TABLE ipv6_prefix ADD CONSTRAINT ipv6_prefix_exclude_constraint EXCLUDE USING gist (prefix WITH &&);

I get the following error:

ERROR:  data type inet 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.

Solution

  • So, I still haven't found the documentation for this, but figured it out with some experimentation.

    You need to add the ops class after the column name while creating the constraint.

    So (prefix inet_ops WITH &&) rather than (prefix WITH &&)

    For those using SQLAlchemy, you can sneak this through:

    class IPv4PrefixTable(IPPrefixTable):
        __tablename__ = 'ipv4_prefix'
        __table_args__ = (
            Index('ipv4_prefix_gist_index', 'prefix', postgresql_using='gist', postgresql_ops={
                'prefix': 'inet_ops'
            }),
            ExcludeConstraint(('prefix inet_ops', '&&'), using='gist', name='ipv4_prefix_exclude_constraint'),
            {'schema': 'mimir'}
        )
    
        id = Column(UUID(as_uuid=True), primary_key=True, server_default=func.uuid_generate_v4())
        prefix = Column(INET, CheckConstraint('family(prefix) = 4', name='valid_ipv4'), unique=False, nullable=False)