Re: INSERT ... ON CONFLICT syntax issues

On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> What's important, in my view, is to keep the simple case simple and so
> I'm not particularly wedded to any of these approaches, just trying to
> help with other suggestions.
>
> INSERT INTO mytable VALUES ('key1','key2','val1','val2')
> ON CONFLICT UPDATE SET val1 = 'val1', val2 = 'val2';
>
> strikes me as a the 99% use-case here that we need to keep sane, and
> it'd be really nice if we didn't have to include the SET clause and
> duplicate those values at all.. That could be something we add later
> though, I don't think it needs to be done now.

You can do that already. That's what the EXCLUDED.* alias that is
automatically added is for (the thing that Andres disliked the
spelling of - or the other thing). This is legal, for example:

INSERT INTO mytable (foo, bar, baz, bat) VALUES ('key1','key2','val1','val2')
ON CONFLICT (foo) UPDATE SET (foo, bar, baz, bat) = (EXCLUDED.foo,
EXCLUDED.bar, EXCLUDED.baz, EXCLUDED.bat)';

I don't want to accept something that automatically merges the
excluded tuple (e.g., "SET (*) = EXLCUDED.*"), for reasons outlined
here: https://wiki.postgresql.org/wiki/UPSERT#VoltDB.27s_UPSERT
--
Peter Geoghegan