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

how about casting parameters on the right side as hstore too?

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

On Wed, Aug 3, 2016 at 9:56 AM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> Tried that, but received "ERROR: column "parameters" is of type
> public.hstore but expression is of type text"
>
> No joy.
>
>
> On Wed, Aug 3, 2016 at 8:38 AM, Richard Albright <rla3rd(at)gmail(dot)com> wrote:
>
>> 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
>>>
>>
>>
>