Re: INSERT ... ON CONFLICT syntax issues

On 05/07/2015 12:01 AM, Andres Freund wrote:
> How about
> 6. The tablename and EXCLUDED? Possibility with the ability to specify
>     an AS for INSERT INTO foo AS whatever?
>
>  From an implementation pov that'd be simple ;)

I did this, because as you say it's simple to implement, and it resolves 
the problem with RETURNING.

BTW, it's worth noting that the <tablename>.col (or TARGET.col before) 
means different things in the DO UPDATE clause, and in RETURNING. 
Consider this example:

postgres=# create table foo (id int4 primary key, t text);
CREATE TABLE
postgres=# insert into foo values (1, 'original');
INSERT 0 1
postgres=# insert into foo values (1, 'inserted') on conflict (id) do 
update set t = excluded.t || foo.t returning foo.t;        t
------------------ insertedoriginal
(1 row)


In the DO UPDATE, foo.t was 'original', but in the RETURNING, it was 
'insertedoriginal'. That's what I was thinking yesterday, when I said 
that it's not straightforward to just replace "target" with 
"<tablename>", but got confused. This isn't new, however; it works the 
same in a normal UPDATE RETURNING.

- Heikki