djangopostgresqlindexingpartialpartial-index

How to create partial index in django 2.2 models


I have a model like this in Django 2.2

class Post(models.Model):
    class Meta:
        verbose_name = _('Post')
        verbose_name_plural = _('Posts')

    phone_number = models.CharField(max_length=11, verbose_name=_('Phone number'), db_index=True)

    token = models.CharField(unique=True, max_length=20, verbose_name=_('Token'), db_index=True)
 
    post_state = models.SmallIntegerField(choices=[
        (PostState.NEW, _('New')),
        (PostState.PUBLISHED, _('Published')),
        (PostState.ARCHIVED, _('Archive')),
        (PostState.REMOVED_BEFORE_PUBLISH, _('Removed before publish')),
        (PostState.REJECTED_AFTER_PUBLISH, _('Rejected after publish')),
    ], default=PostState.NEW, verbose_name=_('Post state'), db_index=True)
    

I want the index I have on phone_number and token to be partial based on post_state value, I know how to do it with SQL commands in Postgres shell but I don't know how to do it in django models so it goes into a migration file too.


Solution

  • So I found the answer to this question.

    class Post(models.Model):
        class Meta:
            verbose_name = _('Post')
            verbose_name_plural = _('Posts')
            indexes = (
                BTreeIndex(fields=('token',), condition=Q(post_state__in=[PostState.NEW, PostState.PUBLISHED])),
            )
    
        phone_number = models.CharField(max_length=11, verbose_name=_('Phone number'), db_index=True)
    
        token = models.CharField(unique=True, max_length=20, verbose_name=_('Token'))
     
        post_state = models.SmallIntegerField(choices=[
            (PostState.NEW, _('New')),
            (PostState.PUBLISHED, _('Published')),
            (PostState.ARCHIVED, _('Archive')),
            (PostState.REMOVED_BEFORE_PUBLISH, _('Removed before publish')),
            (PostState.REJECTED_AFTER_PUBLISH, _('Rejected after publish')),
        ], default=PostState.NEW, verbose_name=_('Post state'))
    

    we make the index in the Meta part of the model and it has a condition argument in which you can pass the partial condition.