[FIXED] How to generate a function-based index in my Python model migration?

Issue

I'm using Django, Python 3.7 and PostGres 9.5. To speed along a particular query, I want to create this functional index, which I'd normally do in PostGres like so ...

CREATE INDEX my_article_idx ON article (regexp_replace(url, '\?.*$', ''))

However in the world of Django and auto-generated migrations, I'm not sure how to annotate my class in my models.py file so that this function-based index would be auto-generated. The field in question in my model looks like this ...

class Article(models.Model):
    ...
    url = models.TextField(default='', null=False)

Solution

You have to create a data migration. Read more about them in docs

Step 1 - create an empty data migration file

python manage.py makemigrations --empty yourappname

Step 2 - Add custom sql to the migration:

# -*- coding: utf-8 -*-
# Generated by Django 1.11.14 on 2018-09-20 08:01
from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('yourapp', '0001_name_of_depending_migration'),
    ]

    operations = [
        migrations.RunSQL(
            sql="CREATE INDEX my_article_idx ON article (regexp_replace(url, '\?.*$', ''))",
            reverse_sql='DROP INDEX my_article_idx ON article'
        )
    ]

Answered By - jozo

Answer Checked By - Marie Seifert (PHPFixing Admin)