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

On Thu, Dec 4, 2014 at 10:27 PM, Anssi Kääriäinen
<anssi(dot)kaariainen(at)thl(dot)fi> wrote:
> For Django's use case this is a requirement. We must inform the user if
> the save() action created a new row or if it modified an existing one.

Can you explain that in more detail, please?

> Another way to do this would be to expose the "excluded" alias in the
> returning clause. All columns of the excluded alias would be null in
> the case of insert (especially the primary key column), and thus if a
> query
> insert into foobar values(2, '2') on conflict (id) update set other_col=excluded.other_col returning excluded.id
> returns a non-null value, then it was an update.

I don't like that idea much, TBH. Consider this:

postgres=# update upsert u set key = 1 from upsert i returning key;
ERROR: 42702: column reference "key" is ambiguous
LINE 1: update upsert u set key = 1 from upsert i returning key;
^

So, suppose this example was actually an ON CONFLICT UPDATE query. If
I similarly make the aliases in the ON CONFLICT UPDATE
("target"/"excluded") visible in the returning list, it becomes
necessary to qualify every column - an ambiguity is introduced by
making both aliases visible, since any non-qualified column in the
RETURNING clause could be from either the "target" or "excluded"
alias/RTE. This is particularly annoying for the common, simple cases.
Also, when there was an update in respect of a any given slot, how, in
general, can I be sure that *any* visible excluded.* attribute is not
null (which you suggest as a reliable proxy for the update path having
been taken)? For one thing, the unique index that arbitrates whether
or not we take the "alternative path" is not restricting to covering
non-nullable attributes. So does the user end up specifying
system/hidden atrributes, just to make what you outline work? That
seems sort of messy.

--
Peter Geoghegan