[Django]-Django: Add a unique index on expressions in postgres

by

2👍

✅

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'),
       ]
👤shawdowfax1497

3👍

As of , it will be possible to make functional unique constraints [Django-doc]. In that case you can define such constraint with:

from django.db.models import UniqueConstraint, Value
from django.db.models.functions import Coalesce

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:
        constraints = [
            UniqueConstraint(Coalesce('scoreA', Value('')), Coalesce('scoreB', Value('')), name='unique_score_combo')
        ]
👤willeM_ Van Onsem

Leave a comment