Updated May 31, 2023
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.
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?
- The UPSERT combines two DML’s first is an UPDATE, and the second is an INSERT.
- 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.
- 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;
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;
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;
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.
