UUID的数组PostgreSQL的GIN指数 [英] PostgreSQL GIN index on array of uuid

问题描述

我想用在 A GIN指数的uuid [] （有对的UUID数组成员有效测试）。然而，当我尝试它PostgreSQL给我一个错误：

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?

注意这是该类型类似的问题 citext ，但所提供的答案是行不通的。

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

推荐答案

这可以使用下面的操作符类来完成：

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;

贷<一个href=\"http://www.sql.ru/forum/798928/uuid-nepolnaya-podderzhka-tipa-kto-kak-vykruchivaetsya\">this指着我在正确的方向。

相关文件是扩展索引接口的，尤其是运营商的策略和功能对于GIN数字有描述。

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

这篇关于UUID的数组PostgreSQL的GIN指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！