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

    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:

    cage   INTEGER,
    animal TEXT,
    EXCLUDE USING gist (cage WITH =, animal WITH <>)

Same error message.

I am able to create this without any problem:

    user_id INTEGER,
    startend TSTZRANGE,
    EXCLUDE USING gist (startend WITH &&)

and this:

    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?

  • 11
    +1 Look here, people! That's how it's done. The question has everything it needs: the RDBMS and version, example code, error message, a clear definition of the problem, links, a display of what the OP has tried. The works. No noise. And this is from a first-time user! Chapeau. Convinced me immediately to have a closer look. Mar 22 '13 at 19:45
  • 2
    Follow-up question: dba.stackexchange.com/questions/37391/… Mar 23 '13 at 3:00

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):


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
    That was actually one of the first things I tried. It was in a much larger script and this error message got buried in the output: ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No such file or directory. I also assumed it was installed already because the ...EXCLUDE USING gist (startend WITH &&)... worked as shown in my original post. Thanks for making have a millionth look at this. Now to research that error. Mar 22 '13 at 21:09
  • 3
    @DenverTimothy: I think I can help with that, too. You probably need to install the contrib package postgresql-contrib-9.2 in your OS first. Depends on your OS. Consider this related answer on SO. Mar 22 '13 at 21:15
  • Also, it might be helpful to note that this is running on Mac OS 10.8.2, installed with the port tool. Mar 22 '13 at 21:17
  • @DenverTimothy: I'm not using Macs, but the principal should be the same. Install the package in your OS before you can run CREATE EXTENSION. Mar 22 '13 at 21:25
  • 1
    link to postgres mailing list thread with answer Jul 10 '18 at 7:30

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


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:

    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, privacy policy and cookie policy

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