Trending September 2023 # Examples To Implement Upsert In Postgresql # Suggested October 2023 # Top 15 Popular

You are reading the article Examples To Implement Upsert In Postgresql updated in September 2023 on the website Chivangcangda.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Examples To Implement Upsert In Postgresql

Introduction to 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:

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;

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

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.