1b5eyq
Last Updated: March 02, 2016
·
1.201K
· andersbrownwort
0b50c2089eed118232ae1ab41e39faf0

Index for uuid[] Array Data Type

PostgreSQL doesn't include an index type for uuid array columns.

Here's a table with a uuid array column:

CREATE TABLE items (
  things uuid[]
);

but when we try to create an index on it:

CREATE INDEX items_things_idx ON items USING GIN(things);
ERROR:  data type uuid[] has no default operator class for access method "gin"

so we must create an index type that understands how to compare elements in an array of UUIDs:

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;

Now our index will create and SELECTs to items in the UUID array will be fast. For example, find all the rows that contain a particular UUID anywhere in the array:

SELECT * FROM items WHERE things @> ARRAY['a6e34e5d-b1fb-4240-8ad9-21ddf23134bb']::uuid[];

4 Responses
Add your response

17824
934794545f4c74ad8a6bcf9939e764a4

This is awesome thank you.

over 1 year ago ·
17826
934794545f4c74ad8a6bcf9939e764a4

Is there a way I can check if this operator has already been created? I want to include it in my migrations, but only execute the sql if the operator hasn't already been created.

over 1 year ago ·
17829
0b50c2089eed118232ae1ab41e39faf0

IF EXISTS doesn't work for CREATE OBJECT so for that I was testing for duplicate object:

DO $$
  BEGIN

    -- -----------------------------------------------------
    -- New Index Type for uuid[] columns
    --
    -- Create a GIN inverted index type for UUID array
    -- columns to enable quick comparisons
    -- -----------------------------------------------------

    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;

  EXCEPTION
    WHEN duplicate_object THEN
      RAISE NOTICE 'error: %', SQLERRM;
  END;
$$;
over 1 year ago ·
17957
934794545f4c74ad8a6bcf9939e764a4

Excellent, thank you. I tried the IF EXISTS, and it didn't work, then chased my tail trying to find the negative version. Catching the exception with duplicate_object works well. Really appreciative of your help. Happt New Year!

over 1 year ago ·