Обсуждение: Proper syntax to update an hstore key-value pair

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

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

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@gmail.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@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

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

John Scalia <jayknowsunix@gmail.com> writes:
> You are correct, though, Tom, that the hstore extension is 1.1. Is there
> some other, not =>,  construct I should be using?

The hstore(text,text) function replaces that.

> Curiously, I just checked
> my personal sandbox database (9.5) and the hstore there is 1.3, but like I
> said earlier the update succeeds on that one.

That's pretty hard to believe, because 9.5 disallows => as an operator
name altogether.

I still suspect that you have got a search path problem.  You might
try, eg, "\do ||" in psql and see whether hstore || is listed or not.
Also check which schema \dx says the hstore extension is in in each
database.

                        regards, tom lane

John Scalia <jayknowsunix@gmail.com> writes:
> Your instruction to run "\do ||" did return a list including left arg type
> as text and right arg type as text. The list does report the schema as
> pg_catalog as opposed to the schema I'm actually working in at the moment,

Yeah, that's the built-in "text || text" operator.  The question is where
is the "hstore || hstore" operator added by the hstore extension.  If
you're not seeing that in \do output, then it's not in your search path.

Actually, it might be better to try "\do *.||", which should show all
operators named || regardless of which schema they are in and whether
that schema is in your path.

                        regards, tom lane