#StackBounty: #postgresql #uuid #postgresql-9.6 How to add a PostgreSQL GIN index on a uuid in Postgres 9.6

Bounty: 50

I’m trying to add a GIN index that includes a UUID in a Postgres 9.6 database. Technically it is a composite index, with composite GIN support coming from the btree_gin plugin.

I try to create the index with this statement:

CREATE EXTENSION btree_gin;

CREATE INDEX IX_text_search_vector ON text_information USING GIN (client_id, text_search_vector);

but I get this error

HINT:  You must specify an operator class for the index or define a default operator class for the data type.

client_id is a UUID and text_search_vector is a tsvector. I don’t think the composite/btree_gin factor is actually relevant, as I get the same error trying to create the index on just client_id alone, but hopefully if there is a solution to this, it is one that will work with a composite index also.

I found PostgreSQL GIN index on array of uuid , which seems to suggest that it should be possible (if an array of UUIDs can be done, then surely an individual UUID can be done). However, the solution there was pretty opaque to me — it’s not immediately obvious how to modify this solution to support a single UUID.

I would prefer a solution that doesn’t involve casting the uuid to another type in the index or in another column, as I would rather not have to write specialized queries with casts in them (we are using django ORM to generate queries atm).


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.