Re: INSERT ... ON CONFLICT syntax issues

* Peter Geoghegan (pg(at)heroku(dot)com) wrote:
> On Sun, Apr 26, 2015 at 11:08 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> >> 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);
> >
> > ?
>
> It's like any other UPDATE - the values of columns not appearing in
> the targetlist are unchanged from the original row version now
> superseded. It doesn't matter that you had some other values in the
> INSERT. You only get what you ask for.

Ok, that makes sense.. So is the concern that an INSERT would end up
getting default values while an UPDATE would preserve whatever's there?

I don't see that as an issue.

Are you still against having a way to say "go forth and update whatever
non-conflicting columns I've specified in the INSERT, if there is a
conflict"..?

Again, not saying it has to be done now, but it'd certainly be nice if
we had it initially because otherwise the ORMs and "frameworks" of the
world will be stuck supporting the more verbose approach for as long as
we support it (~5 years..).

Thanks!

Stephen