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[];Written by Anders Brownworth
Related protips
4 Responses
This is awesome thank you.
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.
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;
$$;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!
