updating the database with single migration?

Question

I have a table that has "projects" that have this field workspace which takes an array of strings. So I have converted this to store a single character

 create table("projects") do
  add(:name, :string, null: false)
  add(:title, :string, null: false)
  add(:workspaces, {:array, :string})

  timestamps()
end

I have written this migration to store a single string

alter table(:projects) do
      modify(:workspace, :string)

I want to run this migration but also I want to update all the data which is stored in the database. I need to update all the workspace data to store a single string.

Suppose if workspace has this value {value1, value2}. It will look for value1 and if that is found it will store the value1 otherwise it will store value2

This is how {value1, value2} it is stored in the database table. So when we load it in elixir it looks something like this ["value1", "value2"]

What can I do here?

Answer 1

If I understood correctly, you want to change the workspaces array column to a workspace string column that contains the first element of the array. If that is the case you can do something like this in a migration:

# Add the `workspace` column:
alter table(:projects) do
  add(:workspace, :string)
end

# Set the value of the new column:
execute("""
  UPDATE projects SET workspace = CASE
    WHEN 'value1' = ANY(workspaces) THEN 'value1'
    WHEN 'value2' = ANY(workspaces) THEN 'value2'
    ELSE 'default_value'
    END
  """)

# Remove the old column
alter table(:projects) do
  remove(:workspaces)
end