Django 1.8 migration unable to cast column id to integer

Question

I'm migrating my site from an SQLite backend to a Postgres backend. We've been running native-Django style migrations (i.e., not South) from the beginning of the project. Most of the migrations run fine, but there's a hiccup in our of our applications.

We got this far in the Postgres migration. (All other apps fully migrated.) All of the migrations ran without incident on SQLite3.

processes
 [X] 0001_initial
 [X] 0002_auto_20150508_2149
 [ ] 0003_auto_20150511_1543
 [ ] 0004_auto_20150528_1739
 [ ] 0005_process_upstream
 [ ] 0006_auto_20150605_1436
 [ ] 0007_auto_20150605_1706
 [ ] 0008_milestone_prevailing_process

These two migrations ran correctly:

0001_initial.py:

class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='DateReason',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(unique=True, max_length=50)),
                ('active', models.BooleanField(default=True)),
                ('final', models.BooleanField(default=False)),
            ],
        ),
        migrations.CreateModel(
            name='EventType',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(unique=True, max_length=50)),
                ('active', models.BooleanField(default=True)),
            ],
        ),
        migrations.CreateModel(
            name='Metric',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(unique=True, max_length=50)),
                ('active', models.BooleanField(default=True)),
            ],
        ),
        migrations.CreateModel(
            name='Process',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(max_length=50)),
                ('sequence', models.PositiveIntegerField()),
                ('sla', models.PositiveSmallIntegerField(null=True, blank=True)),
                ('milestone', models.BooleanField(default=False)),
            ],
            options={
                'ordering': ['workflow', 'sequence'],
            },
        ),
        migrations.CreateModel(
            name='Workflow',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(unique=True, max_length=20)),
                ('active', models.BooleanField(default=True)),
            ],
        ),
        migrations.AddField(
            model_name='process',
            name='workflow',
            field=models.ForeignKey(to='processes.Workflow'),
        ),
        migrations.AlterUniqueTogether(
            name='process',
            unique_together=set([('workflow', 'name'), ('workflow', 'sequence')]),
        ),
    ]

0002_auto_20150508_2149.py:

class Migration(migrations.Migration):

    dependencies = [
        ('processes', '0001_initial'),
    ]

    operations = [
        migrations.CreateModel(
            name='Milestone',
            fields=[
                ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
                ('name', models.CharField(max_length=50)),
                ('sequence', models.PositiveIntegerField()),
                ('workflow', models.ForeignKey(to='processes.Workflow')),
            ],
            options={
                'ordering': ['workflow', 'sequence'],
            },
        ),
        migrations.AlterModelOptions(
            name='process',
            options={'ordering': ['milestone', 'sequence']},
        ),
        migrations.AlterUniqueTogether(
            name='process',
            unique_together=set([('milestone', 'name'), ('milestone', 'sequence')]),
        ),
        migrations.RemoveField(
            model_name='process',
            name='workflow',
        ),
        migrations.AlterUniqueTogether(
            name='milestone',
            unique_together=set([('workflow', 'name'), ('workflow', 'sequence')]),
        ),
    ]

This migration won't run: 0003_auto_20150511_1543.py

class Migration(migrations.Migration):

    dependencies = [
        ('processes', '0002_auto_20150508_2149'),
    ]

    operations = [
        migrations.AlterModelOptions(
            name='process',
            options={'ordering': ['milestone', 'sequence'], 'verbose_name_plural': 'processes'},
        ),
        migrations.AlterField(
            model_name='process',
            name='milestone',
            field=models.ForeignKey(to='processes.Milestone'),
        ),
    ]

Attempting to run this migration results in:

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

The current, fully migrated state of the relevant model tables is:

class Milestone(models.Model):
    """A collection of steps in a workflow"""
    workflow = models.ForeignKey(Workflow, blank=False, null=False)
    name = models.CharField(max_length=50, blank=False, null=False)
    sequence = models.PositiveIntegerField(blank=False, null=False)
    prevailing_process = models.ForeignKey('Process', blank=False, null=True, related_name='controls_milestone')

    class Meta:
        ordering = ['workflow', 'sequence']
        unique_together = (("workflow", "sequence"), ("workflow", "name"))

    def __unicode__(self):
        return u"{0}: {1}".format(self.workflow.name, self.name)


class Process(models.Model):
    """A step in a workflow"""
    milestone = models.ForeignKey(Milestone, blank=False, null=False)
    name = models.CharField(max_length=50, blank=False, null=False)
    sequence = models.PositiveIntegerField(blank=False, null=False)
    sla = models.PositiveSmallIntegerField(blank=True, null=True)
    upstream = models.ForeignKey('self', blank=True, null=True, on_delete=models.SET_NULL, related_name='downstream_set')

    class Meta:
        ordering = ['milestone', 'sequence']
        unique_together = (("milestone", "sequence"), ("milestone", "name"))
        verbose_name_plural = "processes"

    def __unicode__(self):
        return u"{0} {1}: {2}".format(self.milestone.workflow.name, self.milestone.name, self.name)

