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.
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"
DROP VALUE currently and there wasn't in postgres version 9.1 (that's referenced). So, you will get a syntax error if you try to run something like this as part of a migration.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:
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/
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.
VALUE as ALTER TYPE blood ADD VALUE 'NA';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.
blood? And how did you define it to be an enum?