Search code examples
pythonpostgresqlsqlalchemyalembic

Alembic migration to convert serial field to integer

I initially defined one of my SQLAlchemy models as:

class StreetSegment(db.Model):
    id = db.Column(db.Integer, autoincrement=True)    # surrogate ID
    seg_id = db.Column(db.Integer, primary_key=True)  # assigned in another system; don't increment

not realizing that seg_id would become a SERIAL field in my Postgres database. What I really wanted was an INTEGER field with a PK constraint (no autoincrement). I turned off autoincrement like so:

class StreetSegment(db.Model):
    id = db.Column(db.Integer, autoincrement=True)
    seg_id = db.Column(db.Integer, primary_key=True, autoincrement=False)  # <--- see here

but the change isn't reflected when I run migrate in Alembic. I've also tried writing a custom migration with the following operations:

def upgrade():
    op.alter_column('street_segment', 'seg_id', autoincrement=False)

def downgrade():
    op.alter_column('street_segment', 'seg_id', autoincrement=True)

but that gives the warning autoincrement and existing_autoincrement only make sense for MySQL. So my question is: is there any way of using Alembic to convert a SERIAL to an INTEGER in Postgres?

Solution

  • Just set the type explicitly to the one you want. This should work:

    op.alter_column('street_segment', 'seg_id', _type=Integer)