Thread: BUG #18495: invalid type mapping for timestamptz from call of: getMetaData and then geColumns on PgConnection.

The following bug has been logged on the website:

Bug reference:      18495
Logged by:          Juraj Burian
Email address:      jurajburian@gmail.com
PostgreSQL version: 16.0
Operating system:   linux
Description:

Hello folks, 

I can see a non correct type mapping for timestamptz type. Code snippets
used in descriptions are pure SQL or (Scala). We use latest JDBC driver
(version 42.7.3).
The bug is simple. Instead of Types.TIMESTAMP_WITH_TIMEZONE we obtain value
of Types.TIMESTAMP in resultset from getMetadata wit consequential call of:
geColumns.

Here description in more detail form: 

1) Lets define table like this : 
create table if not exists l1.serving_end (
 id int8 not null,
 actor_date timestamptz(0) not null
)
2) call meta = connection.getMetadata, then rs = meta.geColumns(null, "l1",
"serving_end", null), and then examine result set (rs).
in our case we use code like this to fix the problem : 
    val dataType = {
      val dt = rs.getInt("DATA_TYPE")
      if (dt == Types.TIMESTAMP && typeName == "timestamptz") {
        log.warn(
          s"Invalid datatype mapping for timestamptz: $dt instead of
${Types.TIMESTAMP_WITH_TIMEZONE} for: $table:$columnName"
        )
        Types.TIMESTAMP_WITH_TIMEZONE
      } else dt

so we replace:  Types.TIMESTAMP  to correct value
Types.TIMESTAMP_WITH_TIMEZONE in case of timestamtz. 
We can see  warning like this: 
WARN  kafka-to-ca-dwh - Invalid datatype mapping for timestamptz: 93 instead
of 2014 for: l1.serving_end:actor_date.
The code indicates that the mapping for timestamptz is invalid. For other
type mappings we do not observe any problems.

Thanks a lot for looking on this problem.
Best regards
Juraj

p.s  we use statement.setObject(..., type) method,  our fix also fixed
behaviour in insert/update for values of  timestamptz -
java.time.OffsetDateTime is parsed correctly.

On 2024-06-05 15:21 +0200, PG Bug reporting form wrote:
> I can see a non correct type mapping for timestamptz type. Code snippets
> used in descriptions are pure SQL or (Scala). We use latest JDBC driver
> (version 42.7.3).
> The bug is simple. Instead of Types.TIMESTAMP_WITH_TIMEZONE we obtain value
> of Types.TIMESTAMP in resultset from getMetadata wit consequential call of:
> geColumns.

It's a known issue: https://github.com/pgjdbc/pgjdbc/issues/1766

-- 
Erik