As a test earlier today, I was following these instructions: http://stormatics.com/howto-handle-key-value-data-in-postgresql-the-hstore-contrib/ and the section on that page for updating value of a key worked perfectly on the sandbox server, but not my problem child server. When I first tried to follow all the procedures in this web page, I did first have to create the extension in the sandbox database I was using. That's the one that reported 1.3 as in:
\dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+-------------------------------------------------- hstore | 1.3 | public | data type for storing sets of (key, value) pairs plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)
Very odd, when I tried using the hstore(text, text) on my problem server, I only got back a column does not exist error. I probably used it incorrectly, though.
Your instruction to run "\do ||" did return a list including left arg type as text and right arg type as text. The list does report the schema as pg_catalog as opposed to the schema I'm actually working in at the moment, and I'm not sure if this is correct or not.
On Wed, Aug 3, 2016 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
John Scalia <jayknowsunix@gmail.com> writes: > You are correct, though, Tom, that the hstore extension is 1.1. Is there > some other, not =>, construct I should be using?
The hstore(text,text) function replaces that. > Curiously, I just checked > my personal sandbox database (9.5) and the hstore there is 1.3, but like I > said earlier the update succeeds on that one.
That's pretty hard to believe, because 9.5 disallows => as an operator name altogether.
I still suspect that you have got a search path problem. You might try, eg, "\do ||" in psql and see whether hstore || is listed or not. Also check which schema \dx says the hstore extension is in in each database.