java - 来自 Java 的 PostgreSQL 查询中的可选日期参数不起作用

Question

我们正在将一些服务从 Oracle 迁移到 PostgreSQL，并且我们拥有基于 JDBC 的自定义数据库访问层。几乎我们所有的用例都在工作，但我们使用可选参数(where field = :parameter or :parameter is null)的查询不工作。

Oracle 中的原始查询是:

select field
from table
where (reference = ?)
  and (date >= ? or ? is null) 

而且它起作用了，我们在 MySQL 中进行了测试，它也起作用了。但是，使用 PostgreSQL 我们会收到以下错误:org.postgresql.util.PSQLException:错误:无法确定参数 $3 的数据类型

我们直接使用 JDBC PostgreSQL 库进行测试:

private static void testQuery() {
   try {
      String ref = "dummy";
      String sql = "select field From table where (reference = ?)";
      sql += " and (date >= ? or ? is null) ";
      PreparedStatement ps = getConnection().prepareStatement(sql);
      ps.setString(1, ref);
      ps.setNull(2, Types.NULL);
      ps.setNull(3, Types.NULL);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
         System.out.print("Column 1 returned: ");
         System.out.println(rs.getString(1));
      }
      rs.close();
      ps.close();
   } catch (Exception ex) {
      ex.printStackTrace();
   }
}

我们确实使用 coalesce 函数进行了测试并且它起作用了，但我们的想法是让纯 SQL 查询可以在任何数据库中起作用。

我们使用的是 PostgreSQL 12.4，依赖是:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.16</version>
</dependency>

我们可以让它正常工作

ps.setNull(2, Types.NULL);
ps.setNull(3, Types.VARCHAR);

在将参数与 null 进行比较时，似乎必须指定 VARCHAR 类型。这对我们没有太大帮助，因为从查询的角度来看它是相同的参数，所以我们不能根据条件 is null 是否在我们的数据库访问层中使用来放置两种类型。

直到现在，每当参数为空时，我们都使用 VARCHAR 作为 Oracle 和 MySQL 访问的默认值，但是 PostgreSQL 为第二个参数(日期)给出以下错误:

org.postgresql.util.PSQLException:错误:运算符不存在:没有时区的时间戳 >= 字符变化

有什么想法吗？

编辑:如果我没记错也没有看错this thread ，除了在查询中指定可空参数类型的选项(在我看来很丑陋)之外，没有真正的解决方案:select * from my_table where ?::timestamp。似乎even JPA has the same problem并且必须进行类型转换。

Answer 1

作为Andreas在他的第二条评论中指出，问题在于 PostgreSQL 的 JDBC 实现比其他实现更“精致”，并且想明确知道参数是哪种类型，即使传递的值是 null。这与其他实现(如 Oracle 或 MySQL)不同，当值为 null 时，它们都乐于将 Varchar 作为默认类型。

除了将所有查询更改为使用 COALESCE 之外，我们还有其他选择。

Here's an old thread如果有人感兴趣，请解释问题，并且 a more recent discussion .