Introduction to PostgreSQL Merge
PostgreSQL Merge is used to merge two table, to implement merge table we need to have unique index on table using unique index duplication record checking is easily performed. Merge table is also possible without unique index but we need to lock the table before merging two table in PostgreSQL. Merge is very important and useful to merge two table. Merge is related to the upsert command in PostgreSQL, upsert command is introduced from PostgreSQL version 9.5, in PostgreSQL there are no command available like merge. We are implementing merging by 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: Basically merge command is not available in PostgreSQL to use the merge command we are using with upsert and insert on conflict command in PostgreSQL.
- Update: We are using update statement with upsert and insert on conflict statement in PostgreSQL to update the rows and merge into the table.
- Name of table: This is defined as name of table used to merge the two table we are using the same by using insert and update statement in PostgreSQL.
- Name of column: We have used name of column to insert values into the column, also we are using insert column with on conflict statement in PostgreSQL.
- Insert on conflict: Basically merge command is not available in PostgreSQL to use the merge command we are using with insert on conflict and upsert command.
- Set condition: We are using set condition with upsert statement at the time of updating rows from the table in PostgreSQL.
- Where condition: We are using where condition at the time of insert on conflict and upsert statement while merging table.
- Select: This statement is used to select the data from another table and after selecting we have merge the same data into the table.
- Value1 to valueN: This is defined as we are using value of column at the time of insertion data into the table.
How Merge Command works in PostgreSQL?
- Functionality of upsert statement is added from PostgreSQL version 9.5 from PostgreSQL 9.5 we are using upsert clause to merge two table in PostgreSQL. Before 9.5 merge or upsert command is not available.
- Merge command is not available, while using the same it will show the syntax error with merge keyword.
- Below example shows that merge command is not available in PostgreSQL, it will show the error while using in PostgreSQL.
- In below example we have used two table name as table_name1 and table_name2 for merging using merge command.
- But it will give error because merge command is not available 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 table but it will include the on conflict constraint at the time of insertion.
- On conflict with insert statement is indicate the field which was not repeated in the statement.
- Merge is basically used merge two tables. We can use the merge interchangeably with upsert statement.
- Database like oracle, Teradata, db2, MSSQL, firebird, cubrid and vectorwise database will support the standard syntax of merge SQL statement.
- From PostgreSQL version 9.1 we have use common table expression statement is used with larger query.
- Each auxiliary statement is defined as the common table expression. We are using CTE to merge the table using upsert statement.
Examples of PostgreSQL Merge
Given below are the examples mentioned:
We are using table1 and table2 table to describe example 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 upsert statement.
- Below example shows that merge by using 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.
- Below example shows that Merge by using insert conflict do 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.
- Below example shows that Merge by using insert conflict do update.
- We have also used Merge operation on 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
This is a guide to PostgreSQL Merge. Here we discuss the introduction, how merge command works in PostgreSQL? and examples respectively. You may also have a look at the following articles to learn more –
