Change column type from varchar to timestamp

Question

I have a table that is defined like this:

CREATE TABLE session_requests
(
  id character varying(255) NOT NULL,
  authorization_enc character varying(255),
  auto_close integer,
  date_created character varying(255) DEFAULT '1970-01-01 01:00:00'::character varying,,
....
)

I'm trying to do

alter table session_requests alter column date_created type timestamp using date_created::timestamp;

the error that I'm getting is

ERROR:  default for column "date_created" cannot be cast automatically to type timestamp

Anyone has any suggestions?

Answer 1

Do it in one transaction. You can even do it in a single statement:

ALTER TABLE session_requests
  ALTER date_created DROP DEFAULT
 ,ALTER date_created type timestamp USING date_created::timestamp
 ,ALTER date_created SET DEFAULT '1970-01-01 01:00:00'::timestamp;

SQL Fiddle.

Aside: character varying(255) is almost always a bad (pointless) choice in Postgres. More:

• Refactor foreign key to fields