PostgreSQL – Fix gin_trgm_ops Index Creation Failure

indexpostgresql

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

Best Answer

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,...';
Related Question