Convert column type from text (with serialized JSON) to postgresql.JSONB

[chrisjsewell]

Heya, any sqlalchemy/alembic magic to do this?

Currently, my solution is to transfer to a "temporary" column, then drop the original:

table = sa.table(
    'db_table',
    sa.column('id', sa.Integer),
    sa.column('metadata', sa.TEXT),
    sa.column('metadata_temp', postgresql.JSONB),
)

def upgrade():
    connection = op.get_bind()
    column = 'metadata'
    op.add_column(table.name, sa.Column(column + '_temp', postgresql.JSONB(), nullable=False, server_default='{}'))
    for row in connection.execute(sa.select(table.c.id, table.c[column])).all():
        connection.execute(
            table.update()
            .where(table.c.id == row['id'])
            .values({column + '_temp': loads(row[column])})
        )
    op.drop_column(table.name, column)
    op.alter_column(table.name, column + '_temp', new_column_name=column)

[CaselIT]

Hi,

With porstresql you may be able to use https://alembic.sqlalchemy.org/en/latest/ops.html?highlight=postgresql_using#alembic.operations.Operations.alter_column.params.postgresql_using
to render an ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ].

see https://www.postgresql.org/docs/current/sql-altertable.html for details

[chrisjsewell]

That's the one, thanks!

op.alter_column(
    table_name, column, existing_type=sa.TEXT, type_=postgresql.JSONB, postgresql_using=f"{column}::jsonb"
)