MERGE(7)

📦 version 18.6, in opensuse-leap 16.0

MERGE(7) 💖 PostgreSQL 18.6 Documentation

okay bestie, pull up a chair because this is the manual page for MERGE, and she is that girl. conditionally insert, update, or delete rows of a table?? the range?? iconic. let's get into it 💅

✨ NAME ✨

MERGE — conditionally insert, update, or delete rows of a table 🫶

🎀 SYNOPSIS 🎀

okay so here's the whole shape of it, no notes, literally verbatim:

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
    USING data_source ON join_condition
    when_clause [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ]

and data_source is giving:

{ [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

INSERT [( column_name [, ...] )]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
             ( column_name [, ...] ) = ( sub-SELECT )
           } [, ...]

and merge_delete is, and I cannot stress this enough, just:

DELETE

🌸 DESCRIPTION 🌸

okay so MERGE does actions that modify rows in the target table — that's the one called target_table_name — using the data_source. and the whole point, the moment, is that MERGE gives you a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows. like, a task that would otherwise require multiple procedural language statements?? one statement. main character energy fr fr. ✨

first things first: the MERGE command does a join from data_source to the target table, and that produces zero or more candidate change rows. for each candidate change row, the status of MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET] gets set just once — one time, no re-dos — and after that the WHEN clauses are evaluated in the order specified. for each candidate change row, the first clause that evaluates as true is the one that runs. and no more than one WHEN clause is executed for any candidate change row. periodt. 💅

MERGE actions hit the same as regular UPDATE, INSERT, or DELETE commands of the same names. but the syntax of those commands is different — notably there's no WHERE clause and no table name is specified. all actions refer to the target table, though modifications to other tables may be made using triggers. iykyk. 🦋

when DO NOTHING is specified, the source row is just… skipped 🙈. and since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before you get into more fine-grained handling. it's giving triage, honestly.

the optional RETURNING clause makes MERGE compute and return value(s) based on each row inserted, updated, or deleted. any expression using the source or target table's columns, or the merge_action() function, can be computed. by default, when an INSERT or UPDATE action is performed, the new values of the target table's columns are used, and when a DELETE is performed, the old</old> values of the target table's columns are used — but it's also possible to explicitly request old and new values. the syntax of the RETURNING list is identical to that of the output list of SELECT. 🎀

ok so listen, privileges section, don't skip it bestie 🫶

there is no separate MERGE privilege. so let's break down what you actually need:

  • if you specify an update action, you must have the UPDATE privilege on the column(s) of the target table that are referred to in the SET clause.
  • if you specify an insert action, you must have the INSERT privilege on the target table.
  • if you specify a delete action, you must have the DELETE privilege on the target table.
  • if you specify a DO NOTHING action, you must have the SELECT privilege on at least one column of the target table.

you will also need SELECT privilege on any column(s) of the data_source and of the target table referred to in any condition (including join_condition) or expression. and here's the kicker — privileges are tested once at statement start and they're checked whether or not particular WHEN clauses are executed. so no sneaky "but that branch never ran" excuses. 😭

🚨 and now the big one, the vibe check: MERGE is not supported if the target table is a materialized view, a foreign table, or if it has any rules defined on it. no cap. that's just how it is. 💀

more coming, bestie — this page keeps going 🌈

💖 PARAMETERS 💖

Ok bestie, this is the big one — every knob you can turn on a MERGE. Deep breath, we got this 🫶

with_query

The WITH clause lets you drop one or more subqueries that you can then reference by name inside the MERGE query. It's giving pre-gaming your query before the real thing. See Section 7.8 and SELECT(7) for the deets. 🎀

Heads up: WITH RECURSIVE is NOT supported by MERGE. Sorry babe, recursive CTEs are not invited to this party 😭

target_table_name

The name (optionally schema-qualified) of the table or view you're merging into. 💅

  • If ONLY is written before the table name, matching rows get updated or deleted in the named table only.
  • If ONLY is not specified, matching rows also get updated or deleted in any tables that inherit from the named table.
  • Optionally you can slap a * after the table name to explicitly say "yes, include the descendant tables too."
The ONLY keyword and the * option do not touch insert actions — inserts always go into the named table only. No nepo-baby inheritance for inserts 🚫

