17

I would like to change column type of the database from string to integer by using Alembic. If I use pure SQL, it achieves the goal:

alter table statistic_ticket alter column tags type bigint using tags::bigint;

But when I use Alembic like:

import sqlalchemy as sa
def upgrade():
    op.alter_column('statistic_ticket', 'tags', nullable = True, existing_type=sa.String(length=255), type_=sa.Integer, existing_nullable=True)

I got an error:

HINT: Please use USING clause for carrying out the conversion

The SQL statement generated by SQLAlchemy was:

ALTER TABLE statistic_ticket ALTER COLUMN tags TYPE INTEGER' {}

Can someone show me how to do in alembic or the SQL in SQLAlchemy via op.execute(SQL)?

Mark Hildreth
  • 42,023
  • 11
  • 120
  • 109
SieuTruc
  • 475
  • 1
  • 7
  • 16
  • I cleaned up your question, including voting to close the [other question](http://stackoverflow.com/questions/20687151/how-to-use-using-clause-in-alembic) that you had posted on this same topic. I also tagged this question PostgreSQL, since that was what you tagged the other question. Can you just verify that the database you are using is Postgresql, as you had the other database tagged? Thanks – Mark Hildreth Dec 19 '13 at 20:18
  • Thanks , it seems that sqlalchemy/alembic doesn't support that USING clause on postgresql. – SieuTruc Dec 19 '13 at 22:51
  • Did you find a definitive source for that information? If so, I would recommend adding an answer to your own question stating just that (that Alembic does not support the "USING" clause), include the source, then mark your answer as the answer to the question. – Mark Hildreth Dec 19 '13 at 23:32
  • In fact, i can do it by using op.execute("SQL string") but cannot do it by using the default functions of sqlachemy or alembic. – SieuTruc Dec 20 '13 at 09:52
  • 1
    i don't think the "USING" clause is directly supported so at the moment using op.execute() is the most direct way to go. the other method would be to create a custom @compile directive for alembic.ddl.impl.ColumnType, though that's more involved. To do a conditional, check op.get_bind().dialect.name for "postgresql" if you want to fall back to the generic alter_column(). – zzzeek Dec 25 '13 at 19:25
  • Basically the same question as https://stackoverflow.com/q/29069506/22146 – Dag Høidahl Aug 21 '17 at 20:49

1 Answers1

16

From Alembic 0.8.8, you can use the postgresql_using keyword:

op.alter_column('statistic_ticket', 'tags', type_=sa.BigInteger,
                postgresql_using='tags::bigint')

On prior versions, you have to use op.execute:

op.execute('ALTER TABLE statistic_ticket ALTER COLUMN '
           'tags TYPE bigint USING tags::bigint')
RazerM
  • 5,128
  • 2
  • 25
  • 34