Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Wed, 2014-11-19 at 16:52 -0800, Peter Geoghegan wrote:
> Someone mentioned to me privately that they weren't sure that the
> question of whether or not RETURNING only projected actually inserted
> tuples was the right one. Also, I think someone else mentioned this a
> few months back. I'd like to address this question directly sooner
> rather than later, and so I've added a note on the Wiki page in
> relation to this [1]. It's a possible area of concern at this point.

I think the biggest problem with the current approach is that there is
no way to know if a row was skipped by the where clause when using
INSERT ON CONFLICT UPDATE ... WHERE.

I am a developer of the Django ORM. Django reports to the user whether a
row was inserted or updated. It is possible to know which rows were
inserted by returning the primary key value. If something is returned,
then it was an insert. If Django implements updated vs inserted checking
this way, then if PostgreSQL adds RETURNING for update case later on,
that would be a breaking change for Django.

So, if it is not too hard to implement RETURNING for the update case
then I think it should be done. A pseudo column informing if the result
was an update or insert would then be a requirement for Django. Changing
the returning behavior in later releases might cause problems due to
backwards compatibility.

- Anssi