What Happened?
Imagine you have a table called organizations full of precious data. You decide:
Hey, let’s add a new column called plan!
Sounds harmless, right? Wrong. You add it as NOT NULL, meaning every row must have a value. But wait… your table is already filled with rows. PostgreSQL looks at them and says:
Why It’s Not Your Fault
This isn’t a bug in Alembic or SQLAlchemy. It’s PostgreSQL protecting your precious data. Adding a NOT NULL column to existing data without a default is basically like asking someone to fill out a survey you didn’t give them in advance. They don’t know what to answer, so they freak out.
How to Fix It
You’ve got a few ways to make PostgreSQL happy again:
Option 1: Give it a Default
op.add_column(
'organizations',
sa.Column('plan', sa.String(), nullable=False, server_default='free')
)
- Existing rows get a value ('free') automatically.
- After the migration, you can remove the default if you like.
- Quick, painless, and everyone’s happy.
Option 2: Three-Step Dance
Add the column as nullable:
op.add_column(
'organizations',
sa.Column('plan', sa.String(), nullable=True)
)
Update existing rows:
UPDATE organizations SET plan = 'free' WHERE plan IS NULL;
Make it NOT NULL:
op.alter_column('organizations', 'plan', nullable=False)
- More steps, but more control.
- Good if you need custom logic per row.
Option 3: Pre-Migration Prep
Before Alembic touches anything, manually update the table:
- Then run your migration.
- Old rows are ready, and PostgreSQL doesn’t throw a tantrum.
Moral of the Story
Adding a NOT NULL column to a table with existing rows is like adding a new rule to a crowded party: someone will get upset if you don’t provide a plan. Always think about existing data. Give PostgreSQL a default, update the old rows, or do both.
And remember: Alembic errors might look scary, but they’re really just your database’s way of saying:
“Hey, I love your data too much to let it break. Let’s do this right.”
Top comments (0)