0

I'm trying to create a trigram index on a text filed containing names. I've added the pg_trgm extenion to the schema. Running \dx shows it is enabled:

List of installed extensions
   Name    | Version |   Schema   |                            Description                            
-----------+---------+------------+-------------------------------------------------------------------
 btree_gin | 1.3     | pg_catalog | support for indexing common datatypes in GIN
 dblink    | 1.2     | wos_core   | connect to other PostgreSQL databases from within a database
 pg_trgm   | 1.4     | wos_core   | text similarity measurement and index searching based on trigrams
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)

However when I run the following:

CREATE INDEX authors_full_name_idx ON wos_core.interface_table USING GIN (authors_full_name gin_trgm_ops);

I get the following:

ERROR:  operator class "gin_trgm_ops" does not exist for access method "gin"

Can anyone tell me what I'm doing wrong?

                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
  • Bingo! If you want to add it as an answer I'll accept it – Matt Apr 10 at 14:30
1

The schema wos_core is probably not in your search_path.

In that case, you need to prefix the operator with the schema name:

CREATE INDEX authors_full_name_idx 
       ON wos_core.interface_table USING GIN (authors_full_name wos_core.gin_trgm_ops);

Alternatively you can change the search path of your user, to include wos_core:

alter user your_username set search_path = 'public,wos_core,...';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.