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

try

update configuration set parameters = parameters || hstore('CONNECTOR_TIME_OUT'::text, '-1'::text)

On Wed, Aug 3, 2016 at 9:34 AM, John Scalia <jayknowsunix@gmail.com> wrote:

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