Alembic cannot be cast automatically to type integer

Question

I had a model like this:

class Schedule(db.Model):
    __tablename__ = 'schedule'
    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(ARRAY(db.Integer, db.ForeignKey('student.id')))

Then I change the student_id column to be like this:

student_id = db.Column(db.Integer, db.ForeignKey('student.id'))

And here is the snippet of my migration file:

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import INTEGER

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    # ...
    op.alter_column('schedule', 'student_id',
               existing_type=postgresql.ARRAY(INTEGER()),
               type_=sa.Integer(),
               existing_nullable=True)
    # ...
    # ### end Alembic commands ###

When I try to run db migrate command, I got this error message:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DatatypeMismatch) column "student_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING student_id::integer".
 [SQL: 'ALTER TABLE schedule ALTER COLUMN student_id TYPE INTEGER ']

my package versions are:

Flask==0.12
alembic==0.8.10
Flask-Migrate==2.0.3
Flask-SQLAlchemy==2.1
SQLAlchemy==1.1.5
psycopg2==2.8.3

When I try to run on SQL directly using this command:

ALTER TABLE schedule ALTER COLUMN student_id TYPE INTEGER;

I got this error message:

ERROR:  column "student_id" cannot be cast automatically to type integer
HINT:  You might need to specify "USING student_id::integer".
SQL state: 42804

Then I try to add the command with the HINT instruction:

ALTER TABLE schedule ALTER COLUMN student_id TYPE INTEGER USING student_id::integer;

Then I got this error:

ERROR:  cannot cast type integer[] to integer
LINE 1: ...ER COLUMN student_id TYPE INTEGER USING student_id::integer;
                                                             ^
SQL state: 42846
Character: 75

So, my questions are, what's wrong with that..?, any trouble with Alembic or in my code directly..? Please, any help would be appreciated :)

If you want to cast an array (=multiple values) to an integer (=single value) you need to tell Postgres which element of the array to take. https://stackoverflow.com/questions/56753071 — , Sep 11 '19 at 07:52

score 8 · Accepted Answer · answered Sep 11 '19 at 08:01

I figure out this by following @a_horse_with_no_name comment to following this instruction.

So, the answer to my question is, I ran this following command on PostgreSQL directly:

alter table schedule alter column student_id set data type int4 using (student_id[1]::int)

Thanks a lot to @a_horse_with_no_name :)

Alembic cannot be cast automatically to type integer

1 Answers1