CODEWITHSUNDEEP
ruby-on-railsdatabasestringmigrationinteger
dannymcc
dannymcc

Reputation: 3814

Rails Migration to convert string to integer?

Is it possible to change a field that's a string to an integer without clearing the data already entered?

The current db structure for the table in question is:

create_table :people do |t|
t.string :company_id

Is this possible using migrations?

I'm thinking maybe in the migration drop the old field, create a new one that's an integer - but I'm worried this will clear all of the data already entered.

Thanks,

Danny

Upvotes: 41

Views: 34607

Answers (4)

stevec
stevec

Reputation: 52268

I had some empty string values to deal with, so I first convert them to nil before casting to integer. Here's what worked nicely for me:

class ChangeOrderLeadTimeFromStringToInteger < ActiveRecord::Migration[6.0]

  def up
    Product.where(order_lead_time: '').update_all(order_lead_time: nil)
    change_column :products, :order_lead_time, 'integer USING CAST(order_lead_time AS integer)' 
    change_column_null :products, :order_lead_time, true
  end

  def down
    change_column :products, :order_lead_time, :string
    change_column_null :products, :order_lead_time, true 
  end

end

Upvotes: 1

Jo&#227;o Souza
Jo&#227;o Souza

Reputation: 4208

The other answers are correct, yet you can take one step further with the :using keyword:

change_column :people, :company_id, :integer, using: 'company_id::integer'

Upvotes: 62

user3402754
user3402754

Reputation: 3085

Don't drop the column, use this

change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'

The "hint" you got from PostgreSQL basically tells you that you need to confirm you want this to happen, and how data should be converted. To confirm the changes, use the block above in your migration

Upvotes: 74

Zaki
Zaki

Reputation: 1101

Do not drop the column, it will clear the data.

You can however try

change_column :people, :company_id, :integer

and if all values in company_id can be converted to integer, it should be fine.

If that is not the case (ie not all string can be converted by default), then you can do it in two steps: 1) create a new column, then load the company_id in there after some conversion. 2) drop company_id then rename the new column.

You should be careful with both methods (more so for the second one) and you should probably do it first on a copy of the database, if you can.

Upvotes: 40

Related Questions