Change column type from character varying to timestamp without time zone in PostgreSQL

Question

In Rails I created a string column called open_time, but then I realized I should use the datetime type. I did:

change_column :polls, :open_time, :datetime

But it said:

PG::Error: ERROR: column "open_time" cannot be cast to type timestamp without time zone
: ALTER TABLE "polls" ALTER COLUMN "open_time" TYPE timestamp

If I just drop the string column and add new datetime column, I will lose the data stored in the string column. Alternatively, in PostgreSQL I can add a column:

ALTER TABLE polls ADD COLUMN published_time timestamp;

Ant then try to get the data from the string column like:

UPDATE polls SET published_time = strToTimeStamp(open_time);

Are there any functions I can use as strToTimeStamp to convert character varying type to timestamp without time zone?

Answer 1

.. are there any functions I can use as strToTimeStamp that can convert character varying type to timestamp without time zone type?

Use to_timestamp() to convert string data to type timestamp and alter the data type of the column in place:

ALTER TABLE tbl ALTER COLUMN col TYPE timestamp
USING to_timestamp(col, '<your pattern here>');

See:

• Alter character field to date
• Cast varchar type to date