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.
You can use BIGINT as a primary key but with some penalties. BIGINT takes up more space on disk storage than INT and using BIGINT as a primary key (or any index) will add size to the index, perhaps as much as doubling it. This can have a performance impact on searching the index and make it slower to run queries.
The BigInt data type in SQL Server is the 64-bit representation of an integer. It takes up 8 bytes of storage. It can range from -2^63 (-9,223,372,036,854,775,808) to 2^63 (9,223,372,036,854,775,807). Two raised to the power of sixty-three is about nine quintillion, a very big number.
First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.
The int data type is the primary integer data type in SQL Server. The bigint data type is intended for use when integer values might exceed the range that is supported by the int data type. bigint fits between smallmoney and int in the data type precedence chart.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With