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)
As of django-4.0, 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')
]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'),
]