Menu
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?
508
First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.
This file provides documentation on Alembic migration directives. The directives here are used within user-defined migration files, within the upgrade() and downgrade() functions, as well as any functions further invoked by those. All directives exist as methods on a class called Operations .
The syntax to modify a column in a table in PostgreSQL (using the ALTER TABLE statement) is: ALTER TABLE table_name ALTER COLUMN column_name TYPE column_definition; table_name. The name of the table to modify.
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') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
