Case insensitive unique model fields in Django?
I have basically a username is unique (case insensitive), but the case matters when displaying as provided by the user.
I have the following requirements:
- field is CharField compatible
- field is unique, but case insensitive
- field needs to be searchable ignoring case (avoid using iexact, easily forgotten)
- field is stored with case intact
- preferably enforced on database level
- preferably avoid storing an extra field
Is this possible in Django?
The only solution I came up with is "somehow" override the Model manager, use an extra field, or always use 'iexact' in searches.
I'm on Django 1.3 and PostgreSQL 8.4.2.
Solution 1:
As of Django 1.11, you can use CITextField, a Postgres-specific Field for case-insensitive text backed by the citext type.
from django.db import models
from django.contrib.postgres.fields import CITextField
class Something(models.Model):
foo = CITextField()
Django also provides CIEmailField
and CICharField
, which are case-insensitive versions of EmailField
and CharField
.
Solution 2:
Store the original mixed-case string in a plain text column. Use the data type text
or varchar
without length modifier rather than varchar(n)
. They are essentially the same, but with varchar(n) you have to set an arbitrary length limit, that can be a pain if you want to change later. Read more about that in the manual or in this related answer by Peter Eisentraut @serverfault.SE.
Create a functional unique index on lower(string)
. That's the major point here:
CREATE UNIQUE INDEX my_idx ON mytbl(lower(name));
If you try to INSERT
a mixed case name that's already there in lower case you get a unique key violation error.
For fast equality searches use a query like this:
SELECT * FROM mytbl WHERE lower(name) = 'foo' --'foo' is lower case, of course.
Use the same expression you have in the index (so the query planner recognizes the compatibility) and this will be very fast.
As an aside: you may want to upgrade to a more recent version of PostgreSQL. There have been lots of important fixes since 8.4.2. More on the official Postgres versioning site.
Solution 3:
With overriding the model manager, you have two options. First is to just create a new lookup method:
class MyModelManager(models.Manager):
def get_by_username(self, username):
return self.get(username__iexact=username)
class MyModel(models.Model):
...
objects = MyModelManager()
Then, you use get_by_username('blah')
instead of get(username='blah')
, and you don't have to worry about forgetting iexact
. Of course that then requires that you remember to use get_by_username
.
The second option is much hackier and convoluted. I'm hesitant to even suggest it, but for completeness sake, I will: override filter
and get
such that if you forget iexact
when querying by username, it will add it for you.
class MyModelManager(models.Manager):
def filter(self, **kwargs):
if 'username' in kwargs:
kwargs['username__iexact'] = kwargs['username']
del kwargs['username']
return super(MyModelManager, self).filter(**kwargs)
def get(self, **kwargs):
if 'username' in kwargs:
kwargs['username__iexact'] = kwargs['username']
del kwargs['username']
return super(MyModelManager, self).get(**kwargs)
class MyModel(models.Model):
...
objects = MyModelManager()