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

On Wed, 2014-11-26 at 16:59 -0800, Peter Geoghegan wrote:
> On Mon, Nov 24, 2014 at 1:03 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> > Looks like the consensus is that we should have RETURNING project
> > updated tuples too, then.
>
> Attached revision, v1.5, establishes this behavior (as always, there
> is a variant for each approach to value locking). There is a new
> commit with a commit message describing the new RETURNING/command tag
> behavior in detail, so no need to repeat it here. The documentation
> has been updated in these areas, too.

It seems there isn't any way to distinguish between insert and update of
given row. Maybe a pseudo-column can be added so that it can be used in
the returning statement:

insert into foobar(id, other_col) values(2, '2') on conflict (id) update set other_col=excluded.other_col returning id, pseudo.was_updated;

This would ensure that users could check for each primary key value if
the row was updated or inserted.

Of course, the pseudo.was_updated name should be replaced by something
better.

It would be nice to be able to skip updates of rows that were not
changed:

insert into foobar values(2, '2') on conflict (id) update set other_col=excluded.other_col where target is distinct from excluded;

- Anssi Kääriäinen