Re: setString and timestamps

On Fri, 17 Feb 2006, David Goodenough wrote:

> I understand from looking at the archive that there some changes around
> 8.0 in this area, but I am confused as to how to proceed.
>
> I have some code which takes a table and inserts values into it where the
> values have come from XML and are thus all strings.  Some of these XML
> fields are timestamps, but this generic code does not have table specific
> knowledge.
>
> From what I have read I think I have to use something like the metadata
> for this table to find the type of this column, and if it is a timestamp then
> I need to take the string and convert it into a timestamp before I set it
> into the parameterized insert.  Is this right, and are there any other
> datatypes which are not implicitly converted from String to whatever is
> necessary?

In the 8.0 and 8.1 driver all types have strong typing information.  You
can see this with integers as well.  The driver will do the equivalent of:

jurka=# create table inttest(a int);
CREATE TABLE
jurka=# insert into inttest values('1'::varchar);
ERROR:  column "a" is of type integer but expression is of type character
varying
HINT:  You will need to rewrite or cast the expression.

If you really have only string data and no typing information your best
bet is probably to use an 8.2dev driver and set the stringtype=unspecified
URL parameter to restore the old behavior of automatic type
interpretation.

http://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters

Kris Jurka