Allow returning IDs in QuerySet.bulk_create() when updating conflicts.

[Thomas C]

Currently, when using bulk_create with a conflict handling flag turned on (e.g. ignore_conflicts or update_conflicts), the primary keys are not set in the returned queryset, as documented in bulk_create.

While I understand using ignore_conflicts can lead to PostgreSQL not returning the IDs when a row is ignored (see ​this SO thread), I don't understand why we don't return the IDs in the case of update_conflicts.

For instance:

MyModel.objects.bulk_create([MyModel(...)], update_conflicts=True, update_fields=[...], unique_fields=[...])

generates a query without a RETURNING my_model.id part:

INSERT INTO "my_model" (...)
VALUES (...)
    ON CONFLICT(...) DO UPDATE ...

If I append the RETURNING my_model.id clause, the query is indeed valid and the ID is returned (checked with PostgreSQL).

I investigated a bit and ​this in Django source is where the returning_fields gets removed.

I believe we could discriminate the cases differently so as to keep those returning_fields in the case of update_conflicts.

This would be highly helpful when using bulk_create as a bulk upsert feature.

[Mariusz Felisiak]

Thanks for the ticket. I've checked and it works on PostgreSQL, MariaDB 10.5+, and SQLite 3.35+:

django/db/models/query.py

@@ -1837 +1837 @@
         inserted_rows = []
         bulk_return = connection.features.can_return_rows_from_bulk_insert
         for item in [objs[i : i + batch_size] for i in range(0, len(objs), batch_size)]:
-            if bulk_return and on_conflict is None:
+            if bulk_return and (on_conflict is None or on_conflict == OnConflict.UPDATE):
                 inserted_rows.extend(
                     self._insert(
                         item,
                         fields=fields,
                         using=self.db,
+                        on_conflict=on_conflict,
+                        update_fields=update_fields,
+                        unique_fields=unique_fields,
                         returning_fields=self.model._meta.db_returning_fields,
                     )
                 )

Would you like to prepare a patch via GitHub PR? (docs changes and tests are required)

[Thomas C]

Sure I will.

[Mariusz Felisiak]

Replying to Thomas C:

Sure I will.

Thanks. About tests, it should be enough to add some assertions to existing tests: _test_update_conflicts_two_fields(), test_update_conflicts_unique_fields_pk(), _test_update_conflicts_unique_two_fields(), _test_update_conflicts(), and test_update_conflicts_unique_fields_update_fields_db_column() when connection.features.can_return_rows_from_bulk_insert is True.

[Thomas C]

See ​https://github.com/django/django/pull/17051

[Mariusz Felisiak <felisiak.mariusz@…>]

In 89c7454:

Fixed #34698 -- Made QuerySet.bulk_create() retrieve primary keys when updating conflicts.