CODEWITHSUNDEEP
postgresqlindexinguuidgist-index
xjodoin
xjodoin

Reputation: 549

How to use uuid with postgresql gist index type?

I can't use directly uuid with gist index

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

And I got this error:

ERROR: data type uuid has no default operator class for access method "gist"

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

Upvotes: 16

Views: 8857

Answers (3)

scūriolus
scūriolus

Reputation: 968

For anyone having one this error message using Django:

UndefinedObject: data type uuid has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

The above exception was the direct cause of the following exception:

ProgrammingError: data type uuid has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

The best way to install the extension (reversibly) is to edit your migrations files, this is what you need:

# 0000_GistIndex.py

from django.contrib.postgres.operations import BtreeGistExtension
from django.db import migrations


class Migration(migrations.Migration):
    initial = True
    dependencies = []
    operations = [BtreeGistExtension()]

And then, add 0000_GistIndex to the dependencies of 0001_initial.py.

Tested on Django 5.1. Link to the documentation.

I know this answer is more specific than what the OP was looking for, but that page was the first result on a popular search engine.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656596

Postgres 10 or newer

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

Now all you need is to install the extension once per database:

CREATE EXTENSION btree_gist;

Then your index should just work.

Related:

Postgres 9.6 or older

(Original answer.)
Normally I would suggest the additional module btree_gist, but the type uuid is not covered by it.

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

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

The remaining option is a functional GiST index on the text representation:

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

To make use of this functional index, queries must match that expression. You can use the shorthand "value"::text in queries (but not in the index definition without adding more parentheses).

Aside: do not use value as column name it's a reserved word in standard SQL.

The question is: why do you need the GiST index. The best solution depends on the objective.

Upvotes: 18

Ryan
Ryan

Reputation: 2998

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

ALTER EXTENSION btree_gist UPDATE;

in order to get gist indexes to work with UUID's.

Upvotes: 5

Related Questions