Can I easily over-ride Django ORM 'iexact' to use LOWER() instead of UPPER()?

Question

Using Django 1.3x.

I currently have a very, very large and very, very active Postgres data set that has an important column indexed as lower(column).

I just realized that some common queries were quite slow because the Django ORM is generating a query for the field as blah = UPPER(column) when I'm using iexact to match that field.

Is there a simple way to force the ORM to use lower() instead, or do I need to drop into raw SQL for this one?

Thanks!

[side question for the comments: Is there a good reason, overlooked, to have used upper() on the index, rather than lower()?]

Answer 1

Interesting situation here. I'd never really stopped to think about it before. Seems like the use of UPPER for iexact searches was introduced back in revision 8536, in response to ticket 3575, nearly three years ago. Before that Django had been using ILIKE for these types of searches.

I looked through the backend code and the only thing I can find that points to any reason for UPPER vs LOWER seems to be that Oracle defaults to uppercase in its treatment of case-insensitive data. Since the others are agnostic, it seems Django decided to default to UPPER to cover all the bases.

The other impression I got from looking at the source code was that you're not going to get around using UPPER. It's literally all over the place and not just when actually querying the database. Python's upper string extension is used quite frequently as well.

I'd say your best bet is to simply create an index with upper(column) as well or instead of, and go have a drink.

Answer 2

Try .extra() before going to .raw()

MyModel.objects.extra(where=["lower(mycol)=%s"], params=['foo'])