How to Describe an Enumeration Column in a Rails 3 Migration

How do I describe an enumeration column in a Rails 3 migration?

Rails 4.1 contains enum for now!

You can write just

class User < ActiveRecord::Base
  enum status: [ :admin, :user, :banned ]
end

For migration write

t.integer :status

Rails 3 & 4.0

Best solution in my opinion is simple_enum gem.

Migration to add enum column in rails3 and enumerated_attribute

I found a solution. This works:

add_column :client_params, :usage_reports_access, "ENUM('value1','value2', 'value3') DEFAULT 'value1'"

How to use enum in a Postgres database in Rails 3?

Rails does not support the ENUM datatype out of the box. This is because not all databases support it that datatype. I found that a common way of dealing with ENUM values is to manually create the enum column in your database (PostgreSQL in your case), and deal with it as a string column in your Rails application. Then, use the validates_inclusion_of validator to force the use of the allowed values.

validates_inclusion_of :gender, :in => [ "male", "female" ]

And use native SQL in your migration to add the enum field:

class AddEnumType < ActiveRecord::Migration
  def up
    execute ".." # your native PostgreSQL queries to add the ENUM field
  end
end

edit (June 2014)

Rails 4.1 now supports enums. The validates_inclusion_of can now be changed to:

enum gender: [ :male, :female ]

(However, this is still not natively supported by the underlying database, so the native SQL migration is still needed.)

In Rails 4, how do I reference an enum value when initializing my model?

TLDR:

class OrderItem < ApplicationRecord
  self.inheritance_column = 'definitely_not_type'
  enum type: { data: "Data", product: "Product" }

type is the default for the inheritance_column in Rails. This column is mainly used for Single Table Inheritance. When it is present ActiveRecord will use the values in this column as the class for each row it fetches from the database.

So given the table animals with these values:

id | type   | name
------------------------
1  | Cat    | Mr Mittens
2  | Dog    | Laika
3  | Snake  | Snek

When you call Animal.all.map {|m| m.class} you will get [Cat, Dog, Snake]. Or at least you will if those contants actually exist.

If you're not actually using STI and want to use the name type you can just assign whatever you want to self.inheritance_column.

When using Postgres Enum types with Rails, specifying CREATE TYPE xyz_setting AS ENUM in your migration breaks your db/schema.rb file

Custom enum types in PostgreSQL (Rails 7+)

Rails 7 added support for custom enum types in PostgreSQL.

So now, a migration can be written as follows:

# db/migrate/20131220144913_create_articles.rb
def up
  create_enum :article_status, ["draft", "published"]

  create_table :articles do |t|
    t.enum :status, enum_type: :article_status, default: "draft", null: false
  end
end

# There's no built in support for dropping enums, but you can do it manually.
# You should first drop any table that depends on them.
def down
  drop_table :articles

  execute <<-SQL
    DROP TYPE article_status;
  SQL
end

It is also worth mentioning, when you use create_enum, the enum definitions and enum columns will be presented in schema.rb.

Sources:

• Pull Request.
• Rails Guides.
• Rails API.
• Rails 7 adds better support for custom enum types in PostgreSQL by Saeloun.

Rails3: Defining enum and using it as a custom type for db column

Have you considered using the built-in enumeration support in your database? Lots of common RDMBSes have enum support, such as Postgres (see http://www.postgresql.org/docs/9.1/static/datatype-enum.html) and MySQL (see http://dev.mysql.com/doc/refman/5.5/en/enum.html). With that, you can directly create the type in your data store and then use it via one of the ActiveRecord plugins (such as enum_type for Postgres: https://github.com/riscfuture/enum_type).

Alternatively, you could use something like active_enum to structure the enumeration as you described and store fields as integers in the database.

Writing a Rails migration to collapse booleans into an enum

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.

Problem when adding a MySQL enum column via ruby Rails migration

SQLite3::SQLException -- you are executing this query on a SQLite database, not MySQL. Are you using the correct environment?

---

Related Topics