Rails Migration to convert string to integer using conversion

Question

There is a good question here I want to elaborate on. I am trying to convert a column in my database form a string to an integer.

I thought the conversion would be pretty straight forwrad. Currently my strings are

["10", "12", "125", "135", "140", ...]

My migration file includes:

def change
    change_column :table_name, :product_code, :integer
end

Rails tries this but Postgresql thows back an error.

PG::Error: ERROR: column "product_code" cannot be cast automatically to type integer
HINT: Specify a USING expression to perform the conversion.

I am not sure how I use this 'USING' expression in my rails migration.

So I thought the conversion would be pretty straight forward. What should I use as the USING expression?

I have been reading a book about SQL and now am shaking my head. Ofcourse you can't just convert strings to integers without using some SQL statements to say how you want to CAST it. Wish I knew all about SQL a long time ago. — Jay Killeen
– Jay Killeen, Commented Oct 18, 2015 at 2:11

Björn Nilsson · Accepted Answer · 2014-03-14 01:27:32Z

32

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

Source: http://makandracards.com/makandra/18691-postgresql-vs-rails-migration-how-to-change-columns-from-string-to-integer

answered Mar 14, 2014 at 1:27

Björn Nilsson

3,7831 gold badge26 silver badges29 bronze badges

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

4 Comments

dazonic Over a year ago

Doesn't seem to work with blank strings. invalid input syntax for integer: ""

Frans Over a year ago

Well, that makes sense. What integer would represent a blank string?

Michael Johnston Over a year ago

Does this reset the index? After performing such a migration I'm getting wrongly ordered queries.

Björn Nilsson Over a year ago

You should specify an explicit ORDER BY if you care about ordering

florish · Accepted Answer · 2014-10-06 13:01:54Z

19

Adjusted code to support converting blank strings, too:

change_column :table_name, :product_code, 
  "integer USING NULLIF(product_code, '')::int"

Empty string becomes NULL, which becomes 0 on type conversion, which is probably the best you can do in this situation.

answered Oct 6, 2014 at 13:01

florish

5575 silver badges10 bronze badges

1 Comment

watts Over a year ago

Works like a charm. I had the same issue - the migration from stackoverflow.com/a/22394425/1513205 works great on an empty database - but our database already have empty string values in it.

Subhash Chandra · Accepted Answer · 2016-04-15 13:02:43Z

0

When you write Rails migrations to convert a string column to an integer you'd usually write like this:

change_column :table_name, :column_name, :integer

You might get this:

PG::DatatypeMismatch: ERROR:  column "column_name" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

The "hint" basically tells you that you need to confirm you want this to happen, and how data shall be converted. Just say this in your migration:

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

answered Apr 15, 2016 at 13:02

Subhash Chandra

3,2851 gold badge30 silver badges30 bronze badges

Collectives™ on Stack Overflow

Rails Migration to convert string to integer using conversion

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related