#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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070198cd2bbc1b-2906a0b8-0ff7-450d-9844-f7125149a976-000000%40eu-central-1.amazonses.com.
