0

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?

Prerna
  • 3
  • 2

1 Answers1

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'
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The extension is created in database, query also works fine. But after a few days, when trying to run fuzzy search, it gives error. When recreating the extension, everything goes smooth again. Is there a problem with the extension? – Prerna Jun 26 '22 at 01:48
  • Then there must be something on your system that drops the extension, or perhaps drops and re-creates the whole database regularly. PostgreSQL does nothing of that sort by itself. – Laurenz Albe Jun 26 '22 at 11:37
  • Is there any way to check if the whole database drops & re-create regularly? Also, can you provide any solution how to stop this behavior of this database if this's happening? – Prerna Jun 27 '22 at 05:31
  • The database does not drop and re-create. Be assured that this has an outside cause. You can log database activity to find out more. – Laurenz Albe Jun 27 '22 at 05:53
  • Thanks Launenz Albe. The above answer helped me solved the problem. – Prerna Jul 07 '22 at 05:54