Re: INSERT ... ON CONFLICT syntax issues

Andres pointed out on IM that the TARGET alias is a bit crummy. In
particular, adding an ON CONFLICT DO UPDATE can make a RETURNING clause
invalid, because we change the alias of the target rel:

create table foo (id int4 primary key, t text);

This works:

postgres=# insert into foo (id, t) values (1, 'x') returning foo.t;
t
---
x
(1 row)

INSERT 0 1

Same statement with ON CONFLICT DO UPDATE fails:

postgres=# insert into foo (id, t) values (1, 'x') on conflict (id) do
update set t = 'x' returning foo.t;
ERROR: invalid reference to FROM-clause entry for table "foo"
LINE 1: ...'x') on conflict (id) do update set t = 'x' returning foo.t;
^
HINT: Perhaps you meant to reference the table alias "target".

I'll see about fixing that. It's not just a matter of creating another
alias for the same rel, I'm afraid: "foo.t" is supposed to refer to the
tuple that we attempted to insert, like it does without the ON CONFLICT.

But actually, I don't much like the "target" alias in the first place.
We never really completed this discussion, everyone just got tired:

On 04/29/2015 10:13 PM, Stephen Frost wrote:
> * Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
>> On Mon, Apr 27, 2015 at 7:21 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>>> * Don't change the names of the pseudo-alias EXCLUDED.* (or the alias
>>> TARGET.*). Those seem fine to me as well.
>>
>> There seem to be a few votes for NEW and OLD. That's what I proposed
>> originally, and (surprise, surprise) I still like that better too.
>
> I was promoting NEW/OLD, until I realized that we'd end up having a
> problem in trigger functions because NEW/OLD are already defined there,
> unless you have a suggestion for how to improve on that?

Reading through this sub-thread, these spellings have been proposed:

1. TARGET and EXCLUDED

2. NEW and EXISTING

3. NEW and OLD

4. PROPOSED and EXISTING

5. CONFLICTING and EXISTING

Did I miss any? Now, let me opine on these.

EXCLUDED seems fine to me. I don't see us using that term elsewhere, and
it makes me think of exclusion constraints, but nevertheless I think
it's pretty easy remember what it means. TARGET, however, is totally
inscrutable. Peter argued earlier that:

> TARGET is also very descriptive, because it situationally describes
> either the existing tuple actually present in the table, or (from a
> RETURNING clause) the final tuple present in the table post-UPDATE.
> We use the term "target" for that pervasively (in the docs and in the
> code).

but I find that totally unconvincing. It's clear that TARGET refers to
the table being upserted, but it's totally unclear on *which* version of
the tuple it refers to.

NEW and OLD are pretty good. Like in an UPDATE trigger, NEW refers to
the version after the UPDATE, and OLD to the version before. However,
there's the serious problem that in a trigger function, OLD/NEW are
already in use. How bad is that? At least in PL/pgSQL you can work
around it by aliasing the variables, but it's a bit inconvenient. How
often would INSERT .. ON CONFLICT DO UPDATE be used in a trigger?

I don't have much to say about the rest. PROPOSED, EXISTING,
CONFLICTING, they're all fairly descriptive, but long.

- Heikki