EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Merge
 

PostgreSQL Merge

Updated November 7, 2023

PostgreSQL Merge

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

PostgreSQL Merge 1

  • 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:

PostgreSQL Merge 2

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:

PostgreSQL Merge 3

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:

by using insert on conflict do nothing

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:

using insert on conflict do update

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Merge” benefited you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL RAISE EXCEPTION
  2. MOD() in PostgreSQL
  3. PostgreSQL TEXT
  4. PostgreSQL TO_DATE()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW