On Tue, Oct 15, 2013 at 11:34 AM, Peter Geoghegan <p...@heroku.com> wrote: >> Again, other people can have different opinions on this, and that's >> fine. I'm just giving you mine. > > I will defer to the majority opinion here. But you also expressed > concern about surprising results due to the wrong unique constraint > violation being the source of a conflict. Couldn't this syntax (with > the wCTE upsert pattern) help with that, by naming the constant > inserted in the update too? It would be pretty simple to expose that, > and far less grotty than naming a unique index in DML.
Well, I don't know that any of us can claim to have a lock on what the syntax should look like. I think we need to hear some proposals. 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. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers