1

I'm trying to convert a column datatype from an Enum to Text using Alembic with SQLAlchemy PostgreSQL. In my migration file, the upgrade function works perfectly fine and it's able to convert the type from an Enum to Text, but the downgrade doesn't do the opposite. Currently my Enum is defined as follows:

enum_type = sa.Enum(
    "OPEN",
    "QUEUED",
    "COMPLETE",
    "FAILED",
    "CANCELLED",
    "CANCEL_PENDING",
    name="jobstatus",
)

while my column in a different migration version is defined as

sa.Column("status", enum_type, nullable=False),

and my upgrade is defined as:

def upgrade():
    op.alter_column(
        table_name="jobs",
        column_name="status",
        nullable=False,
        existing_type=enum_type,
        type_=sa.Text(),
        postgresql_using="status::text",
    )

and for my downgrade:

def downgrade():

    op.alter_column(
        table_name="jobs",
        column_name="status",
        nullable=False,
        type_=enum_type,
        existing_type=sa.Text(),
        postgresql_using="status::text::jobstatus",
    )

I assume I just flip the existing_type and type_ as well as the postgresql_using to use the type that I want (in this case 'jobstatus'). However, running this script allows me to upgrade fine but when I do downgrade -1, there is no error, but the operation never completes and I have to close the CLI and reset my DB since it completely crashes it and I am unable to open the jobs table again.

I am assuming that there is something wrong with my postgresql_using line, I have tried using postgresql_using=status::text::jobstatus and also postgresql_using=status:enum, but to no avail. What am I missing? And is it even possible to convert between an Enum to Text type directly?

HudZah
  • 55
  • 1
  • 8

0 Answers0