Обсуждение: Re: [BUGS] BUG #1523: precision column value returned from getTypeInfo()

(cc'ing -jdbc)

Sergio Lob wrote:

> After calling Connection.DatabaseMetaData.getTypeInfo() method, the
> PRECISION column of the ResultSet seems hardcoded to value 9, which of
> course in incorrect

Well, it's working as intended in the sense that there is no code to
support retrieving a real precision value -- it is indeed a hardcoded
placeholder value. I'm not sure why '9' off the top of my head, perhaps
making that column NULL would be better? What are the units of this
value anyway?

If you're willing to do the implementation work to improve this, then
post a patch to pgsql-jdbc and I can look at applying it.

-O

On Fri, 4 Mar 2005, Oliver Jowett wrote:

> > After calling Connection.DatabaseMetaData.getTypeInfo() method, the
> > PRECISION column of the ResultSet seems hardcoded to value 9, which of
> > course in incorrect
>
> Well, it's working as intended in the sense that there is no code to
> support retrieving a real precision value -- it is indeed a hardcoded
> placeholder value. I'm not sure why '9' off the top of my head, perhaps
> making that column NULL would be better? What are the units of this
> value anyway?

Compare the existing implementation of ResultSetMetaData.getPrecision.

Kris Jurka

Hi Oliver,
    The Microsoft ODBC 2.0 SDK guide and reference (Appendix D) contains a pretty thorough definition of what precision means in relation to various data types. I have never seen a definition of precision in any JDBC doc I have read, however.
According to ODBC 2.0 spec definition of precision, "the precision of a numeric column or parameter refers to the maximum number of digits used by the data type of the column or parameter. The precision of a nonnumeric column or parameter generally refers to either the maximum length or defined length of the column or parameter. The following table defines the precision for each ODBC SQL data type:

SQL Type                                             Precision
-----------                                             ----------
SQL_CHAR                                          The defined length of the column or parameter. For
SQL_VARCHAR                                  example, the precision of a column defined as CHAR(10)
                                                               is 10

SQL_LONGVARCHAR                       The maximum length of the column or parameter
                                               
SQL_DECIMAL                                    The defined number of digits. For example, the
SQL_NUMERIC                                    precision of a column defined as NUMERIC(10,3) is 10

SQL_BIT                                                1

SQL_TINYINT                                       3

SQL_SMALLINT                                   5

SQL_INTEGER                                      10

SQL_BIGINT                                          19(if signed) or 20 (if unsigned)

SQL_REAL                                             7

SQL_FLOAT                                          15
SQL_DOUBLE

SQL_BINARY                                         The defined length of the column or parameter. For example,
SQL_VARBINARY                                  the precision of a column defined as BINARY(10) is 10

SQL_LONGVARBINBARY                   The maximum length of the column or parameter

SQL_DATE                                              10 (the number of characters in yyyy-mm-dd format)

SQL_TIME                                                8 (the number of characters in hh:mm:ss format)

SQL_TIMESTAMP                                    The number of characters in the
                                                                     "yyyy-mm-dd hh:mm:ss[.f...]" format used by the
                                                                   timestamp data type. For example, if the timestamp does
                                                                   not use seconds of fractional digits, the precision is 16
                                                                  (the number of characters in the "yyyy-mm-dd hh:mm"
                                                                   format). If a timestamp uses thousandths of a second,
                                                                   the precision is 23 ("yyyy-mm-dd  hh:mm:ss.fff" format)



I have found problems with the answer set of other JDBC methods which return metadata information as well. For instance, the description of length values of  certain columns in the DatabaseMetadata.getColumns() method answer set return negative numbers (-4 ?).
This data being crucial to applications that I have written, I have unforunately had to take PostgresSQL off the list of JDBC data sources that I can support through my application.
Anyway, I hope I answered your question about precision.

Sergio Lob

oliver@opencloud.com wrote:

(cc'ing -jdbc)

Sergio Lob wrote:
 

After calling Connection.DatabaseMetaData.getTypeInfo() method, the
PRECISION column of the ResultSet seems hardcoded to value 9, which of
course in incorrect   

Well, it's working as intended in the sense that there is no code to 
support retrieving a real precision value -- it is indeed a hardcoded 
placeholder value. I'm not sure why '9' off the top of my head, perhaps 
making that column NULL would be better? What are the units of this 
value anyway?

If you're willing to do the implementation work to improve this, then 
post a patch to pgsql-jdbc and I can look at applying it.

-O 

Sergio Lob wrote:

>     The Microsoft ODBC 2.0 SDK guide and reference (Appendix D) contains
> a pretty thorough definition of what precision means in relation to
> various data types.

Is this from the ODBC spec or something specific to Microsoft? (I'm not
familiar with ODBC at all).

> SQL_CHAR                                          The defined length of
> the column or parameter. For
> SQL_VARCHAR                                  example, the precision of a
> column defined as CHAR(10)

We can't do this in getTypeInfo() as it's describing all VARCHARs, not a
specific one. What should we return in this case?

> SQL_LONGVARCHAR                       The maximum length of the column
> or parameter

This is going to be about 1GB if I read it correctly (but that varies
depending on what data you put in there). Is that really a useful value
to return?

> SQL_DECIMAL                                    The defined number of
> digits. For example, the
> SQL_NUMERIC                                    precision of a column
> defined as NUMERIC(10,3) is 10

Again, we can't do this in getTypeInfo() as we're describing all
NUMERICs, not a particular one.

> SQL_BINARY                                         The defined length of
> the column or parameter. For example,
> SQL_VARBINARY                                  the precision of a column
> defined as BINARY(10) is 10
>
> SQL_LONGVARBINBARY                   The maximum length of the column or
> parameter

Same as for varchar/longvarchar/etc above.

> SQL_DATE                                              10 (the number of
> characters in yyyy-mm-dd format)
>
> SQL_TIME                                                8 (the number of
> characters in hh:mm:ss format)
>
> SQL_TIMESTAMP                                    The number of
> characters in the
>
> "yyyy-mm-dd hh:mm:ss[.f...]" format used by the [...]

Gah, those look pretty hairy, especially since JDBC has accessors
specifically for date/time/timestamp -- you're not really meant to deal
with them as text..

For the other types (int/long/etc) I will take a look at returning a
better precision value. We already do this in ResultSetMetadata, as Kris
pointed out, so it shouldn't be too painful.

> I have found problems with the answer set of other JDBC methods which
> return metadata information as well. For instance, the description of
> length values of  certain columns in the DatabaseMetadata.getColumns()
> method answer set return negative numbers (-4 ?).
> This data being crucial to applications that I have written, I have
> unforunately had to take PostgresSQL off the list of JDBC data sources
> that I can support through my application.

I'll take a look at the length issue too. What was the actual type of
the column that was returning bad values?

What else have you had problems with? In general the metadata code isn't
heavily used, and is poorly specified in JDBC anyway, so it hasn't had
as much work done on it as the rest of the driver. The only way it's
going to get better is if those people actually using the metadata point
out the problems in detail :)

-O

You made some good points about what to return for precision when we are describing data types and not a  specific column's attributes. Let me describe what ODBC defines. There is an ODBC api call that is roughly equivalent to JDBC method getTypeInfo, named SQLGetTypeInfo(). This function returns an answer set of information about data types supported by the data source. One of the columns returned is "precision". The definition of the precision column is as follows: "The maximum precision of the data type on the data source. NULL is returned for the data types where precision is not applicable".

My take then, is that for something like a CHAR data types, if the maximum supported precision is 32767, then that is the value that should be returned. Same for data types like VARCHAR and DATE/DATETIME...

Here are the answers to your questions:

Q1: Is this from the ODBC spec or something specific to Microsoft? (I'm not familiar with ODBC at all).

Answer: The ODBC standard itself was developed Microsoft Corp, so there is no nothing specific to Microsoft, as they wrote the standard.

Q2: We can't do this in getTypeInfo() as it's describing all VARCHARs, not a specific one. What should we return in this case?

Answer: I can interpret this question in two ways. You are either talking about multiple native data types that map to JDBC VARCHAR type OR you are talking about 2 columns defined as varchar with different length definitions. For interpretation #1, the answer set should have one row for each supported native data type. If more than one native data type maps to JDBC varchar type, they should each have their own row with their own maximum precision column value. For interpretation #2, we are describing data type info, not description of a specific column, so this case does not apply.

Q3: This is going to be about 1GB if I read it correctly (but that varies depending on what data you put in there). Is that really a useful value to return?

Answer: In this case, I think NULL would be appropriate.

Q4: Again, we can't do this in getTypeInfo() as we're describing all NUMERICs, not a particular one.

Answer: see Q2 answer.

Q5: Gah, those look pretty hairy, especially since JDBC has accessors 
specifically for date/time/timestamp -- you're not really meant to deal 
with them as text..

Answer:  This precision information of TIME/DATETIME data types is precisely what I need for my application to work properly. The key to calculating the max precision is how many fractional digits are supported. For instance, if timestamp data type supports down to microseconds, max precision is 26. If timestamp data type does not support fractional seconds at all, max precision is 19. The precision value includes separators in the character count. (eg. "yyyy-mm-dd hh:mm:ss" has precision 19) . Not hard to figure out....

Q6: I'll take a look at the length issue too. What was the actual type of the column that was returning bad values?

DatabaseMetadata.getColumns() returns an answer set with most (if not all) of the VARCHAR columns described as having length of -4.  For instance, the first four columns of the answer set (columns "table_cat", "table_schem", "table_name", and "column_name") return column length -4. As an example, the output says that the actual name of the table (in column 3) has a length of -4 bytes. So my application will attempt to copy -4 bytes from the buffer containing the actual table name instead of copying the correct length.

Q7:  What else have you had problems with? In general the metadata code isn't heavily used, and is poorly specified in JDBC anyway, so it hasn't had as much work done on it as the rest of the driver. The only way it's going to get better is if those people actually using the metadata point out the problems in detail :)

I have not yet found more metadata issues that specifically affect my generic JDBC application. I had another metadata-related issue, but it was fixed in build 310 (support for PreparedStatement.getMetaData() method).
We do extensive QA testing of our JDBC interface (to many different DBMSs), and they have identified several problems with Postgres (not necessarily metadata-related) which I am currently investigating......

Regards, Sergio Lob

Sergio Lob wrote:
 

    The Microsoft ODBC 2.0 SDK guide and reference (Appendix D) contains 
a pretty thorough definition of what precision means in relation to 
various data types.   

Is this from the ODBC spec or something specific to Microsoft? (I'm not 
familiar with ODBC at all).
 

SQL_CHAR                                          The defined length of 
the column or parameter. For
SQL_VARCHAR                                  example, the precision of a 
column defined as CHAR(10)   

We can't do this in getTypeInfo() as it's describing all VARCHARs, not a 
specific one. What should we return in this case?
 

SQL_LONGVARCHAR                       The maximum length of the column 
or parameter   

This is going to be about 1GB if I read it correctly (but that varies 
depending on what data you put in there). Is that really a useful value 
to return?
 

SQL_DECIMAL                                    The defined number of 
digits. For example, the
SQL_NUMERIC                                    precision of a column 
defined as NUMERIC(10,3) is 10   

Again, we can't do this in getTypeInfo() as we're describing all 
NUMERICs, not a particular one.
 

SQL_BINARY                                         The defined length of 
the column or parameter. For example,
SQL_VARBINARY                                  the precision of a column 
defined as BINARY(10) is 10

SQL_LONGVARBINBARY                   The maximum length of the column or 
parameter   

Same as for varchar/longvarchar/etc above.
 

SQL_DATE                                              10 (the number of 
characters in yyyy-mm-dd format)

SQL_TIME                                                8 (the number of 
characters in hh:mm:ss format)

SQL_TIMESTAMP                                    The number of 
characters in the                                                                    
"yyyy-mm-dd hh:mm:ss[.f...]" format used by the [...]   

Gah, those look pretty hairy, especially since JDBC has accessors 
specifically for date/time/timestamp -- you're not really meant to deal 
with them as text..

For the other types (int/long/etc) I will take a look at returning a 
better precision value. We already do this in ResultSetMetadata, as Kris 
pointed out, so it shouldn't be too painful.
 

I have found problems with the answer set of other JDBC methods which 
return metadata information as well. For instance, the description of 
length values of  certain columns in the DatabaseMetadata.getColumns() 
method answer set return negative numbers (-4 ?).
This data being crucial to applications that I have written, I have 
unforunately had to take PostgresSQL off the list of JDBC data sources 
that I can support through my application.   

I'll take a look at the length issue too. What was the actual type of 
the column that was returning bad values?

What else have you had problems with? In general the metadata code isn't 
heavily used, and is poorly specified in JDBC anyway, so it hasn't had 
as much work done on it as the rest of the driver. The only way it's 
going to get better is if those people actually using the metadata point 
out the problems in detail :)

-O 

Sergio Lob wrote:

> My take then, is that for something like a CHAR data types, if the
> maximum supported precision is 32767, then that is the value that should
> be returned. Same for data types like VARCHAR and DATE/DATETIME...

The maximum supported precision for varchar/char/etc is 10485760. We can
return that.

> Here are the answers to your questions:
>
> Q1: Is this from the ODBC spec or something specific to Microsoft? (I'm
> not familiar with ODBC at all).
>
> Answer: The ODBC standard itself was developed Microsoft Corp, so there
> is no nothing specific to Microsoft, as they wrote the standard.

Ah, joy, the sort of standard that revolves around one implementation..

> Q2: We can't do this in getTypeInfo() as it's describing all VARCHARs,
> not a specific one. What should we return in this case?
>
> Answer: I can interpret this question in two ways. [...]

No, I meant that the type-info row describes all varchar(n) types, not
one specific varchar(42) type. If it's meant to return the maximum
precision, that makes some sense.

> Q5: Gah, those look pretty hairy, especially since JDBC has accessors
> specifically for date/time/timestamp -- you're not really meant to deal
> with them as text..
>
> Answer:  This precision information of TIME/DATETIME data types is
> precisely what I need for my application to work properly. The key to
> calculating the max precision is how many fractional digits are
> supported. For instance, if timestamp data type supports down to
> microseconds, max precision is 26. If timestamp data type does not
> support fractional seconds at all, max precision is 19. The precision
> value includes separators in the character count. (eg. "yyyy-mm-dd
> hh:mm:ss" has precision 19) . Not hard to figure out....

Um, I don't see how this is going to work. The native text format of
timestamps etc. (what you get if you call getString() on a timestamp
result column) is not as described in the ODBC spec, so I don't see how
returning a precision value based on the length of the text
representation is useful or portable, and returning a precision value
based on a notional text representation that we don't actually use seems
a bit weird..

What do other JDBC drivers do here? Is the returned "precision"
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at
least said that the returned columns were meant to follow the ODBC spec.
But it doesn't say anything at all :(

> Q6: I'll take a look at the length issue too. What was the actual type
> of the column that was returning bad values?
>
> DatabaseMetadata.getColumns() returns an answer set with most (if not
> all) of the VARCHAR columns described as having length of -4.  For
> instance, the first four columns of the answer set (columns "table_cat",
> "table_schem", "table_name", and "column_name") return column length -4.

Oh, you're looking at the resultset metadata of a metadata-generated
result set? That's a bit of a corner case, I'm not too surprised it is
broken.

Given that there's no underlying table for these metadata result sets,
whatever meta-meta-data we could generate is likely to be quite
arbitary. Expect NULLs..

> As an example, the output says that the actual name of the table (in
> column 3) has a length of -4 bytes. So my application will attempt to
> copy -4 bytes from the buffer containing the actual table name instead
> of copying the correct length.

I do wonder why you don't just use the returned String length, though.

-O

Hi Oliver,
    Regarding your following statement:
=======================

Um, I don't see how this is going to work. The native text format of 
timestamps etc. (what you get if you call getString() on a timestamp 
result column) is not as described in the ODBC spec, so I don't see how 
returning a precision value based on the length of the text 
representation is useful or portable, and returning a precision value 
based on a notional text representation that we don't actually use seems 
a bit weird..

What do other JDBC drivers do here? Is the returned "precision" 
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at 
least said that the returned columns were meant to follow the ODBC spec. 
But it doesn't say anything at all :(

===========================

The Java 2 api spec says in description of Timestamp class method toString():

"Formats a timestamp in JDBC timestamp escape format. yyyy-mm-dd 
hh:mm:ss.fffffffff, where ffffffffff indicates nanoseconds."

The Java 2 api spec says in description of Time class method toString() that it returns a string in hh:mm:ss format. 

This is consistent with ODBC behavior and inconsistent with what you say in your statement. What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should return same as toString() method of a timestamp object as defined in java 2 api spec.

Regards, Sergio

 



oliver@opencloud.com wrote:

Sergio Lob wrote:
 

My take then, is that for something like a CHAR data types, if the 
maximum supported precision is 32767, then that is the value that should 
be returned. Same for data types like VARCHAR and DATE/DATETIME...   

The maximum supported precision for varchar/char/etc is 10485760. We can 
return that.
 

Here are the answers to your questions:

Q1: Is this from the ODBC spec or something specific to Microsoft? (I'm 
not familiar with ODBC at all).

Answer: The ODBC standard itself was developed Microsoft Corp, so there 
is no nothing specific to Microsoft, as they wrote the standard.   

Ah, joy, the sort of standard that revolves around one implementation..
 

Q2: We can't do this in getTypeInfo() as it's describing all VARCHARs, 
not a specific one. What should we return in this case?

Answer: I can interpret this question in two ways. [...]   

No, I meant that the type-info row describes all varchar(n) types, not 
one specific varchar(42) type. If it's meant to return the maximum 
precision, that makes some sense.
 

Q5: Gah, those look pretty hairy, especially since JDBC has accessors 
specifically for date/time/timestamp -- you're not really meant to deal 
with them as text..

Answer:  This precision information of TIME/DATETIME data types is 
precisely what I need for my application to work properly. The key to 
calculating the max precision is how many fractional digits are 
supported. For instance, if timestamp data type supports down to 
microseconds, max precision is 26. If timestamp data type does not 
support fractional seconds at all, max precision is 19. The precision 
value includes separators in the character count. (eg. "yyyy-mm-dd 
hh:mm:ss" has precision 19) . Not hard to figure out....   

Um, I don't see how this is going to work. The native text format of 
timestamps etc. (what you get if you call getString() on a timestamp 
result column) is not as described in the ODBC spec, so I don't see how 
returning a precision value based on the length of the text 
representation is useful or portable, and returning a precision value 
based on a notional text representation that we don't actually use seems 
a bit weird..

What do other JDBC drivers do here? Is the returned "precision" 
consistent with their text representations of date/time types?

I'd be much happier about doing something like this if the JDBC spec at 
least said that the returned columns were meant to follow the ODBC spec. 
But it doesn't say anything at all :(
 

Q6: I'll take a look at the length issue too. What was the actual type 
of the column that was returning bad values?

DatabaseMetadata.getColumns() returns an answer set with most (if not 
all) of the VARCHAR columns described as having length of -4.  For 
instance, the first four columns of the answer set (columns "table_cat", 
"table_schem", "table_name", and "column_name") return column length -4.    

Oh, you're looking at the resultset metadata of a metadata-generated 
result set? That's a bit of a corner case, I'm not too surprised it is 
broken.

Given that there's no underlying table for these metadata result sets, 
whatever meta-meta-data we could generate is likely to be quite 
arbitary. Expect NULLs..
 

As an example, the output says that the actual name of the table (in 
column 3) has a length of -4 bytes. So my application will attempt to 
copy -4 bytes from the buffer containing the actual table name instead 
of copying the correct length.   

I do wonder why you don't just use the returned String length, though.

-O 

Sergio Lob wrote:

> What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should
returnsame as toString() method of a timestamp object as defined in java 2 api spec. 

It returns the string representation the backend gave it. This is true
for all types at the moment. For a timestamp with timezone value this is
generally going to be yyyy-mm-dd hh:mm:ss.nnnnnnzzz:

test=# select now();
               now
-------------------------------
  2005-03-10 10:08:11.707753+13
(1 row)

Does the JDBC spec say somewhere that we should return a different format?

-O

I'll quote you what Oracle docs for their support of TIMESTAMP w/ time zone....

"TIMESTAMP WITH TIME ZONE Data Type

By default, the Oracle TIMESTAMP WITH TIME ZONE data type is mapped to the VARCHAR JDBC data type.

When retrieving TIMESTAMP WITH TIME ZONE values as a string (using resultSet.getString, for example), the value is returned as the string representation of the timestamp including time zone information. The string representation is formatted in the format specified by the Oracle NLS_TIMESTAMP_TZ_FORMAT session parameter.

By default, retrieving TIMESTAMP WITH TIME ZONE values as a timestamp (using resultSet.getTimeStamp, for example) is not supported because the time zone information stored in the database would be lost when the data is converted to a timestamp. To provide backward compatibility with existing applications, you can use the FetchTSWTZasTimestamp property to allow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. The default value of the FetchTSWTSasTimestamp property is false, which disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps."

Oracle recognizes that the JDBC timestamp data type does not allow for timezones, thus they would map PostgresSQL timestamptz type to JDBC type varchar, not timestamp. PostgreSQL currently maps timestamptz to JDBC timestamp in DatabaseMetadata.getTypeInfo() method and in ResultSetMetaData.getColumnType() method. I tend to agree with their interpretation.

Regards, Sergio

oliver@opencloud.com wrote:

Sergio Lob wrote:
 

What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should return same as toString() method of a timestamp object as defined in java 2 api spec.   

It returns the string representation the backend gave it. This is true 
for all types at the moment. For a timestamp with timezone value this is 
generally going to be yyyy-mm-dd hh:mm:ss.nnnnnnzzz:

test=# select now();              now
------------------------------- 2005-03-10 10:08:11.707753+13
(1 row)

Does the JDBC spec say somewhere that we should return a different format?

-O 

By the way, the PostgreSQL ODBC driver has different format for
retrieved timestamps w/ time zone.

For instance, an inputted timestamp w/ time zone value of  '1999-10-30
11:12:13-8', which is pacific standard time zone, displayed a retrieved
character string value of '1999-10-20 15:12:13'  (without the time zone
designation), I suppose it must translate it to the current locale's
timezone. This output format here would conform to the java 2 api spec
JDBC timestamp escape format defined in timestamp.toString() method
description. But I still like Oracle's definition best...

Sergio

Sergio_Lob@iwaysoftware.com wrote:

>I'll quote you what Oracle docs for their support of TIMESTAMP w/ time zone....
>
>"TIMESTAMP WITH TIME ZONE Data Type
>
>By default, the Oracle TIMESTAMP WITH TIME ZONE data type is mapped to the VARCHAR JDBC data type.
>
>
>When retrieving TIMESTAMP WITH TIME ZONE values as a string (u1999-10-30 15:12:13sing resultSet.getString, for
example),the value is returned as the string representation of the timestamp including time zone information. The
stringrepresentation is formatted in the format specified by the Oracle NLS_TIMESTAMP_TZ_FORMAT session parameter. 
>
>By default, retrieving TIMESTAMP WITH TIME ZONE values as a timestamp (using resultSet.getTimeStamp, for example) is
notsupported because the time zone information stored in the database would be lost when the data is converted to a
timestamp.To provide backward compatibility with existing applications, you can use the FetchTSWTZasTimestamp property
toallow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp. The default value of the FetchTSWTSasTimestamp
propertyis false, which disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps." 
>
>
>Oracle recognizes that the JDBC timestamp data type does not allow for timezones, thus they would map PostgresSQL
timestamptztype to JDBC type varchar, not timestamp. PostgreSQL currently maps timestamptz to JDBC timestamp in
DatabaseMetadata.getTypeInfo()method and in ResultSetMetaData.getColumnType() method. I tend to agree with their
interpretation.
>
>
>Regards, Sergio
>
>
>
>oliver@opencloud.com wrote:
>
>
>Sergio Lob wrote:
>
>
>
>
>
>What format does PostgreSQL return in getString() method for a timestamp column, for example? Seems like it should
returnsame as toString() method of a timestamp object as defined in java 2 api spec. 
>
>
>
>
>
>It returns the string representation the backend gave it. This is true
>
>for all types at the moment. For a timestamp with timezone value this is
>
>generally going to be yyyy-mm-dd hh:mm:ss.nnnnnnzzz:
>
>
>
>test=# select now();
>
>               now
>
>-------------------------------
>
>  2005-03-10 10:08:11.707753+13
>
>(1 row)
>
>
>
>Does the JDBC spec say somewhere that we should return a different format?
>
>
>
>-O
>
>
>
>
>

Sergio Lob wrote:

> By default, retrieving TIMESTAMP WITH TIME ZONE values as a timestamp
> (using resultSet.getTimeStamp, for example) is not supported because the
> time zone information stored in the database would be lost when the data
> is converted to a timestamp. To provide backward compatibility with
> existing applications, you can use the FetchTSWTZasTimestamp property to
> allow TIMESTAMP WITH TIME ZONE values to be retrieved as a timestamp.
> The default value of the FetchTSWTSasTimestamp property is false, which
> disables retrieving TIMESTAMP WITH TIME ZONE values as timestamps."

The current PostgreSQL driver allows retrieving it as either a String or
a Timestamp -- application's choice. I don't see much value in an option
to enable/disable retrieving it as a Timestamp TBH.

> Oracle recognizes that the JDBC timestamp data type does not allow for
> timezones, thus they would map PostgresSQL timestamptz type to JDBC type
> varchar, not timestamp.

They actually do this?

I'd map it to timestamp at a lower preference (later in the resultset);
TIMESTAMP WITH TIME ZONE is closer to a TIMESTAMP than to a VARCHAR..

-O