How to Change a Column from ENUM to INTEGER (Foreign Key) Using Alembic?

I am trying to alter a column's type from ENUM to INTEGER (which is also a foreign key) in PostgreSQL using Alembic. However, when I attempt to upgrade the migration, I encounter the following error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.CannotCoerce) cannot cast type serie_enum to integer LINE 1: ...R COLUMN serie_aluno TYPE INTEGER USING serie_aluno::integer
[SQL: ALTER TABLE aluno ALTER COLUMN serie_aluno TYPE INTEGER USING serie_aluno::integer]

Here is the migration file generated by Alembic:

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column('aluno', 'serie_aluno',
               existing_type=postgresql.ENUM('6', '7', '8', '9', name='serie_enum'),
               type_=sa.Integer(),
               existing_nullable=False,
               postgresql_using="NULL")
    op.create_foreign_key(None, 'aluno', 'serie', ['serie_aluno'], ['id_serie'])
    # ### end Alembic commands ###

def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint(None, 'aluno', type_='foreignkey')
    op.alter_column('aluno', 'serie_aluno',
               existing_type=sa.Integer(),
               type_=postgresql.ENUM('6', '7', '8', '9', name='serie_enum'),
               existing_nullable=False)
    # ### end Alembic commands ###

How can I successfully convert a column from ENUM to INTEGER while ensuring the foreign key constraint is created correctly?

Solution

There is no cast defined from your custom enum type serie_aluno to integer. (Unless you create such a cast explicitly.)

But everything can be cast to text. If all enum values are represented by valid integer literals, you can use text as stepping stone:

ALTER TABLE aluno ALTER COLUMN serie_aluno TYPE int USING serie_aluno::text::int;

To then add a FOREIGN KEY constraint pointing to an integer column:

ALTER TABLE aluno ADD CONSTRAINT aluno_serie_fkey FOREIGN KEY (serie_aluno) REFERENCES serie(id_serie);

Or do it in a single ALTER TABLE command:

ALTER TABLE aluno
  ALTER COLUMN serie_aluno TYPE integer USING serie_aluno::text::int
, ADD CONSTRAINT serie_aluno_serie_fkey FOREIGN KEY (serie_aluno) REFERENCES serie(id_serie)
;

fiddle