Django change database field from integer to CharField

Question

I have a Django app with a populated (Postgres) database that has an integer field that I need to change to a CharField. I need to start storing data with leading zeros in this field. If I run migrate (Django 1.8.4), I get the following error:

psycopg2.ProgrammingError: operator does not exist: character varying >= integer

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

I tried searching Google, but didn't really find much help. I don't really know what I'm supposed to do here. Can anyone help?

Answer 1

Originally, I thought that there would be a simple solution where Django or Postgres would do the conversion automatically, but it appears that it doesn't work that way. I think some of suggestions made by others might have worked, but I came up with a simple solution of my own. This was done on a production database so I had to be careful. I ended up adding the character field to the model and did the migration. I then ran a small script in the python shell that copied the data in the integer field, did the conversion that I needed, then wrote that to the new field in the same record in the production database.

for example:

members = Members.objects.all()
for mem in members:
    mem.memNumStr = str(mem.memNum)
    ... more data processing here
    mem.save()

So now, I had the data duplicated in the table in a str field and an int field. I could then modify the views that accessed that field and test it on the production database without breaking the old code. Once that is done, I can drop the old int field. A little bit involved, but pretty simple in the end.

Answer 2

You'll need to generate a schema migration. How you do that will depend on which version of Django you are using (versions 1.7 and newer have built-in migrations; older versions of Django will use south).

Of Note: if this data is production data, you'll want to be very careful with how you proceed. Make sure you have a known good copy of your data you can reinstall if things get hairy. Test your migrations in a non-production environment. Be. Careful!

As for the transformation on the field (from IntegerField to CharField) and the transformation on the field values (to be prepended with leading zeroes) - Django cannot do this for you, you'll have to write this manually. The proper way to do this is to use the django.db.migrations.RunPython migration command (see here).

My advice would be to generate multiple migrations; one that creates a new IntegerField, my_new_column and then write to this new column via RunPython. Then, run a second migration that removes the original CharField my_old_column and renames my_new_column as my_old_column.

Answer 3

from django 2.x you just need to change the field type from

 IntegerField to CharField

and django will automatically alter field and migrate data as well for you.

Answer 4

I thought a full code example would be helpful. I followed the approach outlined by ken-koster in the comment above. I ended up with two migrations (0091 and 0092). It seems that the two migrations could be squashed into one migration but I did not go that far. (Maybe Django does this automatically but the framework here could be used in case the string values are more complicated than a simple int to string conversion. Also I included a reverse conversion example.)

First migration (0091):

from django.db import migrations, models

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0090_auto_20200622_1452'),
    ]

    operations = [
        # store original values in tmp fields
        migrations.RenameField(model_name='member',
                               old_name='mem_num',
                               new_name='mem_num_tmp'),
        # add back fields as string fields
        migrations.AddField(
            model_name='member',
            name='mem_num',
            field=models.CharField(default='0', max_length=64, verbose_name='Number of members'),
        ),
    ]

Second migration (0092):

from django.db import migrations

def copyvals(apps, schema_editor):
    Member = apps.get_model("myapp", "Member")
    members = Member.objects.all()
    for member in members:
        member.rotate_xy = str(member.mem_num_tmp)
        member.save()

def copyreverse(apps, schema_editor):
    Member = apps.get_model("myapp", "Member")
    members = Member.objects.all()
    for member in members:
        try:
            member.mem_num_tmp = int(member.mem_num)
            member.save()
        except Exception:
            print("Reverse migration for member %s failed." % member.name)
            print(member.mem_num)

class Migration(migrations.Migration):

    dependencies = [
        ('myapp', '0091_custom_migration'),
    ]

    operations = [
        # convert integers to strings
        migrations.RunPython(copyvals, reverse_code=copyreverse),

        # remove the tmp field
        migrations.RemoveField(
            model_name='member',
            name='mem_num_tmp',
        ),
    ]