Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
+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. – Erwin Brandstetter Mar 22 '13 at 19:45
Follow-up question:… – Erwin Brandstetter Mar 23 '13 at 3:00
up vote 13 down vote accepted

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

share|improve this answer
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. – Ian Timothy Mar 22 '13 at 21:09
@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. – Erwin Brandstetter 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. – Ian Timothy 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. – Erwin Brandstetter Mar 22 '13 at 21:25

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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