CODEWITHSUNDEEP
postgresqlruby-on-rails-4rails-migrations
Jay Killeen
Jay Killeen

Reputation: 2922

Rails Migration to convert string to integer using conversion

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?

Upvotes: 21

Views: 8145

Answers (3)

Subhash Chandra
Subhash Chandra

Reputation: 3265

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

Upvotes: 0

florish
florish

Reputation: 557

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.

Upvotes: 19

Björn Nilsson
Björn Nilsson

Reputation: 3773

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

Upvotes: 32

Related Questions