How Does PostgreSQL Implement Upsert to Automatically Separate New Data from Old Data


Many businesses continuously insert new data and require simultaneous updates. To achieve this requirement, the pre-update data must be recorded in the historical table. This requirement is similar to an audit requirement, wherein, records must be audited before and after changes.

Although, this article is not about auditing and using triggers. Instead, it focuses on what methods should be used to automatically separate new data from old data. It suggests using the with syntax to complete insert and update in one SQL statement.


PostgreSQL allows implementing insert and update in one SQL statement.

Create a current status table and a historical table using the commands below.

Insert a record that does not exist, which will not trigger insertion into the historical table.

Remember to replace the variables.

Insert a record that does not exist, which will not trigger insertion into the historical table.

Now, insert an existing record with data changes, which triggers insertion into the historical table.

Inserting an existing record with identical data will not trigger insertion into the historical table.

Plan implementation as shown below.

In PostgreSQL, versions earlier than 9.5 do not support insert on conflict, therefore, adjust the SQL as follows.

If the version is earlier than 9.5, the code for the scenario in this article must be as follows:

Rule Syntax

PostgreSQL supports Rule syntax. Create a rule with Rule syntax. If the rule exists, update it. Otherwise, insert it.

However, while using the volatile function, do not directly use exists. Otherwise, it will be processed as an immutable function. For more information, see the following precautions.

If the rule exists, do not insert it (ignore it). Otherwise, update it. Implement idempotent writing to avoid any problem when writing during resumable uploads.

Implement performance stress testing as shown below.

Precautions for Using the Rule Syntax

1) Using exists in the Rule syntax is only suitable for a single insert statement (use the volatile function to solve this problem). Otherwise, constraints are required to ensure uniqueness.

2) Rule syntax does not support copy statements. Hence, repeated copy statements also cause problems.

In any case, we always recommend that tables requiring an upsert must have a primary key.

Original Source:

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