Thread: pg_trgm Extension Installed but Operators and Functions do not exist

I am trying to run a query which was working a few weeks ago, and it utilizes the <<-> operator but I am getting an error: operator does not exist text <<-> text.

If I try instead the function word_similarity() I get a similar (no pun intended) error: function word_similarity(text, text) does not exist.

If I try to install the pg_trgm extension with `CREATE EXTENSION pg_trgm;` I get an error: extension "pg_trgm" already exists.

Running PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit

Any ideas?  Thanks,

"Igal @ Lucee.org" <igal@lucee.org> writes:
> I am trying to run a query which was working a few weeks ago, and it 
> utilizes the <<-> operator but I am getting an error: operator does not 
> exist text <<-> text.

> If I try instead the function word_similarity() I get a similar (no pun 
> intended) error: function word_similarity(text, text) does not exist.

> If I try to install the pg_trgm extension with `CREATE EXTENSION 
> pg_trgm;` I get an error: extension "pg_trgm" already exists.

Seems like a likely bet is that the extension is installed in a schema
that isn't in your search_path.

            regards, tom lane

On 1/28/2018 12:35 PM, Tom Lane wrote:
> "Igal @ Lucee.org" <igal@lucee.org> writes:
>> I am trying to run a query which was working a few weeks ago, and it
>> utilizes the <<-> operator but I am getting an error: operator does not
>> exist text <<-> text.
>>
>> If I try to install the pg_trgm extension with `CREATE EXTENSION
>> pg_trgm;` I get an error: extension "pg_trgm" already exists.
> Seems like a likely bet is that the extension is installed in a schema
> that isn't in your search_path.

Thank you, Tom.  I was unaware of the fact that extensions are installed 
to specific schemas.

For future users who might face this issue, I fixed it by re-creating 
the extension in the pg_catalog schema, which is always in the 
search_path [1]:

   DROP EXTENSION pg_trgm;

   CREATE EXTENSION pg_trgm SCHEMA pg_catalog;

Thanks again,

Igal

[1] 
https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-SEARCH-PATH