The solution is partially working for me, the basic goes quite well.
SELECT * FROM audit_events - is returning the expected result, but when I try to do any filter, E.g.
SELECT * FROM audit_events WHERE ACTOR = “RCF” - it returns
an error saying that the column does not exist. I tried a few different options, but if I reference any column it returns the same error.
I also tried to do something similar with other tables, all work great, but it seems that the audit_events table has something wrong in it and I can’t reference any column.
This should have been a new topic. Don’t be a necromancer!
That said, I suspect the table has mixed or upper case names. PostgreSQL is case sensitive, and any unquoted identifiers are treated as lower case. (BTW, double quotes for identifiers, single quotes for values.)
I believe auditing is the last Ignition table that’s generated uppercase, unfortunately. Which bears out Phil’s suggestion.
Hi. thank you for the tip about the new topic!
Unfortunately, I tried the single quote, double quote, lower case, upper case, lighting candles, and some garlic too
I tried using the Query Builder as well, it all works fantastic with all other tables, but I can’t make any reference to columns in the audit_events table.
This is a very simple query, created by the Query Builder, it is returning the same error.
The simple SELECT * FROM audit_events works
The application is complex and there is some good level of SQL queries running alarm filters from multiple tables. This one was supposed to be the simplest query I have in the application.
Is there something wrong with the audit table?
I am using version 8.0.17 by the way.
Thank you very much!
Interesting that the error message is always coming lower case regardless of how I write the query
I was using the power table for Audit, now I have the need to bring the ACTOR_HOST, so I started writing something from scratch and got stuck in the first attempt to reference any column.
So, my query now is these 3 lines
Anyway, you guys figured it out!!!
The problem was the actor being read as lower case even though I was writing upper case. Adding double quotes to the column name did the trick.
This query here works: