I don't think there's a significant difference between the UPSERT and MERGE. The differences are in marketing (which keyword to use) and in technical details (e.g. concurrency semantics). Not worth splitting a core concept over. We spend a lot of effort keeping like-things-like.
On Tue, Mar 17, 2020 at 1:11 AM Christian Beikov <christian.bei...@gmail.com> wrote: > > AFAIK MERGE has different concurrency semantics than what some DBMS call > UPSERT. PostgreSQL for example has a guaranteed insert or update > semantics whereas MERGE could end with constraint violation errors: > https://wiki.postgresql.org/wiki/UPSERT > > Maybe it's worth adding that to the relational model after all? > > Am 17.03.2020 um 07:17 schrieb Enrico Olivelli: > > Il Lun 16 Mar 2020, 23:55 Julian Hyde <jhyde.apa...@gmail.com> ha scritto: > > > >> Change the unparse operation for the dialect so that you generate UPSERT > >> rather than MERGE. > >> > >> IIRC we did this for another dialect - maybe Phoenix. > >> > > Julian, > > In my case (HerdDB) I need to see the presence of UPSERT in the RelNode > > (EnumerableTableModify oŕ LogicalTableModify) > > I saw the JIRA where you introduced UPSERT for Phoenix > > I will check deeper, thanks > > > > Enrico > > > > > > > >> Julian > >> > >>> On Mar 16, 2020, at 1:22 AM, Enrico Olivelli <eolive...@gmail.com> > >> wrote: > >>> Il Lun 16 Mar 2020, 09:06 Stamatis Zampetakis <zabe...@gmail.com> ha > >>> scritto: > >>> > >>>> Hi Enrico, > >>>> > >>>> I have the impression that UPSERT is currently supported only at the > >> parser > >>>> level [1] so it seems normal that you don't find something relevant in > >>>> LogicalTableModify etc. Note that the SQL standard equivalent is the > >> MERGE > >>>> statement [2] but this also seems to be supported only at the > >>>> parser/validator level [2]. > >>>> I guess it is not necessary to introduce more things in TableModify > >> since > >>>> UPSERT seems to be syntactic sugar. I think that most of the work can be > >>>> done in RelToSqlConverter [4] and possibly the rest of the code can be > >> left > >>>> intact. > >>>> > >>> I would like to sens a patch that introduces the ability to keep the > >>> SqlInsert 'keywords' and pass them to TableModify? > >>> Would it be a good approach? > >>> > >>> The alternative is to introduce a new Operation but it would be a more > >>> invasive change and I think it is not worth > >>> > >>> > >>> Enrico > >>> > >>> > >>>> Best, > >>>> Stamatis > >>>> > >>>> [1] https://issues.apache.org/jira/browse/CALCITE-492 > >>>> [2] https://en.wikipedia.org/wiki/Merge_(SQL) > >>>> [3] https://issues.apache.org/jira/browse/CALCITE-985 > >>>> [4] > >>>> > >>>> > >> https://github.com/apache/calcite/blob/d234626227954eefffe49f42abec65c649ffe3a6/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java#L2395 > >>>>> On Sun, Mar 15, 2020 at 6:53 PM Enrico Olivelli <eolive...@gmail.com> > >>>>> wrote: > >>>>> > >>>>> Hi, > >>>>> I am trying to use UPSERT but it seems to me that in TableModify (or > >>>>> best LogicalTableModify or EnumerableTableModify) there is no way to > >>>>> distinguish an INSERT from an UPSERT. > >>>>> > >>>>> Am I missing something? > >>>>> > >>>>> Regards > >>>>> Enrico > >>>>>