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

On Thu, Oct 2, 2014 at 1:10 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I think if we use the MERGE command for this feature we would need to
> use a non-standard keyword to specify that we want OLTP/UPSERT
> functionality. That would allow us to mostly use the MERGE standard
> syntax without having surprises about non-standard behavior. I am
> thinking of how CONCURRENTLY changes the behavior of some commands.

That would leave you without a real general syntax. It'd also make
having certain aspects of an UPSERT more explicit be a harder goal
(there is no conventional join involved here - everything goes through
a unique index). Adding the magic keyword would break certain other
parts of the statement, so you'd have exact rules for what worked
where. I see no advantage, and considerable disadvantages.

Note that I've documented a lot of this stuff here:

https://wiki.postgresql.org/wiki/UPSERT

Mapping the join thing onto which unique index you want to make the
UPSERT target is very messy. There are a lot of corner cases. It's
quite ticklish.

Please add to it if you think we've missed something.
--
Peter Geoghegan