Rails Migration - Change Integer Column to Array Integer - Postgres

user_6285605 0
Viewed 1946

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 '{}'
casting postgresql ruby-on-rails
3 Answers

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[]'
user_61381190

Initially you have to remove column defaults if you have and You need to specify integer array with column name that your'e needed and it defaults whenever it required.

change_column_default :table_name, :column_name, from: default_value, to: [default_value]
change_column :providers, :benefit_type, :integer, array: true, default: [], using: 'ARRAY[benefit_type]::INTEGER[]'
user_137902940

You can use this to convert existing column with integer type to an integer array

change_column :providers, :benefit_type, 'integer[] USING ARRAY[benefit_type]::INTEGER[]', array: true, null: false, default: []
user_10616640
Related