Django: Add a unique index on expressions in postgres

Question

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)

In Django-3.2 you can create functional indexes, but not in 3.1. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Oct 26, 2021 at 10:46
But, even with Django-3.2, I don't see a way to create a functional unique index — shawdowfax1497
– shawdowfax1497, Commented Oct 26, 2021 at 11:14

willeM_ Van Onsem · Accepted Answer · 2022-04-08 16:07:05Z

3

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

edited Apr 8, 2022 at 16:07

answered Oct 28, 2021 at 10:53

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Comments

shawdowfax1497 · Accepted Answer · 2021-10-29 12:06:32Z

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

answered Oct 29, 2021 at 12:06

shawdowfax1497

1709 bronze badges

1 Comment

Q Caron Over a year ago

Unfortunately, as of may 17 2024, the latest stable version (v2.0.8) has been released on Feb 3, 2023... The last issue triage also dates back to February 2023. I would advise not to use django-postgres-extra within companies.

Collectives™ on Stack Overflow

Django: Add a unique index on expressions in postgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related