概要

PostgreSQLでアプリケーションからパラメータを指定する際、値がnullである場合にどのように対応するかというのが今回のテーマです。パラメータにnullが指定された際Postgres Sql could not determine data type of parameter by Hibernateのstackoverflowの記事で紹介されている通り、is nullの条件を入れてもERROR: could not determine data type of parameterというエラーが発生します。
回避するにはエラー内容に沿って、null値が入りうるパラメータにcastで型指定すれば回避はできます。ただ、そもそもnull判定に、coalesceを使ったらどうなるかを確認してみました。

前提

使用したPostgreSQLのバージョンは17.2になります。

確認結果

SpringBootのJPQLで、以下のようにnull値が入るうるパラメータ（:startDateTimeと:endDateTime）にcoalesceを使ってみたところ、null値でもエラーを回避できました。理由についてAIで確認したところ、coalesceの第二引数で型推論が効くからではとのこと。

@Query("""
    select t from TaskExecuteEntity t 
    where t.taskDefinitionId = :taskDefinitionId 
    and t.executeUserId = :executeUserId
    and t.executeDateTime >= coalesce(:startDateTime, t.executeDateTime)
    and t.executeDateTime <= coalesce(:endDateTime, t.executeDateTime)
    order by t.executeDateTime desc
    limit 300
    """)
List<TaskExecuteEntity> findTaskExecuteList(
    @Param("taskDefinitionId") String taskDefinitionId,
    @Param("executeUserId") String executeUserId,
    @Param("startDateTime") OffsetDateTime startDateTime,
    @Param("endDateTime") OffsetDateTime endDateTime);