Proper syntax to update an hstore key-value pair

From: John Scalia <jayknowsunix(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Proper syntax to update an hstore key-value pair
Date: 2016-08-03 13:34:46
Message-ID: CABzCKRBa6859u6NEcmyddc0iknoMbmMao4AAVpTUS4i464J0uA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,

I've got something strange going on in one of my databases. I need to
update key-value pair to fix one of our configurations. The table is named
"configuration" and the hstore attribute is called "parameters". The update
I've been attempting looks like:

update configuration set parameters = parameters || '"CONNECTOR_TIME_OUT"
=> "-1"'::hstore;

This update works properly from the psql command line on one of my sandbox
databases, but this particular test database reports: ERROR: type "hstore"
does not exist. Yes, I know this pair is a valid, and already existing one
in the database. The really weird part of this is that the same update
works inside pgAdmin III and performs the update.

Now \dx shows that hstore is an installed extension. I've had issues before
where I've had to qualify hstore using ::public.hstore, but in this case
that gives me different error stating that no operator matches the given
name and argument type.

Is there some other syntax that I could use to make this update? I've tried
some variants, mostly with the where and whether I'm using single or double
quotes with no success.

Note that this is not a problem any longer, as pgAdmin did a proper update,
I'd just like to know why this fails in psql on this one server, and for
any future activities I might need to do.
--
Jay

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Richard Albright 2016-08-03 13:38:04 Re: Proper syntax to update an hstore key-value pair
Previous Message Albe Laurenz 2016-08-03 07:11:59 Re: Autovacuum of pg_shdepend