Error while running fuzzy search using pg_trgm extension

Question

I have created pg_trgm extension in postgres. Application works fine for querying for several days. But after few days, gives an error ERROR: operator does not exist: character varying % text Hint: No operator matches the given name and argument types. You might need to add explicit type casts. When dropping the extension and recreating, then everything works smooth.

What goes wrong not able to get? Is there a permanent solution so that I didn't need to create the extension again and again?

Answer 1

Either you didn't create the extension pg_trgm in that database, or you created it in a schema that is not on the search_path active for the query.

You can also schema-qualify an operator:

WHERE col OPERATOR(schema_name.%) 'searchstring'