Postgres and Django Case Sensitivity

Postgres queries are CaSeSensitivE unlike in MySQL.

When a simple Model.objects.get(field='hello') would get the record.
Using a postgres db you would need to use field__iexact='hello'

Even unique doesn’t work with different cases with standard postgres. That is why django has the CIText Mixin and postgres has the citext extension.

Could be a feature not a bug…

This also affects a field with unique_together set.
The case sensitivity will still come into play.

You can make the field lowercase by overriding the model’s save method:

    def save(self, **kwargs):
        '''Override save to enforce project name is lowercase'''
        self.name = self.name.lower()
        super().save()

But you don’t need to. Juse ensure the field is a CICharField. A case insensitive field.

Ran into another problem:

crowdminder=# create extension citext;
ERROR:  extension "citext" already exists

So citext is already there but when running a django migration I get:

django.db.utils.ProgrammingError: type "citext" does not exist
LINE 1: ...llink" ("id" serial NOT NULL PRIMARY KEY, "email" citext NUL...

So I go into psql:

drop extension citext cascade;

Then the migrate works.

It’s crazy and weird.
If this happens on production, it could certainly cause problems.