Updated November 7, 2023
Introduction to PostgreSQL Merge
PostgreSQL does not have a dedicated ‘MERGE’ statement. To merge two tables in PostgreSQL, you typically use the INSERT ON CONFLICT statement. While having a unique index can help prevent duplicate records during the merge, it’s not strictly required. The INSERT ON CONFLICT statement handles conflicts efficiently. Locking the entire table is generally not necessary for basic merge operations. The term ‘upsert’ is often used to describe the functionality of inserting a new record or updating an existing one if a conflict occurs, and this is achieved in PostgreSQL using INSERT ON CONFLICT.
Syntax:
Given below is the syntax:
1. Merge table using upsert.
WITH upsert as (update name_of_tableset condition from name_of_table d where condition
insert into name_of_table select name_of_column1, name_of_column2, .., name_of_columnN from name_of_table where condition;2. Merge table using insert on conflict do update.
INSERT INTO name_of_table (name_of_column1, name_of_column2, .., name_of_columnN) VALUES (value1, value2, …, valueN)ON CONFLICT (name_of_column)DO UPDATE set condition;3. Merge table using insert on conflict do nothing.
INSERT INTO name_of_table (name_of_column1, name_of_column2, .., name_of_columnN) VALUES (value1, value2, …, valueN)ON CONFLICT (name_of_column)DO NOTHING;Below is the parameter description syntax of merge in PostgreSQL:
- With upsert: The merge command is not available in PostgreSQL to use the merge command we use with upsert and insert on conflict command in PostgreSQL.
- Update: We use the update statement with upsert and insert on conflict statement in PostgreSQL to update the rows and merge them into the table.
- Name of table: This is defined as the name of the table used to merge the two tables. We use the same by inserting and updating statements in PostgreSQL.
- Name of column: We have used the name of column to insert values into the column, also, we are using insert column with on conflict statement in PostgreSQL.
- Insert on conflict: The merge command is not available in PostgreSQL to use the merge command with insert on conflict and upsert command.
- Set condition: We use set condition with an upsert statement when updating rows from the table in PostgreSQL.
- Where condition: We use the where condition at the time of insert on conflict and upsert statement while merging the table.
- Select: This statement is used to select the data from another table, and after selecting it, we merge the same data into the table.
- Value1 to valueN: This is defined as using the value of the column at the time of insertion of data into the table.
How does Merge Command work in PostgreSQL?
- PostgreSQL introduced the functionality of the “upsert” statement starting from version 9.5. Since PostgreSQL 9.5, we have been utilizing the “upsert” clause to merge two tables in PostgreSQL. Before 9.5, the merge or upsert command is not available.
- Merge command is not available, while using the same, it will show the syntax error with the merge keyword.
- Below example shows that the merge command is not available in PostgreSQL, it will show the error while using PostgreSQL.
- In the example below, we used table names table_name1 and table_name2 to merge using the merge command.
- But it will give an error because the merge command is unavailable in PostgreSQL.
Code:
MERGE INTO table_name1 USING table_name2 ON table_name1.id = table_name2.id WHEN MATCHED THEN UPDATE SET bal = bal + table_name2.vol WHEN NOT MATCHED THEN INSERT VALUES (table_name2.id, table_name2.vol);Output:
- Upsert in PostgreSQL consists of the common insertion on the table, but it will include the on conflict constraint at the time of insertion.
- When using the “on conflict” clause with an insert statement, it specifies the field that should not be duplicated in the statement.
- Merge is basically used to merge two tables. We can use the merge interchangeably with the upsert statement.
- Databases like Oracle, Teradata, db2, MSSQL, firebird, cubrid and vectorwise databases will support the standard syntax of merge SQL statements.
- Starting from PostgreSQL version 9.1, we utilize a joint table expression statement in conjunction with a larger query.
- We define each auxiliary statement as a common table expression. We are using CTE to merge the table using an upsert statement.
Examples
Given below are the examples mentioned:
We are using table1 and table2 table to describe examples of Merge in PostgreSQL.
Below is the table and data description of table1 and table2 table.
Code:
select * from table1;
\d+ table1;
select * from table2;
\d+ table2;Output:
Example #1
Merge by using an upsert statement.
- The below example shows that merge by using an upsert statement.
- We have used table1 and table2 table to merge the data.
Code:
WITH upsert as(update table2 t2 set stud_id=t2.stud_id+t1.stud_id, add=t1.add from table1 t1 where t2.id=t1.id RETURNING t2.*)
insert into table2 select p.id, p.stud_id,'Delhi' from table1 p where p.id not in (select q.id from upsert q);
select * from table2;Output:
Example #2
Merge by using insert on conflict do nothing.
- The below example shows that Merge by using insert conflict does nothing.
Code:
INSERT INTO table1 (id, stud_id, add) VALUES (101, 111, 'Pune'),(102, 112, 'Mumbai') ON CONFLICT DO NOTHING;
select * from table1;Output:
Example #3
Merge by using insert on conflict do update.
- The below example shows that Merge, by using insert conflict, does update.
- We have also used the Merge operation on the id column.
Code:
INSERT INTO table1 (id, stud_id, add) VALUES (13, 0, 'Mumbai'), (1001, 1111, 'Mumbai') ON CONFLICT ON CONSTRAINT "table1_pkey" DO UPDATE SET id = table1.id + EXCLUDED.id;
select * from table1;Output:
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL Merge” benefited you. You can view EDUCBA’s recommended articles for more information.
