6

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?

treaz
  • 508
  • 2
  • 6
  • 14
  • 1
    You first need to drop/remove the default value. Then you can change the data type. –  Nov 04 '14 at 15:26
  • @a_horse_with_no_name I was hoping not to go there... But your suggestion will definitely work. – treaz Nov 04 '14 at 15:38

1 Answers1

11

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228