Issue with Running Rails Migration in Heroku

Question

I created a migration in my development to change the column type from integer to date in my client table.

Migration:

class UpdateGradToDate < ActiveRecord::Migration
  def change
    change_column :clients, :grad_id, :date
  end
end

Which worked fine in development. However, once I push it to Heroku, I got the following error message.

PG::DatatypeMismatch: ERROR:  column "grad_id" cannot be cast automatically to type date
HINT:  You might need to specify "USING grad_id::date".
: ALTER TABLE "clients" ALTER COLUMN "grad_id" TYPE date

I'm not sure how to correct this problem. I'm thinking perhaps using rails console and changing the column type there but I'm not sure if that's the best solution.

I also tried creating a new migration using using this syntax as well

class ChangeDateFormatInMyTable < ActiveRecord::Migration
  def up
   change_column :clients, :grad_id, :integer
  end

  def down
   change_column :clients, :grad_id, :date
  end
end

I'm not really sure what to do at this point.

How could grad_id possibly be a date? Why not just add a new column that makes sense as a date? — Robert Nubel
– Robert Nubel, Commented Jun 24, 2016 at 19:52

Simone Carletti · Accepted Answer · 2016-06-24 18:04:05Z

2

The issue here is clearly explained by the PostgreSQL error:

column "grad_id" cannot be cast automatically to type date

You are trying to convert a field that stores an integer, into a Date. The field could contain anything, including a value such as 10, 0 or -200. How those value could ever be converted into a Date?

What PostgreSQL is telling you is that you need to help him with the conversion.

Your best way is:

rename grad_id into grad_id_legacy
create a new grad_id Date field
run a script that reads each value from grad_id_legacy and stores the corresponding, converted Date value into grad_id

answered Jun 24, 2016 at 18:04

Simone Carletti

177k50 gold badges371 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Nubel Over a year ago

I would suggest reversing the order of your steps, i.e. create grad_id_date and then later rename it to grad_id, in order to minimize downtime. It's also possible to use a raw SQL ALTER TABLE statement with a USING clause to tell Postgres how to do the conversion, but it will lock the table for the duration of that process.

Collectives™ on Stack Overflow

Issue with Running Rails Migration in Heroku

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related