How to use uuid with postgresql gist in EXCLUDE constraint

Question

I'm getting error while I use Exclude constraint using gist

ALTER TABLE tbl_product ADD EXCLUDE USING gist (base_id WITH =, lifetime WITH &&);

ERROR:  data type uuid 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.

Note: base_id datatype is uuid, lifetime datatype is period

I am using PostgreSQL 9.4. I have to use 9.4 only as I don't have any other option since I am unable to install temporal extension in 9.5, 9.6 and 10 are gives an error.

Answer 1

You'll need the btree_gist extension for that:

btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, and all enum types.

Unfortunately support for uuid was only added in v10.

With v10, you should be able to use

base_id gist_uuid_ops WITH =

in your exclusion constraint.

With 9.4, you could cast the column to a different type first:

(base_id::text) gist_text_ops WITH =

Answer 2

The accepted answer is correct, btree_gist is needed, however the suggested solution does not work (at least not in v12 in 2021). If you've been getting errors like in original question you should do the following:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE tbl_product ADD EXCLUDE USING gist (base_id WITH =, lifetime WITH &&);

As a bonus, I've been using it in Elixir & Ecto 3.5 and here's how to do this in Ecto migration:

execute "CREATE EXTENSION IF NOT EXISTS btree_gist"
create constraint(:tbl_product, "constraint_name", exclude: ~s|gist ("base_id" WITH =, lifetime WITH &&)|)