Re: INSERT ... ON CONFLICT syntax issues

Peter,

* Peter Geoghegan (pg(at)heroku(dot)com) wrote:
> 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)';

Yeah, that's not exactly simpler and I don't expect to see it used very
often (as in, less than 1%) because of that.

> 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

Perhaps I'm missing it, but the reasons that I see there appear to be:

"It'd be like SELECT *" and "we'd have to decide what to do about the
value for unspecified columns". As for the latter- we have to do that
*anyway*, no? What happens if you do:

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

?

As for the "SELECT *" concern, I fail to see how it's any different from
the exact same currently-encouraged usage of INSERT + UPDATE:

INSERT INTO mytable (foo, bar, baz, bat) VALUES ('key1','key2','val1','val2');

... catch the exception

UPDATE mytable SET baz = 'val1', bat = 'val2' WHERE foo = 'key1' and bar = 'key2';

Clearly there are issues with the above if someone is running around
adding columns to tables and PG has to figure out if we should be
setting the non-mentioned columns to NULL or to the default for the
column, but we're all quite happy to do so and trust that whomever is
adding the column has set a sane default and that PG will use it when
the column isn't included in either the INSERT or the UPDATE.

Note that I wasn't suggesting your "SET (*) = EXLCUDED.*" syntax and if
that would expand to something different than what I've outlined above
then it would make sense to not include it (... or fix it to act the
same, and then it's just a more verbose approach).

Further, this is *very* different from how the "SELECT *" concern can
cause things to break unexpectedly- new columns end up getting returned
which the application is unlikely to be prepared for. That doesn't
happen here and so I don't believe it makes any sense to try and compare
the two.

Happy to discuss, of course, and apologies if I missed some other issue-
I was just reading what I found at the link provided.

Thanks!

Stephen