psycopg2.errors.UniqueViolation: could not create unique index

August 06, 2019 - IT

Recently, my migration script raised an error like this:

psycopg2.errors.UniqueViolation: could not create unique index "usersuseremail243f6e77uniq" DETAIL: Key (email)=([email protected]) is duplicated.

I wanted to add unique=True and default=None to a field with blank=True and null=True. The database already contained users with empty email field (empty string) and Django couldn't apply the migration.

According to docs:

One exception is when a CharField has both unique=True and blank=True set. In this situation, null=True is required to avoid unique constraint violations when saving multiple objects with blank values.

Solution?

I had to create custom migration and put it before the email field migration. The migration converts all email values from an empty string to null.

To create an empty migration, use --empty param.

python manage.py makemigrations users --empty

The migration.

from django.db import migrations, models


def postgres_migration_prep_user_email_field(apps, schema_editor):
    User = apps.get_model("users", "User")
    field = "email"

    filter_param = {"{}__exact".format(field): ""}
    update_param = {field: None}
    User.objects.filter(**filter_param).update(**update_param)


class Migration(migrations.Migration):

    dependencies = [("users", "0003_auto_20190725_1034")]

    operations = [
        migrations.RunPython(
            postgres_migration_prep_user_email_field, migrations.RunPython.noop
        )
    ]

This migration does not contain reverse code which is required for unapplying migrations. I didn't need it.