How to change row column in bigint?

Question

In the migration of rails. How to change row type column to bigint?

I have:

t.change :ip_number_from, :integer, :limit => 8 

I get:

 PG::Error: ERROR: column "ip_number_from" cannot be cast to type bigint 

I even tried with two alternatives:

 change_column :ip_to_countries, :ip_number_from, :integer, :limit => 8 change_column :ip_to_countries, :ip_number_from, :bigint 

Still the same error.

Answer 1

Postgres tells you that there is existing data in this column that it does not know how to convert, so it needs an ALTER statement that provides a USING clause for the column to indicate how to distinguish existing values.

Unfortunately, for this you will need to reset the code specific to the database, or use something similar to the solution proposed here:

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

Edit: here is how you can do it directly in SQL while wrapping:

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

Answer 2

What is in the ip_number_from column?

In any case, I would probably:

• create a new bigint column,
• copy data from ip_number_from to new_column manually through the rails console or the rake command,
• remove source ip_number_from column
• rename new_column to ip_number_from

Or you could go down to SQL, as mjtko suggested, but I'm not sure if this will be easier.

Update

I looked what Yul offers. I think it would be a little dangerous to do all this in one migration, since you cannot check if the casting / copying of data was successful. If you want to do this in one go, in your case it will 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_from end 

Then you must add the corresponding migration down.

You can always divide this into several migrations and check the progress / success as you progress.

Answer 3

I recently read - but I don’t remember, somewhere - that you cannot use row columns in "int" columns, but you can do the opposite. Casting from "int" to "string" is an irreversible migration operation.

I will look for a document where I read it and edit my post when I find it.

If you can, the simplest thing is what Jura Triglav offers. (He sent his answer earlier than mine, but I offered the same thing;)).

[Edit] I found where I read this: Irreversible migration .