Support passing unique constraint names to bulk_create().

[Alex Vandiver]

#31685 added support for INSERT ... ON CONFLICT(col1, col2) DO UPDATE SET ... In some cases, however, the unique constraint may be on computed columns, which cannot be used with unique_fields because they are directly quoted.

For instance:

from django.db.models.functions import Upper
UserTopic.objects.bulk_create(
  [ut],
  update_conflicts=True,
  update_fields=['last_updated','visibility_policy'],
  unique_fields=['user_profile_id','stream_id',Upper('topic_name')],
)

...fails because UserTopic has no field named 'Upper(F(topic_name))'.

And:

UserTopic.objects.bulk_create(
  [ut],
  update_conflicts=True,
  update_fields=['last_updated','visibility_policy'],
  unique_fields=['user_profile_id','stream_id','upper(topic_name)'],
)

...fails similarly, with UserTopic has no field named 'upper(topic_name)'.

It would be a useful feature to be able to handle these cases.

[Mariusz Felisiak]

Sounds reasonable.

[Simon Charette]

Maybe the would be an opportunity to deprecate/rename the unique_fields kwarg name to unique_expressions or unique_together to denote that not only fields are acceptable here.

Or we could take an hybrid approach like we did with Index and Constraint where either allow update_fields or update_expressions to be specified.

I get a sense that we should have preferred a unique_constraint kwarg that points to the name of an entry of Meta.constraints or the name of a Field(unique) in the first place instead of forcing users to specify the exact same index definition they have in their model, Meta.unique_together, or Meta.constraints instead.

For backends that support the ON CONFLICT ON CONSTRAINT clause we could have simply used it for the constraint name specified otherwise we know what the index expression associated with the specified constraint is so we can generate the proper SQL.

In other words, for a model like

class UserTopic(models.Model):
    ...
    class Meta:
        constraints = [
            UniqueConstraint(
                'user_profile_id','stream_id',Upper('topic_name'),  
                name='unique_profile_stream_topic'
            )
        ]

The call to bulk_create could simply have been

UserTopic.objects.bulk_create(
    [ut],
    update_fields=['last_updated','visibility_policy'],
    unique_constraint='unique_profile_stream_topic',
)

---

My recommendation would be to introduce a `unique_constraint: str | tuple[str | Expression]` kwarg and deprecate `unique_fields`. When provided a `str` it would be a reference to a `UniqueConstraint` by `.name` and when it's a `tuple` it would be expected to be a index expression where `str` are resolved to field names. I guess the `str` -> `UniqueConstraint.name` part could be a distinct feature request.

[Mariusz Felisiak]

Great suggestion, Simon!

[Alex Vandiver]

It's worth noting that [PostgreSQL's INSERT documentation](​https://www.postgresql.org/docs/current/sql-insert.html) suggests against ON CONFLICT ON CONSTRAINT, preferring the raw list of expressions:

It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using CREATE UNIQUE INDEX ... CONCURRENTLY before dropping the index being replaced.

That is, naming a specific constraint is in general more brittle, since it depends on the specific name, rather than naming the effect of the constraint.

In our particular use case, we also currently only have a unique index, not a constraint -- and I'm not sure how well doing that migration would go. But that's a separate issue.

[Simon Charette]

Makes sense.

I guess that from an application maintenance perspective there are still benefits in using references to already defined constraint though, think of urlpattern names, annotations, lookups.

To take back the example above nothing would prevent the ORM from simply resolving unique_constraint='unique_profile_stream_topic' to ('user_profile_id', 'stream_id', Upper('topic_name')) and avoid the database level usage of ON CONFLICT ON CONSTRAINT if that's discouraged.

In other words, the aliasing capabilities could be solely done at the application level assuming users opt-in into the usage of Meta.constraints. That would also happen to address the issue involved in dealing with the difference between unique indexes and constraints as Postgres treat them differently.

[HAMA Barhamou]

Replying to Simon Charette:

Maybe the would be an opportunity to deprecate/rename the unique_fields kwarg name to unique_expressions or unique_together to denote that not only fields are acceptable here.

Or we could take an hybrid approach like we did with Index and Constraint where either allow update_fields or update_expressions to be specified.

I get a sense that we should have preferred a unique_constraint kwarg that points to the name of an entry of Meta.constraints or the name of a Field(unique) in the first place instead of forcing users to specify the exact same index definition they have in their model, Meta.unique_together, or Meta.constraints instead.

For backends that support the ON CONFLICT ON CONSTRAINT clause we could have simply used it for the constraint name specified otherwise we know what the index expression associated with the specified constraint is so we can generate the proper SQL.

In other words, for a model like

class UserTopic(models.Model):
    ...
    class Meta:
        constraints = [
            UniqueConstraint(
                'user_profile_id','stream_id',Upper('topic_name'),  
                name='unique_profile_stream_topic'
            )
        ]

The call to bulk_create could simply have been

UserTopic.objects.bulk_create(
    [ut],
    update_fields=['last_updated','visibility_policy'],
    unique_constraint='unique_profile_stream_topic',
)

---

My recommendation would be to introduce a unique_constraint: str | tuple[str | Expression] kwarg and deprecate unique_fields. When provided a str it would be a reference to a UniqueConstraint by .name and when it's a tuple it would be expected to be a index expression where str are resolved to field names.

I guess the str -> UniqueConstraint.name part could be a distinct feature request but it seems related to this issue given the only way Django supports creating unique constraints composed of expressions is through Meta.constaints and having to define the exact expression twice is error prone.

Hi, If you depreciated unique_fields, what would that mean for ticket #34277 https://code.djangoproject.com/ticket/34277, which I'm working on?

[HAMA Barhamou]

I will make a proposal to work on the ticket for GSoC 2024.