24

I have to write a SQL query for Postgres database as follows:

DELETE FROM employee WHERE ename IN (?)

I want to pass ename as list or string which will contain multiple employee names, e.g. "abc, bcd, efg". How to set the values?

How to use IN clause with PreparedStatement in Postgres?

lospejos
  • 1,976
  • 3
  • 19
  • 35
Ritesh
  • 245
  • 1
  • 2
  • 5
  • How to use IN clause with PreparedStatement in Postgressql ?? – Ritesh Apr 29 '16 at 03:41
  • 1
    Use array instead: `where ename = any()`. Funny, but PostgreSQL transforms `in` clause into `= any(...)`, internally, can see it in the query plans. – Abelisto Apr 29 '16 at 04:46
  • Possible duplicate of [PreparedStatement IN clause alternatives?](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) – Mark Rotteveel Apr 29 '16 at 06:33

3 Answers3

26

There are several ways to do this. The clean way is to pass an array to the prepared statement. An alternative to Nick's answer is to pass a proper java.sql.Array value:

PreparedStatement pstmt = connection.prepareStatement(
    "DELETE FROM employee WHERE ename = ANY (?)");
String[] idList = new String[] {"abc", "bcd", "efg"};
Array ids = connection.createArray("varchar", idList);
pstmt.setArray(1, ids);

Another option is to use Postgres' string functions to convert the comma separated list to an array. That is probably the easiest way:

PreparedStatement pstmt = connection.prepareStatement(
     "DELETE FROM employee WHERE ename = ANY (string_to_array(?, ','))");
psmt.setString(1, "abc,bcd,efg");

Note that you must not have spaces between the comma and the value! Those spaces will be part of the value that is stored in the array after string_to_array() has done it's job so comparison would e.g. be ename = ' bcd' which would fail if there is no leading space in the column ename(which is highly likely)

  • 8
    Not only are spaces a problem, but if a comma is part of one of the values, you'll have trouble. From what I can tell, there's no way to escape it. The best option I can see is using `the_leafs_just_won_the_stanley_cup` as a delimiter, since that hasn't been true since before SQL was invented, so it wouldn't be one of the values. – Opux Jan 18 '19 at 14:44
  • 2
    Works good, minor change: I had to use `connection.createArrayOf` instead of `connection.createArray` (keep parameters the same). Thanks. – Andres Oct 29 '19 at 20:06
13

Like this:

DELETE FROM employee WHERE ename = ANY(?)

The parameter should be formatted as an array literal, e.g. "{abc,bcd,efg}".

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63
  • 1
    The part about array literal formatting saved my day. I was trying to use in a prepared statement. Thanks – Huzaifa Dec 26 '22 at 11:19
-10

You might want to try the below:

DELETE FROM employee 
 WHERE ename IN ('abc', 'bcd', 'efg')
drneel
  • 2,887
  • 5
  • 30
  • 48
Eric Tan
  • 1
  • 1
  • I am using PreparedStatement, we don't know how many records we need to delete. that will come dynamically. , so how use place holders, or if we can do with only one placeholder, then how to set the values as a List or String.??? – Ritesh Apr 29 '16 at 03:48