sequelize: Migration on ENUM with postgres dialect: type already exists

This is still an issue

The best way to modify enum values is to queryInterface.changeColumn:

module.exports = {
  up  : function (queryInterface, Sequelize) {
    return queryInterface
      .changeColumn(tableName, 'status', {
        type: Sequelize.ENUM('a', 'b', 'c', 'd'),
        allowNull: false
      });
  },
  down: function (queryInterface, Sequelize) {
    return queryInterface
      .changeColumn(tableName, 'status', {
        type: Sequelize.ENUM('a', 'b', 'c'),
        allowNull: false
      });
  }
};

We have QueryInterface.dropAllEnums(), I think we should add a simple dropEnum() method.

finally got it working:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query("ALTER TYPE enum_type_name ADD VALUE 'new_value'");
  },

  down: (queryInterface, Sequelize) => {
    var query = 'DELETE FROM pg_enum ' +
      'WHERE enumlabel = \'new_value\' ' +
      'AND enumtypid = ( SELECT oid FROM pg_type WHERE typname = \'enum_type_name\')';
    return queryInterface.sequelize.query(query);
  }
};

change enum_type_name and new_value to fit your needs.

is not fixed

I am facing the same issue. I was able to bypass it with using raw query

module.exports = {
  up: function (queryInterface, Sequelize) {
    return queryInterface.sequelize.query("CREATE TYPE \"enum_users_sellerAccountType\" AS ENUM(\'sa\', \'sm\'); ALTER TABLE \"users\" ADD COLUMN \"sellerAccountType\" \"enum_users_sellerAccountType\";");
  },

  down: function (queryInterface, Sequelize) {
    return queryInterface.sequelize.query("DROP TYPE \"enum_users_sellerAccountType\"; ALTER TABLE \"users\" DROP COLUMN \"sellerAccountType\";");
  }
};

Is it a bug in sequelize, that causes enum migration to fail?

This issue should definitely be reopened

@naartjie @tomprogers No, it does not seem to work on Postgres. ERROR: type "tableName_status" already exists

@Edudjr that almost worked for me, but I had to make an ever so slight change to your up function:

up: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(`ALTER TYPE "enum_type_name" ADD VALUE 'new_value'`);
}

As you can see, I had to wrap my enum name in double quotes; for whatever reason, that’s how Sequelize created my enum, so without the double quotes I got a “type does not exist” error. I’m not sure if this is a standard for Sequelize, but I figured I’d post my findings here in case anyone else ran into a similar issue. Also note that due to the use of both single and double quotes, I wrapped the query in tick marks since I’m using a new enough version of Node that supports template strings, thereby avoiding the need to escape any strings.

And for the down function, I made similar changes for readability:

down: (queryInterface, Sequelize) => {
    return queryInterface.sequelize.query(`
        DELETE 
        FROM
            pg_enum
        WHERE
            enumlabel = 'new_value' AND
            enumtypid = (
                SELECT
                    oid
                FROM
                    pg_type
                WHERE
                    typname = 'enum_type_name'
            )
    `);
}

Strangely enough, enum_type_name here in the pg_type table did not have double quotes in its actual value, unlike the type that was created.

That’s a postgres issue. Dropping an enum column does not drop the enum itself (since an enum might be used in several tables as I understand it)

Hello, this thread is very long and was closed twice already… Can someone please provide an up-to-date MVCE for this? Something that I can just copy+paste and run and see the problem. If I can easily reproduce it, I will reopen this issue. Thanks!!

This may be a bit off topic to this particular thread, but I wanted to post my solution to a similar problem incase anyone ends up here like I did.

I was trying to set an ENUM along with allowNull: true, then do a bulk update, and then changeColumn to allowNull: false

await queryInterface.addColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: true // set to true to allow bulk update
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

await queryInterface.bulkUpdate(
        'payments',
        {
          entity_id: 1,
          entity_type: SHIPPER
        },
        {},
        { transaction }
      );

await queryInterface.changeColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: false,
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

^^ This code threw the error enum_payments_entity_type already exists. Even when trying to manually drop it, alter the column, etc, I could never get it to work. My solution was to set the column as a STRING first, then come back and set it as an ENUM.

await queryInterface.addColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.STRING, // NOTE THIS IS A STRING
          allowNull: true // set to true to allow bulk update
        },
        { transaction }
      );

await queryInterface.bulkUpdate(
        'payments',
        {
          entity_id: 1,
          entity_type: SHIPPER
        },
        {},
        { transaction }
      );

await queryInterface.changeColumn(
        'payments',
        'entity_type',
        {
          type: Sequelize.ENUM,
          allowNull: false,
          values: [SHIPPER, CARRIER]
        },
        { transaction }
      );

I am not very well versed in Sequelize (or in RDMS in general) but this bug kicked my butt. Hopefully if this is helpful to someone. And if this is bad practice, feel free to downvote it and tell me why.

@janmeier: Any idea how to correctly edit enum values through migration then?

No, there is no update. Can’t answer your second question.

still not fixed 😭

@naartjie @tomprogers @jraut is not possible to “change” an enum in Postgres. You have to destroy it and create it again with the new values.

Checkout this issue https://github.com/sequelize/sequelize/issues/7151 and this comment https://github.com/sequelize/sequelize/issues/7151#issuecomment-344246084.

I created this npm package to change enums in Postgres: https://www.npmjs.com/package/sequelize-replace-enum-postgres. Hope it helps.

Good luck!

I asked this on SO here and this is something sweet and simple that seems to be doing it

down: (queryInterface, Sequelize) => queryInterface.removeColumn('TableName', 'ColumnName') .then(() => queryInterface.sequelize.query('DROP TYPE "enum_TableName_ColumnName";')); };

I think issues #2577 and #7649 cover everything mentioned in this issue already.

@kytwb

Any idea how to correctly edit enum values through migration then?

I was able to modify an ENUM by:

1. Modifying the array assigned to the values property of the configuration object passed as the second argument to sequelize.define to include a new value
2. Create a migration in which the up simply calls ModelNameHere.sync()

That generated the SQL statements required to modify the ENUM-type to match what I’d expressed in JavaScript code.

Erin