Mechanism to know whether an ON CONFLICT actually conflicted

[PeterJCLaw]

One of the use-cases I have for ON CONFLICT handling is to ensure some data (a single row) is in the database and then act based on whether it was present. I'm currently using hand-crafted SQL for this, and then checking the number of affected rows.

From what I can tell, there isn't a way to work out whether an INSERT has actually conflicted or not in the current API -- is this something you'd consider adding?

[Photonios]

If you have a good idea how to do this, then I'd like to hear and we can come up with an elegant way to implement it. I have no objections against implementing something for this. Seems useful :)

[PeterJCLaw]

It looks like the answer at https://stackoverflow.com/a/42217872/67873 (note: not the accepted answer) has a mechanism for this.

[PeterJCLaw]

The challenge I see here is what the Python API would look like.

On the one hand, I'd really quite like it to be something a little bit like Django's get_or_create in the single-item case:

obj, created = (
    MyModel.objects
    .on_conflict(['name'], ConflictAction.UPDATE)
    .insert_and_get(name='swen')
)

However that would break existing usages of insert_and_get (and of upsert_and_get). Having another name for this would solve the problem, though I can't think of a good one right now.

While we could do something like attach a did_conflict value to the returned model, there's no way to do that for plain insert/upsert (which return primary keys). It's also highly non-obvious to users, so I'm rejecting this route.

The bulk scenario is a bit easier as there is no existing bulk_insert_and_get (though I have just noticed the return_model parameter to bulk_insert though that's only obeyed when a conflict is anticipated):

created_models, updated_models = (
    MyModel.objects
    .on_conflict(['name'], ConflictAction.UPDATE)
    .bulk_insert_and_get([
        MyModel(name='swen'),
        MyModel(name='henk'),
        MyModel(name='adela')
    ])
)