Difference between UPSERT and MERGE?

Multi tool use
Multi tool use












0















From the PostgreSQL wiki,




MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.




Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5



What then is MERGE? And how does it fit into the mix?










share|improve this question



























    0















    From the PostgreSQL wiki,




    MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.




    Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5



    What then is MERGE? And how does it fit into the mix?










    share|improve this question

























      0












      0








      0








      From the PostgreSQL wiki,




      MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.




      Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5



      What then is MERGE? And how does it fit into the mix?










      share|improve this question














      From the PostgreSQL wiki,




      MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.




      Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5



      What then is MERGE? And how does it fit into the mix?







      postgresql merge terminology upsert






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Apr 7 '18 at 9:12









      Evan CarrollEvan Carroll

      33k1074225




      33k1074225






















          1 Answer
          1






          active

          oldest

          votes


















          2














          Generally,





          • UPSERT is built off of INSERT


          • MERGE focuses on merging/synchronizing tables and provides



            • conditionality (where clauses)


            • DELETE support.




          UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance



          ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
          ON CONFLICT (country) DO NOTHING;


          But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.



          As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"




          • UPSERT is atomic in its insert-or-update

          • SQL MERGE doesn't technically require a UNIQUE INDEX.


          See also




          • PostgreSQL patch returned 9/11


          • PostgreSQL 11 MERGE syntax (since removed)






          share|improve this answer





















          • 1





            Looks like MERGE was reverted from now.

            – film42
            Mar 13 at 18:30











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f203292%2fdifference-between-upsert-and-merge%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          2














          Generally,





          • UPSERT is built off of INSERT


          • MERGE focuses on merging/synchronizing tables and provides



            • conditionality (where clauses)


            • DELETE support.




          UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance



          ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
          ON CONFLICT (country) DO NOTHING;


          But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.



          As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"




          • UPSERT is atomic in its insert-or-update

          • SQL MERGE doesn't technically require a UNIQUE INDEX.


          See also




          • PostgreSQL patch returned 9/11


          • PostgreSQL 11 MERGE syntax (since removed)






          share|improve this answer





















          • 1





            Looks like MERGE was reverted from now.

            – film42
            Mar 13 at 18:30
















          2














          Generally,





          • UPSERT is built off of INSERT


          • MERGE focuses on merging/synchronizing tables and provides



            • conditionality (where clauses)


            • DELETE support.




          UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance



          ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
          ON CONFLICT (country) DO NOTHING;


          But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.



          As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"




          • UPSERT is atomic in its insert-or-update

          • SQL MERGE doesn't technically require a UNIQUE INDEX.


          See also




          • PostgreSQL patch returned 9/11


          • PostgreSQL 11 MERGE syntax (since removed)






          share|improve this answer





















          • 1





            Looks like MERGE was reverted from now.

            – film42
            Mar 13 at 18:30














          2












          2








          2







          Generally,





          • UPSERT is built off of INSERT


          • MERGE focuses on merging/synchronizing tables and provides



            • conditionality (where clauses)


            • DELETE support.




          UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance



          ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
          ON CONFLICT (country) DO NOTHING;


          But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.



          As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"




          • UPSERT is atomic in its insert-or-update

          • SQL MERGE doesn't technically require a UNIQUE INDEX.


          See also




          • PostgreSQL patch returned 9/11


          • PostgreSQL 11 MERGE syntax (since removed)






          share|improve this answer















          Generally,





          • UPSERT is built off of INSERT


          • MERGE focuses on merging/synchronizing tables and provides



            • conditionality (where clauses)


            • DELETE support.




          UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance



          ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
          ON CONFLICT (country) DO NOTHING;


          But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.



          As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"




          • UPSERT is atomic in its insert-or-update

          • SQL MERGE doesn't technically require a UNIQUE INDEX.


          See also




          • PostgreSQL patch returned 9/11


          • PostgreSQL 11 MERGE syntax (since removed)







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 16 mins ago

























          answered Apr 7 '18 at 9:12









          Evan CarrollEvan Carroll

          33k1074225




          33k1074225








          • 1





            Looks like MERGE was reverted from now.

            – film42
            Mar 13 at 18:30














          • 1





            Looks like MERGE was reverted from now.

            – film42
            Mar 13 at 18:30








          1




          1





          Looks like MERGE was reverted from now.

          – film42
          Mar 13 at 18:30





          Looks like MERGE was reverted from now.

          – film42
          Mar 13 at 18:30


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f203292%2fdifference-between-upsert-and-merge%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          rb prr44,p34JVDtQ,xPInAUhL0PvBy,dD,3lsoSwk4aONtJWU VNXBF,N7i0aa,nipEbJa
          ZqkMK 7x

          Popular posts from this blog

          Xianyang

          Transaction terminated running SELECT on secondary AG group

          Justizvollzugsanstalt Augsburg-Gablingen