[Django] #36567: Allow updating fields using expressions in bulk_create with update_conflicts

[Django]

#36567: Allow updating fields using expressions in bulk_create with
update_conflicts
------------------------------+-----------------------------------------
Reporter: Take Weiland | Type: Uncategorized
Status: new | Component: Uncategorized
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
------------------------------+-----------------------------------------
My use-case is as follows:
I have a list of objects that I need to "upsert" into the database.
However one of the fields must only be updated if it has no value yet.
In raw SQL (PostgreSQL) I would write this as follows:

{{{
INSERT INTO my_table (id, user_id, field_a, field_b)
VALUES (...), (...), (...)
ON CONFLICT (user_id) DO UPDATE SET field_a = EXCLUDED.field_a, field_b =
COALESCE(my_table.field_b, EXCLUDED.field_b)
RETURNING id;
}}}

There is no way to express this query using Django's ORM as far as I can
tell.

`INSERT ... ON CONFLICT` is covered by `bulk_create` with
`update_conflicts`. However it only lets you specify a list of fields to
update.

Some API bike-shedding: I could imagine it being supported like this:

{{{
MyModel.objects.bulk_create(
objs,
update_conflicts=True,
update_fields={
'field_b': Coalesce(F('field_b'), Inserted('field_b'))
}
)
}}}
Note the use of the pseudo DB-Function `Inserted`. It allows refering to
the values that would originally be inserted. There is different syntax
for this depending on the DB Vendor:
- PostgreSQL and SQLite use the pseudo-table-name "excluded":
`excluded.field_name` (https://www.postgresql.org/docs/current/sql-
insert.html, https://sqlite.org/lang_upsert.html)
- MariaDB uses the `VALUE`-function: `VALUE(field_name)`
(https://mariadb.com/docs/server/reference/sql-statements/data-
manipulation/inserting-loading-data/insert-on-duplicate-key-update,
https://mariadb.com/docs/server/reference/sql-functions/secondary-
functions/miscellaneous-functions/values-value)
- MySQL uses the pseudo-table-name "new": `new.field_name`
(https://dev.mysql.com/doc/refman/9.4/en/insert-on-duplicate.html)
- Oracle doesn't support `ON DUPLICATE KEY UPDATE` or a similar construct
(this is already a limitation of `update_conflicts`).

This functionality is already implemented in the database operations in
`on_conflict_suffix_sql`, however it is hardcoded to not allow custom
expressions.
--
Ticket URL: <https://code.djangoproject.com/ticket/36567>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36567: Allow updating fields using expressions in bulk_create with
update_conflicts

-------------------------------+--------------------------------------
Reporter: Take Weiland | Owner: (none)
Type: Uncategorized | Status: closed
Component: Uncategorized | Version: 5.2
Severity: Normal | Resolution: duplicate

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------+--------------------------------------
Changes (by Take Weiland):

* resolution: => duplicate
* status: new => closed

--
Ticket URL: <https://code.djangoproject.com/ticket/36567#comment:1>

[Django]

#36567: Allow updating fields using expressions in bulk_create with
update_conflicts
-------------------------------+--------------------------------------
Reporter: Take Weiland | Owner: (none)
Type: Uncategorized | Status: closed
Component: Uncategorized | Version: 5.2
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------

Comment (by Take Weiland):

See https://code.djangoproject.com/ticket/35793
--
Ticket URL: <https://code.djangoproject.com/ticket/36567#comment:2>