HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

ERROR: data type text[] has no default operator class for access method "gist"

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorclassoperatormethodtextgisttypehasdefaultfor

Problem

Whenever I try to try to create a GIST index on text[], I get the above error.

CREATE TABLE bar(f)
AS VALUES
  (ARRAY['foo','bar','baz']);

CREATE INDEX ON bar USING gist(f);

ERROR:  data type text[] 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.


I'm trying to create an Exclusion Constraint on bar, so I need a GIST index.

Solution

smlar Extension

I was able to create a gist index on a text[] column using the smlar extension (written by Teodor Sigaev of text search fame)

# git clone git://sigaev.ru/smlar.git
# cd smlar/
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install
# psql

postgres=# create extension smlar;
postgres=# create index on bar using gist(f _text_sml_ops);

Code Snippets

# git clone git://sigaev.ru/smlar.git
# cd smlar/
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install
# psql

postgres=# create extension smlar;
postgres=# create index on bar using gist(f _text_sml_ops);

Context

StackExchange Database Administrators Q#185174, answer score: 2

Revisions (0)

No revisions yet.