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(at)gmail(dot)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
>