On Tue, Oct 7, 2014 at 5:23 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > IIRC it wasn't agreed that we needed to identify which indexes in the > upsert SQL statement itself, since this would be possible in other > ways and would require programmers to know which unique constraints > are declared.
Kevin seemed quite concerned about that. That is something that seems hard to reconcile with supporting the MERGE syntax. Perhaps Kevin can comment on that, since he was in favor of both being able to specify user intent by accepting a unique index, while also being in favor of the MERGE syntax. > All of the other syntax could easily remain the same, leaving us with > a command that looks like this... > > MERGE CONCURRENTLY INTO foo USING VALUES () > WHEN NOT MATCHED THEN > INSERT > WHEN MATCHED THEN > UPDATE > > Since MERGE now supports DELETE and IGNORE as options, presumably we > would also want to support those for the UPSERT version also. > I think it would be useful to also support a mechanism for raising an > error, as DB2 allows. It seems like what you're talking about here is just changing the spelling of what I already have. I think that would be confusing to users when the time comes to actually implement a fully-generalized MERGE, even with the clearly distinct MERGE CONCURRENTLY variant outlined here (which, of course, lacks an outer join, unlike MERGE proper). However, unlike the idea of trying to square the circle of producing a general purpose MERGE command that also supports the UPSERT use-case, my objection to this much more limited proposal is made purely on aesthetic grounds. I think that it is not very user-friendly; I do not think that it's a total disaster, which is what trying to solve both problems at once (MERGE bulkloading and UPSERTing) would result in. So FWIW, if the community is really set on something that includes the keyword MERGE, which is really all you outline here, then I can live with that. -- 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
