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?
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
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);
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.