"cannot be cast to type integer" error

Question

This is my first question so I will appreciate patience.

I changed a few attributes to IntegerField from CharField. Listed below is the code:

rating_choices = (
    (1,"1"),
    (2,"2"),
    (3,"3"),
    (4,"4"),
    (5,"5"),
)

class Rating(models.Model):
    article = models.ForeignKey(Article,null=True)
    organization = models.IntegerField(choices=rating_choices, default=1)
    support = models.IntegerField(choices=rating_choices, default=1)
    readability = models.IntegerField(choices=rating_choices, default=1)
    tags = models.IntegerField(choices=rating_choices, default=1)
    comments = models.TextField()
    def get_overall_rating(self):
        return fsum(self.organization + self.support + self.support + self.readability + self.tags)/5.0
    overall_rating = property(get_overall_rating)
admin.site.register(Rating)

I did a south migration to migrate on Postgres and this is the error I get:

Error in migration: collect_data:0010_auto__chg_field_rating_tags__chg_field_rating_support__chg_field_ratin
Traceback (most recent call last):
  File "manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/core/management/__init__.py", line 443, in execute_from_command_line
    utility.execute()
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/core/management/__init__.py", line 382, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/core/management/base.py", line 196, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/core/management/base.py", line 232, in execute
    output = self.handle(*args, **options)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/management/commands/migrate.py", line 108, in handle
    ignore_ghosts = ignore_ghosts,
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/__init__.py", line 213, in migrate_app
    success = migrator.migrate_many(target, workplan, database)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 235, in migrate_many
    result = migrator.__class__.migrate_many(migrator, target, migrations, database)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 310, in migrate_many
    result = self.migrate(migration, database)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 133, in migrate
    result = self.run(migration)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 107, in run
    return self.run_migration(migration)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 81, in run_migration
    migration_function()
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/migration/migrators.py", line 57, in <lambda>
    return (lambda: direction(orm))
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellow/jellow/apps/collect_data/migrations/0010_auto__chg_field_rating_tags__chg_field_rating_support__chg_field_ratin.py", line 13, in forwards
    db.alter_column('collect_data_rating', 'tags', self.gf('django.db.models.fields.IntegerField')())
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/db/generic.py", line 44, in _cache_clear
    return func(self, table, *args, **opts)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/db/generic.py", line 522, in alter_column
    flatten(values),
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/south/db/generic.py", line 273, in execute
    cursor.execute(sql, params)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/db/backends/util.py", line 40, in execute
    return self.cursor.execute(sql, params)
  File "/Users/narensathiya/Documents/Documents/Jellow/Jellow/jellowenv/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 52, in execute
    return self.cursor.execute(query, args)
django.db.utils.DatabaseError: column "tags" cannot be cast to type integer

Any help will be much appreciated and thank you in advance!!

Answer 1

if you are happy to throw away your data, you can delete the column and create a new one

if you want to keep your data, you need to either

a) give your new column a different name, or
b) create a temporary column to hold the data during the transition

you then need a sequence of migrations

1. a schema migration to add the new (or temp) column
2. a data migration that explicitly moves the data, doing any required conversion (e.g. "A" -> 1)
3. possibly a schema migration deleting your temporary column

Answer 2

You need to add "USING (col_name::integer)" to eliminate this error. But in that case you have to use direct query.

migrateEngine.execute('ALTER TABLE test ALTER COLUMN testScore TYPE INTEGER USING testScore::integer')