Some issues with UUID while migrating from SQLite to Postgresql in Django 2.0

Question

I am trying to migrate my Django 2.0.4 project from SQLite to PostgreSQL 10 following the steps described here, but I am having differents problems. During the project I changed some Integer fields to UUID4 fields. I managed to run python manage.py migrate --run-syncdb manually editing auto_increment migration file making changes of this type (see id field): From

class Migration(migrations.Migration):

    dependencies = [
        ('dumps', '0011_auto_20180608_1714'),
    ]

    operations = [
        migrations.CreateModel(
            name='Report',
            fields=[
                ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
                ('data', models.DateTimeField(auto_now_add=True, verbose_name='Date')),
            ],
        ),
        ...
        ...
        ...

To

class Migration(migrations.Migration):

    dependencies = [
        ('dumps', '0011_auto_20180608_1714'),
    ]

    operations = [
        migrations.CreateModel(
            name='Report',
            fields=[
                ('id', models.UUIDField(default=uuid.uuid4, editable=False, primary_key=True, serialize=False, verbose_name='ID')),
                ('data', models.DateTimeField(auto_now_add=True, verbose_name='Date')),
            ],
        ),
        ...
        ...
        ...

Next, I commented all auto_increment files in which there was an AlterTable on uuid fields, but when I run python manage.py loaddata datadump.json I obtain the following error:

django.db.utils.ProgrammingError: Problem installing fixture 'C:\Users\djangoproject\datadump.json': Could not load myApp.Reservation(pk=10d00b08-bf35-469f-b53f-ec28f8b6ecb3): ERROR:  column "reservation_id" is integer type but the expression is uuid type
LINE 1: UPDATE "myApp_reservation" SET "reservation_id" = '066cff3c-4b...

Hi @bit the issue here is exactly as described, you changed an IntegerField to UUIDField...how deep are you into this project? Are you still in local development? — , Aug 27 '18 at 14:30
The only other thing I can think of now is backing up everything - and seeing if dropping the `primary_key=True` constraint ... but definitely back everything up first! — , Aug 27 '18 at 14:31

score 1 · Answer 1 · answered Aug 27 '18 at 14:38

1

I think the issue here is that you have old migrations which refer to the int PK field column as an AutoField() before you made the change to use a UUIDField().

You may need to leave the id field as it was (perhaps reverse back your migrations to the point at which the swithc was made), and include a new field (and thus column of type uuid) named uuid in your Report model:

class Report(models.Model)

    id = models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')
    uuid = models.UUIDField(default=uuid.uuid4, editable=False, serialize=False, verbose_name='UUID')
    data = models.DateTimeField(auto_now_add=True, verbose_name='Date')
    ...

Then re-run database migrations ... you'll likely hit some more migration errors but give me a shout and I can advise on where to go from there in the chat.

answered Aug 27 '18 at 14:38

Hi @Micheal J Roberts and thanks for your answer. Unfortunately, my project is already over and in production environment. I am doing these tests in local env before to try in production. I would like to keep 'id' field as uuid type instead of to add a second field named 'uuid'. Otherwise I have to change model, view and each template file – bit Aug 27 '18 at 15:09
You can’t change over like that tho - unfortunately. As you’ll already have stored ids as integers. – Aug 27 '18 at 15:19
It is very strange that such a common problem does not have an official solution. If I add `uuid` field, can I delete `id` and rename `uuid` in `id` with a further `python manage makemigrations/migrate`? – bit Aug 27 '18 at 15:26
It’s not got a solution no because of database design, once db columns are specified with a type and start filling to with data you don’t really have an option to switch. I wouldn’t advise doing it on the production environment. Just add in the extra field you need and update the codebase accordingly. Do any of your views/urls rely on this id field? – Aug 27 '18 at 15:34
Yes, I used it in view file as href link. I will try to follow your suggestion, but I am very surprised that there is no less expensive solution – bit Aug 27 '18 at 16:01
the solution you proposed to me does not seems to work because, as I said, the project is already started and the change `id = models.AlterField(...)` => `id = models.UUIDField` was made halfway through the project. So if I try to edit my 'Report' class according to your directions I have a `django.db.utils.IntegrityError: datatype mismatch` – bit Aug 28 '18 at 09:25
You will have to add blank=True to the uuid field, and backfill your old entries. – Aug 28 '18 at 09:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/178922/discussion-between-bit-and-micheal-j-roberts). – bit Aug 28 '18 at 09:42
the same error: `django.db.utils.IntegrityError: datatype mismatch` – bit Aug 28 '18 at 10:12
What column names have you got? It seems to me that you have maybe implemented my solution on top of your broken database...please revert back to before you manually changed your migrations files. – Aug 28 '18 at 10:15
I revert back to the last working version of my project with sqllite. I revert back every migration file to their original version. I only edited model according to your suggestion and run `python manage.py makemigrations` / `python manage.py migrate`. The error appears when I run migrate – bit Aug 28 '18 at 10:25
Did you add `uuid` or replace `id` with `uuid`? – Aug 28 '18 at 10:27
I added uuid field just like you suggested: `id = models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')` `uuid = models.UUIDField(default=uuid.uuid4, editable=False, serialize=False, verbose_name='UUID', blank=True)` – bit Aug 28 '18 at 10:35
What I do not understand is how it all works with SQLlite (and with uuid field named 'id') but migration to Postgres failed. Indeed, in fact, the whole project works on sqllite – bit Aug 28 '18 at 10:39

score 0 · Accepted Answer · answered Aug 28 '18 at 12:59

0

I understood where the error was. The problem was in postgre table scheme: 'id' field had 'integer' type instead of 'uuid'. I converted it to 'uuid' and the import was successful.

answered Aug 28 '18 at 12:59

bit

427
1
6
14

Some issues with UUID while migrating from SQLite to Postgresql in Django 2.0

2 Answers2