How to Change Column Type in Heroku

How do I change column type in Heroku?

Do the following:

1. rename the column A
2. create the new column B as date
3. move the data from A to B
4. remove A

In other words

def self.up
  rename_column :contacts, :date_entered, :date_entered_string
  add_column :contacts, :date_entered, :date

  Contact.reset_column_information
  Contact.find_each { |c| c.update_attribute(:date_entered, c.date_entered_string) } 
  remove_column :contacts, :date_entered_string
end

Change Heroku Postgres database column type while retaining data

Yes, you can. Use following query to do that:

ALTER TABLE $TABLE_NAME ALTER COLUMN $COLUMN_NAME TYPE <float type, e.g. float(64)>;

Cannot change column type during migrating to heroku

I quote the manual about ALTER TABLE:

A USING clause must be provided if there is no implicit or assignment
cast from old to new type.

What you need is:

ALTER TABLE messages ALTER COLUMN sender_id TYPE integer

This works with or without data as long as all entries are convertible to integer.

If you have defined a DEFAULT for the column, you may have to drop and recreate that for the new type.

Here is blog article on how to do this with ActiveRecord.

Error on Heroku whenever I try to change database column type

Your error message is coming from PostgreSQL but you are developing using MySQL.

It's a very good idea to use the same database technology in development and production (as well as testing, staging, and any other environments you may have). This will minimize surprises when you move from one environment to the next, e.g. when you deploy your code to Heroku.

I recommend one of the following two courses of action:

• Switch to PostgreSQL on your development machine.

  This will probably cause your migration to fail locally, but that's a good thing! Now you can troubleshoot and fix the error in development, which is much better than dealing with issues that pop up in production.

  In this case the problem is linked to the current type of your price column. PostgreSQL isn't able to convert that data type to a float by itself and needs some help from you.

• Switch to MySQL on Heroku.

  Heroku uses PostgreSQL out of the box, but it also supports many other data stores. Pick one of the MySQL providers and use it instead of PostgreSQL.

In both cases, try to match the version number of the database provider.

Either of these will work, but my strong preference would be the first option. MySQL is a bit too fast and loose with data types for my liking. If you choose to go with MySQL take a close look at your data before and after running your migration locally to make sure it is doing "the right thing".

Edit: It looks like you're actually using SQLite on your local machine. That won't work on Heroku due to its ephemeral filesystem. You'll have to use a client-server database on Heroku, and I strongly recommend using the same one on your local development machine.

Error on Heroku trying to change DB column Type

The issue is, as I think, that you already have in Heroku database some values of test_type, which are string, and which can not be changed to boolean.

There are two options to overcome the issue.

First: change the migration to:

change_column :scores, :test_type, 'boolean USING CAST(test_type AS boolean)'

and run rake db:migrate.

Second (I would prefer this one more) is to drop the Heroku database and run the migrations again (in the form you wrote it at the very beginning, since there would be no need to cast anything).

Updating columns in heroku production vs local

You can make use of find_or_create_by. Just create another migration and loop over the seeds. As for heroku this will be a safer way to change seeds without losing production data.

class AddPriceToItemSeeds < ActiveRecord::Migration
  def change
    items= [
      {name: "Kale", type: "veggie", price: 2},
      {name: "Apple", type: "fruit", price: 1},
      {name: "MF Kombucha", type: "drink", price: 10000},
      {name: "Cider", type: "drink", price: 2},
      {name: "Carrot", type: "veggie", price: 1}
    ]
    items.each do |item|
      Item.find_or_create_by(name: item[:name], type: item[:type]) do |record|
        record.price= item[:price]
      end
    end
  end
end

---

Related Topics