Re: [HACKERS] MERGE SQL Statement for PG11 - Mailing list pgsql-hackers

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
matchthe 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
matchthe 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
thatoccur in the   process do fire triggers.  INSERT OR UPDATE would be much more   useful.
 


Nico
-- 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers