PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
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, afterbtree_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-9.2
(for Postgres 9.2). Or just postgresql-contrib
for the Red Hat family. Consider this related answer on SO:
- Error when creating unaccent extension on PostgreSQL
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.