PostgreSQL on Conflict Clause: Creating Actions on Conflict of Keys

With a relational database such as PostgreSQL, you can use the upsert feature to insert the values into a row. When the values you want to insert already exist in the table row, PostgreSQL updates the values. If no such values exist, it creates a new row. When an “insert” command finds a conflicting data in the target row, it raises a conflict and causes an error. However, using the PostgreSQL conflict cause, you can define what actions to occur when there is a conflicting data. More on this is covered in this post.

Working with PostgreSQL INSERT ON CONFLICT

A PostgreSQL “insert” command creates a new entry or updates an existing record. For that, you must use the ON CONFLICT clause which lets you define what action should be taken when the data conflicts.

Here’s the basic syntax to use:

The “target_column” is the column that you want to retain a unique identity and when you expect to have a case of conflicting data. The action is what to be done when a conflict arises. Two actions can be taken:

1. DO NOTHING – When you specify this action, PostgreSQL takes no action and the existing record won’t be updated since there is a conflicting case. Moreover, no error occurs as you suppress the action.

2. DO UPDATE – This option updates the record after a conflict occurs.

The following is the table that we will use for all our examples. We have the “client_id” as our unique column:

Suppose we want to insert the values into the previous table. If there is a conflicting case, we will get an error. For this example, we try to insert the values into the “client_id=1” which already exists in the table. PostgreSQL will detect that an entry with the “client_id” target already exists. Since it can’t accept duplicates, it throws an error.

To handle such instances without throwing an error, we must use the ON CONFLICT clause. The following are the two options that you can use:

Option 1: DO NOTHING

When a conflict arises, you can use the DO NOTHING option, leaving the existing record as it is and no error will arise. Using the earlier example where we tried to insert the values to an existing column, if we add the ON CONFLICT(target_column) DO NOTHING option, the insert command will go through successfully, but no changes will occur.

Here’s how you can implement it:

Let’s list the records on our table. Notice how the record with “client_id = 1” doesn’t change since it already exists, and the ON CONFLICT option that we selected leaves it as it is.

Option 2: ON CONFLICT UPDATE

Sometimes, you may choose to update the existing record even when a conflict occurs. You must define what new values you wish to set for the existing record, and your insert command will work without raising errors.

Note that we are using the EXCLUDED table which is a virtual table that holds the values that you wish to insert and using them with the SET command to replace the existing values. The following is how the previous “insert” command is implemented to update the existing record when a conflict occurs:

We can list the record to verify that our “insert” command updated the existing record with the new values and that no duplicates have occurred.

Sometimes, you may create a conditional update using the ON CONFLICT clause for the update action. For this case, the query only inserts the new values after running a conditional check against the existing record, causing a conflict.

For instance, using the following table record as our target, let’s create a conditional update:

Let’s say we only want to update the existing record if the “order_id” that we specify with the “insert” command is greater than the existing value for the “order_id”. In that case, we write our INSERT ON CONFLICT command as follows:

The previously specified condition doesn’t satisfy. Hence, no update occurs to our target record. We verified this as follows:

However, the update occurs when we change the insert values to ensure that we satisfy the condition. Moreover, we managed to set a new value for our “order_id” which is what we specified within the query.

Conclusion

PostgreSQL ON CONFLICT lets you define what action to take when conflicting data is detected with the INSERT statement. You can opt to DO NOTHING or UPDATE the existing record. We defined how the two actions can be implemented and provided examples for each.