ruby-on-railspostgresqlenums

Migration error: ENUM type already exists


About versions:

I've got two model with ENUM status, when i try to run migration for second model i get this error:

PG::DuplicateObject: ERROR: type "status" already exists :
CREATE TYPE status AS ENUM ('created', 'published', 'archived'); /Ruby/Projects/Apps/alpsplease/alps_app/db/migrate/20180215204243_add_status_to_experiences.rb:3:in `up'

Caused by: ActiveRecord::StatementInvalid: PG::DuplicateObject: ERROR: type "status" already exists : CREATE TYPE status AS ENUM ('created', 'published', 'archived'); /Ruby/Projects/Apps/alpsplease/alps_app/db/migrate/20180215204243_add_status_to_experiences.rb:3:in `up'

Caused by: PG::DuplicateObject: ERROR: type "status" already exists /Ruby/Projects/Apps/alpsplease/alps_app/db/migrate/20180215204243_add_status_to_experiences.rb:3:in `up' Tasks: TOP => db:migrate (See full trace by running task with --trace)

What's the correct way to generate same ENUM type for second model or add existing type to second model? Because creating ENUM like "status_model" doesn't looks like ideal solution.

Migration code:

class AddStatusToExperiences < ActiveRecord::Migration[5.1]
  def up
    execute <<-SQL
      CREATE TYPE status AS ENUM ('created', 'published', 'archived');
    SQL

    add_column :experiences, :status, :status, default: 'created', index: true
  end

  def down
    remove_column :experiences, :status

    execute <<-SQL
      DROP TYPE status;
    SQL
  end
end

Solution

  • Since you already have a status enum type from your previous model, the solution is extremely easy and straightforward: just specify the type as :status and you're good to go. No additional CREATE TYPE or DROP TYPE in the migration.

    Just know that if you modify the enum type, all tables using it will of course be influenced in the range of possible values. Nonetheless, here's a tip in case you want to add a new value at some point: you need to disable database transactions for that. I.e.

    class AddNewStatus < ActiveRecord::Migration[5.0]
      disable_ddl_transaction! # enums cannot be altered from within a transaction
    
      def change
        execute <<-SQL
          ALTER TYPE status ADD VALUE 'foobar';
        SQL
      end
    end
    

    For more information about sharing enum types, see this post: Share enum declaration values between models

    In case you don't want to share the type (i.e. they just incidentally have the same name but a different set of possible values), you can rename the existing type via ALTER TYPE status RENAME TO experience_status and issue another another CREATE TYPE with a second name as you did before, referring to the new type for the column of the second table.