current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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.

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.

share|improve this answer
    
Curious to know: might adding btree_gin have been enough, or was the above required? –  Denis Nov 13 '13 at 18:39
    
No, btree_gin does not include array types. –  Florent Guillaume Nov 13 '13 at 22:22
    
this is great, thank you –  Michael Baldry Nov 27 at 13:08
    
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 Baldry Nov 28 at 8:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.