Re: Timestamp input + copy

Thanks, guys. sed took a couple hours, but it's all done now, and the data
are loaded. I still like Postgres!

Kevin

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, July 28, 2004 5:39 PM
To: Kevin Bartz
Cc: 'Chris Smith'; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Timestamp input + copy

"Kevin Bartz" <kbartz(at)loyaltymatrix(dot)com> writes:
> Alternatively, I could load the bad column as a string and then tell
> Postgres to recast it (using the "using" clause) with to_timestamp. The
> syntax listed in the documentation, however,

> alter table bonusticket
> alter submit_date type timestamp using to_timestamp(substring(submit_date,
> 1, 19), 'YYYY-MM-DD HH24:MI:SS');

> throws a syntax error, it explains, at the word "type." I have the latest
> version, 7.4.3 on a 64-bit SuSE box. Any suggestions?

You apparently are reading development-tip documentation rather than 7.4
documentation; that flavor of ALTER does not exist in 7.4.

I think the only real solution in 7.4 is to load the data into a temp
table in which this column is declared as "text", and then transfer to
the final table using INSERT/SELECT (or possibly CREATE TABLE AS) with
the appropriate conversion expression.

I suspect though that the sed-based solution is going to be quicker than
any of these.

regards, tom lane