Take the 2-minute tour ×

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.

share|improve this question
add comment

2 Answers

up vote 9 down vote accepted

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.

share|improve this answer
+1 really excellent solution to the uniqueness issue. –  Chris Pratt Oct 14 '11 at 21:51
Thank you for the solution. I ended up using this one and the one below so now you can't just work around the code. –  noobzie Oct 17 '11 at 15:50
Great solution. Is there a way to do this using Django ORM? Or should I do it in PostgreSQL directly? –  Cartucho Apr 12 '13 at 19:02
@fcrazy: I am no expert with Django, but a single raw SQL call for the CREATE UNIQUE INDEX ... statement should do the job. –  Erwin Brandstetter Apr 12 '13 at 19:19
@ErwinBrandstetter Thanks Erwin, I made my own research and seems that a good place to do this in Django is adding the file <appname>/sql/<modelname>.sql, where <appname> is the given app, just as explain it here: docs.djangoproject.com/en/1.5/ref/django-admin/… –  Cartucho Apr 12 '13 at 19:50
add comment

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:
            del kwargs['username']
        return super(MyModelManager, self).get(**kwargs)

class MyModel(models.Model):
   objects = MyModelManager()
share|improve this answer
Thanks! Works like a charm. –  noobzie Oct 17 '11 at 15:50
I like the hackier version better than custom method version +1 for hackiness! –  Burhan Khalid Aug 28 '13 at 10:41
I prefer this method, especially the hackier version, over the accepted answer because this is DBMS-agnostic. It makes you stick with Django's case-insensitive QuerySet methods in the end, so Django can still generate the SQL statements with the proper collation coercion, regardless of the DBMS backend. –  Kal Nov 1 '13 at 8:51
add comment

Your Answer


