How do I change a string column into a bigint?

Postgres is telling you that there is existing data in that column which it doesn't know how to convert, so it needs an ALTER statement which supplies a USING clause for the column to specify how to cast existing values.

Unfortunately, you're going to need to drop down the database-specific code to accomplish this, or use something similar to the solution suggested here:

http://webjazz.blogspot.co.uk/2010/03/how-to-alter-columns-in-postgresql.html

Edit: Here's how you might do it directly in SQL in your migration:

execute <<-SQL  ALTER TABLE ip_to_countries  ALTER COLUMN ip_number_from TYPE bigint USING ip_number_from::bigintSQL

What is in your ip_number_from column?

In any case, I would probably:

• create a new column of type bigint,
• copy the data from ip_number_from to new_column manually through rails console or a rake task,
• drop the original ip_number_from column
• rename new_column to ip_number_from

Or you could drop down to SQL, like mjtko suggested, but I'm not sure it will be any easier.

Update

I looked up what Yule is suggesting. I think it would be a bit dangerous to do all of this in one migration, since you can't really check if the casting/copying of data was successful. If you did want to do it in one migration, in your case it would look something like this:

def up  add_column :table, :new_column, :bigint  Model.reset_column_information  Model.all.each do |m|    m.update_attribute :new_column, Model.bigint_from_ip_number_from(m)  end  drop_column :table, :ip_number_from  rename_column :table, :new_column, :ip_number_fromend

You should also then add a corresponding down migration.

You can always split this up in multiple migrations and check the progress/success as you go.

I've recently read - but don't remember where - that you can't cast "string" columns to "int" columns but you can do the contrary. The cast from "int" to "string" is an irreversible migration operation.

I'll search the document where I read that and edit my post when I'll find it.

If you can, the easiest thing to do would be what Jure Triglav is suggesting. (He posted his answer before mine but I was suggesting the same thing ;) ).

[Edit] I found back where I read that: Irreversible Migration tuto.