OffsetDateTime inserted as VARCHAR and String

[wiradikusuma]

I have the following model:

final case class Wallet(
						   ...
						   pointExpiry: Option[OffsetDateTime],
						   spinExpiry: OffsetDateTime,
						   ...
						   id: Int = 0,
					   )

final class WalletTable(tag: Tag) extends Table[Wallet](tag, "wallet") {
	def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
	...
	def pointExpiry = column[Option[OffsetDateTime]]("point_expiry")
	def spinExpiry = column[OffsetDateTime]("spin_expiry")
	...
	def * = (..., pointExpiry, spinExpiry, ..., id).mapTo[Wallet]
}

I tried to INSERT, and here's the log:

15 00:30:19 [DB-1] DEBUG slick.compiler.QueryCompiler - Source:
| Bind
|   from s2: TableExpansion
|     table s3: Table wallet
|     columns: TypeMapping
|       0: ProductNode
...
|         4: Path s3.point_expiry : Option[java.time.OffsetDateTime']
|         5: Path s3.spin_expiry : java.time.OffsetDateTime'
...
|         16: Path s3.id : Int'
|   select: Pure t4
|     value: Path s2.id : Int'
...
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.statement - Preparing insert statement (returning: id): insert into "wallet" (...,"point_expiry","spin_expiry",...)  values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.statement - Executing prepared update: HikariProxyPreparedStatement@2041279209 wrapping insert into "wallet" (...,"point_expiry","spin_expiry",...)  values (...,NULL,'2100-12-31T00:00+07:00',...)
RETURNING "id"
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - /-----+-----+-----+---------+------------------------+-----+-----+---------+---------+-----+--------+---------+---------+---------------------------+---------------------------\
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - | 1   | 2   | 3   | 4       | 5                      | 6   | 7   | 8       | 9       | 10  | 11     | 12      | 13      | 14                        | 15                        |
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - | Int | Int | Int | VARCHAR | String                 | Int | Int | DECIMAL | DECIMAL | Int | String | Boolean | VARCHAR | String                    | String                    |
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - |-----+-----+-----+---------+------------------------+-----+-----+---------+---------+-----+--------+---------+---------+---------------------------+---------------------------|
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - | 1   | 1   | 1   | NULL    | 2100-12-31T00:00+07:00 | 100 | 100 | NULL    | NULL    | 0   | 111    | true    | NULL    | 2019-12-15T00:30:05.37... | 2019-12-15T00:30:05.37... |
15 00:30:19 [DB-1] DEBUG slick.jdbc.JdbcBackend.parameter - \-----+-----+-----+---------+------------------------+-----+-----+---------+---------+-----+--------+---------+---------+---------------------------+---------------------------/

What I don't understand: If I copy-paste the generated INSERT statement from the log and run it manually, the data is successfully inserted.

But from Tomcat (where I'm calling it from) it throws error:

15-Dec-2019 00:30:19.560 SEVERE [http-nio-8080-exec-1] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [jersey-servlet] in context with path [] threw exception [org.glassfish.jersey.server.ContainerException: org.postgresql.util.PSQLException: ERROR: column "point_expiry" is of type date but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 225] with root cause
	org.postgresql.util.PSQLException: ERROR: column "point_expiry" is of type date but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 225
		at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2505)
		at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2241)
		at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310)
		at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:447)
		at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:368)
		at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:158)
		at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:124)
		at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
		at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
		at slick.jdbc.LoggingPreparedStatement.$anonfun$executeUpdate$5(LoggingStatement.scala:170)

What do I miss?

[tminglei]

Need more details. Can you provide full usage codes?

[wiradikusuma]

Hi @tminglei , it's standard:

trait PgProfile extends ExPostgresProfile
	with PgArraySupport
	with PgDate2Support
	with PgHStoreSupport
	with PgLTreeSupport
	with PgRangeSupport
	with PgSearchSupport {
	override val api: API = PgAPI

	object PgAPI extends API
		with ArrayImplicits
		with DateTimeImplicits
		with HStoreImplicits
		with LTreeImplicits
		with RangeImplicits
		with SearchImplicits
		with SearchAssistants {}

}

object PgProfile extends PgProfile

def create(wallet: Wallet): DBIO[Wallet] = {
	this returning this.map(_.id) into ((t, id) => t.copy(id = id)) += wallet
}

FYI, I changed everything ("Find, Replace All") to Instant, and now the code works. Maybe special config is needed for OffsetDateTime?

[tminglei]

No, I don't remember any special config for OffsetDateTime.

But to object PgProfile extends PgProfile, I always write it as

override val api: API = new API {}

trait API extends super.API with ArrayImplicits
     ...

Because one early scala bug.