Writing a Rails migration to collapse booleans into an enum

I made a mistake when modeling my data and created a model with 4 booleans, only one of which are active at a time. I want to create a column of Postgres enum type, and then set the value based on which boolean flag was previously set.

I know I want to start with something like this:

class ChangePositionTypeToBeEnumInPosition < ActiveRecord::Migration[6.0]
  def change

    reversible do |change|
      change.up do
        execute <<-SQL
          CREATE TYPE position_type AS ENUM ('chair', 'jboard', 'eboard', 'aboard');
        SQL

        # TODO: Execute code to create a new column and set values based off of existing values in the same row
        # TODO: Drop the 4 boolean columns
      end

      change.down do
        # TODO: Create the 4 boolean columns
        # TODO: Set one to true depending on the enum state

        execute <<-SQL
          DROP TYPE position_type;
        SQL
      end
    end
  end
end

My question is: What can I do where my TODO comments are? Previously I wrote a migration to go from an integer to a Postgres enum in my User model, and my change_column code looked like this:

        change_column :users, :member_type, <<-SQL.strip
          member_status USING 
          CASE member_type
                WHEN '0'  THEN 'inactive'::member_status
                WHEN '1'  THEN 'active'::member_status
                WHEN '2'  THEN 'suspended'::member_status
          END
        SQL

I imagine that the code I’m trying to write is some variation of this with a flurry of if statements. Any input would be appreciated.

Answer

Although you have suggested the approach yourself in question, but here is the solution anyway.

Based on this article. You can try something like below:

class ChangePositionTypeToBeEnumInPosition < ActiveRecord::Migration[6.0]
  def change

    reversible do |change|
      change.up do
        execute <<-SQL
          CREATE TYPE position_type_enum AS ENUM ('chair', 'jboard', 'eboard', 'aboard');
        SQL
        add_column :positions, :position_type, :position_type_enum
        # You may also want to add index on this column

        # If this table's size is huge(10m-20m+), this is not a good idea in that case.
        execute <<-SQL
          WITH cte AS
          ( 
            SELECT  id,
                    CASE
                      WHEN chair = true THEN 'chair'::position_type_enum
                      WHEN jboard = true THEN 'jboard'::position_type_enum
                      WHEN eboard = true THEN 'eboard'::position_type_enum
                      ELSE 'aboard'::position_type_enum
                    END AS position_type
            FROM positions
          )
          UPDATE positions p
          SET position_type = c.position_type
          FROM cte c
          WHERE p.id = c.id;
        SQL

        remove_column :positions, :chair, :boolean
        remove_column :positions, :jboard, :boolean
        remove_column :positions, :eboard, :boolean
        remove_column :positions, :aboard, :boolean
      end

      change.down do
        add_column :positions, :chair, :boolean
        add_column :positions, :jboard, :boolean
        add_column :positions, :eboard, :boolean
        add_column :positions, :aboard, :boolean

        execute <<-SQL
          UPDATE positions
          SET chair = CASE WHEN position_type = 'chair' THEN true ELSE false END,
              jboard = CASE WHEN position_type = 'jboard' THEN true ELSE false END,
              eboard = CASE WHEN position_type = 'eboard' THEN true ELSE false END,
              aboard = CASE WHEN position_type = 'aboard' THEN true ELSE false END
        SQL

        remove_column :positions, :position_type
        execute <<-SQL
          DROP TYPE position_type_enum;
        SQL
      end
    end
  end
end

I have not tried to run this code, you should correct any typos you may find(and comment here as well so i can update answer).

Please also read the article i have linked above for proper usage of postgres enums on models.