Re: INSERT ... ON CONFLICT syntax issues
От | Stephen Frost |
---|---|
Тема | Re: INSERT ... ON CONFLICT syntax issues |
Дата | |
Msg-id | 20150426180848.GC30322@tamriel.snowman.net обсуждение исходный текст |
Ответ на | Re: INSERT ... ON CONFLICT syntax issues (Peter Geoghegan <pg@heroku.com>) |
Ответы |
Re: INSERT ... ON CONFLICT syntax issues
(Peter Geoghegan <pg@heroku.com>)
|
Список | pgsql-hackers |
Peter, * Peter Geoghegan (pg@heroku.com) wrote: > On Sun, Apr 26, 2015 at 6:34 AM, Stephen Frost <sfrost@snowman.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
В списке pgsql-hackers по дате отправления: