Issue with Case Sensitivity and Double Quotes in PostgreSQL Query through Logstash


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.

See this thread. I realize you may not have control of it, but mixed case names in Postgres are a terrible idea. Your query contains both single and double quotes, so depending on whether you surround the entire query with single or double quotes you will need to escape the other type of quotes in a way that both logstash and the JDBC code un-escapes them to the right level. I have no idea how to do that.

If SQL allows double quotes around the 2024-03-05 it might be easier.