Ability to explicitly set insert_fields and update_fields

[anpr]

My case is that I want to bulk_upsert a bunch of (Django) user entries.

Both when inserting and updating, the password should not be set. For some reason (I didn't investigate why) the resulting SQL query has password as insert field, even though it's not part of the dicts passed to bulk_upsert.

I'd like to have keyword arguments insert_fields and update_fields which are used instead of _get_upsert_fields if specified, in case you know what you're doing. Moreover, there could be cases where the insert and update really take different fields.

[illagrenan]

It would be super if one could pass dicts for INSERT and different dicts for UPDATE in case of conflict.

For example, as shown in this SQL snippet:

INSERT INTO foo_bar (
  a, b, c, d, e
) VALUES (
  ..., ..., ..., ..., ...
)
ON CONFLICT (a)
  DO UPDATE SET
    conflicted_at = CLOCK_TIMESTAMP()
WHERE ...

Maybe something like this?

(
    MyModel.objects.on_conflict(['name'], ConflictAction.UPDATE)
        .bulk_insert([
        dict(name='swen'),
        dict(name='henk'),
        dict(name='adela')
    ]).conflict_update([
        dict(foo='aa'),
        dict(foo='bb'),
        dict(foo='cc')
    ])
)

[Skorpyon]

Does this issue have any chance? Very good if it will support F expression. My current task require insert default value or update existed row with .update(field=F('field') + 1)

[alternativshik]

Does this issue have any chance? Very good if it will support F expression. My current task require insert default value or update existed row with .update(field=F('field') + 1)

Need the same thing, upsert doesn't work with F() expressions

[litchfield]

Great library! However, the upsert implementation is not complete without the ability to explicitly set update fields (ie the DO UPDATE clause). It should also be made clear that upsert_* and insert_* are the same (consider removing one- it's confusing as is).

Once this has been sorted out, we can put it forward for inclusion in django.contrib.postgres.

[a3kov]

Yeah, it relies on sort of "guessing" - see "magical fields" in the code...Whenever you encounter the word "magic" in software, you should be alarmed :) I would make an update_fields required kwarg for upsert, but then it would require a major version bump since it breaks the current function signature and thus the API guarantee Of course, all "magic" should be removed also

[a3kov]

I have my own homegrown bulk_upsert which is much simpler, it has update_fields, batching, automatic skipping of redundant updates, but then it doesn't support functions or expressions. I use it in my own project but I don't think it would help broader audience because of it's simplicity. The upsert in this project technically has better foundations - custom database backend, compiler, etc, but then it has this strange "magic". I could help with fixing this, but the status of the project is not clear: it promises 1 day response time for PRs, but then there's been some unanswered PRs for quite some time.

[Photonios]

I've taken a shot at this because I had a need for it: https://github.com/SectorLabs/django-postgres-extra/pull/189