Upsert

In this section, we are going to understand the working of PostgreSQL upsert attribute, which is used to insert or modify the data if the row that is being inserted already and be present in the table with the help of insert on Conflict command.

In RDBMS (relational database management system), the term upsert is known as merge. When we are inserting a new row into a particular table, the PostgreSQL will upgrade the row if it is already present, or else, it will add the new row. And this process is known as upsert, which is the combination of insert or update command.

In the PostgreSQL, the below query is used to upsert the table using the INSERT ON CONFLICT command:

INSERT INTO table_name(column_list)   

VALUES(value_list)  

ON CONFLICT target action; 

For supporting the upsert attribute, the PostgreSQL added the ON CONFLICT target action clause to the INSERT command.

In the above command, the Conflict_target can be one of the following:

Target	description
column_name	It is used to specify a column name in the particular table.
ON CONSTRAINT constraint_ name	In this, the constraint name could be the name of the UNIQUE constraint.
WHERE predicate	The WHERE clause with a predicate.

In the above command, the Conflict_action can be one of the following:

Actions	Description
DO NOTHING	It defines that we do nothing if the row already presents in the table.
DO UPDATE SET column_1 = value_1, .. WHERE condition	This action is used to update some fields in the table.

Note: If we are using an earlier version of PostgreSQL, we will need a workaround to have the upsert feature as the ON CONFLICT clause is only accessible from PostgreSQL 9.5. version.

Examples of PostgreSQL upsert feature

To understand the working of PostgreSQL Upsert feature in real-time, we need to follow the below process:

Firstly, we will create a new table called customers with the help of below Create command as follows:

CREATE TABLE officers (  

    officers_id serial PRIMARY KEY,  

    officers_name VARCHAR UNIQUE,  

    officers_email VARCHAR NOT NULL,  

    Officers_address VARCHAR NOT NULL  

);  

Output

Once we implemented the above query, we will get the below message window; the Officers table has been created successfully.

The Officers table contains four columns, such as officers_id, officers_name, officers_email, and officers_address.

And to assure the officer’s name uniqueness, we use the unique constraint for the officers_name column.

Once we are done with creating a Officers table, we will insert some data into it using the INSERT command.

INSERT INTO officers   

VALUES   

(101,'Denny', '[email protected]','Newyork'),  

(102,'John', '[email protected]','Boston'),  

(103,'Olivia', '[email protected]','Newyork'),  

(104,'Mike', '[email protected]','Newyork'); 

Output

Once we implemented the above query, we will get the below message window; the values have been inserted successfully in the Officers table.

Let us assume that one of the officers want to change their address Newyork to Florida, so we can modify it with the help of the UPDATE command.

We use the below INSERT ON CONFLICT command to describe the upsert feature:

 INSERT INTO officers (officers_name, officers_addrerss )  

VALUES('Olivia','Florida')  

ON CONFLICT ON CONSTRAINT officers_name  

DO NOTHING; 

The above command defines that if the officers_name present in the officers table, and do nothing or simply ignore it.

The below command is similar to the above command, but it uses the officers_name column in the place of the unique constraint officers_name because of the INSERT command’s target.

 INSERT INTO officers   

VALUES(102,'Olivia','[email protected]','Florida')  

ON CONFLICT (officers_name)  

DO NOTHING; 

Output

After implementing the above command, we will get the below message window, which displays the INSERT 0 0, which means that the DO-NOTHING action will return INSERT 0 0 for a conflict. Therefore, the insert command succeeded, and no rows were inserted.

Let us assume that we want to combine the new officers_address with the old officers_address when inserting an officer which is already present.

In the below command, we use the UPDATE clause as the action of the INSERT command:

INSERT INTO officers   

VALUES(102,'Olivia','[email protected]','Florida')  

ON CONFLICT (officers_name)  

DO UPDATE SET officers_address =   

EXCLUDED.officers_address || ',' || officers.officers_address;  

Output

After executing the above query, we will get the below message window; the values have been updated successfully into the officers table.

To check, if the above upsert feature is working fine or not, we will use the select command as we can see in the below command:

Select * from officers;  

Output

After executing the above command, we will get the below output: