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

user3402754 · Accepted Answer · 2017-03-05 16:58:41Z

75

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 5, 2017 at 16:58

answered Jun 30, 2016 at 8:02

user3402754

3,1052 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jared Menard Over a year ago

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.

João Souza · Accepted Answer · 2017-05-26 13:41:57Z

63

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, 2017 at 13:41

João Souza

4,2483 gold badges28 silver badges42 bronze badges

2 Comments

Stephen Over a year ago

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).

hiveer Over a year ago

for my case this answer is not working, it prompts: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type integer: "public"

jigfox · Accepted Answer · 2010-08-21 09:26:59Z

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, 2010 at 9:26

jigfox

18.2k3 gold badges62 silver badges74 bronze badges

answered Aug 21, 2010 at 9:18

Zaki

1,1019 silver badges7 bronze badges

4 Comments

dannymcc Over a year ago

Great, thanks. I presume I don't have to state the current type in the first example?

Evolve Over a year ago

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.

Josh Kovach Over a year ago

Evolve, in PG, you can do this change_column :table_name, :column_name, 'integer USING CAST(column_name AS integer)'. Source: makandracards.com/makandra/…

user3402754 Over a year ago

The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted.

stevec · Accepted Answer · 2022-12-30 02:39:04Z

1

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

answered Dec 30, 2022 at 2:39

stevec

55.2k52 gold badges314 silver badges436 bronze badges

Collectives™ on Stack Overflow

Rails Migration to convert string to integer?

4 Answers 4

1 Comment

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related