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

Django 1.11 (2.0 should be fine too) + PostgreSQL:

1. First, create an empty migration:

   python3 manage.py makemigrations appName --empty
   

2. Django uses UPPER for inexact lookups. So create a migration for adding an UPPER(yourField) index:

   # -*- coding: utf-8 -*-
   # Generated by Django 1.11.7 on 2017-12-14 23:11
   from __future__ import unicode_literals
   
   
   from django.db import migrations
   
   class Migration(migrations.Migration):
   
       dependencies = [
           ('stats', '0027_remove_siteuser_is_admin'),
       ]
   
       operations = [
           migrations.RunSQL(
               sql=r'CREATE INDEX "stats_siteuser_upper_idx" ON "stats_siteuser" (UPPER("email"));',
               reverse_sql=r'DROP INDEX "stats_siteuser_upper_idx";'
           ),
       ]
   

As of 3.2 you can add *expressions to Index.

If you wanted to create

 CREATE INDEX size_term_insisitive_idx ON app_metadataterms (upper(term), size);

something like that should work.

from django.db.models import Index
from django.db.models.functions import Upper


class MetadataTerms(models.Model):
    term = models.CharField(max_length=200)
    size = models.IntegerField(default=0)
    validity = models.IntegerField(default=0, choices=TERM_VALIDITY_CHOICES)
    
    class Meta:
        indexes = [
            Index(
                Upper('term'), 'size',
                name='size_term_insisitive_idx',
            ),
        ]

Prior to Django 1.9 (not yet released), you could use the sqlcustom command, but if you look at the dev documentation for the upcoming 1.9, you’ll see that that command is conspicuously missing.

So:

• In <= 1.8.*, @daniel-rucci’s answer applies. Put your SQL in an SQL directory and it’ll be run in non-deterministic order.
• In >= 1.9, you need to start using the new RunSQL function as part of a migration. You could also do this in 1.7 or 1.8, if you so desired.

To inject custom sql into the django model management commands check out django-admin.py sqlcustom

You would put an sql file containing your create index in <app_name>/sql/<model_name>.sql

From the docs on when they are applied:

The SQL files are piped directly into the database after all of the
models’ table-creation statements have been executed. Use this SQL
hook to make any table modifications, or insert any SQL functions into
the database.

And you can view the custom sql for each app by running manage.py sqlcustom <app_name>