Home Explore Blog CI



postgresql

1st chunk of `doc/src/sgml/ref/merge.sgml`
3ffc60cf97e647250fc3b6fd0c1cc51adc672892dba89bd30000000100000fa2
<!--
doc/src/sgml/ref/merge.sgml
PostgreSQL documentation
-->

<refentry id="sql-merge">
 <indexterm zone="sql-merge">
  <primary>MERGE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>MERGE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>MERGE</refname>
  <refpurpose>conditionally insert, update, or delete rows of a table</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
[ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
<replaceable class="parameter">when_clause</replaceable> [...]
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
            { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]

<phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>

{ [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]

<phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>

{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }

<phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase>

INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }

<phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase>

UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
             ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
             ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
           } [, ...]

<phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase>

DELETE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>MERGE</command> performs actions that modify rows in the
   target table identified as <replaceable class="parameter">target_table_name</replaceable>,
   using the <replaceable class="parameter">data_source</replaceable>.
   <command>MERGE</command> provides a single <acronym>SQL</acronym>
   statement that can conditionally <command>INSERT</command>,
   <command>UPDATE</command> or <command>DELETE</command> rows, a task
   that would otherwise require multiple procedural language statements.
  </para>

  <para>
   First, the <command>MERGE</command> command performs a join
   from <replaceable class="parameter">data_source</replaceable> to
   the target table
   producing zero or more candidate change rows.  For each candidate change
   row,

Title: MERGE SQL Command in PostgreSQL
Summary
This documentation describes the MERGE SQL command in PostgreSQL, which allows for conditional insertion, updating, or deletion of rows in a target table based on a data source. The command joins the data source with the target table, and for each candidate row, actions are performed based on specified conditions (WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, WHEN NOT MATCHED).