I can't create field of Enum type: sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "companytype" does not exist

from aenum import Enum

class CompanyType(Enum):
    type1 = 1
    type2 = 2

class Company(BaseModel):

    __tablename__ = 'company'

    company_type = db.Column(db.Enum(CompanyType), default=CompanyType.type1, nullable=False)

The strange thing is I already have another model with enum field and it worked fine, created the variable in the database itself. But I don't remember what exactly I did then. This time I have the exception when I try to update the database with alembic.

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject) type "companytype" does not exist LINE 1: ALTER TABLE company ADD COLUMN type companytype NOT ... ^

[SQL: ALTER TABLE company ADD COLUMN type companytype NOT NULL] (Background on this error at: http://sqlalche.me/e/13/f405)

The code the Alembic generates is:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('company', sa.Column('type', sa.Enum('type1', 'type2', name='companytype'), nullable=True))
    # ### end Alembic commands ###

I have a feeling I have to say database to create this variable but I don't know how.

UPDATE

I found a workaround. It turned out that the problem happens only when a table already exists. So, I created a temp table having the same column, and the script generated the enum variable in the database. Then I deleted that table and added the column to my Company table, and it finally worked. Not sure, if it's a bug, and whose.

Solution

The issue you have is a bug in Alembic. At the moment you need to alter the upgrade function manually to successfully upgrade the database when the Enum already exists:

from sqlalchemy.dialects import postgresql

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    companytype_enum = postgresql.ENUM('type1', 'type2', name='companytype', create_type=False)
    companytype_enum.create(op.get_bind(), checkfirst=True)
    op.add_column('company', sa.Column('type', companytype_enum, nullable=True))
    # ### end Alembic commands ###