Scala 3: PgDate2Support fails to cast OffsetDateTime as `timestamp with time zone`

[robinraju]

I started seeing this issue after upgrading my app to Scala 3 with the latest pre-release 0.22.0-M5 But, the same version of slick-pg works as expected with scala 2.13.12

Scala 2.13.12 ✅

Slick - 3.5.0-M5 Slick-pg - 0.22.0-M5 Postgres - 14

Logs

[info] [2023-12-30 19:49:49,684] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.statementAndParameter] - Executing prepared update: HikariProxyPreparedStatement@1733586564 wrapping insert into "products" ("name","description","price_amount","price_currency","category","manufacturer","image_url","created_at","last_updated_at")  values ('Strawberry','Not just any strawberry',2.5,'GBP',1000,1000,'strawberry.png','2023-12-30 19:49:49.65481+00'::timestamp with time zone,'2023-12-30 19:49:49.654841+00'::timestamp with time zone)
[info] RETURNING "id"
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - /------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------\
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | 1          | 2                       | 3          | 4      | 5    | 6    | 7              | 8                         | 9                         |
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | String     | String                  | BigDecimal | String | Int  | Int  | String         | TIMESTAMP_WITH_TIMEZONE   | TIMESTAMP_WITH_TIMEZONE   |
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - |------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------|
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | Strawberry | Not just any strawberry | 2.5        | GBP    | 1000 | 1000 | strawberry.png | 2023-12-30T19:49:49.65... | 2023-12-30T19:49:49.65... |
[info] [2023-12-30 19:49:49,700] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - \------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------/

Scala 3.3.1 ⚠️

Slick - 3.5.0-M5 Slick-pg - 0.22.0-M5 Postgres - 14

Logs

[info] [2023-12-30 20:33:45,010] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.statementAndParameter] - Executing prepared update: HikariProxyPreparedStatement@176542380 wrapping insert into "products" ("name","description","price_amount","price_currency","category","manufacturer","image_url","created_at","last_updated_at")  values ('Strawberry','Not just any strawberry',2.5,'GBP',1000,1000,'strawberry.png','2023-12-30T20:33:44.982519Z','2023-12-30T20:33:44.982548Z')
[info] RETURNING "id"
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - /------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------\
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | 1          | 2                       | 3          | 4      | 5    | 6    | 7              | 8                         | 9                         |
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | String     | String                  | BigDecimal | String | Int  | Int  | String         | String                    | String                    |
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - |------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------|
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - | Strawberry | Not just any strawberry | 2.5        | GBP    | 1000 | 1000 | strawberry.png | 2023-12-30T20:33:44.98... | 2023-12-30T20:33:44.98... |
[info] [2023-12-30 20:33:45,026] [DEBUG] [slick.db-4] [slick.jdbc.JdbcBackend.parameter] - \------------+-------------------------+------------+--------+------+------+----------------+---------------------------+---------------------------/
[info] org.postgresql.util.PSQLException: ERROR: column "created_at" is of type timestamp with time zone but expression is of type character varying
[info]   Hint: You will need to rewrite or cast the expression.
[info]   Position: 178

Relevant code

trait ExtendedPostgresProfile extends ExPostgresProfile with PgDate2Support with PgArraySupport {

  // Add back `capabilities.insertOrUpdate` to enable native `upsert` support; for postgres 9.5+
  override protected def computeCapabilities: Set[Capability] =
    super.computeCapabilities + slick.jdbc.JdbcCapabilities.insertOrUpdate

  override val api: ExtPostgresAPI = PostgresAPI

  private object PostgresAPI extends ExtPostgresAPI with ArrayImplicits with Date2DateTimeImplicitsDuration {
    implicit val strListTypeMapper: DriverJdbcType[List[String]] = new SimpleArrayJdbcType[String]("text").to(_.toList)
  }

}

object ExtendedPostgresProfile extends ExtendedPostgresProfile

Slick Table definition

class ProductsTable(tag: Tag) extends Table[ProductTableRow](tag, "products") {

  def id: Rep[Option[ProductId]]                = column[Option[ProductId]]("id", O.AutoInc, O.PrimaryKey)
  def name: Rep[String]                         = column[String]("name")
  def description: Rep[String]                  = column[String]("description")
  def priceAmount: Rep[BigDecimal]              = column[BigDecimal]("price_amount")
  def priceCurrency: Rep[String]                = column[String]("price_currency")
  def category: Rep[Option[CategoryId]]         = column[Option[CategoryId]]("category")
  def manufacturer: Rep[Option[ManufacturerId]] = column[Option[ManufacturerId]]("manufacturer")
  def imageUrl: Rep[String]                     = column[String]("image_url")
  def createdAt: Rep[OffsetDateTime]            = column[OffsetDateTime]("created_at")
  def lastUpdatedAt: Rep[OffsetDateTime]        = column[OffsetDateTime]("last_updated_at")
  def productPrice: MappedProjection[ProductPrice] =
    (priceAmount, priceCurrency) <> ((ProductPrice.apply _).tupled, ProductPrice.unapply)

  override def * : ProvenShape[ProductTableRow] =
    (
      id,
      name,
      description,
      productPrice,
      category,
      manufacturer,
      imageUrl,
      createdAt,
      lastUpdatedAt
    ) <> ((ProductTableRow.apply _).tupled, ProductTableRow.unapply)
}

Insert a row

def create(product: ProductItem): DBIO[Option[ProductId]] =
    productsQuery.returning(productsQuery.map(_.id)) += product