Difference between UPSERT and MERGE?

Multi tool use
From the PostgreSQL wiki,
MERGE
is typically used to merge two tables, and was introduced in the 2003 SQL standard. TheREPLACE
statement (a MySQL extension) or UPSERT sequence attempts anUPDATE
, or on failure,INSERT
. This is similar toUPDATE
, 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
add a comment |
From the PostgreSQL wiki,
MERGE
is typically used to merge two tables, and was introduced in the 2003 SQL standard. TheREPLACE
statement (a MySQL extension) or UPSERT sequence attempts anUPDATE
, or on failure,INSERT
. This is similar toUPDATE
, 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
add a comment |
From the PostgreSQL wiki,
MERGE
is typically used to merge two tables, and was introduced in the 2003 SQL standard. TheREPLACE
statement (a MySQL extension) or UPSERT sequence attempts anUPDATE
, or on failure,INSERT
. This is similar toUPDATE
, 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
From the PostgreSQL wiki,
MERGE
is typically used to merge two tables, and was introduced in the 2003 SQL standard. TheREPLACE
statement (a MySQL extension) or UPSERT sequence attempts anUPDATE
, or on failure,INSERT
. This is similar toUPDATE
, 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
postgresql merge terminology upsert
asked Apr 7 '18 at 9:12
Evan CarrollEvan Carroll
33k1074225
33k1074225
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Generally,
UPSERT
is built off ofINSERT
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 11MERGE
syntax (since removed)
1
Looks likeMERGE
was reverted from now.
– film42
Mar 13 at 18:30
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Generally,
UPSERT
is built off ofINSERT
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 11MERGE
syntax (since removed)
1
Looks likeMERGE
was reverted from now.
– film42
Mar 13 at 18:30
add a comment |
Generally,
UPSERT
is built off ofINSERT
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 11MERGE
syntax (since removed)
1
Looks likeMERGE
was reverted from now.
– film42
Mar 13 at 18:30
add a comment |
Generally,
UPSERT
is built off ofINSERT
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 11MERGE
syntax (since removed)
Generally,
UPSERT
is built off ofINSERT
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 11MERGE
syntax (since removed)
edited 16 mins ago
answered Apr 7 '18 at 9:12
Evan CarrollEvan Carroll
33k1074225
33k1074225
1
Looks likeMERGE
was reverted from now.
– film42
Mar 13 at 18:30
add a comment |
1
Looks likeMERGE
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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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