How can I migrate a table with data in a table whi

Question

I've added a column

registered_on = db.Column(db.DateTime, nullable=False)

to my users table. The migration which was automatically created is

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    ...
    op.add_column('users', sa.Column('registered_on', sa.DateTime(), nullable=False))
    ...

When I execute it, I got an exception about an invalid value 0000-00-00 00:00:00 (or so).

How should I adjust the migration script to not have this problem?

(In this situation, filling in a dummy value would probably have been the best)

Answer 1

I can think of two ways to do this:

1. Add a server_default to the add_column operation:

op.add_column('users', sa.Column('registered_on', sa.DateTime(), nullable=False, server_default=func.now()))

2. First insert the column as nullable, then edit all your rows to have a value. Once all the rows have been given a non-null value, do another migration to alter the column to non-nullable.