On Tue, Oct 15, 2013 at 1:19 PM, Peter Geoghegan <p...@heroku.com> wrote: >> 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?
I'm not sure what that means in the context of an UPSERT operation. If the update case is, when a = 1 then make a = 2, then which value goes in column a when we insert, 1 or 2? But I suppose if you can work that out it's just a matter of mentioning the column as both a key column and a non-key column. > What about composite unique > constraints? MySQL certainly supports all that, for example. That's why it allows you to specify N key columns rather than restricting you to just one. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers