Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 able 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?

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
1  
Follow-up question: dba.stackexchange.com/questions/37391/… –  Erwin Brandstetter Mar 23 '13 at 3:00

1 Answer 1

up vote 6 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):

CREATE EXTENSION btree_gist;
share|improve this answer
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. –  Denver Timothy Mar 22 '13 at 21:09
2  
@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. –  Denver 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

 
discard

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.