pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWeird query error from one of my users
3+ messages / 3 participants
[nested] [flat]
* Weird query error from one of my users
@ 2015-11-23 19:57 John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: John Scalia @ 2015-11-23 19:57 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I have a user submitting a query involving a column that is an hstore type.
The query seems to run just fine from her pgAdmin III console, but I cannot
get it to run from a psql session. I've been studying the hstore page on
the postgresql site but nothing there is quite like this query on that
page. Here's the query:
SELECT
a.id as sva_id,
m.account_number_hash as account_num_hash,
a.status as status,
a.balance_currency as currency,
a.balance as balance,
'datamart_fdw_m2'::text as platform
FROM account a
INNER JOIN switch_mcontainer m ON (m.attributes -> 'EXTERNAL_ID')::text
= a.external_id::text;
The psql error says: ERROR: operator does not exist: public.hstore ->
unknown
I don't quite understand the error as the postgresql hstore page shows the
"->" as a valid operator. Anyone have any ideas? And of course, the better
question is why does it run correctly in pgAdmin? Please let me know if you
need the table descriptions for either the account or mcontainer or both
tables.
Thanks in advance,
Jay
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Weird query error from one of my users
@ 2015-11-23 20:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2015-11-23 20:18 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> The psql error says: ERROR: operator does not exist: public.hstore ->
> unknown
> I don't quite understand the error as the postgresql hstore page shows the
> "->" as a valid operator. Anyone have any ideas? And of course, the better
> question is why does it run correctly in pgAdmin?
Different search_path setting?
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] 3+ messages in thread
* Re: Weird query error from one of my users
@ 2015-11-23 20:20 Scott Mead <scottm@openscg.com>
parent: John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Mead @ 2015-11-23 20:20 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Mon, Nov 23, 2015 at 2:57 PM, John Scalia <jayknowsunix@gmail.com> wrote:
> Hi all,
>
> I have a user submitting a query involving a column that is an hstore
> type. The query seems to run just fine from her pgAdmin III console, but I
> cannot get it to run from a psql session.
>
Are you sure that the two of you are connected to the same instance ?
> I've been studying the hstore page on the postgresql site but nothing
> there is quite like this query on that page. Here's the query:
>
> SELECT
> a.id as sva_id,
> m.account_number_hash as account_num_hash,
> a.status as status,
> a.balance_currency as currency,
> a.balance as balance,
> 'datamart_fdw_m2'::text as platform
> FROM account a
> INNER JOIN switch_mcontainer m ON (m.attributes ->
> 'EXTERNAL_ID')::text = a.external_id::text;
>
> The psql error says: ERROR: operator does not exist: public.hstore ->
> unknown
>
> I don't quite understand the error as the postgresql hstore page shows the
> "->" as a valid operator. Anyone have any ideas? And of course, the better
> question is why does it run correctly in pgAdmin? Please let me know if you
> need the table descriptions for either the account or mcontainer or both
> tables.
>
> Thanks in advance,
> Jay
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-11-23 20:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-23 19:57 Weird query error from one of my users John Scalia <jayknowsunix@gmail.com>
2015-11-23 20:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-23 20:20 ` Scott Mead <scottm@openscg.com>
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