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

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
>>
>
>