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

Поиск
Список
Период
Сортировка

Proper syntax to update an hstore key-value pair

От
John Scalia
Дата:
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

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

От
Richard Albright
Дата:
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

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

От
John Scalia
Дата:
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


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

От
Richard Albright
Дата:
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@gmail.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@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



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

От
Tom Lane
Дата:
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


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

От
John Scalia
Дата:
As I explicitly set my search path prior to any of this, I don't think that's an issue. Of course, I could have simply have set it wrongly. In any case, adding ::hstore after the parameters threw a "type hstore does not exist" and enclosing the whole CONNECTOR_TIME_OUT... inside parenthesis caused the same does not exist error.

You are correct, though, Tom, that the hstore extension is 1.1. Is there some other, not =>,  construct I should be using? 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.

On Wed, Aug 3, 2016 at 9:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

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

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


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

От
John Scalia
Дата:
As a test earlier today, I was following these instructions: http://stormatics.com/howto-handle-key-value-data-in-postgresql-the-hstore-contrib/
and the section on that page for updating value of a key worked perfectly on the sandbox server, but not my problem child server. When I first tried to follow all the procedures in this web page, I did first have to create the extension in the sandbox database I was using. That's the one that reported 1.3 as in:

 \dx
                           List of installed extensions
  Name   | Version |   Schema   |                   Description
---------+---------+------------+--------------------------------------------------
 hstore  | 1.3     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Very odd, when I tried using the hstore(text, text)  on my problem server, I only got back a column does not exist error. I probably used it incorrectly, though.

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, and I'm not sure if this is correct or not.

On Wed, Aug 3, 2016 at 11:47 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

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

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


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

От
John Scalia
Дата:
Well, I discovered that I had different versions of hstore across several databases. Some were 1.1 and others were 1.3. On the 1.3 hstore versions, the \do *.|| produces a public hstore - hstore operator, while the 1.1 shows the same as public.hstore. Maybe I just wasn't casting this far enough. On both versions that operator shows as being in the public schema. My search path does include that.

On Wed, Aug 3, 2016 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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

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

От
Tom Lane
Дата:
John Scalia <jayknowsunix@gmail.com> writes:
> Well, I discovered that I had different versions of hstore across several
> databases. Some were 1.1 and others were 1.3. On the 1.3 hstore versions,
> the \do *.|| produces a public hstore - hstore operator, while the 1.1
> shows the same as public.hstore. Maybe I just wasn't casting this far
> enough. On both versions that operator shows as being in the public schema.
> My search path does include that.

Hmm.  AFAICS, every extant version of hstore ought to provide

 operator ||(hstore,hstore)

and no other operator named ||.  You might try comparing the results
of "\dx+ hstore" across your installations.

            regards, tom lane


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

От
John Scalia
Дата:
Sorry if I wasn't clear. Every version I found did include that operator, but there were differences in how they were
defined.In the 1.1 version the arguments for "||" were listed as being public.hstore while in 1.3 the args were just
hstore.I'm going to revisit my tests tomorrow morning. I've gotten all my versions now at 1.3, but it's been a very
longday since I started at 6:30 am this morning. 

Thanks for your help, everyone.

Sent from my iPad

> On Aug 3, 2016, at 2:49 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> John Scalia <jayknowsunix@gmail.com> writes:
>> Well, I discovered that I had different versions of hstore across several
>> databases. Some were 1.1 and others were 1.3. On the 1.3 hstore versions,
>> the \do *.|| produces a public hstore - hstore operator, while the 1.1
>> shows the same as public.hstore. Maybe I just wasn't casting this far
>> enough. On both versions that operator shows as being in the public schema.
>> My search path does include that.
>
> Hmm.  AFAICS, every extant version of hstore ought to provide
>
> operator ||(hstore,hstore)
>
> and no other operator named ||.  You might try comparing the results
> of "\dx+ hstore" across your installations.
>
>            regards, tom lane


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

От
Tom Lane
Дата:
John Scalia <jayknowsunix@gmail.com> writes:
> Sorry if I wasn't clear. Every version I found did include that
> operator, but there were differences in how they were defined. In the 1.1
> version the arguments for "||" were listed as being public.hstore while in
> 1.3 the args were just hstore.

I'm beginning to suspect you have a rogue copy of hstore someplace.
Try "\dT *.hstore" to see if you get more than one hit.

            regards, tom lane