[FIXED] How do I change a string column into a bigint?

Issue

In rails migration. How do I change a string type column to a 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 the 2 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.

Solution

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::bigint
SQL

Answered By - mjtko

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)