Use of the PostgreSQL Upsert (INSERT ON CONFLICT DO) Function

Image for post
Image for post

Background

Upsert (INSERT ON CONFLICT DO) is a new function of PostgreSQL 9.5. When a constraint error occurs during data insertion, data insertion is rolled back or changed to update. The syntax for the same is as follows:

For versions earlier than PostgreSQL 9.5, use functions or the with syntax to implement functions similar to upsert.

Upsert Usage Example for Versions Later Than 9.5

Execute the following command to create a test table with one field as the unique key or primary key.

create table test(id int primary key, info text, crt_time timestamp);

Step 1) If a table does not exist, insert it. Otherwise, update it.

Step 2) If the data does not exist, insert it. Otherwise, do not perform any operation.

Upsert Usage Example for Versions Earlier Than 9.5

Use different methods to meet the actual requirements.

1) Functions

Use functions to upsert data. To write data in batches, create variables as shown below.

2) With Syntax — Method 1

create table test(id int primary key, info text, crt_time timestamp);

If the exists, update it. Otherwise, insert it.

with upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *) insert into test select $id,$info,$crt_time where not exists (select 1 from upsert where id=$id);

Replace the variables and perform a test.

with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 from upsert where id=1);

When a nonexistent value is inserted, only one session has the successfully inserted value, whereas the other session returns a primary key constraint error.

3) With Syntax — Method 2

Ensure concurrency even if the table does not have a primary key or a unique constraint.

create table test(id int, info text, crt_time timestamp);

3.1) Data Inserted to Only One Session.

When the same data item is updated, the first established session locks the record, whereas the session that is established later enters the waiting state.

Next, replace the variables and perform a test.

3.2) Data Inserted to Only One Session.

When the same data item is updated, the session that is established first updates the data, whereas the session that is established later directly returns an error.

Next, replace the variables and perform a test.

4) Rule Method

PostgreSQL has supported Rule syntax for a long time. Create a rule with Rule syntax. If the rule exists, update it. Otherwise, insert it. However, when using the volatile function, do not directly use exists. Otherwise, it will be processed as an immutable function.

If it exists, do not insert it (ignore it). Otherwise, update it.

Implement idempotent writing so no problems occur while writing during resumable uploads.

Execute the following code to create a volatile function.

Create a rule as shown below.

Implement performance stress testing as shown below.

Always take the following precautions for using the Rule syntax.

1) The use of exists in the Rule syntax is only suitable for a single insert statement (use the volatile function to solve this problem).

2) Rule syntax does not support copy statements. Repeated copy statements also cause problems.

In any case, it is recommended that tables requiring upsert have a primary key.

Original Source:

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store