Django unexpected IntegrityError with PostgreSQL

Question

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.

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.

it because unique=True inside your field of title, so if you has duplicates title with same name, it should return error.. — binpy
– binpy, Commented Mar 2, 2017 at 15:18
Yes i know but i want to display an error in the form not an exception. MySQL does this right out of the box. Is there any way i can accomplish this thing in PostgreSQL. I read somewhere on Google groups about this issue, they recommend creating a function index or something in in the PostgreSQL. Is that how to solve this issue ? — Cody
– Cody, Commented Mar 2, 2017 at 15:22

Graham · Accepted Answer · 2017-09-27 17:47:39Z

2

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)

edited Sep 27, 2017 at 17:47

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Mar 2, 2017 at 15:20

Adam Hopkins

7,1306 gold badges36 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cody Over a year ago

I know what migrations are. I want to show an error in the form not exception

Cody Over a year ago

Moreover if i don't issue CREATE UNIQUE INDEX title_unique on wiki_course (LOWER(title)); command manually, postgresql won't even detect the difference between "Programming Basics" and "programming basics". It will simply add the later to the database.

Adam Hopkins Over a year ago

Then, the "django" way to handle that would be to catch on the save method. Or, if you're talking about on a form on the validation. I will try and post the code I have in my head when I am back at a computer,and update my answer.

Cody Over a year ago

Yes pls post your answer. Thanks

Collectives™ on Stack Overflow

Django unexpected IntegrityError with PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related