Indexes

Unique Index

Warning

In Django 2.2 or newer, you might want to use UniqueConstraint instead.

The UniqueIndex lets you create a unique index. Normally Django only allows you to create unique indexes by specifying unique=True on the model field.

Although it can be used on any Django model, it is most useful on views and materialized views where unique=True does not work.

from django.db import models
from psqlextra.indexes import UniqueIndex

class Model(models.Model):
    class Meta:
        indexes = [
            UniqueIndex(fields=['name']),
        ]

    name = models.CharField(max_length=255)

Model.objects.create(name='henk')
Model.objects.create(name='henk') # raises IntegrityError

Conditional Unique Index

Warning

In Django 3.1 or newer, you might want to use condition instead.

The ConditionalUniqueIndex lets you create partial unique indexes in case you ever need unique_together constraints on nullable columns.

Before:

from django.db import models

class Model(models.Model):
    class Meta:
        unique_together = ['a', 'b']

    a = models.ForeignKey('some_model', null=True)
    b = models.ForeignKey('some_other_model')

# Works like a charm!
b = B()
Model.objects.create(a=None, b=b)
Model.objects.create(a=None, b=b)

After:

from django.db import models
from psqlextra.indexes import ConditionalUniqueIndex

class Model(models.Model):
    class Meta:
        indexes = [
            ConditionalUniqueIndex(fields=['a', 'b'], condition='"a" IS NOT NULL'),
            ConditionalUniqueIndex(fields=['b'], condition='"a" IS NULL')
        ]

    a = models.ForeignKey('some_model', null=True)
    b = models.ForeignKey('some_other_model')

# Integrity Error!
b = B()
Model.objects.create(a=None, b=b)
Model.objects.create(a=None, b=b)

Case Insensitive Unique Index

Warning

In Django 3.1 or newer, you might want to use condition instead.

The CaseInsensitiveUniqueIndex lets you create an index that ignores the casing for the specified field(s).

This makes the field(s) behave more like a text field in MySQL.

from django.db import models
from psqlextra.indexes import CaseInsensitiveUniqueIndex

class Model(models.Model):
    class Meta:
        indexes = [
            CaseInsensitiveUniqueIndex(fields=['name']),
        ]

    name = models.CharField(max_length=255)

Model.objects.create(name='henk')
Model.objects.create(name='Henk') # raises IntegrityError