Django, Postgres - column cannot be cast automatically to type integer

Question

In my database i have column:

currency = models.CharField(max_length=10, blank=True, null=True)

I want to change this column from CharField to IntegerField. So in models.py i change this:

currency = models.IntegerField(blank=True, null=True)

then i made migrations: python manage.py makemigrations and python manage.py migrate. After that actions it rise error:

django.db.utils.ProgrammingError: column "currency" cannot be cast automatically to type integer
HINT:  Specify a USING expression to perform the conversion.

After that in pgAdmin3 console i made this changes:

ALTER TABLE my_table ALTER COLUMN currency TYPE integer USING (currency::integer);

But i still got that error, I tried to change all back, but error doesn't disappear. What i have to do to escape this error. Thank you

Answer 1

I think django migrations does not perform casting, I looked in the documentation but I did not find any thing about column casting.

• if the existing data is not that important for you, you can delete the column and create a new one

  1. first step remove currency from you model and apply migration
  2. add again the currency with the new definition and apply again the migration

• if you want to keep your data, you need to give your new column a different name and use the old column as a temporary column to hold the data during the transition.

Important: Postgresql is more strongly typed in recent versions, and as explained here some casting may not work in PosgreSQL unless it's explicitly done. And it required to be more specific about the type. So you have to make the right choice based on your values:

alter table my_table alter column currency type bigint using currency::bigint

or maybe:

alter table my_table alter column currency type numeric(10,0) using currency::numeric

Answer 2

It is a PSQL issue when changing from certain data types to others... I had a similar problem an I did something a bit hackey but it worked ... but only because I didn't have any important data in the column

1) delete the most recent migration for that app 2) delete/comment out the "column" on the object 3) migrate the app with the missing column 4) reinstate/uncomment the offending "column" 5) migrate again

this is all a long way to delete and recreate the column on the actual db without using sql ... figured I would share in case it might help someone down the road