How do I change a string column into a bigint?

Question

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.

Answer 1

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