On Fri, Oct 27, 2017 at 02:13:27PM -0700, srielau wrote: > While the standard may not require a unique index for the ON clause I have > never seen a MERGE statement that did not have this property. So IMHO this > is a reasonable restrictions.
I don't understand how one could have a conflict upon which to turn INSERT into UPDATE without having a UNIQUE constraint violated... The only question is whether one should have control over -or have to specify- which constraint violations lead to UPDATE vs. which ones lead to failure vs. which ones lead to doing nothing. The row to update is the one that the to-be-inserted row conflicted with -- there can only have been one if the constraint violated was a PRIMARY KEY constraint, or if there is a PRIMARY KEY at all, but if there's no PRIMARY KEY, then there can have been more conflicting rows because of NULL columns in the to-be-inserted row. If the to-be-inserted row conflicts with multiple rows, then just fail, or don't allow MERGE on tables that have no PK (as you know, many think it makes no sense to not have a PK on a table in SQL). In the common case one does not care about which UNIQUE constraint is violated because there's only one that could have been violated, or because if the UPDATE should itself cause some other UNIQUE constraint to be violated, then the whole statement should fail. PG's UPSERT is fantastic -- it allows very fine-grained control, but it isn't as pithy as it could be when the author doesn't care to specify all that detail. Also, something like SQLite3's INSERT OR REPLACE is very convenient: pithy, INSERT syntax, upsert-like semantics[*]. I'd like to have this in PG: INSERT INTO .. ON CONFLICT DO UPDATE; -- I.e., update all columns of the existing -- row to match the ones from the row that -- would have been inserted had there not been -- a conflict. -- -- If an INSERTed row conflicts and then the -- UPDATE it devolves to also conflicts, then -- fail. and INSERT INTO .. ON CONFLICT DO UPDATE -- I.e., update all columns of the existing -- row to match the ones from the row that -- would have been inserted had there not been -- a conflict. -- ON CONFLICT DO NOTHING; -- If an INSERTed row conflicts and then the -- UPDATE it devolves to also conflicts, then -- DO NOTHING. [*] SQLite3's INSERT OR REPLACE is NOT an insert-or-update, but an insert-or-delete-and-insert, and any deletions that occur in the process do fire triggers. INSERT OR UPDATE would be much more useful. Nico -- -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers