postgresql 删除并正确创建带有续集的 ENUM？

Drop and create ENUM with sequelize correctly?

提问by ZenDD

How to correctly drop and then recreate ENUM type with sequelize for Postgres in migrations? For example this migration doesn't drop enum_Users_statusenum... so any attempts to recreate/change statusvalues after they have been once created fail.

如何在迁移中为 Postgres 正确删除然后使用 sequelize 重新创建 ENUM 类型？例如，此迁移不会删除enum_Users_status枚举...因此任何在创建/更改status值后重新创建/更改值的尝试都会失败。

module.exports = {
    up: function (queryInterface, DataTypes) {
        queryInterface.createTable('Users', {
            //...
            status: {
                type: DataTypes.ENUM,
                values: [
                    'online',
                    'offline',
                ],
                defaultValue: 'online'
            }
            //...
        })
    },

    down: function (queryInterface) {
        queryInterface.dropTable('Users')
    },
}

Eventually i did manage to delete the enum type inside down, but then upmigration (which is supposed to create this statusenum from scratch) fails, saying something like public.enum_Users_statusenum type doesn't exist..

最终我确实设法删除了里面的枚举类型down，但是up迁移（应该status从头开始创建这个枚举）失败了，说像public.enum_Users_status枚举类型这样的东西不存在..

采纳答案by Abel Osorio

UPDATE: I have used this in three projects up to now, so I decided to create a npm module: https://www.npmjs.com/package/replace-enum-postgresql.

更新：到目前为止，我已经在三个项目中使用了它，所以我决定创建一个 npm 模块：https://www.npmjs.com/package/replace-enum-postgresql 。

I made a utility to do this, hope you it this helpful.

我制作了一个实用程序来执行此操作，希望对您有所帮助。

utils/replace_enum.js:

utils/replace_enum.js：

'use strict';

/**
 * Since PostgreSQL still does not support remove values from an ENUM,
 * the workaround is to create a new ENUM with the new values and use it
 * to replace the other.
 *
 * @param {String} tableName
 * @param {String} columnName
 * @param {String} defaultValue
 * @param {Array}  newValues
 * @param {Object} queryInterface
 * @param {String} enumName - Optional.
 *
 * @return {Promise}
 */
module.exports = function replaceEnum({
  tableName,
  columnName,
  defaultValue,
  newValues,
  queryInterface,
  enumName = `enum_${tableName}_${columnName}`
}) {
  const newEnumName = `${enumName}_new`;

  return queryInterface.sequelize.transaction((t) => {
    // Create a copy of the type
    return queryInterface.sequelize.query(`
      CREATE TYPE ${newEnumName}
        AS ENUM ('${newValues.join('\', \'')}')
    `, { transaction: t })
      // Drop default value (ALTER COLUMN cannot cast default values)
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            DROP DEFAULT
      `, { transaction: t }))
      // Change column type to the new ENUM TYPE
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            TYPE ${newEnumName}
            USING (${columnName}::text::${newEnumName})
      `, { transaction: t }))
      // Drop old ENUM
      .then(() => queryInterface.sequelize.query(`
        DROP TYPE ${enumName}
      `, { transaction: t }))
      // Rename new ENUM name
      .then(() => queryInterface.sequelize.query(`
        ALTER TYPE ${newEnumName}
          RENAME TO ${enumName}
      `, { transaction: t }))
      .then(() => queryInterface.sequelize.query(`
        ALTER TABLE ${tableName}
          ALTER COLUMN ${columnName}
            SET DEFAULT '${defaultValue}'::${enumName}
      `, { transaction: t }));
  });
}

and this is my examplemigration:

这是我的示例迁移：

'use strict';

const replaceEnum = require('./utils/replace_enum');

module.exports = {
  up: (queryInterface, Sequelize) => {
    return replaceEnum({
      tableName: 'invoices',
      columnName: 'state',
      enumName: 'enum_invoices_state',
      defaultValue: 'created',
      newValues: ['archived', 'created', 'paid'],
      queryInterface
    });
  },

  down: (queryInterface, Sequelize) => {
    return replaceEnum({
      tableName: 'invoices',
      columnName: 'state',
      enumName: 'enum_invoices_state',
      defaultValue: 'draft',
      newValues: ['archived', 'draft', 'paid', 'sent'],
      queryInterface
    });
  }
};

回答by shakir ullah

If you want to change/edit type enum without losing data. here is my migration code. hopefully it helps.

如果您想在不丢失数据的情况下更改/编辑类型枚举。这是我的迁移代码。希望它有帮助。

queryInterface.changeColumn(
  'table_name',
  'Column_name',
  {
    type: Sequelize.TEXT,
  },
),
queryInterface.sequelize.query('drop type enum_tableName_columnName;')
.then(() => queryInterface.changeColumn(
  'table_name',
  'column_name',
  {
    type: Sequelize.ENUM('value1','value2'),
  },
)),

回答by goud1it

Dropping the ENUM manually in downworked pretty well for me.

手动删除 ENUMdown对我来说效果很好。

module.exports = {
    up: function (queryInterface, DataTypes) {
        queryInterface.createTable('Users', {
            //...
            status: {
                type: DataTypes.ENUM,
                values: [
                    'online',
                    'offline',
                ],
                defaultValue: 'online'
            }
            //...
        })
    },

    down: function (queryInterface) {
        return queryInterface.sequelize.transaction(t => {
            return Promise.all([
                queryInterface.dropTable('Users'),
                queryInterface.sequelize.query('DROP TYPE IF EXISTS "enum_Users_status";'),
            ]);
        });
    }
};

回答by 7hibault

Elaborating on shakir ullah's post and a comment on github, here's what worked for me:

详细阐述 shakir ullah 的帖子和github 上的评论，以下是对我有用的内容：

module.exports = {
  up: (queryInterface, Sequelize) => {
    // 1. Change the type of the column to string
    return queryInterface.changeColumn('Users', 'status', {
      type: Sequelize.STRING,
    })
    // 2. Drop the enum
    .then(() => {
      const pgEnumDropQuery = queryInterface.QueryGenerator.pgEnumDrop('Users', 'status');
      return queryInterface.sequelize.query(pgEnumDropQuery);
    })
    // 3. Create the enum with the new values
    .then(() => {
      return queryInterface.changeColumn('Users', 'status', {
        type: Sequelize.ENUM,
        values: [
          'online',
          'offline',
        ],
        defaultValue: 'online'
      });
    })
  },

  // Here I made the choice to restore older values but it might not work
  // if rows were inserted with the new enum.
  // What you want to do then is up to you. Maybe lose the enum and keep
  // the column as a string.
  down: (queryInterface, Sequelize) => {
    // Do as above to restore older enum values
    return queryInterface.changeColumn('Users', 'status', {
      type: Sequelize.STRING,
    }).then(() => {
      const pgEnumDropQuery = queryInterface.QueryGenerator.pgEnumDrop('Users', 'status');
      return queryInterface.sequelize.query(pgEnumDropQuery);
    }).then(() => {
      return queryInterface.changeColumn('Users', 'status', {
        type: Sequelize.ENUM,
        values: [
          'older',
          'values',
        ],
        defaultValue: 'older'
      });
    })
  },
}