If target_table_name is a view, it has to be one of these two flavors:

  1. automatically updatable with no INSTEAD OF triggers, or
  2. it has INSTEAD OF triggers for every type of action specified in the WHEN clauses — that's INSERT, UPDATE, and DELETE.

Views with rules? Not supported. Hard pass, we don't do that here. 🙈

target_alias

A substitute name for the target table. Once you give it an alias, the alias completely hides the table's real name — main character energy, the real name is gone. ✨

So like, if you write MERGE INTO foo AS f, the rest of your MERGE statement has to call this table f, not foo. Periodt.

source_table_name

The name (optionally schema-qualified) of the source table, view, or transition table. 🌸

  • ONLY before the table name → matching rows come from the named table only.
  • No ONLY → matching rows also come from any tables inheriting from the named table.
  • Optionally * after the table name → explicitly include those descendant tables.

source_query

A query — so a SELECT statement or a VALUES statement — that supplies the rows getting merged into the target table. 💖 Syntax-wise, go read SELECT(7) or VALUES(7), they've got you.

source_alias

A substitute name for the data source. When you provide an alias, it completely hides the actual table name and the fact that a query was even issued. Iconic little mystery machine 🕵️♀️

join_condition

join_condition is an expression that results in a value of type boolean (think: basically a WHERE clause) and it says which rows in the data_source match rows in the target table. 💫

⚠️ Warning ⚠️ Only columns from the target table that are trying to match data_source rows should appear in join_condition. Subexpressions of join_condition that only reference the target table's columns can change which action gets taken — often in ways that are lowkey surprising. 😭 If both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY TARGET] clauses are specified, the MERGE command does a FULL join between data_source and the target table. For that to work, at least one join_condition subexpression has to use an operator that can support a hash join, or all of the subexpressions have to use operators that can support a merge join. No cap, it's that or nothing 🔥

when_clause

At least one WHEN clause is required. You literally cannot MERGE without one, bestie, it's mandatory 💅

A WHEN clause can specify WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT MATCHED [BY TARGET].

Fun fact: the SQL standard only defines WHEN MATCHED and WHEN NOT MATCHED (which means "no matching target row"). WHEN NOT MATCHED BY SOURCE is an extension to the SQL standard, and so is the option to append BY TARGET to WHEN NOT MATCHED to make its meaning more explicit. We're built different 😌

Here's how the three of them behave:

  • If the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in the data_source to a row in the target table → the WHEN clause is executed if the condition is absent or it evaluates to true.
  • If the WHEN clause specifies WHEN NOT MATCHED BY SOURCE and the candidate change row represents a row in the target table that does not match a row in the data_source → the WHEN clause is executed if the condition is absent or it evaluates to true.
  • If the WHEN clause specifies WHEN NOT MATCHED [BY TARGET] and the candidate change row represents a row in the data_source that does not match a row in the target table → the WHEN clause is executed if the condition is absent or it evaluates to true.

condition

An expression that returns a value of type boolean. If this expression for a WHEN clause returns true, then that clause's action runs for that row. Simple as that. ✨

Where can a condition peek? Depends on the clause, bb:

  • A condition on a WHEN MATCHED clause can refer to columns in both the source and the target relations.
  • A condition on a WHEN NOT MATCHED BY SOURCE clause can only refer to columns from the target relation, because by definition there's no matching source row. 🙈
  • A condition on a WHEN NOT MATCHED [BY TARGET] clause can only refer to columns from the source relation, because by definition there's no matching target row.
Also: only the system attributes from the target table are accessible. That's the rule 🌈

merge_insert

The spec for an INSERT action that inserts one row into the target table. 🎀

  • Target column names can be listed in any order.
  • If you give no list of column names at all, the default is all the columns of the table, in their declared order.

Each column that isn't in the explicit or implicit column list gets filled with a default value — either its declared default value, or null if it doesn't have one.

If the target table is a partitioned table, each row gets routed to the appropriate partition and inserted there. If the target table is a partition, an error occurs if any input row violates the partition constraint. 🚨

Other rules, don't skip these:

  • Column names may not be specified more than once.
  • INSERT actions cannot contain sub-selects.
  • Only one VALUES clause can be specified.
  • The VALUES clause can only refer to columns from the source relation, since by definition there's no matching target row.

merge_update

The spec for an UPDATE action that updates the current row of the target table. 💖

Column names may not be specified more than once.

And neither a table name nor a WHERE clause is allowed. Nope, not even a little one 🚫

merge_delete

Specifies a DELETE action that deletes the current row of the target table. 💀

Do not include the table name or any other clauses — you know, all the stuff you'd normally do with a DELETE(7) command. This one is bare. Just vibes and deletion.

column_name

The name of a column in the target table. If needed, the column name can be qualified with a subfield name or an array subscript. (Inserting into only some fields of a composite column leaves the other fields null — noted 🫶)

Do not include the table's name in the specification of a target column. That's a rookie move, don't do it bestie 😭

OVERRIDING SYSTEM VALUE

Without this clause, it's an error to specify an explicit value (anything other than DEFAULT) for an identity column defined as GENERATED ALWAYS. This clause overrides that restriction and lets you do it anyway. Rebel behavior, we love it 🔥

OVERRIDING USER VALUE

If this clause is specified, then any values supplied for identity columns defined as GENERATED BY DEFAULT are ignored, and the default sequence-generated values get applied instead. Sorry to your input, it's not getting used 💅

DEFAULT VALUES

All columns get filled with their default values. ✨

(An OVERRIDING clause is not permitted in this form. Pick a lane, babe.)

expression

An expression to assign to the column. Where it's allowed to pull values from depends on the clause:

  • Used in a WHEN MATCHED clause → the expression can use values from the original row in the target table, and values from the data_source row.
  • Used in a WHEN NOT MATCHED BY SOURCE clause → the expression can only use values from the original row in the target table.
  • Used in a WHEN NOT MATCHED [BY TARGET] clause → the expression can only use values from the data_source row.

DEFAULT

Set the column to its default value — which will be NULL if no specific default expression has been assigned to it. Classic 🌸

sub-SELECT

A SELECT sub-query that produces as many output columns as are listed in the parenthesized column list right before it. 🎀

  • The sub-query must yield no more than one row when executed.
  • If it yields one row, its column values get assigned to the target columns.
  • If it yields no rows, NULL values get assigned to the target columns.

And where it can look:

  • Used in a WHEN MATCHED clause → the sub-query can refer to values from the original row in the target table, and values from the data_source row.
  • Used in a WHEN NOT MATCHED BY SOURCE clause → the sub-query can only refer to values from the original row in the target table.

output_alias

An optional substitute name for OLD or NEW rows in the RETURNING list. 💫

By default, old values from the target table can be returned by writing OLD.column_name or OLD.*, and new values can be returned by writing NEW.column_name or NEW.*.

When you provide an alias, those names get hidden and you have to refer to the old or new rows using the alias instead. For example:

RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*

output_expression

An expression to be computed and returned by the MERGE command after each row is changed — whether that's inserted, updated, or deleted. 👑

The expression can use any columns of the source or target tables, or the merge_action() function to return extra info about which action was executed.

Writing * returns all columns from the source table, followed by all columns from the target table. Often that leads to a lot of duplication, since it's super common for source and target tables to share a bunch of the same columns. 😭 You can dodge that by qualifying the * with the name or alias of the source or target table. ✨

A column name or * can also be qualified using OLD or NEW, or the corresponding output_alias for OLD or NEW, to make old or new values from the target table come back.

And here's the fine print on which values you get:

  • An unqualified column name from the target table, or a column name or * qualified using the target table name or alias → returns new values for INSERT and UPDATE actions, and old values for DELETE actions.

output_name

A name to use for a returned column. That's it, that's the whole thing, name your little output bb 🎀

🌈 OUTPUTS 🌈

On successful completion, a MERGE command returns a command tag of the form:

MERGE total_count

The total_count is the total number of rows changed — whether inserted, updated, or deleted. If total_count is 0, then no rows were changed in any way. Nothing happened. Zero. Zilch. The void stared back 💀

If the MERGE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted, updated, or deleted by the command. 🫶

NOTES 🫶 (the lore, bestie, read it fr)

So here's the whole backstage timeline of what actually goes down when MERGE runs. Like, the choreography:

  1. Do all the BEFORE STATEMENT triggers for every action you specified, whether or not their WHEN clauses match. Yeah — doesn't matter if the condition is a lie, the statement trigger still shows up to the party. 💅
  2. Join the source to the target table. That resulting query gets optimized like a normal query would (no special treatment, no cap) and it spits out a set of candidate change rows. Then for each candidate change row, this happens:
    1. Figure out whether the row is MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET].
    2. Test each WHEN condition in the order you wrote them until one of them returns true. First one wins, periodt.
    3. The moment a condition returns true, do these:
      1. Fire any BEFORE ROW triggers that match the action's event type.
      2. Do the specified action, which also invokes any check constraints on the target table.
      3. Fire any AFTER ROW triggers that match the action's event type.

And if the target relation is a view with INSTEAD OF ROW triggers for the action's event type? Those get used to perform the action instead. Iconic swap. 🦋

  1. Do any AFTER STATEMENT triggers for the actions specified, whether or not those actions actually occurred. Which is basically the same energy as an UPDATE statement that modifies zero rows. 😭

So to summarise (because I know you want the tl;dr 🎀): statement triggers for an event type — say, INSERT — will fire whenever you specify an action of that kind. Meanwhile, row-level triggers only fire for the specific event type actually being executed. So a single MERGE command might fire statement triggers for both UPDATE and INSERT, even though only the UPDATE row triggers actually fired. Not me pointing that out at 2am. 💀

BIG ONE, listen up bestie: you gotta make sure your join produces at most one candidate change row for each target row. Like, a target row should not join to more than one data source row. If it does, only one of those candidate change rows gets used to modify the target row — and any later attempts to modify that same row will throw an error. 🚨

This can also happen if row triggers make changes to the target table and then those modified rows get subsequently modified by MERGE too. If the repeated action is an INSERT, you get a uniqueness violation. If it's a repeated UPDATE or DELETE, you get a cardinality violation — and that latter behavior is literally required by the SQL standard, so no, we can't just vibe past it. 🙈

And here's the plot twist: this is different from historical PostgreSQL behavior for joins in UPDATE and DELETE statements, where the second and subsequent attempts to modify the same row were just... silently ignored. Gone. Not today though. 💅

If a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind — that's MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET]. If you specify a later WHEN clause of that same kind, it would be provably unreachable and PostgreSQL raises an error at you. And if you specify no final reachable clause of either kind, it's fully possible that no action at all gets taken for a candidate change row. Just sitting there. Doing nothing. Living its truth. 😌

The order in which rows are generated from the data source is indeterminate by default. You can use a source_query to specify a consistent ordering if you need one — and honestly you might need one, because it can be required to avoid deadlocks between concurrent transactions. Be safe out there. 🫶

When MERGE runs concurrently with other commands that modify the target table, the usual transaction isolation rules apply. Check out Section 13.2 for the full explanation of behavior at each isolation level. 📖

You might also wanna consider using INSERT ... ON CONFLICT as an alternative statement — it gives you the ability to run an UPDATE if a concurrent INSERT occurs. But heads up: there are a whole variety of differences and restrictions between the two statement types, and they are not interchangeable. Pick one, don't try to make them the same thing. ✨

EXAMPLES 🌸 (the fun part, let's go)

Do some maintenance on customer_accounts based on the new recent_transactions. Very glow-up of you:

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Try to insert a new stock item along with the quantity of stock. If the item already exists, update the stock count of the existing item instead. And do not allow entries that have zero stock, bestie, we have standards. Then return details of all changes made. 👑

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.winename, old.stock AS old_stock, new.stock AS new_stock;

The wine_stock_changes table might be, for example, a temporary table recently loaded into the database. Classic. 🍓

Update wines based on a replacement wine list: insert rows for any new stock, update the modified stock entries, and delete any wines not present in the new list. Spring cleaning but make it SQL. 🧚

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

COMPATIBILITY 💜

This command conforms to the SQL standard. ✅

But these bits are extensions to the SQL standard, so don't go expecting them everywhere:

  • The WITH clause
  • The BY SOURCE and BY TARGET qualifiers to WHEN NOT MATCHED
  • The DO NOTHING action
  • The RETURNING clause

And that's the whole page, bestie!! 🎀✨ This one's from PostgreSQL 18.6, dated 2026, and you'll find it filed under MERGE(7). Slay, go merge responsibly, mwah 💖💅

📦 where this page comes from