Skip site navigation (1) Skip section navigation (2)

changing column datatype from char(20) to timestamp

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 (view raw or flat)
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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2008-04-29 17:46:23
Subject: Re: changing column datatype from char(20) to timestamp
Previous:From: Alan HodgsonDate: 2008-04-29 15:31:52
Subject: Re: Configuration on CentOS 4.6

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group