changing column datatype from char(20) to timestamp - Mailing list pgsql-novice

Greetings,
This is a followup to an issue that I had about 9 months ago:
http://archives.postgresql.org/pgsql-novice/2007-08/msg00067.php

Back then, I was effectively mangling timestamps in a table, by
casting to char(20) to work around problem with a webapp.  The app
finally got fixed, and I'm looking into how to try to get all this
ugly data from char(20) into a sane 'timestamp without time zone'
format.  Right now, its all:

date_created   | character(20) | not null

I want to change it to:
date_created   | timestamp without time zone | not null

Unfortunately, I can't just do:
ALTER TABLE data ALTER COLUMN date_created TYPE timestamp ;

since the data is currently in this format:
04-29-2008 10:03:28

since, its getting inserted via a query like this:
INSERT INTO data (date_created) VALUES ((select
to_char(current_timestamp, 'MM-DD-YYYY HH24:MI:SS')) ;

Surely there must be a way to fix this without having to dump the
data, fix the format, and reinsert it ?

thanks

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama@gmail.com
LlamaLand https://netllama.linux-sxs.org