current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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)
share|improve this question
    
Do you just want to convert a value or alter the data type of the column in the table? –  Erwin Brandstetter Feb 19 at 5:22
    
@ErwinBrandstetter alter the data type, you answered it perfectly below. –  at. Feb 19 at 7:42

1 Answer 1

up vote 1 down vote accepted

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.