Each Answer to this Q is separated by one/two green lines.
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.
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
CICharField, which are case-insensitive versions of
Store the original mixed-case string in a plain text column. Use the data type
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.
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
The second option is much hackier and convoluted. I’m hesitant to even suggest it, but for completeness sake, I will: override
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()
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
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
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.
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.
You can use lookup=’iexact’ in UniqueValidator on serializer, like this:
Unique model field in Django and case sensitivity (postgres)
I liked Chris Pratt’s Answer but it didn’t worked for me, because the
models.Manager-class doesn’t have the
I had to take an extra step via a custom
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.
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)