Django: Add a unique index on expressions in postgres

Issue

Consider the django model –

class Students(models.Model)
   id = models.BigAutoField(primary_key=True)
   scoreA = models.CharField(null=True, max_length=15)
   scoreB = models.CharField(null=True, max_length=15)
   

I’m looking to add this unique index.

create unique index unq_idx on students ((case  when scoreA is not NULL then scoreA else '' end), (case when scoreB is not NULL then scoreB else '' end));

How do I add it through the django ORM ?

I’m using Django 3.1 with postgres 12.1

The use-case is to have a unique constraint over the two fields which doesn’t allow multiple NULL values (Link)

Solution

Got it working with Django 3.2 using Index.expressions and the UniqueIndex tweak from django-postgres-extra

class Students(models.Model)
   id = models.BigAutoField(primary_key=True)
   scoreA = models.CharField(null=True, max_length=15)
   scoreB = models.CharField(null=True, max_length=15)

   class Meta:
       indexes = [
           UniqueIndex(
               Case(When(scoreA__isnull=False, then=F('scoreA')), default=Value('')),
               Case(When(scoreB__isnull=False, then=F('scoreB')), default=Value('')),
               name='unique_idx'),
       ]

Answered By – shawdowfax1497

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published