From: Dave Cramer <davecramer@postgres.rocks>
To: PostgreSQL@bergeon.org
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type
Date: Tue, 16 May 2023 18:28:42 -0400
Message-ID: <CADK3HH+SSdFSnDPLvaCCPinX844WkU1MH37OVV++HiQqvnY_Ww@mail.gmail.com> (raw)
In-Reply-To: <007e01d98832$566bfbc0$0343f340$@Bergeon.org>
References: <17911-76b72ef0b040e9da@postgresql.org>
<CADK3HHLrcck+oa_-axwR6GY5pLy9bTZku-mzrEdP3SvJMg-O6g@mail.gmail.com>
<007e01d98832$566bfbc0$0343f340$@Bergeon.org>
On Tue, 16 May 2023 at 16:10, <PostgreSQL@bergeon.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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: davecramer@postgres.rocks, PostgreSQL@bergeon.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17911: Database or JDBC Driver Provides Incorrect Type
In-Reply-To: <CADK3HH+SSdFSnDPLvaCCPinX844WkU1MH37OVV++HiQqvnY_Ww@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox