proper way to set PreparedStatement parameter to null under postgres

Question

im using JDBC4 (over JDK6) to connect to postgresql 9.0 using their latest JDBC4 driver.

conn = dpaDs.getConnection();
PreparedStatement stmt = conn.prepareStatement("delete from fss where f_id=? and f_lastmodified=?");
UUID target = UUID.fromString("8c5c5ac2-3a2a-49f3-ae48-29226d6ea3e7");
stmt.setObject(1, target, 1111);
//non of these work
//stmt.setNull(2,93);
//stmt.setObject(2,null, 93);
//stmt.setObject(2,null, java.sql.Types.NULL);
//stmt.setNull(2, java.sql.Types.NULL);
int rowCount = stmt.executeUpdate();
int g = 8;

what is the correct way to set the 2nd parameter to null ? i know that i can use "where ... f_lastmodified is null", but is there no way to get around this?

postgresql jdbc is the only database that does not yet support your valid approach. See "Add support for setObject(<arg>, null)" for a discussion of the industry standard. Try Oracle, SQL Server, MySQL, HSQL, Sybase. See bug and the discussion "Cannot pass null in Parameter in Query for ISNULL" at mailing list [email protected] for another very compelling use case where the null check is even guarded by an ISNULL condition. — user250343
– user250343, Commented Jun 3, 2012 at 17:54

user330315 · Accepted Answer · 2011-05-26 05:54:52Z

5

That won't work.

NULL comparison cannot be done using the = operator. If you want to test for NULL, you have to use and f_lastmodified IS NULL.

You can't use a parameter in a prepared statement for an IS NULL condition.

answered May 26, 2011 at 5:54

user330315

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

2 Comments

Andrew Lazarus Over a year ago

I think that ? IS NULL is fine in a prepared statement and looks like it solves the OP's problem. There is also the IS NOT DISTINCT FROM operator although I was unpleasantly surprised to see that PG doesn't seem able to use it with an index.

user330315 Over a year ago

? IS NULL won't work, because that would require passing a column name as the parameter and that is not supported with PreparedStatements.

Brett Ryan · Accepted Answer · 2020-12-13 22:58:36Z

1

In postgres you can use is not distinct from which is effectively a null safe equality check.

If you alter the query to use is not distinct from it will now allow you to set null as parameter 2.

delete
  from fss
 where f_id = ?
   and f_lastmodified is not distinct from ?

answered Dec 13, 2020 at 22:58

Brett Ryan

28.4k35 gold badges135 silver badges172 bronze badges

Comments

Eric Stokes · Accepted Answer · 2016-02-17 16:29:21Z

0

If you use named parameters (e.g. :p instead of ?) instead of positional ones then you can do the following,

((:p is null and f_lastmodified is null) or f_lastmodified = :p)

Which is a bit awkward, but it gets the job done. I have not tried this with jdbc, only Npgsql on .net, but I think it should be the same.

answered Feb 17, 2016 at 16:29

Eric Stokes

211 bronze badge

Collectives™ on Stack Overflow

proper way to set PreparedStatement parameter to null under postgres

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related