4

I have my pg_trgm module installed.

pg_trgm | 1.0     | extensions | text similarity measurement and index ...

The schema set is extensions. To use it I have to run something like this select:

extensions.similarity('hello','hallo');

I'm trying to run a statement using the % operator and got the following message.

mydb=# select * from rssdata where description % 'Brazil';
ERROR:  operator does not exist: character varying % unknown
LINE 1: select * from rssdata where description % 'Brazil';
                                            ^
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

What is necessary to run % or <-> operators?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Emerson R Orci
  • 85
  • 1
  • 6

1 Answers1

9

Most probably the problem is with the search_path setting. Run:

SHOW search_path;

Is the schema where you installed pg_trgm included? If not, include it.

Or, if you have the necessary privileges, you can change the schema of an extension with:

ALTER EXTENSION pg_trgm SET SCHEMA public;  -- or the schema you want

Alternatively, you can schema-qualify functions - and even operators using the OPERATOR() construct:

SELECT * FROM rssdata WHERE extensions.similarity(description, 'Brazil') > .8;
SELECT * FROM rssdata WHERE description OPERATOR(extensions.%) 'Brazil';

Removes dependency on the search_path. But it's tedious.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    Thanks. I changed it to pg_catalog. ALTER EXTENSION pg_trgm SET SCHEMA pg_catalog; Seems that it works. – Emerson R Orci Apr 09 '14 at 23:11
  • 1
    @user3491961: But `pg_catalog` is a bad idea. This should be reserved for system objects. Install extensions to the `public` schema or to a dedicated `extension` schema and be sure to include the schema in the search_path. Do *not* abuse `pg_catalog` for that! – Erwin Brandstetter Apr 09 '14 at 23:13
  • Thanks. Started yesterday learning Postgresql. Thanks for this. – Emerson R Orci Apr 10 '14 at 19:51
  • create extentions within the schema: `CREATE EXTENSION IF NOT EXISTS btree_gin WITH SCHEMA extensions;` `CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA extensions;` should work after this – bersen Jan 11 '17 at 21:58