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

On Tue, 16 May 2023 at 16:10, <PostgreSQL(at)bergeon(dot)org> wrote:

> 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.
>
>
>

Ah, yes, we are aware of this and there is a PR to fix it, but it appears
to have been dropped by the author correct mapping for postgres timestamptz
type to sql type TIMESTAMP_W… by lopata2 · Pull Request #2715 ·
pgjdbc/pgjdbc (github.com) <https://github.com/pgjdbc/pgjdbc/pull/2715>

<https://github.com/pgjdbc/pgjdbc/pull/2715>Care to take it up ?

Dave

>
>