We have a production postgresql database (v14) that we have the hstore extension loaded and are using it with a custom postgresql operator to parse out specific key-pair values within our sql queries. These queries work in our production/development environment and I can run them in Datagrip with no issues. We are currently using Knowi.io as a BI tool and have been looking at Metabase. Here's a simplified, minimal query that produces the error in Metabase:
SELECT custom_values -> 'source'
The error response is:
ERROR: operator does not exist: shared_extensions.hstore -> unknown Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 140
We have a database that has a number of schemas defined, including and a shared_extensions schema with some custom operators and hstore defined within it.
I believe it's a search path related issue, so I included the shared_extensions schema (that exists in the databasse) in Metabase Databases configuration but, it didn't resolve the issue.
I can reproduce the error in Datagrip by not including the shared_extensions schema in the search path. How do I specify a search path in Metabase?