On Tue, Sep 30, 2014 at 8:30 AM, Simon Riggs <si...@2ndquadrant.com> wrote: >>>>> No explanation of why the CONFLICTING() syntax differs from OLD./NEW. >>>>> syntax used in triggers >>>> >>>> Why should it be the same? >>> >>> Because it would be a principled approach to do that. >> >> That is just an assertion. The MERGE syntax doesn't use that either. > > MERGE allows "AS row" which then allow you to refer to row.x for > column x of the input.
It does, but that isn't what you suggested. You talked about the OLD.*/NEW.* syntax. >> I don't care if we change the spelling to "WHEN MATCHED >> UPDATE/IGNORE". That seems fine. But MERGE is talking about a join, >> not the presence of a would-be duplicate violation. > > I don't understand that comment. I just mean that if you want to replace ON CONFLICT UPDATE with WHEN MATCHED UPDATE - that little part of the grammar - that seems okay. >> Multi-assignment updates (with or without CONFLICTING()) are supported, FWIW. > > If I want the incoming row to overwrite the old row, it would be good > to have syntax to support that easily. Well, maybe I'll get around to that when things settle down. That's clearly in the realm of "nice to have", though. > Why doesn't > INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON > CONFLICT UPDATE SET t = 'fails'; > end up with this in the table? > > 1 a > 2 fails A "cardinality violation" - just like MERGE. As with MERGE, the final value of every row needs to be deterministic (within the command). > What happens with this? > > BEGIN; > INSERT INTO UNIQUE_TBL VALUES (2, 'b') ON CONFLICT UPDATE SET t = 'fails'; > INSERT INTO UNIQUE_TBL VALUES (2, 'b') ON CONFLICT UPDATE SET t = 'fails'; > COMMIT; It works fine. No cardinality violation with two separate commands. See the new ExecLockUpdateTuple() function within nodeModifyTable.c for extensive discussion on how this is handled. -- 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