pass array to postgres in JDBC

Question

I want to do the following query in Postgres using JDBC:

with things as (values(1),(2)) select * from things;

So my Java code looks like this:

String sql = "with things as (?) select * from things";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setArray(1, conn.createArrayOf("INTEGER", new Integer[]{1, 2});

But this is throwing the following error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

I don't think you can do this using prepared statements in Java. But, the bigger question is why do you want to do this? SQL is a language mainly geared towards extracting data from tables which already exist, not really for populating them and then selecting from them. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 10, 2019 at 11:07

Roman-Stop RU aggression in UA · Accepted Answer · 2019-06-10 13:30:01Z

1

You can do what you need using unnest like this:

Integer[] id = {1, 2};
Array array = connection.createArrayOf("int4", id);

try (PreparedStatement stmt = connection.prepareStatement(
         "with things as (select unnest((?)::integer[])) select * from things")) {
    stmt.setArray(1, array);
    ResultSet rs = stmt.executeQuery();
    // use the result set
}

answered Jun 10, 2019 at 13:30

Roman-Stop RU aggression in UA

16k3 gold badges52 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pass array to postgres in JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related