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

On Thu, Oct 2, 2014 at 02:08:30PM -0700, Peter Geoghegan wrote:
> 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.

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.

Josh was pointing out that we don't want to surprise our users, so I
suggested an additional keyword, which addresses his objections, but as
you said, if that standard MERGE syntax doesn't give us what we want,
then that is the fatal objection to using only MERGE.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +