{
    "mode": "man",
    "parameter": "MERGE",
    "section": "7",
    "url": "https://www.chedong.com/phpMan.php/man/MERGE/7/json",
    "generated": "2026-10-07T13:41:56Z",
    "synopsis": "[ WITH withquery [, ...] ]\nMERGE INTO [ ONLY ] targettablename [ * ] [ [ AS ] targetalias ]\nUSING datasource ON joincondition\nwhenclause [...]\nwhere datasource is:\n{ [ ONLY ] sourcetablename [ * ] | ( sourcequery ) } [ [ AS ] sourcealias ]\nand whenclause is:\n{ WHEN MATCHED [ AND condition ] THEN { mergeupdate | mergedelete | DO NOTHING } |\nWHEN NOT MATCHED [ AND condition ] THEN { mergeinsert | DO NOTHING } }\nand mergeinsert is:\nINSERT [( columnname [, ...] )]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }\nand mergeupdate is:\nUPDATE SET { columnname = { expression | DEFAULT } |\n( columnname [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( columnname [, ...] ) = ( sub-SELECT )\n} [, ...]\nand mergedelete is:\nDELETE",
    "sections": {
        "NAME": {
            "content": "MERGE - conditionally insert, update, or delete rows of a table\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "[ WITH withquery [, ...] ]\nMERGE INTO [ ONLY ] targettablename [ * ] [ [ AS ] targetalias ]\nUSING datasource ON joincondition\nwhenclause [...]\n\nwhere datasource is:\n\n{ [ ONLY ] sourcetablename [ * ] | ( sourcequery ) } [ [ AS ] sourcealias ]\n\nand whenclause is:\n\n{ WHEN MATCHED [ AND condition ] THEN { mergeupdate | mergedelete | DO NOTHING } |\nWHEN NOT MATCHED [ AND condition ] THEN { mergeinsert | DO NOTHING } }\n\nand mergeinsert is:\n\nINSERT [( columnname [, ...] )]\n[ OVERRIDING { SYSTEM | USER } VALUE ]\n{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }\n\nand mergeupdate is:\n\nUPDATE SET { columnname = { expression | DEFAULT } |\n( columnname [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |\n( columnname [, ...] ) = ( sub-SELECT )\n} [, ...]\n\nand mergedelete is:\n\nDELETE\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "MERGE performs actions that modify rows in the target table identified as targettablename,\nusing the datasource.  MERGE provides a single SQL statement that can conditionally INSERT,\nUPDATE or DELETE rows, a task that would otherwise require multiple procedural language\nstatements.\n\nFirst, the MERGE command performs a join from datasource to the target table producing zero\nor more candidate change rows. For each candidate change row, the status of MATCHED or NOT\nMATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For\neach candidate change row, the first clause to evaluate as true is executed. No more than one\nWHEN clause is executed for any candidate change row.\n\nMERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same\nnames. The syntax of those commands is different, notably that there is no WHERE clause and\nno table name is specified. All actions refer to the target table, though modifications to\nother tables may be made using triggers.\n\nWhen DO NOTHING is specified, the source row is skipped. Since actions are evaluated in their\nspecified order, DO NOTHING can be handy to skip non-interesting source rows before more\nfine-grained handling.\n\nThere is no separate MERGE privilege. If you specify an update action, you must have the\nUPDATE privilege on the column(s) of the target table that are referred to in the SET clause.\nIf you specify an insert action, you must have the INSERT privilege on the target table. If\nyou specify a delete action, you must have the DELETE privilege on the target table. If you\nspecify a DO NOTHING action, you must have the SELECT privilege on at least one column of the\ntarget table. You will also need SELECT privilege on any column(s) of the datasource and of\nthe target table referred to in any condition (including joincondition) or expression.\nPrivileges are tested once at statement start and are checked whether or not particular WHEN\nclauses are executed.\n\nMERGE is not supported if the target table is a materialized view, foreign table, or if it\nhas any rules defined on it.\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "withquery\nThe WITH clause allows you to specify one or more subqueries that can be referenced by\nname in the MERGE query. See Section 7.8 and SELECT(7) for details. Note that WITH\nRECURSIVE is not supported by MERGE.\n\ntargettablename\nThe name (optionally schema-qualified) of the target table to merge into. If ONLY is\nspecified before the table name, matching rows are updated or deleted in the named table\nonly. If ONLY is not specified, matching rows are also updated or deleted in any tables\ninheriting from the named table. Optionally, * can be specified after the table name to\nexplicitly indicate that descendant tables are included. The ONLY keyword and * option do\nnot affect insert actions, which always insert into the named table only.\n\ntargetalias\nA substitute name for the target table. When an alias is provided, it completely hides\nthe actual name of the table. For example, given MERGE INTO foo AS f, the remainder of\nthe MERGE statement must refer to this table as f not foo.\n\nsourcetablename\nThe name (optionally schema-qualified) of the source table, view, or transition table. If\nONLY is specified before the table name, matching rows are included from the named table\nonly. If ONLY is not specified, matching rows are also included from any tables\ninheriting from the named table. Optionally, * can be specified after the table name to\nexplicitly indicate that descendant tables are included.\n\nsourcequery\nA query (SELECT statement or VALUES statement) that supplies the rows to be merged into\nthe target table. Refer to the SELECT(7) statement or VALUES(7) statement for a\ndescription of the syntax.\n\nsourcealias\nA substitute name for the data source. When an alias is provided, it completely hides the\nactual name of the table or the fact that a query was issued.\n\njoincondition\njoincondition is an expression resulting in a value of type boolean (similar to a WHERE\nclause) that specifies which rows in the datasource match rows in the target table.\n\nWarning\nOnly columns from the target table that attempt to match datasource rows should\nappear in joincondition.  joincondition subexpressions that only reference the\ntarget table's columns can affect which action is taken, often in surprising ways.\n\nwhenclause\nAt least one WHEN clause is required.\n\nIf the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in\nthe target table, the WHEN clause is executed if the condition is absent or it evaluates\nto true.\n\nConversely, if the WHEN clause specifies WHEN NOT MATCHED and the candidate change row\ndoes not match a row in the target table, the WHEN clause is executed if the condition is\nabsent or it evaluates to true.\n\ncondition\nAn expression that returns a value of type boolean. If this expression for a WHEN clause\nreturns true, then the action for that clause is executed for that row.\n\nA condition on a WHEN MATCHED clause can refer to columns in both the source and the\ntarget relations. A condition on a WHEN NOT MATCHED clause can only refer to columns from\nthe source relation, since by definition there is no matching target row. Only the system\nattributes from the target table are accessible.\n\nmergeinsert\nThe specification of an INSERT action that inserts one row into the target table. The\ntarget column names can be listed in any order. If no list of column names is given at\nall, the default is all the columns of the table in their declared order.\n\nEach column not present in the explicit or implicit column list will be filled with a\ndefault value, either its declared default value or null if there is none.\n\nIf the target table is a partitioned table, each row is routed to the appropriate\npartition and inserted into it. If the target table is a partition, an error will occur\nif any input row violates the partition constraint.\n\nColumn names may not be specified more than once.  INSERT actions cannot contain\nsub-selects.\n\nOnly one VALUES clause can be specified. The VALUES clause can only refer to columns from\nthe source relation, since by definition there is no matching target row.\n\nmergeupdate\nThe specification of an UPDATE action that updates the current row of the target table.\nColumn names may not be specified more than once.\n\nNeither a table name nor a WHERE clause are allowed.\n\nmergedelete\nSpecifies a DELETE action that deletes the current row of the target table. Do not\ninclude the table name or any other clauses, as you would normally do with a DELETE(7)\ncommand.\n\ncolumnname\nThe name of a column in the target table. The column name can be qualified with a\nsubfield name or array subscript, if needed. (Inserting into only some fields of a\ncomposite column leaves the other fields null.) Do not include the table's name in the\nspecification of a target column.\n\nOVERRIDING SYSTEM VALUE\nWithout this clause, it is an error to specify an explicit value (other than DEFAULT) for\nan identity column defined as GENERATED ALWAYS. This clause overrides that restriction.\n\nOVERRIDING USER VALUE\nIf this clause is specified, then any values supplied for identity columns defined as\nGENERATED BY DEFAULT are ignored and the default sequence-generated values are applied.\n\nDEFAULT VALUES\nAll columns will be filled with their default values. (An OVERRIDING clause is not\npermitted in this form.)\n\nexpression\nAn expression to assign to the column. If used in a WHEN MATCHED clause, the expression\ncan use values from the original row in the target table, and values from the datasource\nrow. If used in a WHEN NOT MATCHED clause, the expression can use values from the\ndatasource row.\n\nDEFAULT\nSet the column to its default value (which will be NULL if no specific default expression\nhas been assigned to it).\n\nsub-SELECT\nA SELECT sub-query that produces as many output columns as are listed in the\nparenthesized column list preceding it. The sub-query must yield no more than one row\nwhen executed. If it yields one row, its column values are assigned to the target\ncolumns; if it yields no rows, NULL values are assigned to the target columns. The\nsub-query can refer to values from the original row in the target table, and values from\nthe datasource row.\n",
            "subsections": []
        },
        "OUTPUTS": {
            "content": "On successful completion, a MERGE command returns a command tag of the form\n\nMERGE totalcount\n\nThe totalcount is the total number of rows changed (whether inserted, updated, or deleted).\nIf totalcount is 0, no rows were changed in any way.\n",
            "subsections": []
        },
        "NOTES": {
            "content": "The following steps take place during the execution of MERGE.\n\n1. Perform any BEFORE STATEMENT triggers for all actions specified, whether or not their\nWHEN clauses match.\n\n2. Perform a join from source to target table. The resulting query will be optimized\nnormally and will produce a set of candidate change rows. For each candidate change row,\n\n1. Evaluate whether each row is MATCHED or NOT MATCHED.\n\n2. Test each WHEN condition in the order specified until one returns true.\n\n3. When a condition returns true, perform the following actions:\n\n1. Perform any BEFORE ROW triggers that fire for the action's event type.\n\n2. Perform the specified action, invoking any check constraints on the target table.\n\n3. Perform any AFTER ROW triggers that fire for the action's event type.\n\n3. Perform any AFTER STATEMENT triggers for actions specified, whether or not they actually\noccur. This is similar to the behavior of an UPDATE statement that modifies no rows.\n\nIn summary, statement triggers for an event type (say, INSERT) will be fired whenever we\nspecify an action of that kind. In contrast, row-level triggers will fire only for the\nspecific event type being executed. So a MERGE command might fire statement triggers for both\nUPDATE and INSERT, even though only UPDATE row triggers were fired.\n\nYou should ensure that the join produces at most one candidate change row for each target\nrow. In other words, a target row shouldn't join to more than one data source row. If it\ndoes, then only one of the candidate change rows will be used to modify the target row; later\nattempts to modify the row will cause an error. This can also occur if row triggers make\nchanges to the target table and the rows so modified are then subsequently also modified by\nMERGE. If the repeated action is an INSERT, this will cause a uniqueness violation, while a\nrepeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required\nby the SQL standard. This differs from historical PostgreSQL behavior of joins in UPDATE and\nDELETE statements where second and subsequent attempts to modify the same row are simply\nignored.\n\nIf a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind\n(MATCHED or NOT MATCHED). If a later WHEN clause of that kind is specified it would be\nprovably unreachable and an error is raised. If no final reachable clause is specified of\neither kind, it is possible that no action will be taken for a candidate change row.\n\nThe order in which rows are generated from the data source is indeterminate by default. A\nsourcequery can be used to specify a consistent ordering, if required, which might be needed\nto avoid deadlocks between concurrent transactions.\n\nThere is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and DELETE cannot contain\nRETURNING or WITH clauses.\n\nWhen MERGE is run concurrently with other commands that modify the target table, the usual\ntransaction isolation rules apply; see Section 13.2 for an explanation on the behavior at\neach isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an\nalternative statement which offers the ability to run an UPDATE if a concurrent INSERT\noccurs. There are a variety of differences and restrictions between the two statement types\nand they are not interchangeable.\n",
            "subsections": []
        },
        "EXAMPLES": {
            "content": "Perform maintenance on customeraccounts based upon new recenttransactions.\n\nMERGE INTO customeraccount ca\nUSING recenttransactions t\nON t.customerid = ca.customerid\nWHEN MATCHED THEN\nUPDATE SET balance = balance + transactionvalue\nWHEN NOT MATCHED THEN\nINSERT (customerid, balance)\nVALUES (t.customerid, t.transactionvalue);\n\nNotice that this would be exactly equivalent to the following statement because the MATCHED\nresult does not change during execution.\n\nMERGE INTO customeraccount ca\nUSING (SELECT customerid, transactionvalue FROM recenttransactions) AS t\nON t.customerid = ca.customerid\nWHEN MATCHED THEN\nUPDATE SET balance = balance + transactionvalue\nWHEN NOT MATCHED THEN\nINSERT (customerid, balance)\nVALUES (t.customerid, t.transactionvalue);\n\nAttempt to insert a new stock item along with the quantity of stock. If the item already\nexists, instead update the stock count of the existing item. Don't allow entries that have\nzero stock.\n\nMERGE INTO wines w\nUSING winestockchanges s\nON s.winename = w.winename\nWHEN NOT MATCHED AND s.stockdelta > 0 THEN\nINSERT VALUES(s.winename, s.stockdelta)\nWHEN MATCHED AND w.stock + s.stockdelta > 0 THEN\nUPDATE SET stock = w.stock + s.stockdelta\nWHEN MATCHED THEN\nDELETE;\n\nThe winestockchanges table might be, for example, a temporary table recently loaded into\nthe database.\n",
            "subsections": []
        },
        "COMPATIBILITY": {
            "content": "This command conforms to the SQL standard.\n\nThe WITH clause and DO NOTHING action are extensions to the SQL standard.\n\nPostgreSQL 16.14                                2026                                        MERGE(7)",
            "subsections": []
        }
    },
    "summary": "MERGE - conditionally insert, update, or delete rows of a table",
    "flags": [],
    "examples": [
        "Perform maintenance on customeraccounts based upon new recenttransactions.",
        "MERGE INTO customeraccount ca",
        "USING recenttransactions t",
        "ON t.customerid = ca.customerid",
        "WHEN MATCHED THEN",
        "UPDATE SET balance = balance + transactionvalue",
        "WHEN NOT MATCHED THEN",
        "INSERT (customerid, balance)",
        "VALUES (t.customerid, t.transactionvalue);",
        "Notice that this would be exactly equivalent to the following statement because the MATCHED",
        "result does not change during execution.",
        "MERGE INTO customeraccount ca",
        "USING (SELECT customerid, transactionvalue FROM recenttransactions) AS t",
        "ON t.customerid = ca.customerid",
        "WHEN MATCHED THEN",
        "UPDATE SET balance = balance + transactionvalue",
        "WHEN NOT MATCHED THEN",
        "INSERT (customerid, balance)",
        "VALUES (t.customerid, t.transactionvalue);",
        "Attempt to insert a new stock item along with the quantity of stock. If the item already",
        "exists, instead update the stock count of the existing item. Don't allow entries that have",
        "zero stock.",
        "MERGE INTO wines w",
        "USING winestockchanges s",
        "ON s.winename = w.winename",
        "WHEN NOT MATCHED AND s.stockdelta > 0 THEN",
        "INSERT VALUES(s.winename, s.stockdelta)",
        "WHEN MATCHED AND w.stock + s.stockdelta > 0 THEN",
        "UPDATE SET stock = w.stock + s.stockdelta",
        "WHEN MATCHED THEN",
        "DELETE;",
        "The winestockchanges table might be, for example, a temporary table recently loaded into",
        "the database."
    ],
    "see_also": []
}