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

On 9 October 2014 09:11, Peter Geoghegan <pg(at)heroku(dot)com> wrote:

>> You may also wish to support the AS keyword, as MERGE does to make the
>> above even more clear.
>>
>> e.g. SET col = EXISTING.col + NEW.col
>
> That's less clear, IMV. EXISTING.col is col - the very same Var. So
> why qualify that it's the existing value in one place but not the
> other? In fact, you can't do that now with updates in general:
>
> postgres=# update upsert u set u.val = 'foo';
> ERROR: 42703: column "u" of relation "upsert" does not exist
> LINE 1: update upsert u set u.val = 'foo';
> ^
> LOCATION: transformUpdateStmt, analyze.c:2068

YES, which is exactly why I did not say this, I said something different.

> This does work, which is kind of what you outline:
>
> postgres=# update upsert u set val = u.val;
> UPDATE 3

YES, which is why I said it.

> But MERGE accepts the former in other systems (in general, and for
> MERGE), where Postgres won't (for UPDATEs in general). Parse analysis
> of UPDATE targetlists just rejects this outright.
>
> FWIW, is any of the two tuples reference here "NEW", in any sense?
> Informally, I'd say the new value is the resulting row - the final row
> value after the UPDATE. We want to refer to the existing row, and the
> row proposed for insertion (with all before trigger effects carried
> forward).

YES, which is why I specifically requested the ability to reference
"the incoming data".

Common sense interpretations make for quicker and easier discussions.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services