60

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 this example which does not work for me:

CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

Same error message.

And this one, which does not work for me either:

CREATE TABLE zoo (
    cage   INTEGER,
    animal TEXT,
    EXCLUDE USING gist (cage WITH =, animal WITH <>)
);

Same error message.

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 about figuring out how to make this work, or figuring out why it won't work. Any ideas?

1

2 Answers 2

52

Install the additional module btree_gist as is mentioned in the manual at the location you linked to:

You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, after btree_gist is installed, the following constraint will reject overlapping ranges only if the meeting room numbers are equal:

In modern PostgreSQL you only need to run (once per database):

CREATE EXTENSION btree_gist;

You need to have the "contrib" package installed in your OS first. Details depend on your OS and the software repository used. For the Debian family it's typically postgresql-contrib-13 (for Postgres 13). Or just postgresql-contrib for the Red Hat family. Consider this related answer on SO:

1
5

if someone can't or doesn't want to use this:

CREATE EXTENSION btree_gist;

As it was in my case, because Django 1.11 ORM does not support this index and I didn't want to write SQL outside Django. I used something similar to:

EXCLUDE USING gist (
    int4range(userid, userid, '[]') WITH =,
    startend WITH && 
)

'[]' is used to make sure both bounds are inclusive. Tested with Postgres 9.6 and 10.5.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.