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

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:

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

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:

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.

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:

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.

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:

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:

Output

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