Doobie `analysis` rejects `TypoInstant` -> `timestamptz` mapping

[kolemannix]

This is not necessarily an issue with typo; I'd be happy with a workaround on either side but am unsure exactly what is best.

Analysis output:

  ✕ C10 created_at       TIMESTAMPWITHTIMEZONE (timestamptz) NOT NULL  →  String
    TIMESTAMPWITHTIMEZONE (timestamptz) is not coercible to String
    according to the JDBC specification or any defined mapping. Fix
    this by changing the schema type to OTHER or JAVAOBJECT; or the
    Scala type to an appropriate object type.

Doobie 1.0.0-RC5 postgres driver 42.7.3

I could certainly override doobie's analysis (either the input or output) as a workaround, but am curious what the intended path is, or if this is a known issue. I'm also interested in the rationale for TypoInstant's string representation beyond what is in the generated code:

This is java.time.TypoInstant, but with microsecond precision and transferred to and from postgres as strings. The reason is that postgres driver and db libs are broken

This explains its existence somewhat but could be more detailed; could also link to postgres docs showing that this is a 'good' / safe way to send timestamptzs.

[kolemannix]

Ok so I was having a bit of a skill issue moment because I did not find the date/time page and was not casting to ::text in my query, since this is pre-existing doobie query now outputting typo types.

I am now receiving an even more mysterious failure:

    ✕ C10 created_at       VARCHAR (text) NULL?     →  String
      VARCHAR (text) is not coercible to String according to the JDBC
      specification or any defined mapping. Fix this by changing the
      schema type to OTHER or JAVAOBJECT; or the Scala type to an
      appropriate object type.

The column is a TypoInstant and my select expr looks like the following, the coalesce was to try and remove the unknown nullability that doobie is suggesting above: coalesce(aic.created_at::text, '') as created_at

[oyvindberg]

Honestly, the argument for doing it this way is a bit convoluted, but it's done to support the three database libraries.

Also, a core design decision I made was to go through the implicit machinery, for maximum reusability.

So to try to describe the background:

• anorm has a design fault where it always calls ResultSet#getObject(n) regardless of column type, so we have to deal with the crap code paths that leads you through. Especially the codepath for timetz[] is completely broken and throws away time zones.
• running timestamptz through that default code path for the type silently converts from UTC to something it picks up from the JVM/OS, when that conversion should be done explicitly in code. I think this is why so many people think timestamptz includes time zone other than UTC, because it appears like it does locally. I needed to go through a ::text type cast in postgres to avoid that
• it was a nice bonus to be able to control precision for datetime types to make == work across platforms
• all the libraries do weird things with datetime types, especially when using arrays. supporting arrays of all types is also a core design decision in typo, because I think it's very useful and underused because it's so hard to use correctly. This means I needed my own Typo-types to have a name tag to hang implicit instances on, to avoid orphan instances.

So one thing that is going to change is that I'll drop the decision to go through the implicit machinery and always refer to the concrete instances instead (that is, inlineImplicits will always be true). This is primarily done to support java and kotlin in the near future. Maybe I'll be able to revisit this encoding and these problems after that is in place.

[oyvindberg]

VARCHAR (text) is not coercible to String does not make any sense to me at all. it's likely something in the type class definitions for String in doobie I guess

[kolemannix]

Seems this can be closed; though your detailed explanation above would probably be good to capture on that date/time docs page.