Add extra value to Enum type in postgres rails

Question

I am running the given below migation to add an extra value to my blood enum.

class AddTypesToBlood < ActiveRecord::Migration[5.2]
  def up
    execute <<-SQL
      ALTER TYPE blood ADD ATTRIBUTE 'NA';
    SQL
  end

  def down
    execute <<-SQL
      ALTER TYPE blood DROP ATTRIBUTE 'NA';
    SQL
  end
end

But the above migration is throwing error. Pease have look to terminal screen shot for error.

Answer 1

Since you're going the route of adding a new enum value through SQL, I'm going to guess that you defined the column to be an enum also through SQL. So using Postgres's Documentation:

ALTER TYPE name ADD VALUE new_enum_value [ { BEFORE | AFTER } existing_enum_value ]

Give this a shot:

def up
  execute <<-SQL
    ALTER TYPE blood ADD VALUE 'NA';
  SQL
end

def down
  execute <<-SQL
    ALTER TYPE blood DROP VALUE 'NA';
  SQL
end

Another way of going about enums in Rails is by having the enum defined in the model. So what you can do is have your model have an attribute (called blood) be of type integer. Then in the model you can do:

class Model < ApplicationRecord
  enum blood: [
    :A,
    :B,
    :AB,
    :O,
    :NA
  ]
end

This way when you want to modify the values of the enum, you don't have to create another migration. Another benefit to doing it this way is you get to use strings (or symbols) as the value for the enum. I.e.:

# Both work
model.update_attributes(blood: 'O')
model.update_attributes(blood: :O)

And when you access the blood attribute, you still get a string back:

puts model.blood # => "O"

Answer 2

If you take a look at the documentation, you'll see that the syntax is

ALTER TYPE blood ADD VALUE 'NA';

There is no way to drop a value from an enum type in PostgreSQL, so you won't be able to undo that change.

If an attribute has a value range that changes, don't use enum types for it.

Answer 3

The accepted answer cannot work since there's no such thing as DROP VALUE (and there wasn't in previous Postgres versions)

The steps are basically:

1. CREATE new enum TYPE (new_enum)
2. (optional, if your column has a default) DROP old_enum's type default on the referencing table
3. ALTER column of the referencing table to use new_enum (and if applicable, to have new_enum as default value)
4. DROP old_enum
5. RENAME new_enum to old_enum (if you want to keep the old name for the TYPE)

Here's an example of the fundamental steps:

https://www.munderwood.ca/index.php/2015/05/28/altering-postgresql-columns-from-one-enum-to-another/

Answer 4

Based on Rich Steinmetz's answer, here is what my reversible migration for something like this looks like.

This assumes you have a Human model with a blood_type column which uses the blood type with four possible values (A, B, AB and O) and a default value of O, and you are trying to add NA as a value.

class AddTypeToBlood < ActiveRecord::Migration[6.1]
  def up
    execute <<-DDL
      ALTER TYPE blood ADD VALUE 'NA';
    DDL
  end

  def down
    execute <<-DDL
      CREATE TYPE old_blood AS ENUM (
        'A', 'B', 'AB', 'O'
      );
    DDL

    add_column :humans, :old_blood_type, :old_blood, null: false, default: "O"

    Human.where(blood_type: "NA").update_all(blood_type: "O")
    Human.all.each { |human| human.update(old_blood_type: human.blood_type) }

    remove_column :humans, :blood_type
    rename_column :humans, :old_blood_type, :blood_type

    execute <<-DDL
      DROP TYPE blood;
      ALTER TYPE old_blood RENAME TO blood;
    DDL
  end
end

NB: If you want to change the default value for the blood_type column to NA, you must do so in a separate migration.