4

I have an existing populated database and would like to convert a PositiveIntegerField into a FloatField. I am considering simply doing a migration:

    migrations.AlterField(
        model_name='mymodel',
        name='field_to_convert',
        field=models.FloatField(
            blank=True,
            help_text='my helpful text',
            null=True),
    ),

Where the field is currently defined as:

field_to_convert = models.PositiveIntegerField(
    null=True,
    blank=True,
    help_text='my helpful text')

Will this require a full rewrite of the database column? How well might this conversion scale for larger databases? How might it scale if the vast majority values were null? In what circumstances would this conversion fail? This is a backed by a Postgres database if that makes a difference.

Matt
  • 5,028
  • 2
  • 28
  • 55

2 Answers2

8

Will this require a full rewrite of the database column?

No, it won't. I did an experiment with PostgreSQL, MySQL, and SQLite the conversion from integer to float goes well in every case, I also put some values as null to match your situation.

If you have a value 3, it just will change to 3.0.

How might it scale if the vast majority values were null?

Well, since you keep null=True in the configuration of your field all null values will remain null, no problem with that. If you remove null=True you might need to specify a default value.

In what circumstances would this conversion fail?

Taking an int column and converting it to float (real) should not fail, if you find a bizarre, weird and very special case it would be a very big finding.

If you have doubts about the migration outcome...

... you can first take a look into migrations SQL with sqlmigrate, and of course, you could backup your database.

Raydel Miranda
  • 13,825
  • 3
  • 38
  • 60
1

You can use sqlmigrate to check generated sql for your migration.

$ python manage.py sqlmigrate app_label migration_name

Keep in mind, that its output depends on the Django version and the database you have in settings. For the setup I had on hand (Django 1.11, Postgres 9.3) for your migration I got:

BEGIN;
--
-- Alter field field_to_convert on mymodel
--
ALTER TABLE "myapp_mymodel" DROP CONSTRAINT "myapp_mymodel_field_to_convert_check";
ALTER TABLE "myapp_mymodel" ALTER COLUMN "field_to_convert" TYPE double precision USING "field_to_convert"::double precision;
COMMIT;

Which looks good to me both in terms of performance and reliability. I'd say go ahead with the AlterField.

If you want to be extra safe, you can always go: rename field -> create field -> run python -> drop field. This will give you more control over the migration process. Check this answer for details.

Igonato
  • 10,175
  • 3
  • 35
  • 64