RE: BUG #17911: Database or JDBC Driver Provides Incorrect Type

   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.

            Stephen C. Bergeon

From: Dave Cramer [mailto:davecramer@postgres.rocks]
Sent: Tuesday, May 16, 2023 6:43 AM
To: postgresql@bergeon.org; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type