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