Alembic: alter column type with USING

Question

I'm attempting to use alembic to convert a SQLAlchemy PostgreSQL ARRAY(Text) field to a BIT(varying=True) field for one of my table columns.

The column is currently defined as:

cols = Column(ARRAY(TEXT), nullable=False, index=True) 

I want to change it to:

cols = Column(BIT(varying=True), nullable=False, index=True) 

Changing column types doesn't seem to be supported by default, so I'm editing the alembic script by hand. This is what I have currently:

def upgrade():     op.alter_column(         table_name='views',         column_name='cols',         nullable=False,         type_=postgresql.BIT(varying=True)     )   def downgrade():     op.alter_column(         table_name='views',         column_name='cols',         nullable=False,         type_=postgresql.ARRAY(sa.Text())     ) 

However, running this script gives the error:

Traceback (most recent call last):   File "/home/home/.virtualenvs/deus_lex/bin/alembic", line 9, in <module>     load_entry_point('alembic==0.7.4', 'console_scripts', 'alembic')()   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py", line 399, in main     CommandLine(prog=prog).main(argv=argv)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py", line 393, in main     self.run_cmd(cfg, options)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/config.py", line 376, in run_cmd     **dict((k, getattr(options, k)) for k in kwarg)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/command.py", line 165, in upgrade     script.run_env()   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/script.py", line 382, in run_env     util.load_python_file(self.dir, 'env.py')   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/util.py", line 242, in load_python_file     module = load_module_py(module_id, path)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py     mod = imp.load_source(module_id, path, fp)   File "./scripts/env.py", line 83, in <module>     run_migrations_online()   File "./scripts/env.py", line 76, in run_migrations_online     context.run_migrations()   File "<string>", line 7, in run_migrations   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/environment.py", line 742, in run_migrations     self.get_context().run_migrations(**kw)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/migration.py", line 305, in run_migrations     step.migration_fn(**kw)   File "/home/home/deus_lex/winslow/scripts/versions/2644864bf479_store_caselist_column_views_as_bits.py", line 24, in upgrade     type_=postgresql.BIT(varying=True)   File "<string>", line 7, in alter_column   File "<string>", line 1, in <lambda>   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/util.py", line 387, in go     return fn(*arg, **kw)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/operations.py", line 470, in alter_column     existing_autoincrement=existing_autoincrement   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 147, in alter_column     existing_nullable=existing_nullable,   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/alembic/ddl/impl.py", line 105, in _exec     return conn.execute(construct, *multiparams, **params)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute     return meth(self, multiparams, params)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 69, in _execute_on_connection     return connection._execute_ddl(self, multiparams, params)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 783, in _execute_ddl     compiled   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context     context)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception     exc_info   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause     reraise(type(exception), exception, tb=exc_tb)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context     context)   File "/home/home/.virtualenvs/deus_lex/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute     cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) column "cols" cannot be cast automatically to type bit varying HINT:  Specify a USING expression to perform the conversion.  'ALTER TABLE views ALTER COLUMN cols TYPE BIT VARYING' {} 

How can I change my script with the USING expression?

Answer 1

Since version 0.8.8, alembic supports PostgreSQL's USING with the postgresql_using parameter:

op.alter_column('views', 'cols', type_=postgresql.BIT(varying=True), postgresql_using='col_name::expr')