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 UPSERT
 

PostgreSQL UPSERT

Updated May 31, 2023

PostgreSQL UPSERT

 

 

Introduction to PostgreSQL UPSERT

The term UPSERT is referred to as merge in the case of relational databases. When you try to insert a new record into the table, then PostgreSQL will check whether the same record already exists or not in the table. If a similar row already exists in the table, PostgreSQL will update the existing record; otherwise, it will insert the record as a new entry. As we can see, the UPSERT operation either updates or inserts the record into the table and because of this reason, we call this term UPSERT(either UPDATE or INSERT). In this topic, we are going to learn about PostgreSQL UPSERT.

Watch our Demo Courses and Videos

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

Syntax

We can use the following INSERT ON CONFLICT statement as a syntax to use the UPSERT operation in PostgreSQL:

INSERT INTO table(columns) VALUES(values)
ON CONFLICT target action;

Explanation

To support the UPSERT operation, PostgreSQL added the ON CONFLICT target action.

  • Column: Defines the column name.
  • ON CONSTRAINT constraint_name: Defines the name of constraint name can be the UNIQUE constraint.
  • WHERE condition: Defines the WHERE clause with a condition
  • Action: DO NOTHING: Defines that if a table already contains the do nothing.
  • DO UPDATE SET column_name_1 = value_field_1, … WHERE predicate: We can update some fields in the table.

How UPSERT works in PostgreSQL?

  1.  The UPSERT combines two DML’s first is an UPDATE, and the second is an INSERT.
  2.  If we compare the IF EXISTS and UPSERT to understand the working of the UPSERT, then the for each update operation, the UPSERT removes an additional read from the table. But in the case of the INSERT operation, the UPSERT and IF EXISTS operations use the same number of reading operations on the table.
  3.  To check whether a record already exists in the table, the PostgreSQL UPSERT statement uses the record’s unique identifier as the key to match records in the table.

Examples to Implement UPSERT in PostgreSQL

Let’s create a table named ‘company’ to understand the PostgreSQL UPSERT feature:

We will create a company table by using the following CREATE TABLE statement.

CREATE TABLE company (
comp_id serial PRIMARY KEY,
comp_name VARCHAR UNIQUE,
comp_email VARCHAR NOT NULL,
comp_contact VARCHAR
);

Here you can see that the table company contains four columns comp_id, comp_name, comp_email, and comp_contact. In addition, we added the UNIQUE constraint on the comp_name column of the company table to ensure the company’s uniqueness.

Now we will insert some records into the table by INSERT INTO statement as follows:

INSERT INTO company (comp_name, comp_email)
VALUES
('Bloomberg', '[email protected]'),
('Facebook', '[email protected]'   ),
('Google', '[email protected]'   );

Illustrate the result of the above INSERT statement by using the following snapshot and the SELECT statement:

select * from company;

PostgreSQL Upsert output 1

Now we can update the email id information of any row using an UPDATE statement. Consider an example where Facebook has changed its email id from [email protected] to [email protected].

We will use the following INSERT ON CONFLICT statement to demonstrate the UPSERT feature:

INSERT INTO company (comp_name, comp_email)
VALUES
(
'Facebook',
'[email protected]'
)
ON CONFLICT ON CONSTRAINT company_comp_name_key
DO NOTHING;

The above statement defines that if the company name exists in the company table, do nothing and ignore the same.

Illustrate the result of the above INSERT statement by using the following snapshot and the SELECT statement:

select * from company;

PostgreSQL Upsert output 2

The following statement is similar to the above statement. Still, it will use the comp_name column of the company table instead of the unique constraint comp_name as the target of the INSERT statement.

INSERT INTO company (comp_name, comp_email)
VALUES
(
'Facebook',
'[email protected]'
)
ON CONFLICT (comp_name)
DO NOTHING;

Now, if we want to concatenate the new company email with the old one while inserting an existing company table.

We can use the UPDATE clause as the action of the INSERT INTO statement as defined below:

INSERT INTO company (comp_name, comp_email)
VALUES
(
'Facebook',
'[email protected]'
)
ON CONFLICT (comp_name)
DO UPDATE
SET comp_email = EXCLUDED.comp_email || ';' || customers.comp_email;

Illustrate the result of the above INSERT statement by using the following snapshot and the SELECT statement:

select * from company;

PostgreSQL Upsert output 3

Advantages of using UPSERT in PostgreSQL

  •  The PostgreSQL UPSERT is more than two data manipulation operations combined as an Update operation and an Insert operation.
  •  If we use the PostgreSQL UPSERT feature, then for each update, we remove an additional read from the table.
  •  We can avoid unnecessary read operations on databases by using the UPSERT operation for updating the records.
  •  Using the UPSERT operation, we can avoid duplicate key violations as it updates or inserts a record in the table.

Conclusion

From the above article, we hope you understand how to use the PostgreSQL UPSERT and how the PostgreSQL UPSERT works. Also, we have added some examples of PostgreSQL UPSERT to understand it in detail.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL UPSERT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Full Join
  2. Wildcards in PostgreSQL
  3. PostgreSQL Database
  4. PostgreSQL INTERSECT

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