Rails 3 migration now fail in rails 5

Question

I have this migration where I convert a column from integer to an array of string.

class ChangeWdayFromIntegerToStringInResourceWeekDayStart < ActiveRecord::Migration[4.2]
  def up
    change_column :resource_week_day_starts, :wday, :string, default: []
    add_column :resource_week_day_starts, :number_days, :integer, default: 7
  end

  def down
    change_column :resource_week_day_starts, :wday, :string, default: nil
    change_column :resource_week_day_starts, :wday, 'integer USING CAST(wday AS integer)'
    remove_column :resource_week_day_starts, :number_days
  end
end

This migration works pretty well when we where in rails 3, but we have migrate to rails 5 and now we try to setup a new server. When running the migration in rails 5 we got this error message:

PG::DatatypeMismatch: ERROR:  column "wday" cannot be cast automatically to type character varying[]
HINT:  You might need to specify "USING wday::character varying[]".
: ALTER TABLE "resource_week_day_starts" ALTER COLUMN "wday" TYPE character varying[]
/home/ruby/src/mapsbooking/db/migrate/20170307000000_change_wday_from_integer_to_string_in_resource_week_day_start.rb:3:in `up'

I have try many ways to fix this up. But nothing works.

Can somebody help me

Thanks

Answer 1

You have three problems:

1. As max says in the comments, you need to include array: true in the options so that you get an array column.
2. You need an SQL expression to convert a single integer to an array of strings so that you can include a suitable USING clause in the ALTER TABLE.
3. change_column wants to change the type and the default separately.

(1) is easy, add array: true to the change_column options.

(2) is a little harder but a couple options come to mind. You could use the element-to-array concatenation operator and a type cast:

wday::varchar || array[]::varchar[]

:: is a type cast, || is the concatenation operator, and array[] is an empty array. Or, if that's too much punctuation, you could use the array_append function to do the same thing:

array_append(array[]::varchar[], wday::varchar)

(3) can be dealt with by dropping the old default with a change_column_default call before the change_column.

Putting them together:

change_column_default :resource_week_day_starts, :wday, nil
change_column :resource_week_day_starts,
              :wday,
              :string,
              array: true,
              default: [],
              using: 'array_append(array[]::varchar[], wday::varchar)'

This could leave you with array[null] values in wday if you currently have nulls inwday`. You can clean those up after if necessary.