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

Question

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)

jozo · Accepted Answer · 2021-07-13 11:35:25Z

5

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'
        )
    ]

edited Jul 13, 2021 at 11:35

answered Mar 17, 2019 at 19:50

jozo

4,8722 gold badges31 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dave Over a year ago

Hi, I already have a number of migrations generated in my appname/migrations folder, including an appname/migrations/0001_initial.py file. How would I do what you are suggesting in light of that?

jozo Over a year ago

If you are worried about the line with "dependencies" - Django will correctly choose last migration from migration folder when generating an empty migration. Just run that command from Step 1. Or do you stuck on something else?

Dave Over a year ago

Ah I got confused about what that statement did. But everything does work as you describe it. Thx

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related