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

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Lonni J Friedman <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: changing column datatype from char(20) to timestamp
Date: 2008-04-29 17:46:45
Message-ID: 20080429104232.H54920@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Bruce HyattDate: 2008-04-29 20:12:09
Subject: Re: Configuration on CentOS 4.6
Previous:From: Tom LaneDate: 2008-04-29 17:46:23
Subject: Re: changing column datatype from char(20) to timestamp

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