Hello,
I'm encountering an issue while trying to fetch data from a PostgreSQL database using Logstash's JDBC input. The problem seems to revolve around case sensitivity and the use of double quotes in column names. Despite following the conventional syntax for PostgreSQL, I keep receiving a syntax error related to my query's structure.
Here's the error message I'm getting:
JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near \"\\\"\n Position: 138", :cause=>"#<Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near \"\\\"\n Position: 138>"}
And this is the query causing the issue:
"select id, name, data::text, results::text, "creatTime" as date_time from <table_name> al where "creatTime" > '2024-03-05' ORDER BY id ASC"
data and results r jsonb at db
As you can see, the query utilizes double quotes to handle case sensitivity for the column name "creatTime". However, the error suggests a syntax issue near these double quotes.
I've double-checked the query for any potential misplacements of quotes or typos but to no avail. The column names and their case sensitivity are correctly represented as they exist in the database. I'm suspecting the issue might be related to how Logstash or the JDBC input plugin interprets the query, especially concerning the handling of double quotes and case sensitivity.
Has anyone encountered a similar issue or can offer insight into what might be going wrong here? Any suggestions on how to correctly format the query or configure Logstash to handle this scenario would be greatly appreciated.
Thank you in advance for your assistance.