query check error on postgresql with meta for OffsetTimeZone

[getArtemUsername]

Hi! I've got some query checking issue with postgresql.

insert into tsz_test (ts) values (?)
  ✓ SQL Compiles and TypeChecks
  ✕ P01 OffsetDateTime  →  TIMESTAMP (timestamptz)
    OffsetDateTime is not coercible to TIMESTAMP (timestamptz)
    according to the JDBC specification. Expected schema type was
    TIMESTAMPWITHTIMEZONE.

The table ddl is

create table tsz_test(ts timestamp with time zone not null);

Code:

private val odt = OffsetDateTime.of(2019, 1, 1, 1, 0, 0, 0, ZoneOffset.ofHours(4))
...
check(sql"""insert into tsz_test (ts) values ($odt)""".update)

Looks like it does not like jdbc type TimestampWithTimezone in meta definition but with Timestamp(I tried custom meta) all works fine.

[tpolecat]

Thanks for the report, we may be able to fix this in the Meta definition for OffsetDateTime.

[neko-kai]

IIRC, If you use TIMESTAMP instead of TIMESTAMP_WITH_TIMEZONE, the driver won't find OffsetDateTime-specific code - https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L622

TIMESTAMP (timestamptz)

Also, this is odd, timestamptz is timestamp with timezone...

[japgolly]

I had the same problem and I created the following as a local solution:

• https://gist.github.com/japgolly/582bd8c0dcb7d1ef6ac314db62c6c42e
  • TypeInfoCache.java.patch is the hacky little change
  • patch-postgres.sh is a script that you can use to keep the hack in sync as you update the postgres jar (customise the tgt and ver assignments for your own project setup)

[japgolly]

Actually sorry credit where credit is due: @triggerNZ came up with the hack a year ago, I just revised it and created the automation around it.