pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProper syntax to update an hstore key-value pair
13+ messages / 3 participants
[nested] [flat]
* Proper syntax to update an hstore key-value pair
@ 2016-08-03 13:34 John Scalia <jayknowsunix@gmail.com>
2016-08-03 13:38 ` Re: Proper syntax to update an hstore key-value pair Richard Albright <rla3rd@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: John Scalia @ 2016-08-03 13:34 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 13:38 ` Richard Albright <rla3rd@gmail.com>
2016-08-03 13:56 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Richard Albright @ 2016-08-03 13:38 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 13:38 ` Re: Proper syntax to update an hstore key-value pair Richard Albright <rla3rd@gmail.com>
@ 2016-08-03 13:56 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:33 ` Re: Proper syntax to update an hstore key-value pair Richard Albright <rla3rd@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: John Scalia @ 2016-08-03 13:56 UTC (permalink / raw)
To: Richard Albright <rla3rd@gmail.com>; +Cc: pgsql-admin
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
>>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 13:38 ` Re: Proper syntax to update an hstore key-value pair Richard Albright <rla3rd@gmail.com>
2016-08-03 13:56 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 14:33 ` Richard Albright <rla3rd@gmail.com>
0 siblings, 0 replies; 13+ messages in thread
From: Richard Albright @ 2016-08-03 14:33 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 14:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2016-08-03 14:34 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-08-03 15:19 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: John Scalia @ 2016-08-03 15:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2016-08-03 16:47 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-08-03 17:12 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: John Scalia @ 2016-08-03 17:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 17:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 19:29 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2016-08-03 17:25 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-08-03 19:29 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 19:49 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: John Scalia @ 2016-08-03 19:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 19:29 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 20:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2016-08-03 19:49 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 19:29 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 19:49 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-08-03 20:19 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 20:40 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: John Scalia @ 2016-08-03 20:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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 long day 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Proper syntax to update an hstore key-value pair
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:34 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 19:29 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 19:49 ` Re: Proper syntax to update an hstore key-value pair Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 20:19 ` Re: Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
@ 2016-08-03 20:40 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2016-08-03 20:40 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2016-08-03 20:40 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-03 13:34 Proper syntax to update an hstore key-value pair John Scalia <jayknowsunix@gmail.com>
2016-08-03 13:38 ` Richard Albright <rla3rd@gmail.com>
2016-08-03 13:56 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 14:33 ` Richard Albright <rla3rd@gmail.com>
2016-08-03 14:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 15:19 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 16:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 17:12 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 17:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 19:29 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-03 20:19 ` John Scalia <jayknowsunix@gmail.com>
2016-08-03 20:40 ` Tom Lane <tgl@sss.pgh.pa.us>
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