Can't set a nonstring value to null in database with jooq UpdateQuery in Kotlin

Question

(It's similar to this, but this time it knows it's POSTGRES.)

I have an UpdateQuery:

      val uq = kontxt.updateQuery(table("felhaszn"))
      uq.addValue(field("kilepett"), null as Timestamp?

and it generates (println(uq.getSQL())):

update felhaszn set /*...*/ kilepett = cast(? as varchar) /*...*/

Why varchar?
At uq.execute() it throws an

ERROR: column "kilepett" is of type timestamp without time zone but expression is of type character varying

While the output of println(uq.toString()) shows the correct SQL. So I could work around it by kontxt.query(uq.toString()).execute() rather than just uq.execute().

Answer 1

This wouldn't happen if you would be using the code generator, in case of which you'd have type information attached to your columns, and jOOQ could bind the correct type:

val uq = kontxt.updateQuery(FELHASZN)
uq.addValue(FELHASZN.KILEPETT, null as Timestamp?)

Your cast to Timestamp? doesn't help here, because that type information isn't maintained by the JVM at runtime. The null reference has no type, and your field("kilepett") doesn't have any type information attached to it, either (it's a Field<Object?>). If for some reason you cannot use the code generator (I highly recommend you do!), you'll have to attach this information explicitly:

uq.addValue(field("kilepett", SQLDataType.TIMESTAMP), null as Timestamp?)