uuid数组的PostgreSQL GIN索引

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:

我想在uuid []上使用GIN索引（以便对uuids数组进行有效的成员资格测试）。但是当我尝试它时，PostgreSQL给了我一个错误：

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.

请注意，这是类型citext的类似问题，但提供的答案不起作用。

2 个解决方案

#1

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.

相关文档在索引的接口扩展中，特别是GIN的操作员策略和功能编号。

#2

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)

从PostgreSQL 10开始，不再需要自定义运算符类_uuid_ops，因为现在在anyarry上有一个通用的内置opclass array_ops（参见：https：//www.postgresql.org/docs/current/static/gin-builtin-opclasses的.html）

#1