Thread: PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table:

    CREATE TABLE test (
        user_id INTEGER,
        startend TSTZRANGE,
        EXCLUDE USING gist (user_id WITH =, startend WITH &&)
    );

But I get this error:

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

The PostgreSQL docs use these this example, which does not work for me
(http://www.postgresql.org/docs/9.2/static/rangetypes.html):

    CREATE TABLE room_reservation (
        room text,
        during tsrange,
        EXCLUDE USING gist (room WITH =, during WITH &&)
    );
    ERROR:  data type integer 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.

And this one, which does not work for me either (http://www.postgresql.org/docs/9.2/static/btree-gist.html):

    CREATE TABLE zoo (
        cage   INTEGER,
        animal TEXT,
        EXCLUDE USING gist (cage WITH =, animal WITH <>)
    );
    ERROR:  data type text 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.

I am able to create this without any problem:

    CREATE TABLE test (
        user_id INTEGER,
        startend TSTZRANGE,
        EXCLUDE USING gist (startend WITH &&)
    );

and this:

    CREATE TABLE test (
        user_id INTEGER,
        startend TSTZRANGE,
        EXCLUDE USING btree (user_id WITH =)
    );

I've spent quite a bit of time searching for hints on figuring out how to make this work, or figuring out why it won't
work.I've also been trying to understand CREATE OPERATOR and CREATE OPERATOR CLASS, but those are over my head for now.
Couldanyone point me in the right direction? 

On Fri, Mar 03/22/13, 2013 at 10:14:45AM -0600, Denver Timothy wrote:
> In PostgreSQL 9.2.3 I am trying to create this simplified table:
>
>     CREATE TABLE test (
>         user_id INTEGER,
>         startend TSTZRANGE,
>         EXCLUDE USING gist (user_id WITH =, startend WITH &&)
>     );
>
> But I get this error:
>
>     ERROR:  data type integer 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.
>
> I've spent quite a bit of time searching for hints on figuring out how to make this work, or figuring out why it
won'twork. I've also been trying to understand CREATE OPERATOR and CREATE OPERATOR CLASS, but those are over my head
fornow. Could anyone point me in the right direction? 

CREATE EXTENSION btree_gist;

-Ryan Kelly

On Mar 22, 2013, at 2:57 PM, Ryan Kelly <rpkelly22@gmail.com> wrote:

> On Fri, Mar 03/22/13, 2013 at 10:14:45AM -0600, Denver Timothy wrote:
>> In PostgreSQL 9.2.3 I am trying to create this simplified table:
>>
>>    CREATE TABLE test (
>>        user_id INTEGER,
>>        startend TSTZRANGE,
>>        EXCLUDE USING gist (user_id WITH =, startend WITH &&)
>>    );
>>
>> But I get this error:
>>
>>    ERROR:  data type integer 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.
>>
>> I've spent quite a bit of time searching for hints on figuring out how to make this work, or figuring out why it
won'twork. I've also been trying to understand CREATE OPERATOR and CREATE OPERATOR CLASS, but those are over my head
fornow. Could anyone point me in the right direction? 
>
> CREATE EXTENSION btree_gist;

That was one of the first things I tried, but going back and looking at things for the millionth time, I found the
errorwas buried in the script output: 

ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No such file
ordirectory 

I also assumed it was installed because at least one of the previous examples worked.

It turns out the contrib module (and several others) were not installed. On Mac OS 10.8.2 using MacPorts, I did this:

$ su
# port build postgresql92
# cd `port work postgresql92`/postgresql-<version>/contrib
# for d in *; do test -d $d && ( echo $d; cd $d; make all && make install; cd .. ); done

Now it works as expected.