Squashing the migrations didn't help. The Postgres database is clean except for table definitions. The relevant Postgres table definitions in their stuck form are:

scorecard=# \d processes_milestone
                                   Table "public.processes_milestone"
   Column    |         Type          |                            Modifiers
-------------+-----------------------+------------------------------------------------------------------
 id          | integer               | not null default nextval('processes_milestone_id_seq'::regclass)
 name        | character varying(50) | not null
 sequence    | integer               | not null
 workflow_id | integer               | not null
Indexes:
    "processes_milestone_pkey" PRIMARY KEY, btree (id)
    "processes_milestone_workflow_id_21e7e70ae59594a8_uniq" UNIQUE CONSTRAINT, btree (workflow_id, sequence)
    "processes_milestone_workflow_id_363216929a08f11e_uniq" UNIQUE CONSTRAINT, btree (workflow_id, name)
    "processes_milestone_846c77cf" btree (workflow_id)
Check constraints:
    "processes_milestone_sequence_check" CHECK (sequence >= 0)
Foreign-key constraints:
    "processes_workflow_id_53b7557aa3f3378e_fk_processes_workflow_id" FOREIGN KEY (workflow_id) REFERENCES processes_workflow(id) DEFERRABLE INITIALLY DEFERRED

scorecard=# \d processes_process
                                  Table "public.processes_process"
  Column   |         Type          |                           Modifiers
-----------+-----------------------+----------------------------------------------------------------
 id        | integer               | not null default nextval('processes_process_id_seq'::regclass)
 name      | character varying(50) | not null
 sequence  | integer               | not null
 sla       | smallint              |
 milestone | boolean               | not null
Indexes:
    "processes_process_pkey" PRIMARY KEY, btree (id)
    "processes_process_milestone_20dc77c2825fcc38_uniq" UNIQUE CONSTRAINT, btree (milestone, name)
    "processes_process_milestone_5bb869985140bf86_uniq" UNIQUE CONSTRAINT, btree (milestone, sequence)
Check constraints:
    "processes_process_sequence_check" CHECK (sequence >= 0)
    "processes_process_sla_check" CHECK (sla >= 0)
Referenced by:
    TABLE "collection_implementation" CONSTRAINT "collection__process_id_6461d2ef37b3f126_fk_processes_process_id" FOREIGN KEY (process_id) REFERENCES processes_process(id) DEFERRABLE INITIALLY DEFERRED

I'm basically out of ideas. It looks like it's already an integer, and really, what else would it expect a Django-specified primary key to be?

Solution 1

The problem is the migration from Process.milestone as a boolean field to Process.milestone as a foreign key. Postgres doesn't wait for a migration to fail on uncastable data. It wants a rule to alter the table in advance.

If you don't intend any sort of data migration between two fields, the easiest option is to simply drop and add the field. In this specific case, it would mean changing the operations as follows:

operations = [
    migrations.RemoveField(
        model_name='process',
        name='milestone'
    ),
    migrations.AddField(
        model_name='process',
        name='milestone',
        field=models.ForeignKey(to='processes.Milestone'),
    ),
    migrations.AlterModelOptions(
        name='process',
        options={'ordering': ['milestone', 'sequence'], 'verbose_name_plural': 'processes'},
    )
]

Solution 2

Postgres doesn't know how to convert a boolean field into an integer, even if the table is empty. You need to tell it with a using clause. You can use an SQL migration for the integer conversion before you convert to a foreign key. I don't think there's a way to do this without any SQL, django doesn't know how to do that.

ALTER_SQL = '''
    ALTER TABLE processes_process ALTER COLUMN milestone TYPE integer USING (
        CASE milestone
            when TRUE then ...
            when FALSE then ...
        END
        );
    '''

class Migration(migrations.Migration):

    dependencies = [
        ('processes', '0002_auto_20150508_2149'),
    ]

    operations = [
        migrations.AlterModelOptions(
            name='process',
            options={'ordering': ['milestone', 'sequence'], 'verbose_name_plural': 'processes'},
        ),
        migrations.RunSQL(ALTER_SQL, None, [
            migrations.AlterField(
                model_name='process',
                name='milestone',
                field=models.IntegerField(),
                preserve_default=True,
            ),
        ]),
        migrations.AlterField(
            model_name='process',
            name='milestone',
            field=models.ForeignKey(to='processes.Milestone'),
        ),
    ]

If the table is empty, you might be able to get away with an empty using clause or an empty case.