How can I create a case-insensitive database index in Django? -

i using django create database tables, so:

class metadataterms(models.model):     term = models.charfield(max_length=200)     size = models.integerfield(default=0)     validity = models.integerfield(default=0, choices=term_validity_choices) 

i running lookup queries find appropriate row correct term, matched in case-insensitive way. e.g.:

metadataterms.objects.filter(term__iexact=search_string, size=3) 

this lookup clause translates in sql:

 select "app_metadataterms"."id", "app_metadataterms"."term", "app_metadataterms"."size" "app_metadataterms" (upper("app_metadataterms"."term"::text) = upper('jack nicklaus survives')  , "app_metadataterms"."size" = 3 ); 

on postgres, can perform explain query on above, , query plan:

                                    query plan -----------------------------------------------------------------------------------  seq scan on app_metadataterms  (cost=0.00..1233.01 rows=118 width=21)    filter: ((size = 3) , (upper((term)::text) = 'jack nicklaus survives'::text)) 

because term field not indexed, , not indexed in case-normalized way, above query needs perform slow seq[uential] scan operation across database rows.

then insert simple case-normalized index, e.g.:

 create index size_term_insisitive_idx on app_metadataterms (upper(term), size); 

the above query runs 6x faster:

                                         query plan ---------------------------------------------------------------------------------------------  bitmap heap scan on app_metadataterms  (cost=5.54..265.15 rows=125 width=21)    recheck cond: ((upper((term)::text) = 'jack nicklaus survives'::text) , (size = 3))    ->  bitmap index scan on size_term_insisitive_idx  (cost=0.00..5.51 rows=125 width=0)          index cond: ((upper((term)::text) = 'jack nicklaus survives'::text) , (size = 3)) 

my question is: how can inject creation of advanced db indexes django model management commands?

prior django 1.9 (not yet released), use sqlcustom command, if @ dev documentation upcoming 1.9, you'll see that command conspicuously missing.

so:

• in <= 1.8.*, @daniel-rucci's answer applies. put sql in sql directory , it'll run in non-deterministic order.
• in >= 1.9, need start using new runsql function part of migration. in 1.7 or 1.8, if desired.