JDBC plugin: Doesn't work when the table name contains a hyphen

[prodrg]

Hi guys,

I am using the JDBC plugin and I need to extract info from a Postgresql database. The issue I'm having is that the table I want to grab info has a hyphen on the name, and this is causing a conflict.

This is my pipeline:

input {
  jdbc {
    jdbc_connection_string => "jdbc:postgresql://servermani.com:5432/hg-sertak"
    jdbc_user => "username"
    jdbc_password => "${postgres_pass}"
    jdbc_driver_library => "/usr/share/logstash/sql/postgresql-42.2.5.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    #parameters => { "table_name" => "table-name" }
    statement => "SELECT * from table-name"
  }
}
output {
  stdout { codec => json_lines }
}

And these are the error lines I'm getting:

[2018-12-11T03:21:45,130][INFO ][logstash.runner          ] Starting Logstash {"logstash.version"=>"6.5.2"}
[2018-12-11T03:21:52,363][INFO ][logstash.pipeline        ] Starting pipeline {:pipeline_id=>"postgresql", "pipeline.workers"=>1, "pipeline.batch.size"=>125, "pipeline.batch.delay"=>50}
[2018-12-11T03:21:53,787][INFO ][logstash.pipeline        ] Pipeline started successfully {:pipeline_id=>"postgresql", :thread=>"#<Thread:0x9a502c4 run>"}
[2018-12-11T03:22:00,792][ERROR][logstash.inputs.jdbc     ] Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near "-"
  Position: 17: SELECT * from table-name
[2018-12-11T03:22:00,930][WARN ][logstash.inputs.jdbc     ] Exception when executing JDBC query {:exception=>#<Sequel::DatabaseError: Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near "-"
   Position: 17>}
[2018-12-11T03:22:01,919][INFO ][logstash.pipeline        ] Pipeline has terminated {:pipeline_id=>"postgresql", :thread=>"#<Thread:0x9a502c4 run>"}

How can I escape the hyphen?

Thank you

[guyboertje]

https://www.google.co.uk/search?q=postgres+hyphen+table+name+sql&oq=postgres+hyphen+table+name+sql&aqs=chrome..69i57.14943j0j8&sourceid=chrome&ie=UTF-8

[prodrg]

Mmm, I have already tried putting "", `` or even single quotes, and the following errors appeared:

[2018-12-11T03:22:00,792][ERROR][logstash.inputs.jdbc     ] Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near "`"
  Position: 17: SELECT * from `table-name`
[2018-12-11T03:22:00,792][ERROR][logstash.inputs.jdbc     ] Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near "\"
  Position: 17: SELECT * from \"table-name\"
[2018-12-11T03:22:00,792][ERROR][logstash.inputs.jdbc     ] Java::OrgPostgresqlUtil::PSQLException: ERROR: syntax error at or near "'table-name'"
  Position: 17: SELECT * from 'table-name'

[guyboertje]

Have you tried single quotes in the plugin setting...
statement => 'SELECT * from "table-name"'

[prodrg]

Yes, this works, but unfortunately, by a matter of requirements for the Logstash pipeline, I'm forced to use double quotes for the value part instead of the single ones. I forgot to indicate that, my apologies.

I ended using the statement_path option, as it is a cleaner way to execute the query, and helpful for bigger queries.

Thank you.

[guyboertje]

I was going to suggest the statement_path setting next.