Case insensitive unique model fields in Django?
PythonDjangoPostgresqlModelPython Problem Overview
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.
Python Solutions
Solution 1 - Python
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 - Python
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 - Python
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()
Solution 4 - Python
Since a username is always lowercase, it's recommended to use a custom lowercase model field in Django. For the ease of access and code-tidiness, create a new file fields.py
in your app folder.
from django.db import models
from django.utils.six import with_metaclass
# Custom lowecase CharField
class LowerCharField(with_metaclass(models.SubfieldBase, models.CharField)):
def __init__(self, *args, **kwargs):
self.is_lowercase = kwargs.pop('lowercase', False)
super(LowerCharField, self).__init__(*args, **kwargs)
def get_prep_value(self, value):
value = super(LowerCharField, self).get_prep_value(value)
if self.is_lowercase:
return value.lower()
return value
Usage in models.py
from django.db import models
from your_app_name.fields import LowerCharField
class TheUser(models.Model):
username = LowerCharField(max_length=128, lowercase=True, null=False, unique=True)
End Note : You can use this method to store lowercase values in the database, and not worry about __iexact
.
Solution 5 - Python
As of December 2021, with the help of Django 4.0 UniqueConstraint expressions you can add a Meta class to your model like this:
class Meta:
constraints = [
models.UniqueConstraint(
Lower('<field name>'),
name='<constraint name>'
),
]
I'm by no mean a Django professional developer and I don't know technical considerations like performance issues about this solution. Hope others comment on that.
Solution 6 - Python
You can use citext postgres type instead and not bother anymore with any sort of iexact. Just make a note in model that underlying field is case insensitive. Much easier solution.
Solution 7 - Python
You can use lookup='iexact' in UniqueValidator on serializer, like this: https://stackoverflow.com/questions/1857822/unique-model-field-in-django-and-case-sensitivity-postgres/47999495#47999495
Solution 8 - Python
I liked Chris Pratt's Answer but it didn't worked for me, because the models.Manager
-class doesn't have the get(...)
or filter(...)
Methods.
I had to take an extra step via a custom QuerySet
:
from django.contrib.auth.base_user import BaseUserManager
from django.db.models import QuerySet
class CustomUserManager(BaseUserManager):
# Use the custom QuerySet where get and filter will change 'email'
def get_queryset(self):
return UserQuerySet(self.model, using=self._db)
def create_user(self, email, password, **extra_fields):
...
def create_superuser(self, email, password, **extra_fields):
...
class UserQuerySet(QuerySet):
def filter(self, *args, **kwargs):
if 'email' in kwargs:
# Probably also have to replace...
# email_contains -> email_icontains,
# email_exact -> email_iexact,
# etc.
kwargs['email__iexact'] = kwargs['email']
del kwargs['email']
return super().filter(*args, **kwargs)
def get(self, *args, **kwargs):
if 'email' in kwargs:
kwargs['email__iexact'] = kwargs['email']
del kwargs['email']
return super().get(*args, **kwargs)
This worked for me in a very simple case but is working pretty good so far.
Solution 9 - Python
You can also override "get_prep_value" by Django Models Field
class LowerCaseField:
def get_prep_value(self, value):
if isinstance(value, Promise):
value = value._proxy____cast()
if value:
value = value.strip().lower()
return value
class LCSlugField(LowerCaseField, models.SlugField):
pass
class LCEmailField(LowerCaseField, models.EmailField):
pass
email = LCEmailField(max_length=255, unique=True)