Postgres SQL in section and setArray ()

Question

I am using Java 1.7 and JDBC 4 and Postgres. I am trying to use PreparedStatement with an array to populate an SQL statement. But in the SQL generation, there seems to be "{" and "}". Here is the code:

PreparedStatement ptmt = connection.prepareStatement("select * from foo where id in (?)"); String[] values = new String[3]; values[0] = "a"; values[1] = "b"; values[2] = "c"; ptmt.setArray(1, connection.createArrayOf("text", values)); 

The resulting SQL is as follows:

 select * from foo where id in ('{"a","b","c"}') 

What can't work. Here's how it should look:

 select * from foo where id in ("a","b","c") 

or

 select * from foo where id in ('a','b','c') 

What am I missing here?

Answer 1

When your database field is of type array , you can use PreparedStatement.setArray() to send the array to the query. But in your case it is not an array, and the variable has no arguments, and you cannot do this. i.e.

 PreparedStatement ptmt = connection.prepareStatement("select * from foo where id in (?)"); 

can take only one parameter. If you want to pass 3 parameters, you need to do

 PreparedStatement ptmt = connection.prepareStatement("select * from foo where id in (?, ?, ?)"); 

And do ptmt.setString(n, "String") three times.

If your argument is not constant, then build the query dynamically, although you will lose efficiency.

Answer 2

PostgreSQL has a couple of array features that can handle this situation.

The first is the unsest function, available since 8.4. A bit awkward but effective.

 select * from foo where id in (SELECT * FROM unnest(?)); 

Next is the intersection operator of the array.

 select * from foo where ARRAY[id] && ?; 

Converts a column value to an array with one element, and then checks for intersection with the array that you configured as a parameter.

As far as I can tell, they are functionally equivalent, but I have not tested what could be more efficient.

Answer 3

I would call it PgJDBC problem. In fact, an array constructor or an array literal with an explicit lite should be written, for example:

 select * from foo where id in (ARRAY['a','b','c']) 

or

 select * from foo where id in ('{"a","b","c"}'::text[]) 

As a workaround, you can write:

 "select * from foo where id in ( (?)::text[] )" 

.. although I have not tested it.

Please consider writing this as a unit test for PgJDBC and submitting it to the PgJDBC project via github as an error report. See how existing unit tests work, and just add another one. All this is pretty simple JUnit.