Rails Migration to convert string to integer?

Question

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

Chidi Ekuma · Answer 1 · 2017-03-05T16:58:41.023

70

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

edited Mar 05 '17 at 16:58

answered Jun 30 '16 at 08:02

Chidi Ekuma

2,811
2
19
30

If you are using postgres, this is the correct answer, assuming that all of the values in this column are string versions of integers and can be correctly cast into integers without data loss. – Jared Menard Apr 14 '20 at 19:03

score 56 · Answer 2 · answered May 26 '17 at 13:41

56

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'

answered May 26 '17 at 13:41

João Souza

4,032
3
25
38

This answer worked best for me as well. In case anyone gets an error about, "ERROR: invalid input syntax for integer: """ make sure no empty strings exist on the column you're trying to convert to int (null/nil is fine). – Stephen Oct 01 '19 at 19:24

score 40 · Accepted Answer · edited Aug 21 '10 at 09:26

40

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.

edited Aug 21 '10 at 09:26

jigfox

18,057
3
60
73

answered Aug 21 '10 at 09:18

Zaki

1,101
9
7

Great, thanks. I presume I don't have to state the current type in the first example? – dannymcc Aug 21 '10 at 09:44
3

The first approach generated errors for me when I pushed the code to Heroku. PG::DatatypeMismatch: ERROR: column "company_id" cannot be cast automatically to type integer HINT: Specify a USING expression to perform the conversion. : ALTER TABLE "collaborators" ALTER COLUMN "company_id" TYPE integer rake aborted! StandardError: An error has occurred, this and all later migrations canceled: PG::DatatypeMismatch: ERROR: column "company_id" cannot be cast automatically to type integer HINT: Specify a USING expression to perform the conversion. – Evolve Apr 15 '14 at 07:43
31

Evolve, in PG, you can do this `change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'`. Source: http://makandracards.com/makandra/18691-postgresql-vs-rails-migration-how-to-change-columns-from-string-to-integer – Josh Kovach Sep 12 '14 at 16:56
The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted. – Chidi Ekuma Mar 05 '17 at 16:55

score 1 · Answer 4 · answered Dec 30 '22 at 02:39

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

Rails Migration to convert string to integer?

4 Answers4

Linked