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

A ProgrammingError is usually a message straight from Postgres. Double check that the column is now integer. Did you commit the transaction that altered the column type? — Andomar
– Andomar, Commented Dec 22, 2015 at 9:39

Dhia · Accepted Answer · 2015-12-22 11:31:08Z

21

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

edited Dec 22, 2015 at 11:31

answered Dec 22, 2015 at 10:37

Dhia

10.7k11 gold badges62 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

m.antkowicz Over a year ago

It is not about Django and migrations I guess rather about PostgreSQL - I had a similar problem some time ago with Integer -> Boolean conversion. What was working on SQLite didn't want to work on PostgreSQL.

rafirosenberg · Accepted Answer · 2018-06-14 15:37:01Z

7

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

answered Jun 14, 2018 at 15:37

rafirosenberg

3333 silver badges9 bronze badges

Comments

CatByte-io · Accepted Answer · 2023-09-23 19:03:58Z

Similar to the answer by @rafirosenberg, I had a similar issue and the fix was to delete migrations. My specific error was:

"django.db.utils.ProgrammingError: cannot cast type double 
precision to geometry
LINE 1: ...COLUMN "gps" TYPE geometry(POINT,4326) USING 
"gps"::geometry..."

The problem was that I had run the inital migrations with the type as a FloatField. So I had to go into the app in the file tree as demonstrated below, and deleted 0001_inital.py and following migration files. Then, running python manage.py makemigrations and python manage.py migrate both completed without problems.

- django-project
  - app0
  - app1
    - migrations
      __init__.py
      0001_inital.py
  - django-project

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related