[Python] Alembic's server_default and Postgres

I would like to run a migration using alembic from a sqlalchemy model to another. The initial model looks like this:

from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy_utils import UUIDType

class Foo(db.Model):
__tablename__ = 'foo'
id = db.Column(UUIDType(binary=True), primary_key=True)
foo_field = db.Column(JSONB)


and I want to make foo_field not nullable:

class Foo(db.Model):
__tablename__ = 'foo'
id = db.Column(UUIDType(binary=True), primary_key=True)
foo_field = db.Column(JSONB, nullable=False)


I want to run an alembic script to alter the column, but since some of my existing foo_field are null, I want to use server_default to apply a default value (an empty dict) when altering

op.alter_column('foo',
'foo_field',
existing_type=postgresql.JSONB(astext_type=sa.Text()),
nullable=False,
server_default="{}")


I tried different options to pass to this server_default, such as text("{}"), lambda: {}, {}, text("SELECT '{}'"). But it seems to be just ignored and all I get is an IntegrityError when running the upgrade :

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) column "foo_field" contains null values [SQL: 'ALTER TABLE foo ALTER COLUMN foo_field SET NOT NULL'] (Background on this error at: http://sqlalche.me/e/gkpj)


Does a string representing an empty dict is considered as not-null? What should be passed to this server_default? Should I fraction the code in multiple steps to fill the default value before setting the field not nullable (not the prefered option, I have a bunch of columns that I want to apply this to)?

I am using postgres 10.5, I read that postgres 11 will kinda handle better this sort of operation, should I switch to it now?

Thanks in advance for help/advices

Continue reading...