Writing a Rails migration to collapse booleans into an enum

Question

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 1

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.