Postgres Timestamp null

[o4f6bgpac3]

Version: (e.g. 4.8.4)
Module: (e.g. quill-jdbc)
Database: (e.g. postgres)

Expected behaviour

This is a PostgreSQL bug/limitation but will appear in certain conditions when using quill to generate queries.
Someone raised it with PostgreSQL a long time ago but nothing that I'm aware of has been fixed:
https://www.postgresql.org/message-id/CAG2M1febgZbhBwm0DQK4Y5ekDrTbzTyAa1syKcdXw%2B6Y8caK4g%40mail.gmail.com

Actual behaviour

If there's a 'timestamp' field and the 'where' clause generates to where ? is null, it will fail with error:
could not determine data type of parameter $1

Steps to reproduce the behaviour

docker-compose.yml:

services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "5400:5432"
    volumes:
      - ./src/main/resources/sql/init.sql:/docker-entrypoint-initdb.d/init.sql

init.sql:

CREATE TABLE my_table (
                          id INT PRIMARY KEY,
                          timestamp_field TIMESTAMP
);

INSERT INTO my_table (id, timestamp_field) VALUES
                                               (1, '2024-01-01 00:00:00'),
                                               (2, '2024-01-02 00:00:00');

build.sbt:

ThisBuild / scalaVersion := "3.3.1"

lazy val root = project
  .in(file("."))
  .settings(
    name := "quill-timestamp-bug",
    version := "0.1.0",
    libraryDependencies ++= Seq(
      "io.getquill" %% "quill-jdbc" % "4.8.4",
      "org.postgresql" % "postgresql" % "42.7.3",
      "org.scalatest" %% "scalatest" % "3.2.18" % Test,
      "org.testcontainers" % "postgresql" % "1.20.0" % Test
    )
  )

QuillTimestampBugTest.scala:

package example

import io.getquill.*
import org.scalatest.flatspec.AnyFlatSpec
import org.scalatest.matchers.should.Matchers
import java.sql.Timestamp
import javax.sql.DataSource
import org.postgresql.ds.PGSimpleDataSource
import org.scalatest.Ignore

class QuillTimestampBugTest extends AnyFlatSpec with Matchers:
  private val pgDataSource = new PGSimpleDataSource()
  pgDataSource.setURL("jdbc:postgresql://localhost:5400/testdb")
  pgDataSource.setUser("postgres")
  pgDataSource.setPassword("postgres")

  val ctx = new PostgresJdbcContext(SnakeCase, pgDataSource)
  import ctx._

  "queryWithNullTimestamp" should "generate correct SQL for None timestamp" in {
    val ts: Option[Timestamp] = None
    val q = queryWithNullTimestamp(ts)

    val sql = ctx.translate(q)
    sql should include("WHERE null IS NULL")
    println(s"Generated SQL: $sql")
  }

  it should "run the query without throwing an exception" in {
    val ts: Option[Timestamp] = None
    val q = queryWithNullTimestamp(ts)

    noException should be thrownBy ctx.run(q)
  }

  private def queryWithNullTimestamp(ts: Option[Timestamp]) =
    quote {
      query[MyTable].filter(t => lift(ts).isEmpty)
    }

Workaround

The query needs to change where ? is null to where ?::timestamp is null to get around the PostgreSQL limitation.

@getquill/maintainers