I received this error when trying to alter a column of type time
to type timestamp
:
PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone
It's not important to me how the existing times are converted, but I do need this column to be changed. How can I force this or cast this?
This is the SQL statement:
ALTER TABLE "students" ALTER COLUMN "time_since_missing_schedule_notification" TYPE timestamp USING CAST(time_since_missing_schedule_notification AS timestamp)
I'm using Rails/ActiveRecord and this was the Ruby code that generated the above SQL statement:
change_column :students, :time_since_missing_schedule_notification, 'timestamp USING CAST(time_since_missing_schedule_notification AS timestamp without time zone)'
When I connect to Heroku's PostgreSQL server, I get these versions:
psql (9.3.1, server 9.2.7)
Best Answer
Since time does not have a
date
component you need to provide a date. You can just adddate
andtime
values in theALTER TABLE
statement: