Django create unique function index

Question

With postgresql we can create unique function index:

create unique index user_date_checkin
on unique_user_date (user_id, (timezone('UTC'::text, create_time)::date));

But with Django 3.2:

class UserCheckin(GeneralModel):
   id = models.BigAutoField(primary_key=True)
   user_id = models.BigIntegerField()
   create_time = models.DateTimeField()
   
   class Meta:
       indexes = [
           models.Index("user_id", TruncDate("create_time"), name="user_date_checkin"),
        ]

can only got such sql generation:

create index user_date_checkin
    on test (user_id, (timezone('UTC'::text, create_time)::date));

And UniqueConstraint

constraints = [
    models.UniqueConstraint(
        fields=["user_id", TruncDate("create_time")],
        name="user_date"),
]

got refers to the nonexistent field 'TruncDate(F(create_time)) error

So how can I create unique index with function in Django 3.2?

update

In django source django/db/backends/base/schema.py I found:

    if condition or include or opclasses:
        sql = self.sql_create_unique_index
    else:
        sql = self.sql_create_unique

But I don't have such condition or opclasses in this case

update

After add condition:

    indexes = [
        models.Index("user_id", TruncDate("create_time"),
            condition=Q(user_id__isnull=False), name="user_date_checkin"),
    ]

Still don't add unique here:

-- auto-generated definition
create index user_date_checkin
    on voip_usercheckin (user_id, (timezone('UTC'::text, create_time)::date))
    where (user_id IS NOT NULL);

Abdul Aziz Barkat · Accepted Answer · 2024-06-10 15:14:00Z

4

From Django 4.0 onwards you can have functional unique constraints so you can write the follows:

constraints = [
    models.UniqueConstraint(
        "user_id",
        TruncDate("create_time"),
        name="user_date"),
]

Before Django 4.0 functional constraints were not supported so as a workaround you can execute your sql through a custom migration.

First run python manage.py makemigrations yourapp --empty where yourapp is the app where you have your model. Next edit the generated migration and add a RunSQL [Django-docs] operation:

from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ...
    ]

    operations = [
        migrations.RunSQL(
            sql="create unique index user_date_checkin on unique_user_date (user_id, (timezone('UTC'::text, create_time)::date));",
            reverse_sql="DROP INDEX IF EXISTS user_date_checkin;"
        ),
    ]

After this you can apply this migration by running python manage.py migrate which will create the index in the database.

edited Jun 10, 2024 at 15:14

answered Sep 1, 2021 at 5:44

Abdul Aziz Barkat

22.3k9 gold badges104 silver badges94 bronze badges

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

1 Comment

Chris Over a year ago

I think this could be the documentation now it is implemented: docs.djangoproject.com/en/5.0/ref/contrib/postgres/indexes/…

Collectives™ on Stack Overflow

Django create unique function index

update

update

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

update

update

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related