From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Scalia <jayknowsunix@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Proper syntax to update an hstore key-value pair
Date: Wed, 03 Aug 2016 10:34:24 -0400
Message-ID: <22859.1470234864@sss.pgh.pa.us> (raw)
In-Reply-To: <CABzCKRBa6859u6NEcmyddc0iknoMbmMao4AAVpTUS4i464J0uA@mail.gmail.com>
References: <CABzCKRBa6859u6NEcmyddc0iknoMbmMao4AAVpTUS4i464J0uA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
John Scalia <jayknowsunix@gmail.com> writes:
> 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;
That doesn't look right, because || and => have the same precedence.
You'd need to write
update configuration set parameters =
parameters || ('"CONNECTOR_TIME_OUT" => "-1"'::hstore);
But you'd be better off moving to use the hstore() function as Richard
suggested, because the => operator is gone as of PG 9.2 or so.
> 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.
Probably a search path problem, but you've not provided enough details
to diagnose.
> 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.
I'm betting this is hstore 1.1 or later, which hasn't got the => hstore
operator, because we deprecated it some time before that and needed to get
rid of it altogether to make room for the SQL-standard meaning of =>.
Use the function.
> [ later ]
> Tried that, but received "ERROR: column "parameters" is of type
> public.hstore but expression is of type text"
I'm still betting on a search path problem, causing || to resolve as
text||text not hstore||hstore.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, jayknowsunix@gmail.com
Subject: Re: Proper syntax to update an hstore key-value pair
In-Reply-To: <22859.1470234864@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox