Re: BUG #1523: precision column value returned from getTypeInfo()

The following bug has been logged online:

Bug reference: 1523
Logged by: Sergio Lob
Email address: sergio_lob(at)iwaysoftware(dot)com
PostgreSQL version: 8.0.1
Operating system: linux
Description: precision column value returned from getTypeInfo()
always has value 9
Details:

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

To repro:
1. connect to a PostgresSQL JDBC data source
2. Invoke Connection.getMetaData() on that connection to get
DatabaseMetaData object
3. Invoke DataBaseMetaData.getTypeInfo() to get ResultSet of all supported
data types
4. Invoke ResultSet.next() to get to first row
5. Invoke ResultSet.getString(3) to get PRECISION column value for current
row. Notice that no matter what row is checked in the answer set, precision
of data type is ALWAYS 9.

(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(at)opencloud(dot)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(at)opencloud(dot)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 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

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(at)opencloud(dot)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(at)iwaysoftware(dot)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 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(at)opencloud(dot)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
>
>
>
>
>

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