From: Nico Williams <nico@cryptonector.com>
To: srielau <serge@rielau.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: MERGE SQL Statement for PG11
Date: Fri, 27 Oct 2017 18:54:30 -0500
Message-ID: <20171027235429.GK4496@localhost> (raw)
In-Reply-To: <1509138807748-0.post@n3.nabble.com>
References: <CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com>
<CA+TgmoZuce2iHSBVpkX5qud-UbxhtY85opA47LTP=QW16J47wg@mail.gmail.com>
<CANP8+j+jG76Okkibk7Xu=T6C5-gSpXX9sudANS9nGyR9yWEgRA@mail.gmail.com>
<CA+TgmobMx66NWCoje=PEGnbMQQdZojJi7v9RqsuY23vkNsTf1Q@mail.gmail.com>
<1509138807748-0.post@n3.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nico@cryptonector.com, serge@rielau.com
Subject: Re: MERGE SQL Statement for PG11
In-Reply-To: <20171027235429.GK4496@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox