Change column type from character varying to timestamp without time zone
datetime, postgresql, ruby-on-rails-3, types
Solution
.. 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
Problem
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`?