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

On 2014-09-29 14:57:45 -0700, Kevin Grittner wrote:
> Peter Geoghegan <pg(at)heroku(dot)com> wrote:
>
> > I think the fact that no MERGE implementation does what you want
> > should be convincing. It is *horrifically* complicated to make
> > what you want work, if indeed it is technically feasible at all.
> > Isn't this already complicated enough?
>
> What about the MERGE syntax I posted makes it hard to implement the
> statement validation and execution code you already have? (I'm
> asking about for the UPSERT case only, not an implementation of all
> aspects of the standard syntax.)

> To recap, in summary that would be:
>
> MERGE INTO tablename [ alias ]
> USING ( relation ) [ alias ]
> ON ( boolean-expression )
> WHEN MATCHED THEN
> UPDATE SET target-column = expression
> [ , target-column = expression ] ...
> WHEN NOT MATCHED THEN
> INSERT ( target-columns ) VALUES ( expressions )
>
> The initial implementation could restrict to these exact clauses
> and require that the boolean-expression used equality-quals on all
> columns of a unique index on only NOT NULL columns.

That'll make it really hard to actually implement real MERGE.

Because suddenly there's no way for the user to know whether he's
written a ON condition that can implement UPSERT like properties
(i.e. the *precise* column list of an index) or not.

Greetings,

Andres Freund

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