Re: Timestamp input + copy

"Kevin Bartz" <kbartz@loyaltymatrix.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