id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36567	Allow updating fields using expressions in bulk_create with update_conflicts	Take Weiland		"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."	Uncategorized	closed	Uncategorized	5.2	Normal	duplicate			Unreviewed	0	0	0	0	0	0