From: | "Lonni J Friedman" <netllama(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | changing column datatype from char(20) to timestamp |
Date: | 2008-04-29 17:12:20 |
Message-ID: | 7c1574a90804291012m124c96a0w8b791b4b7f47169a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)com
LlamaLand https://netllama.linux-sxs.org
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-29 17:46:23 | Re: changing column datatype from char(20) to timestamp |
Previous Message | Alan Hodgson | 2008-04-29 15:31:52 | Re: Configuration on CentOS 4.6 |