Last Updated: February 06, 2017

Adding a PostgreSQL sequence with Alembic

If you change the primary key in a Postgres table, the Alembic auto migration script won't create a new sequence on the new key column.

This throws an error when you then try to insert a record into the table, as there's no autoincrement set on the primary key field. And it can't be set because there's no sequence.

The answer is:

from sqlalchemy.schema import Sequence, CreateSequence

def upgrade():
 ### commands auto generated by Alembic - please adjust! ###
 op.execute(CreateSequence(Sequence('table_id_seq')))
 op.alter_column("table", "id", nullable=False, server_default=sa.text("nextval('table_id_seq'::regclass)"))
 ### end Alembic commands ###