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.
