August 10, 2020

Changing Data Column Type in Rails App to Integer From String

Sometimes you start building your app and the easiest thing is to build out your models using the string type, which becomes VARCHAR in SQL speak. But then as you start coding, you realize it would be better to have some of those columns as integers. That's what happened to me on a recent app where I later decided the most efficient way to store the data I wanted was an integer that represented a specific value.

So I needed to change the column in my database from string to integer.

But as you will see with changing to an integer, there is an extra step from the typical column change.

So first start with creating the migration. I am using a fictitious table called "things."

$ rails generate migration change_column_to_be_integer_in_things

Now, typically a change in data type would look something like this.

def change
  change_column :my_table, :target_column, :integer

But that won't work when changing from a string to something numeric such as integer. You will see an error when trying to migrate that looks like this:

PG::DatatypeMismatch: ERROR: column "target_column" cannot be cast automatically to type integer

So you have to use the CAST argument as such:

def change
  change_column :my_table, :target_column, 'integer USING CAST(target_column AS integer)'

What is CAST? Simply put, it's an SQL Conversions Function used to convert an expression from one data type to another.

Once you update the migration to use CAST, your migration should run fine and you will have converted a string into an integer.