Model uniqueness constraint with date extraction

[Thierry Backes]

Hey,

 

I have a model which has a date and a foreign key field. I want a uniqueness constraint that each fk can only be used once per month, so Unique(date__year, date__month, fk). However, when I use this in my model’s metadata:

 

models.UniqueConstraint(fields=['date__year', 'date__month', 'category'], name='date_month_cat_unique')

 

I get an error that the date__year field doesn’t exist. This is a bit strange because I use this syntax throughout my app and I also know that such constraints are supported by Postgres:

 

create unique index year_month_uq

  on foo

  ( extract(year from mydate),

    extract(month from mydate)

  ) ;

 

As per https://dba.stackexchange.com/questions/210736/postgresql-enforcing-unique-constraint-on-date-column-parts

 

What am I doing wrong here? I’m using Django 3 and Postgres.

 

Thanks

[Simon Charette]

Hello Thierry,

Django doesn't support support constraints on lookups/expressions but there's ungoing work to do so[0].

In the mean time you'll have to rely on a `RunSQL` operation  in your migrations to create the constraint.

Cheers,

Simon

[0] https://code.djangoproject.com/ticket/30916