postgresql Alembic：使用 USING 更改列类型

Alembic: alter column type with USING

提问by bard

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.

我正在尝试使用 alembic 将 SQLAlchemy PostgreSQL ARRAY(Text) 字段转换为我的表列之一的 BIT(variing=True) 字段。

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:

默认情况下似乎不支持更改列类型，因此我正在手动编辑 alembic 脚本。这是我目前所拥有的：

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?

如何使用 USING 表达式更改我的脚本？

采纳答案by ThiefMaster

Unfortunately you need to use raw SQL as alembic doesn't output a USINGstatement ever when changing types.

不幸的是，您需要使用原始 SQL，因为 alembicUSING在更改类型时永远不会输出语句。

However, writing custom SQL for this is quite easy:

但是，为此编写自定义 SQL 非常简单：

op.execute('ALTER TABLE views ALTER COLUMN cols TYPE bit varying USING expr')

Of course you have to replace exprwith an expression that converts the old data type to the new data type.

当然，您必须替换expr为将旧数据类型转换为新数据类型的表达式。

回答by Dag H?idahl

Since version 0.8.8, alembic supports PostgreSQL's USINGwith the postgresql_usingparameter:

从版本0.8.8，蒸馏器支持PostgreSQL的USING与postgresql_using参数：

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