Django create unique function index

user_976371 0
Viewed 163

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);
postgresql django-models django
1 Answers

Django does not support functional constraints yet. But this feature is already in development and will be part of Django version 4.0 which is expected to be released in December 2021. For now 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.

After Django 4.0 is released you can likely (might change) write the follows:

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