Can I easily move 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 dataset that has an important column with an index lower(column) .

I only realized that some general queries were rather slow because Django ORM generates a query for a field like blah = UPPER(column) when I use iexact to match this field.

Is there an easy way to force ORM to use lower() instead, or do I need to switch to raw SQL for this?

Thanks!

[side question for comments: is there a good reason omitted to use upper() for the index, not lower() ?]

Answer 1

An interesting situation here. I have never stopped thinking about this before. It seems that the use of UPPER for iexact searches was introduced in edition 8536 in response to ticket 3575 , almost three years ago. Prior to this, Django used ILIKE for these types of searches.

I looked at the backend code and the only thing I can find points to any UPPER vs LOWER reason, it seems that Oracle uses uppercase by default when processing UPPER data. Since other agnostics, it seems, Django decided by default UPPER cover all the bases.

Another impression I got from viewing the source code was that you are not going to get by with UPPER . This is literally everywhere, not just the actual query of the database. The Python UPPER string extension is used quite often.

I would say that it’s best to just create an index using upper(column) , not, and go for a drink.

Answer 2

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

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