Postgres SQL in clause and setArray()

Question

I am using Java 1.7 and JDBC 4 and Postgres. I am trying to use a PreparedStatement with an array to fill a SQL in clause. But, the SQL generated seems to have "{" and "}" in it. 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 looks like this:

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

Which, can't work. This is 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?

adkisson · Accepted Answer · 2018-09-21 06:10:35Z

12

Use = ANY subquery expression.

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

And if you need to enforce types in the query you can do something like this.

select * from foo where id = ANY(?::text[])

The PostgreSQL documentation has more details. This snippet is worth noting:

SOME is a synonym for ANY. IN is equivalent to = ANY.

answered Sep 21, 2018 at 6:10

adkisson

4654 silver badges6 bronze badges

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

1 Comment

Raj Rajeshwar Singh Rathore Over a year ago

If anyone wants to use the NOT IN using this it can be used as NOT ( id = ANY(?::text[]))

Pradeep Pati · Accepted Answer · 2013-05-21 03:23:11Z

5

When your database field is of type array, then you can use the PreparedStatement.setArray() to send an array to the query. But, in your case, it's not really an array, rather is a variable no of arguments, and you can't do that. i.e.

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

can take only one parameter. If you want 3 parameters to be passed, you have to do

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

And do ptmt.setString(n, "String") thrice.

If your no of arguments aren't constant, then construct the query dynamically, although, you loose the efficiency.

answered May 21, 2013 at 3:23

Pradeep Pati

5,9293 gold badges31 silver badges43 bronze badges

Comments

Wade Kaple · Accepted Answer · 2015-09-23 19:40:14Z

5

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

First is the unnest function, available since 8.4. A little cludgy, but effective.

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

Next is the array intersection operator.

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

Converts your column value into an array with a single element and then checks for intersection with the array that you set up as a parameter.

As best as I can tell, these are functionally equivalent, but I haven't checked which might be more performant.

answered Sep 23, 2015 at 19:40

Wade Kaple

3213 silver badges4 bronze badges

1 Comment

Laurence Gonsalves Over a year ago

A possible downside to where id in (SELECT * FROM unnest(?)) is that is causes getParameterMetaData to fail with an exception: "ERROR: could not determine polymorphic type because input has type "unknown"". The other approach you suggested, where ARRAY[id] && ?, doesn't have this problem.

Craig Ringer · Accepted Answer · 2013-05-21 03:31:19Z

2

I'd consider this a PgJDBC issue. It should really be writing an array constructor or an array literal with an explicit cast, eg:

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 should be able to write:

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

.. though I haven't tested to verify that.

Please consider writing this up as a unit test for PgJDBC and submit it to the PgJDBC project via github as a bug report. Look at how the existing unit tests work and just add another. It's all pretty simple JUnit.

answered May 21, 2013 at 3:31

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

3 Comments

Doo Dah Over a year ago

I was really hoping I could make this work. But, when I try: select * from teamdata where teamdata.teamid in ('{"t11","t54"}'::text[] ), I get "No operator matches the given name and argument type(s). You might need to add explicit type casts." In my case, the id column is a varchar. Sorry about changing the query on you, but, that is the actual query I am using.

Craig Ringer Over a year ago

@DooDah Make sure you can run it by hand with psql first, work out the correct query and types. Then adapt to PgJDBC. ::text[] is just a cast; you can similarly cast to varchar[]. Though in practice Pg will treat them as the same data type so I'm not sure that's the problem.

Doo Dah Over a year ago

Gotcha. Will give that a try. Still learning some of the Postgres subltlies. Thanks for the tip. I will also try to submit this as a unit test for the PgJDBC driver.

Dharman · Accepted Answer · 2024-10-11 14:49:01Z

0

WHERE (COALESCE(:conditions) IS NULL OR id IN (:conditions))

edited Oct 11, 2024 at 14:49

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Oct 11, 2024 at 14:31

Grizzly Russo

1

Collectives™ on Stack Overflow

Postgres SQL in clause and setArray()

5 Answers 5

1 Comment

Comments

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related