PostgreSQL GIN index on array of uuid

Question

I would like to use a GIN index on uuid[] (to have efficient membership tests for arrays of uuids). However when I try it PostgreSQL gives me an error:

mydb=> CREATE TABLE foo (val uuid[]);
CREATE TABLE
mydb=> CREATE INDEX foo_idx ON foo USING GIN(val);
ERROR:  data type uuid[] has no default operator class for access method "gin"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

How can I add the necessary operator class so that it works?

Note that this is a similar question for the type citext but the provided answer doesn't work.

Florent Guillaume · Accepted Answer · 2019-11-05 23:09:36Z

30

Note: this answer is obsolete as this is now part of a standard PostgreSQL, see tbussmann's other answer (which you should upvote).

Original answer:

This can be done using the following operator class:

CREATE OPERATOR CLASS _uuid_ops DEFAULT 
  FOR TYPE _uuid USING gin AS 
  OPERATOR 1 &&(anyarray, anyarray), 
  OPERATOR 2 @>(anyarray, anyarray), 
  OPERATOR 3 <@(anyarray, anyarray), 
  OPERATOR 4 =(anyarray, anyarray), 
  FUNCTION 1 uuid_cmp(uuid, uuid), 
  FUNCTION 2 ginarrayextract(anyarray, internal, internal), 
  FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal), 
  FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal), 
  STORAGE uuid;

Credits to this for pointing me in the right direction.

The relevant documentation is in Interfacing extensions to indexes, in particular the operator strategy and function numbers for GIN are described there.

edited Nov 5, 2019 at 23:09

answered Nov 13, 2013 at 16:54

Florent Guillaume

8,3671 gold badge26 silver badges25 bronze badges

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

5 Comments

Denis de Bernardy Over a year ago

Curious to know: might adding btree_gin have been enough, or was the above required?

Florent Guillaume Over a year ago

No, btree_gin does not include array types.

Michael Baldry Over a year ago

Just a note, you can't use this on Heroku or any other postgres host, as it requires superuser privs to create an operator class!

Michael Nelson Over a year ago

I tried using this on PostGres 9.6 but after running it I receive the same error as the OP. I thought maybe the underscore in FOR TYPE _uuid was a typo, but when I create FOR TYPE uuid I get another error when creating the index cache lookup failed for type 3177436692 . Any thoughts on what I might be doing wrong?

Pawel Zieminski Over a year ago

What about upgrades? Should we drop this operator class definition if, say, we go from 9.6 to version 10?

tbussmann · Accepted Answer · 2024-10-28 09:19:49Z

13

As of PostgreSQL 10 the custom operator class _uuid_ops is no longer necessary as there is now a general built-in opclass array_ops on anyarry (see: https://www.postgresql.org/docs/current/gin.html#GIN-BUILTIN-OPCLASSES)

edited Oct 28, 2024 at 9:19

answered Jul 6, 2018 at 16:45

tbussmann

64310 silver badges14 bronze badges

2 Comments

Florent Guillaume Over a year ago

Thanks @tbussmann, I referenced your answer in my original one.

Learner Over a year ago

Provided link seems to be removed from postgres, it gives page not found error. may be it is postgresql.org/docs/current/…

Collectives™ on Stack Overflow

PostgreSQL GIN index on array of uuid

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related