Postgres enum not created, even when it has in the past.

Question

Migrated issue, originally created by faichenshing

When creating a new ENUM, it is not generated correctly. They have to be manually created and dropped.

We're using Postgres 9.6, from the docker image postgres:9.6-alpine. Our requirements.txt has alembic==0.9.6 and SQLAlchemy==1.1.15. I believe the default driver is psycopg2, and we haven't made any changes to that. We're using the Flask-Migrate, at version Flask-Migrate==2.1.1.

Before you think this is a duplicate of https://bitbucket.org/zzzeek/alembic/issues/278/autogenerate-doesnt-correctly-handle , we've had many migrations in the past that have correctly detected new enums and generated successful code to create and destroy them, so we're not sure why this one failed.

The command being run was manage.py db upgrade, since we're using this as a flask script.

Here's the base error:

bash
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) type "colors" does not exist
LINE 1: ALTER TABLE app_example ADD COLUMN colors colors
                                                     ^
 [SQL: 'ALTER TABLE app_example ADD COLUMN colors colors']

And a gist of the entire traceback

Here's the generated code that produced that error:

 python
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('app_example', sa.Column('colors', sa.Enum('RED', 'GREEN, 'BLUE, 'PURPLE', name='colors'), nullable=True))
    # ### end Alembic commands ###

Here's what works, inspired by this StackOverflow post:

 python
def upgrade():
    colors = postgresql.ENUM('RED', 'GREEN, 'BLUE, 'PURPLE', name='colors')
    colors.create(op.get_bind())
    op.add_column('app_example', sa.Column('colors', sa.Enum('RED', 'GREEN, 'BLUE, 'PURPLE', name='colors'), nullable=True))

For comparison, previous migrations have done things functionally identical to that above generated code, and were successful.

python
op.create_table('tags',
  sa.Column('id', sa.Integer(), nullable=False),
  sa.Column('text', sa.Unicode(), nullable=True),
  sa.Column('type', sa.Enum('User', 'System', name='tagtype'), server_default='User', nullable=False),
  sa.PrimaryKeyConstraint('id')
)

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

Kudos on finding #278 and identifying that as where these all go.

we've had many migrations in the past that have correctly detected new enums and generated successful code to create and destroy them,

can you illustrate one of those? because as it stands, alembic does not generate the ENUM externally as you have in your second example, it will do what you see in the first one and that will fail. There's nothing I see different from #278 so far.