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

Question

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.

Josha Inglis · Accepted Answer · 2016-01-07 02:32:25Z

2

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)

answered Jan 7, 2016 at 2:32

Josha Inglis

1,05811 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alonme Over a year ago

@jusha Inglis - Does this still work for you? i am getting errors with SQLAlchemy==1.3.11

Josha Inglis Over a year ago

@Alonme I don't know, sorry. It's been a while since I've used sqlalchemy in anger.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related