# JDBC postgres multiple statement syntax

**URL:** <https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193>\
**Category:** Logstash\
**Created:** [April 3, 2019, 12:32pm UTC](https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193 "2019-04-03T12:32:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![henriot-gricad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/henriot-gricad/32/43394_2.png) [@henriot-gricad](https://discuss.elastic.co/u/henriot-gricad)\
**Post date:** [April 3, 2019, 12:32pm UTC](https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193/1 "2019-04-03T12:32:16Z")

</div>

Hi all,

**How do you specify multiple statements for JDBC postgres queries in logstash?**

As per the doc it's possible : [\_configuring\_multiple\_sql\_statements](https://www.elastic.co/guide/en/logstash/7.x/plugins-inputs-jdbc.html#_configuring_multiple_sql_statements) but there is no detail as to how to achieve this.

I have one DB with one user with which I'd like to run multiple statements to input into a single elasticsearch index. Running a single postgresql select query does not work, it's impractical. As it seems possible to do this elegantly with elastic I'd like to do it that way. I have not found any further info through search engines though.

I presume I would have to have a configuration file starting with something like this:

```
input {
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(id) as coll_count from coll"
    # schedule
    schedule => "*/5 * * * *"
    }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(id) as obj_count from obj"
    # schedule
    schedule => "*/5 * * * *"
    }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://pg.myhost.fr:5432/mydb"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # The request
    statement => "select count(*) from (select distinct name from coll) as temp"
    # schedule
    schedule => "*/5 * * * *"
    }
  }

```

but I see no way to differentiate them or identify them in the filter and output sections.

Is there a manpage for this? Thanks.

Cheers,

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [April 3, 2019, 2:59pm UTC](https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193/2 "2019-04-03T14:59:10Z")

</div>

You are right that you need a separate input for each statement. You could use one of the common options like [tags](https://www.elastic.co/guide/en/logstash/7.x/plugins-inputs-jdbc.html#plugins-inputs-jdbc-tags) to distinguish them.

---

<div class="post-metadata">

**Author:** ![henriot-gricad](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/henriot-gricad/32/43394_2.png) [@henriot-gricad](https://discuss.elastic.co/u/henriot-gricad)\
**Post date:** [April 4, 2019, 7:55am UTC](https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193/3 "2019-04-04T07:55:16Z")

</div>

Hi,

Thanks for your reply Badger.

As a side note, even without using tags, the filter section is clever enough to function just using the unambiguous postgresql result column name as a tag to define a filter.

My functional setup looks like this if it can be of any help to anyone else:

```
# Logstash pipeline
input {
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(id) as users_count from user"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(id) as objects_count from data"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select count(*) as owner_count from (select distinct owner_name from coll) as temp"
    # Schedule
    schedule => "*/10 * * * *"
  }
  jdbc {
    # DB connexion
    jdbc_connection_string => "jdbc:postgresql://myhost.fr:5432/icat"
    jdbc_user => "db_ro"
    jdbc_password => "secret"
    # JDBC driver
    jdbc_driver_library => "/nix/store/4m16yjh855l52vc6f1yibgxhmh7mcdmd-postgresql-jdbc-42.2.2/share/java/postgresql-jdbc.jar"
    jdbc_driver_class => "org.postgresql.Driver"
    # Request
    statement => "select sum(size) as used_space from data"
    # Schedule
    schedule => "*/10 * * * *"
  }
}

filter {
  mutate {
    convert => {
      "used_space" => "integer"
    }
  }
}

output {
  elasticsearch {
    index => "cloud_storage"
  }
}

```

Cheers,

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 2, 2019, 7:55am UTC](https://discuss.elastic.co/t/jdbc-postgres-multiple-statement-syntax/175193/4 "2019-05-02T07:55:18Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.