Django ProgrammingError: relation already exists after a migration created in the Django source code?

Question

I recently checked out the master branch of a project, and there were model changes not yet reflected in a migration:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  No migrations to apply.
  Your models have changes that are not yet reflected in a migration, and so won't be applied.
  Run 'manage.py makemigrations' to make new migrations, and then re-run 'manage.py migrate' to apply them.

Following the instructions, I ran makemigrations to create them:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py makemigrations
Migrations for 'auth':
  venv/lib/python3.6/site-packages/django/contrib/auth/migrations/0009_auto_20180425_1129.py
    - Alter field email on user
Migrations for 'lucy_web':
  lucy_web/migrations/0146_auto_20180425_1129.py
    - Alter field description on sessiontype
    - Alter field short_description on sessiontype

Interestingly, the 0009_auto_20180425_1129.py migration was created in the venv containing Django's source code (version 1.11.9), which I don't believe anyone on our team changed. Here is this migration:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.9 on 2018-04-25 18:29
from __future__ import unicode_literals

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('auth', '0008_alter_user_username_max_length'),
    ]

    operations = [
        migrations.AlterField(
            model_name='user',
            name='email',
            field=models.EmailField(blank=True, max_length=254, unique=True, verbose_name='email address'),
        ),
    ]

It seems 'innocent enough', but when I try to migrate, I get the following ProgrammingError:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  Applying auth.0009_auto_20180425_1129...Traceback (most recent call last):
  File "/Users/kurtpeek/Documents/Dev/lucy2/lucy-web/venv/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: relation "auth_user_email_1c89df09_uniq" already exists

Some of the answers at django.db.utils.ProgrammingError: relation already exists seem to be pretty drastic, like deleting all migrations or using the command option --fake, without providing an explanation of what fundamentally is causing the error.

Any idea how to address this error?

Answer 1

After searching for several solutions, I have found one without data loss based on Lemayzeur's suggestion:

STORED_DATA = {}

def store_data(apps, schema_editor):
    User = apps.get_model('users', 'User')
    for user in User.objects.all():
        STORED_DATA[user.id] = user.email


def restore_data(apps, schema_editor):
    User = apps.get_model('users', 'User')
    for key, value in STORED_DATA.items():
        user = User.objects.get(id=key)
        user.email = value
        user.save()


class Migration(migrations.Migration):

    dependencies = [
        ...
    ]

    operations = [
        migrations.RunPython(store_data, restore_data),
        migrations.RemoveField(
            model_name='user',
            name='email',
        ),
        migrations.AddField(
            model_name='user',
            name='email',
            field=models.EmailField(
                blank=True,
                max_length=254,
                unique=True,
                verbose_name='email address',
            )
        ),
        migrations.RunPython(restore_data, store_data),
    ]

Answer 2

In my case, the culprit was

User._meta.get_field("email")._unique = True

somewhere in the .py files.

After deleting the line, makemigrations stopped creating migration file in auth folder, while keeping the uniq constraint that was created by that line.

Answer 3

Try this, this will work:

NOTE: All data in this field will be lost

After running the last migrations, you have this file 0009_auto_20180425_1129.py which is waiting for a migrate... If you have not this file anymore, re run makemigrations to have one last migration file waiting for migrate.

Go trough that file, in your case 0009_auto_20180425_1129.py, and inside operations

I suppose you don't have any data in db

add these lines:

migrations.RemoveField(
    model_name='user',
    name='email',
),
migrations.AddField(
    model_name='user',
    name='email',
    field=models.EmailField(blank=True, max_length=254, unique=True, verbose_name='email address'   
),

feel free to comment what you get after

Answer 4

It turns out that the auth_user_email_1c89df09_uniq relation is actually a Constraint (so not data). I managed to migrate by simply dropping/deleting this constraint in pgAdmin, and similarly for the auth_user_email_1c89df09_like index (for which a ProgrammingError popped up after that).

After this, I was able to migrate:

(venv) Kurts-MacBook-Pro-2:lucy-web kurtpeek$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auditlog, auth, contenttypes, lucy_web, oauth2_provider, otp_static, otp_totp, sessions, two_factor
Running migrations:
  Applying auth.0009_auto_20180425_1129... OK
  Applying lucy_web.0146_auto_20180425_1129... OK

and the constraint and index have been put back into the auth_user table: