sequelize: Error with Sequelize.sync and ENUMs: enum label already exists

I thought this was the same as #546, but I think it may be a new issue. When I start up my app I am receiving the following errors from Postgres using Sequelize 2.0.0 beta:

15:11:47 db.1   | ERROR:  enum label "pending" already exists
15:11:47 db.1   | STATEMENT:  ALTER TYPE "enum_users_status" ADD VALUE 'pending' BEFORE 'active'
15:11:47 db.1   | ERROR:  enum label "active" already exists
15:11:47 db.1   | STATEMENT:  ALTER TYPE "enum_users_status" ADD VALUE 'active' BEFORE 'inactive'
15:11:47 db.1   | ERROR:  enum label "inactive" already exists
15:11:47 db.1   | STATEMENT:  ALTER TYPE "enum_users_status" ADD VALUE 'inactive' AFTER 'active'

The model in question looks like

module.exports = User = db.define("user", {
    display_name: Sequelize.STRING
    avatar_url: Sequelize.STRING
    role: 
        type: Sequelize.ENUM
        values: ["basic", "admin"]
        allowNull: false
        defaultValue: "basic"
        validate:
            isIn: [['basic', 'admin']]
    status: 
        type: Sequelize.ENUM
        values: ["pending", "active", "inactive"]
        allowNull: false
        defaultValue: "pending"

})

I’ve tried running the sync with sequelize.sync({force: true}); and sequelize.sync(); - both cause the error above. If I go into psql and drop the database, then recreate it, everything works fine on the next start. But then after restart it happens again and renders my application unusable… Any thoughts?

About this issue

Original URL
State: closed
Created 11 years ago
Comments: 25 (16 by maintainers)

Commits related to this issue

unit test for ENUM that breaks on postgres #1020 — committed to thanpolas/sequelize by thanpolas 10 years ago
Merge pull request #1348 from LJ1102/master Increment enumIdx as intended, fixes issue #1020 updated test — committed to sequelize/sequelize by mickhansen 10 years ago

Most upvoted comments

Try using migration with the following code:

'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.

Edudjr on Sep 15, 2017

Try using migration with the following code:

'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.

Brilliant!

victor-nweze-deel on Nov 14, 2018

Opened #4770

faceleg on Oct 28, 2015