Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 2 October 2014 22:37, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> OK, it is was just an idea I wanted to point out, and if it doesn't
> work, it more clearly cements that we need UPSERT _and_ MERGE.

It seems clear that having two different initial keywords is popular
because it provides clarity about which aspects of the commands will
be supported.

I like the idea of making the two commands as close as possible in
syntax, which will make it easier to program for and encourage
adoption.
The command name could easily be MERGE [CONCURRENTLY] since that uses
the same concept from earlier DDL syntax/keywords.

In UPSERT, we don't need the ON keyword at all. If we are altering the
syntax, then we can easily remove this.

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.

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.

More complex example of MERGE

MERGE INTO product AS T
USING (SELECT sales.id, sum(sold) AS sold, max(catalog.name) as name
FROM sales, catalog WHERE sales.id = catalog.id GROUP BY sales.id) AS S
ON S.id = T.id
WHEN MATCHED AND T.inventory = S.sold
THEN DELETE
WHEN MATCHED AND T.inventory < S.sold
THEN SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT =
'Oversold: ' || S.name
WHEN MATCHED
THEN UPDATE SET inventory = T.inventory - S.sold
WHEN NOT MATCHED
THEN INSERT VALUES(S.id, S.name, -S.sold);

Full example would be similar to this

MERGE CONCURRENTLY INTO product AS T
USING VALUES ()
WHEN MATCHED AND T.inventory = S.sold
THEN DELETE
WHEN MATCHED AND T.inventory < S.sold
THEN SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT =
'Oversold: ' || S.name
WHEN MATCHED
THEN UPDATE SET inventory = T.inventory - S.sold
WHEN NOT MATCHED
THEN INSERT VALUES(S.id, S.name, -S.sold);

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services