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:11from __future__ import unicode_literalsfrom django.db import migrationsclass 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.

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.