Alembic: Autogenerate doesn't correctly handle postgresql enums

[sqlalchemy-bot]

Migrated issue, originally created by Ulrich Petri (@ulope)

According to #67 enum types used in columns have to be explicitly created and destroyed.

However the autogenerator currently doesn't produce those explicit commands. This problem is made worse by the fact that the autogenerated migration appears to work fine the first time it is applied and even removed. The dangling enum type only causes a conflict the second time the migration is applied.

edit by mike: note also that anything we do for PG native enums also has to run outside of a transaction block, so this is really not going to be easy. #123 provides the feature for this however if autogenerate, or the operation itself, is going to make that happen automatically, that's a very dramatic behavior.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

#67 is probably not a great built-in solution to this issue, as that solution does not accommodate for the fact that ENUM has different implementations on different backends.

The issue here might be mostly the same as #122. The issue of PG ENUM is mostly unhandled as there are many issues and use cases to be worked out. The emission of the CREATE/DROP might be best automated as within the handling of Enum itself, but then this is tricky as in #122 the type might already exist or not.

Basically I don't have a solution to this right now which covers every case and that is the majority of work to be done.

[sqlalchemy-bot]

Leonardo (@leotada) wrote:

Why does not check if the type exists and tries to create if not, or alter if the name is the same.
https://stackoverflow.com/questions/7624919/check-if-a-user-defined-type-already-exists-in-postgresql

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

@leotada sure. The work is, "works with the generic Enum as well as PG ENUM, doesn't interfere with other backends, has plenty of functional tests".

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #443 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #122 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #471 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #484 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Issue #488 was marked as a duplicate of this issue.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: autogenerate - rendering
• added labels: postgresql typing issues

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Autogenerate doesn't correctly handle enums" to "Autogenerate doesn't correctly handle postgresql e"

[matt1hong]

I'm quite confused right now.

Issue #471 was closed because it was deemed a duplicate of this issue (which is specific to postgresql ENUMs).

But downgrade scripts aren't being generated for sqlalchemy.Enums either.

[zzzeek]

But downgrade scripts aren't being generated for sqlalchemy.Enums either.

A generic Enum is a VARCHAR in the database. If you add or remove items to the enum, there is no datatype to be changed. There is a CHECK constraint, unfortunately, which Alembic does not have support for right now, support for this would be #363.

[AFlowOfCode]

Lot of dupes but hard to find information for how to do this yourself. Here's an example of a migration to add a postgres enum to a pre-existing table.

def upgrade():
    status_enum = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum')
    status_enum.create(op.get_bind())
    op.add_column('mytable', sa.Column('status', status_enum))

def downgrade():
    op.drop_column('mytable', 'status')
    op.execute("DROP TYPE mytable_status_enum;")

[ziima]

Workaround to reuse enum in backend-agnostic (I hope) case:

def upgrade():
    connection = op.get_bind()
    if connection.dialect.name == "postgresql":
        status_enum = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False)
    else:
        status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum')
    op.add_column('mytable', sa.Column('status', status_enum))

[ziima]

Workaround based on @zzzeek's answer from sqlalchemy/sqlalchemy#5593:

def upgrade():
    status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False)
    status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum')
    status_enum.with_variant(status_enum_postgres, 'postgresql')
    op.add_column('mytable', sa.Column('status', status_enum))

It seems to be working fine.

[JohnnyFee]

Workaround based on @zzzeek's answer from sqlalchemy/sqlalchemy#5593:

def upgrade():
    status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='mytable_status_enum', create_type=False)
    status_enum = sa.Enum('pending', 'accepted', 'canceled', name='mytable_status_enum')
    status_enum.with_variant(status_enum_postgres, 'postgresql')
    op.add_column('mytable', sa.Column('status', status_enum))

It seems to be working fine.

This solution seems to not work for that latest version 1.5.8.
We tried this codes:

status_enum_postgres = postgresql.ENUM('pending', 'accepted', 'canceled', name='emotion', create_type=False)
status_enum_postgres.create(op.get_bind(), checkfirst=False)
op.add_column('mytable', sa.Column('status', status_enum_postgres, nullable=True))

It works great.

[ziima]

@JohnnyFee My solution works for me at alembic 1.5.8, but I still use sqlalchemy 1.3, which may be the difference. I still encounter type "mytable_status_enum" already exists error, when I try your solution.

[tyoc213]

Is there a way to just reuse the enum? or should I create another different one for each table?

like I have this enum: enabled, disabled, pending

In lots of different places, the best way would be to create a different enum for each one?

[zzzeek]

Is there a way to just reuse the enum? or should I create another different one for each table?

like I have this enum: enabled, disabled, pending

In lots of different places, the best way would be to create a different enum for each one?

you can make just one Enum object and reuse it, sure. In PostgreSQL, the enum is a totally separate construct independent of the table.