CODEWITHSUNDEEP
pythonmysqldjangopostgresql
Cody
Cody

Reputation: 2649

Django unexpected IntegrityError with PostgreSQL

I am using postgreSQL with Django 1.10 and python 3.4. I have a Course model defined like this:

class Course(models.Model):

    title = models.CharField(max_length=200, unique=True)
    content = models.TextField(max_length=200)

Then i manually added unique index to the title column using the following command.

CREATE UNIQUE INDEX title_unique on wiki_course (LOWER(title));

Lets say database already has "Programming Basics" as title. When I add "programming basics" to title and hit save , it shows me the following error.

IntegrityError at /admin/wiki/course/add/
duplicate key value violates unique constraint "title_unique"
DETAIL:  Key (lower(title::text))=(programming basics) already exists.
Request Method: POST
Request URL:    http://127.0.0.1:8000/admin/wiki/course/add/
Django Version: 1.10.5
Exception Type: IntegrityError
Exception Value:    
duplicate key value violates unique constraint "title_unique"

On the otherhand if i switch the database from MySQL and try again it would tell me Course already exists.

enter image description here

Is there any way to achieve this behavior in PostgreSQL?

Update: One solution is to use to use citext type field for the title. To do this first you have to enable citext extension, using the following command.

CREATE EXTENSION IF NOT EXISTS citext;

Then use alter statement to change the type of the desired column.

ALTER TABLE course ALTER COLUMN title TYPE citext;

After executing these two queries. Django shows error in the form instead of throwing an IntegrityError exception.

If someone knows a better solution to it pls post it.

Upvotes: 1

Views: 622

Answers (1)

Adam Hopkins
Adam Hopkins

Reputation: 7102

MIGRATIONS

Your best bet is to not alter constraints on the database itself and instead allow Django to handle changes to your models.

Let's say you had this already existing.

class Course(models.Model):
    title = models.CharField(max_length=200)

Then, you decide to make the title unique.

class Course(models.Model):
    title = models.CharField(max_length=200, unique=True)

To enforce this, you do not go into the database and call the command directly. Instead we allow Django to handle the migrations for us.

$ ./manage.py makemigrations
$ ./manage.py migrate

SOLUTION #1 - ON SAVE

class Course(models.Model):
    title = models.CharField(max_length=200, unique=True)

    def save(self, *args, **kwargs):
        try:
            Course.objects.get(title__iexact=self.title)
            raise models.ValidationError
        except Course.DoesNotExist:
            pass
        super().save(*args, **kwargs)

The problem with this method is that it makes another call to your database for each save. Not ideal.

SOLUTION #2 - Two fields

You could also have a sort of dummy field that always stores the lower case string and provide the unique on that.

class Course(models.Model):
    title = models.CharField(max_length=200)
    lower_title = models.CharField(max_length=200, unique=True, blank=True)

    def save(self, *args, **kwargs):
        self.lower_title = self.title.lower()
        super().save(*args, **kwargs)

Here, we set blank=True so that the lower_title does not throw an error for being empty.

One drawback of this is that it creates slightly more overhead in your database because the title is stored in two locations. The idea here though being that we run the unique constraint against a field that we ALWAYS know will be lowercase. Then, just use title where you need to.

Similarly, we could use django's slugify to achieve a similar result.

from django.template.defaultfilters import slugify


class Course(models.Model):
    title = models.CharField(max_length=200)
    slug = models.SlugField(max_length=200, unique=True)

    def save(self, *args, **kwargs):
        self.slug = slugify(self.title)
        super().save(*args, **kwargs)

Upvotes: 2

Related Questions