Attached is a simple one-class program that should demonstrate the issue. The constants DB_URL, DB_USER_ID and DB_USER_PASSWORD must be changed for your environment. You also must have the PostgreSQL JDBC driver on the class-path.
The program creates a table named TestTable and then queries the JDBC driver for column information about the table. Then it displays the results. The same issue occurs if checking the ResultSetMetaData resulting from executind a DB select statement.
In my current test environment the program results display as follows…
Connecting to the DB.
DB Product: PostgreSQL version 15.2
DB Driver: PostgreSQL JDBC Driver version 42.2.14
Dropping the table if it exists.
Creating the table.
The data type for column 'id' is: Integer
The data type for column 'charvalue' is: Char
The data type for column 'varcharvalue' is: VarChar
The data type for column 'textvalue' is: VarChar
The data type for column 'binaryvalue' is: Binary
The data type for column 'intvalue' is: Integer
The data type for column 'floatvalue' is: Real
The data type for column 'doublevalue' is: Double
The data type for column 'booleanvalue' is: Bit
The data type for column 'datevalue' is: Date
The data type for column 'timevaluenotimezone' is: Time
The data type for column 'timevaluewithtimezone' is: Time
The data type for column 'timestampnotimezone' is: Timestamp
The data type for column 'timestampwithtimezone' is: Timestamp
All done.
The problem is demonstrated by the fact that column timevaluewithtimezone shows format Time instead of TimeWithTimeZone, and column timestampwithtimezone shows format Timestamp instead of TimestampWithTimeZone. Which means the driver is returning the java.sql.Types constants of Types.TIME instead of Types.TIME_WITH_TIMEZONE, and Types.TIMESTAMP instead of Types.TIMESTAMP_WITH_TIMEZONE, respectively.
By reporting the wrong SQL-Type, applications and libraries do not know the correct column type and cannot automatically process them properly. This is important if the application/library wants to call the ResultSet.getObject(column, class) method to return the appropriate Java type such as java.time.LocalTime vs java.time.OffsetTime or java.time.LocalDateTime vs java.time.OffsetDateTime.
