I'm using sqlalchemy-migrate to alter the type of one of the columns in a table in a Postgre SQL database. The upgrade script I'm using is:
# -*- cofing: utf-8 -*-
from sqlalchemy import MetaData, Table, Column, String, Integer
from migrate import changeset
metadata = MetaData()
def upgrade(migrate_engine):
# ALTER TABLE courses ALTER COLUMN number SET DATA TYPE character varying;
metadata.bind = migrate_engine
courses = Table('courses', metadata, Column("number", Integer), extend_existing=True)
courses.c.number.alter(type=String)
def downgrade(migrate_engine):
# ALTER TABLE courses ALTER COLUMN number SET DATA TYPE integer;
metadata.bind = migrate_engine
courses = Table('courses', metadata, Column("number", String), extend_existing=True)
courses.c.number.alter(type=Integer, cast='numeric')
The upgrade part seems to work but the downgrade always fails with the following error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "number" cannot be cast to type integer
'\nALTER TABLE courses ALTER COLUMN number TYPE INTEGER' {}
Now, if I were using plain SQL I could use ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numeric
to alter the column type back from character varying
to integer
, but I don't know how to achieve that using sqlalchemy-migrate.
Is there a way to force sqlalchemy to include USING number::numeric
in the ALTER
clause? or is there another way to avoid the error I posted above?
I appreciate your help.
Looks like sqlalchemy.migrate doesn't have support for rendering valid query in case of altering from String to Integer column types for postgresql.
In your case I would implement it as a direct query execution and move on.
def downgrade(migrate_engine):
# ALTER TABLE courses ALTER COLUMN number SET DATA TYPE integer;
migrate_engine.execute('ALTER TABLE courses ALTER COLUMN number TYPE INTEGER USING number::numeric')
BTW migrating from String to Integer may fail for different reasons - when column value would contain some value that cannot be converted to number. So I would add some extra validation into application logic to keep downgrade migration possible later on.