On Tue, Oct 15, 2013 at 9:56 AM, Robert Haas <robertmh...@gmail.com> wrote: > Well, I don't know that any of us can claim to have a lock on what the > syntax should look like.
Sure. But it's not just syntax. We're talking about functional differences too, since you're talking about mandating an update, which is a not the same as an "update locked row only conditionally", or a delete. I get that it's a little verbose, but then this is ORM plumbing for many of those that would prefer a more succinct syntax. Those people would also benefit from having their ORM do something much more powerful for them when needed. > I think we need to hear some proposals. Agreed. > You've heard my gripe about the current syntax (which Andres appears > to share), but I shan't attempt to prejudice you in favor of my > preferred alternative, because I don't have one yet. FWIW, I sincerely see very real advantages to what I've proposed here. To me, the fact that it's convenient to implement is beside the point. > There could be > other ways of avoiding that problem, though. Here's an example: > > UPSERT table (keycol1, ..., keycoln) = (keyval1, ..., keyvaln) SET > (nonkeycol1, ..., nonkeycoln) = (nonkeyval1, ..., nonkeyvaln) > > That's pretty ugly on multiple levels, and I'm definitely not > proposing that exact thing, but the idea is: look for a record that > matches on the key columns/values; if found, update the non-key > columns with the corresponding values; if not found, construct a new > row with both the key and nonkey column sets and insert it. If no > matching unique index exists we'll have to fail, but we stop short of > having to mention the name of that index. What if you want to update the key columns - either the potential conflict-causing one, or another? What about composite unique constraints? MySQL certainly supports all that, for example. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
