Geek Answers Handbook

How to use uuid with postgresql gist index type?

I cannot use uuid directly with gist index

CREATE INDEX idx_leaderboads_values_gist ON leaderboard_entry USING gist (id_leaderboard , value);

And I got this error:

ERROR: uuid data type does not have a default operator class for the Essence access method

TIP. You must specify an operator class for the index or define a default operator class for the data type.

+6
source share
2 answers

Postgres 10 or later

btree_gist now also covers the uuid data type, for example Paul commented . (And some other data types, great all enum types.)

Now all you have to do: install the extension once in the database:

 CREATE EXTENSION btree_gist;

Then your index should work.

on this topic:

Postgres 9.6 and later

(Original answer.)
I usually propose an additional btree_gist module, but the uuid type is not covered by it.

In theory, since UUID is a 128-bit quantity ( for documentation ), the most efficient way would be to convert it to two bigint or float8 for the index. But none of these casts are defined in standard Postgres.

I found stab in this direction on the pqsql-hackers list , but that seems unsuccessful.

The remaining option is the GiST functional index in the text view:

 CREATE INDEX idx_leaderboads_values_gist ON leaderboard_entry USING gist (id_leaderboard, cast("value" AS text));

To use this functional index, queries must match this expression. You can use the abbreviated "value"::text in queries (but not in the index definition without adding brackets).

Also: do not use value as the column name of the reserved word in standard SQL .

Question: why is the GiST index needed. The best solution depends on the goal.

+8
source

If you use Postgres 10 and you migrated your database from a previous version using dump / restore, you may need to run:

 ALTER EXTENSION btree_gist UPDATE;

to make gist indexes work with UUIDs.

+1
source

More articles:


All Articles