* Peter Geoghegan (p...@heroku.com) wrote: > On Sun, Apr 26, 2015 at 11:35 AM, Stephen Frost <sfr...@snowman.net> wrote: > > 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. > > I think it easily could be.
Ok.. Can you elaborate on that? Would it be an issue that's different
from the same thing done as independent commands?
Perhaps it'd be an issue for individuals who attempt to combine some
more complicated INSERT/UPDATE logic and don't realize that they'd get
whatever the existing value is for the non-specified columns rather than
the default value, but I'm sure they'd realize it on testing it and,
well, there's lots of ways users can misuse SQL and PG and get what they
expect 99% of the time (JOIN would be a great example..) only to have
things break one day.
> > 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..).
>
> The more verbose approach is entirely necessary much of the time. For example:
>
> insert into upsert_race_test (index, count)
> values ('541','-1') on conflict update set count=TARGET.count +
> EXCLUDED.count;
>
> Merging like this will be a very common requirement.
I was just about to reply to myself that I didn't intend to say that we
would remove the more verbose syntax but rather that they'd have to
use the more verbose syntax as long as we supported a release which
*didn't* have the simpler syntax, which would be ~5 years.
Thanks!
Stephen
signature.asc
Description: Digital signature
