Weird query error from one of my users

From: John Scalia <jayknowsunix(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Weird query error from one of my users
Date: 2015-11-23 19:57:51
Message-ID: CABzCKRB5sXDEBMBArAB0yVUw-NMmKfTarZMtVggJtejWihGvWQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2015-11-23 20:18:55 Re: Weird query error from one of my users
Previous Message Jerry Sievers 2015-11-23 17:37:45 Re: pg_restore new parameter request