Re: Proper syntax to update an hstore key-value pair

[John Scalia <jayknowsunix@xxxxxxxxx>]

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@xxxxxxxxxxxxx> wrote:

John Scalia <jayknowsunix@xxxxxxxxx> 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.

                        regards, tom lane