Search code examples
ruby-on-railspostgresqlcasting

Rails Migration - Change Integer Column to Array Integer - Postgres

I have a benefit_type integer column in Provider Model Which is a enum column.

Provider.rb

enum: ['abc', 'bcd']

Now I want to migrate to array_enum

Provider.rb

array_enum: {'abc': 0, 'bcd': 1}

So, to accommodate this change I want to change my column to array of integer. In my migration I have,

change_column :providers, :benefit_type, :integer, array: true, default: {}, using: "(string_to_array(benefit_type, ','))"

Error:

 Caused by:
PG::UndefinedFunction: ERROR:  function string_to_array(integer, unknown) does not exist
LINE 1: ...ALTER COLUMN "benefit_type" TYPE integer[] USING (string_to_...
                                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit 
type casts.

Also tried:

   change_column :providers, :benefit_type, :integer, array: true, default: []

Error:

 Caused by:
ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  column "benefit_type" cannot 
be cast automatically to type integer[]
HINT:  You might need to specify "USING benefit_type::integer[]".
: ALTER TABLE "providers" ALTER COLUMN "benefit_type" TYPE integer[], ALTER COLUMN 
"benefit_type" SET DEFAULT '{}'
Solution

  • You need to specify integer array with column name in using keyword.

    change_column :providers, :benefit_type, :integer, array: true, default: [], using: 'ARRAY[benefit_type]::INTEGER[]'