I am using MS Access to get data from a postgresql source. It's my first time using postgresql and hstore although I have some other experience in T-SQL.
I need to track Hstore key 'Stage_id' value.
My code is as follows;
SELECT Log.action_tstamp_stm, Opp.opportunity_code,
Log.raw_data, Log.raw_data_old,
Log.changed_fields, Log.changed_fields_old
FROM public_ea_crm_opportunity AS Opp
INNER JOIN logs_logged_actions AS Log
ON Opp.id = Log.transaction_id
WHERE Log.table_name="ea_crm_opportunity" AND Log.action="U"
I now need to filter that code to extract the values, however when I change the SELECT part to e.g. Log.raw_data -> 'Stage_id' AS raw_stage, an error comes up saying
Syntax error (missing operator) in query expression 'Log.raw_data -> 'stage_id''
and the > character is highlighted in the query when I close that message. The same error comes up if I try to add AND Log.raw_data ? 'stage_id' to the filters, to return rows containing that key.
What am I doing wrong? It seems syntactically correct per my various google searches trying to learn this. Is it that Access can't deal with this data type? If so, is there a way I can convert the whole lot to text then use string functions to extract the key values from that?
(yes I know you may be aghast at the suggestion, but the system this is replacing did exactly that)