SoFunction
Updated on 2025-03-03

Detailed explanation of PostgreSQL upsert (insert update) data operation

This article describes how to use the PostgreSQL upsert feature to insert or update data when the inserted data already exists.

1. Introduction to PostgreSQL upsert

In a relational database, upsert is a combination word, that is, when inserting a record into the table, it will be updated if the record already exists, otherwise a new record will be inserted. In order to use this feature, you need to use itINSERT ON CONFLICTStatement:

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

In this syntaxtargetIt can be one of the following:

  • (column_name) – column name
  • ON CONSTRAINT constraint_name – The name of the unique constraint
  • WHERE predicate – where clause with predicate.

actionProbably one of the following:

DO NOTHING – If the row already exists in the table, no action is performed.
DO UPDATE SET column_1 = value_1, … WHERE condition – Update some fields in the table.

Notice:ON CONFLICTThe clause is only valid from PostgreSQL version 9.5. If you need to use it in the previous version, you need to use other methods to implement it.

2. PostgreSQL upsert example

The following statement creates a customerrs table to demonstrate the PostgreSQL upsert feature:

DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
	customer_id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	email VARCHAR NOT NULL,
	active bool NOT NULL DEFAULT TRUE
);

The customers table includes four fields: customer_id, name, email, active. The name field has unique constraints to ensure that the name is unique.

The following insert statement adds several new records:

INSERT INTO 
 customers (name, email)
VALUES 
 ('IBM', 'contact@'),
 ('Microsoft', 'contact@'),
 ('Intel', 'contact@');

Suppose Microsoft now modifys the email field from contact@ to hotline@. We can use update update statement because we need to demonstrate the upsert feature, we use it hereINSERT ON CONFLICTStatement:

INSERT INTO customers (NAME, email)
VALUES('Microsoft','hotline@') 
ON CONFLICT ON CONSTRAINT customers_name_key 
DO NOTHING;

The above statement means that if there is a name table, nothing will be done. The following statement is equivalent to the above, but uses the name column instead of the unique constraint name:

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@') 
ON CONFLICT (name) 
DO NOTHING;

Assume that when the record already exists, you need to connect the new email to the original email address, then the update action is:

INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@') 
ON CONFLICT (name) 
DO 
 UPDATE SET email =  || ';' || ;

Used hereEXCLUDEDA virtual table that contains the records we want to update, that is, the new records (relative to the original records customers). The field on the right side of the equation needs to be distinguished by the table name so that the field is not reported with unclear errors.
Readers can verify on their own whether the results are as you wish.

3. Summary

This article is introduced byINSERT ON CONFLICTImplement PostgreSQL insert update feature.

This is the end of this article about the detailed explanation of PostgreSQL upsert (insert update) tutorial. For more related PostgreSQL upsert content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!