Rails Migration - Change Integer Column to Array Integer - Postgres

Question

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 '{}'

Lifton

I have used this using: 'ARRAY[benefit_type]::INTEGER[]'. Maybe that can solve your purpose. – Harleigh

@PraveshKhatri I am getting this ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR: default for column "benefit_type" cannot be cast automatically to type integer[] – Lifton

I have tried it, Its working fine in my environment using rails 5.2 change_column :providers, :benefit_type, :integer, array: true, default: [], using: 'ARRAY[benefit_type]::INTEGER[]' This is the full syntax. – Harleigh

@PraveshKhatri Please add your comment as answer. I will accept it. THIS WORKED :) I just had to remove my old default value. – Lifton

Answer 1

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

Harleigh

I just had to remove my old default value and this solution worked. – Lifton

Answer 2

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

Imperator

Hi, it appears that you copied this answer from another answer in the same thread. You're using someone else's work without giving the author credit. This amounts to plagiarism, and is not welcome on Stack Overflow. Remember to always add prominent attribution when using other sources. Thanks! Btw, here is the source of this comment template :) – Oxpecker

Hi @ArdentCoder, I faced the same issue today and fixed the above issue using removing column defaults. so i added text describing to remove column defaults if already added. Then to proceed with change_column. This is the right way. – Imperator

@ArdentCoder, Very sorry this is my 1st answer. i don't know how to explain. Now i had changed my solution. Is this fine?. This is what i fixed in my issue in development today. – Imperator

No worries, I noticed that you were new here and didn't flag or downvote your answer. Instead, I politely communicated the matter with you and you have rectified the error. The downvote wasn't there until I put that comment, so here you get my upvote to bring the net vote count back to where it was, and hopefully this will keep you motivated to contribute more good quality answers :) – Oxpecker

Answer 3

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: []

Alek