5

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)
at.
  • 167
  • 1
  • 1
  • 3

1 Answers1

8

Since time does not have a date component you need to provide a date. You can just add date and time values in the ALTER TABLE statement:

ALTER TABLE students ALTER COLUMN time_since_missing_schedule_notification
 TYPE timestamp USING ('2000-1-1'::date + time_since_missing_schedule_notification)
Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633