Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL GIN index on array of uuid

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.

like image 457
Florent Guillaume Avatar asked Nov 13 '13 16:11

Florent Guillaume


2 Answers

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.

like image 93
Florent Guillaume Avatar answered Oct 17 '22 23:10

Florent Guillaume


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/static/gin-builtin-opclasses.html)

like image 11
tbussmann Avatar answered Oct 17 '22 23:10

tbussmann