How to bulk delete rows in Postgresql with a where clause that might contain null values using Spring JDBC

In Postgres, using Spring JDBC's NamedParameterJdbcTemplate, if I want to delete a row using a where clause that may include a null value, I have to do something like this...

val sql = if (someOtherValue == null) {
    "delete from some_table where some_column = :someValue and some_other_column is null"
} else {
    "delete from some_table where some_column = :someValue and some_other_column = :someOtherValue"
}

If I want to do a bulk delete given a collection of inputs where some of the someOtherValues may be null, is there any way to do that in a single SQL statement? Or do I have to split my collection into records that contain a null someOtherValue and those that don't?

If you swap out = for is not distinct from, you can compare to null just fine:

delete from some_table 
where some_column = :someValue 
  and some_other_column is not distinct from :someOtherValue

This lets you pass a null in :someOtherValue and that's going to match a null in some_other_column.

Regular equality some_other_column = null comparison leads to null result even if the left operand is a null so your where clause discards/filters those out. Meanwhile, some_other_column is not distinct from null yields true if the left operand is null which I understand is what you're trying to do: _{demo at db<>fiddle}