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.

Laurenz Albe · Accepted Answer · 2018-05-11 06:43:23Z

3

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 =

edited May 11, 2018 at 6:43

answered May 9, 2018 at 20:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thavaprakash Swaminathan Over a year ago

still getting error

ERROR:  operator class "gist_uuid_ops" does not exist for access method "gist" STATEMENT:  ALTER TABLE tbl_product ADD EXCLUDE USING gist (base_id gist_uuid_ops WITH =, lifetime WITH &&);

Laurenz Albe Over a year ago

Maybe you installed the extension in a schema that is not in your search_path. What do you get for \dx btree_gist and SELECT opcowner::regrole, opcnamespace::regnamespace FROM pg_opclass WHERE opcname = 'gist_uuid_ops'; ?

Thavaprakash Swaminathan Over a year ago

# \dx btree_gist List of installed extensions ┌────────────┬─────────┬────────┬─────────────────────────────────────┐ │ Name │ Version │ Schema │ Description │ ├────────────┼─────────┼────────┼─────────────────────────────────────┤ │ btree_gist │ 1.0 │ public │ support for indexing common datatypes in GiST │ └────────────┴─────────┴────────┴─────────────────────────────────────┘ (1 row) # SELECT * FROM pg_opclass WHERE opcname = 'gist_uuid_ops'; (0 rows)

Laurenz Albe Over a year ago

Sorry, I missed that you are on 9.4. See my extended answer.

Bartosz Błaszków · Accepted Answer · 2021-01-19 10:31:52Z

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

answered Jan 19, 2021 at 10:31

Bartosz Błaszków

4113 silver badges3 bronze badges

Collectives™ on Stack Overflow

How to use uuid with postgresql gist in EXCLUDE constraint

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related