Postgres Pro
Downloads
Home   >   mailing lists

Thread: changing column datatype from char(20) to timestamp

changing column datatype from char(20) to timestamp

From
"Lonni J Friedman"
Date:
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

Re: changing column datatype from char(20) to timestamp

From
Tom Lane
Date:
"Lonni J Friedman" <netllama@gmail.com> writes:
> 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

I think you're just missing an explicit cast:

ALTER TABLE data ALTER COLUMN date_created TYPE timestamp USING date_created::timestamp;

You should first check that datestyle is set to MDY to avoid
any confusion about the field order, but other than that I don't
see why you'd have a format problem.

            regards, tom lane

Re: changing column datatype from char(20) to timestamp

From
Stephan Szabo
Date:
On Tue, 29 Apr 2008, Lonni J Friedman wrote:

> 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 ;

In recent versions I think something like
 ALTER TABLE data ALTER COLUMN date_created TYPE timestamp USING
 CAST(date_created as timestamp);
should work. If you have data for which the cast will fail, you can use
some other expression after the USING.

Re: changing column datatype from char(20) to timestamp

From
"Lonni J Friedman"
Date:
On Tue, Apr 29, 2008 at 10:46 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Lonni J Friedman" <netllama@gmail.com> writes:
>  > 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
>
>  I think you're just missing an explicit cast:
>
>  ALTER TABLE data ALTER COLUMN date_created TYPE timestamp USING date_created::timestamp;
>
>  You should first check that datestyle is set to MDY to avoid
>  any confusion about the field order, but other than that I don't
>  see why you'd have a format problem.

Excellent, that worked perfectly.  thanks!


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