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, Oct 18 '15 at 02:11

score 31 · Accepted Answer · answered Mar 14 '14 at 01:27

31

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 '14 at 01:27

Björn Nilsson

3,703
1
24
29

Doesn't seem to work with blank strings. `invalid input syntax for integer: ""` – dazonic Jul 08 '14 at 00:26
Well, that makes sense. What integer would represent a blank string? – Frans Jul 06 '15 at 11:58
Does this reset the index? After performing such a migration I'm getting wrongly ordered queries. – Michael Johnston Feb 23 '17 at 20:52
You should specify an explicit ORDER BY if you care about ordering – Björn Nilsson Feb 24 '17 at 12:05

score 19 · Answer 2 · answered Oct 06 '14 at 13:01

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 06 '14 at 13:01

florish

557
5
10

1

Works like a charm. I had the same issue - the migration from http://stackoverflow.com/a/22394425/1513205 works great on an empty database - but our database already have empty string values in it. – watts Feb 11 '16 at 18:17

score 0 · Answer 3 · answered Apr 15 '16 at 13:02

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

Rails Migration to convert string to integer using conversion

3 Answers3