postgresql
  1. postgresql-upsert
PreviousNext

Upsert - (PostgreSQL Table)

In PostgreSQL, an upsert is a way to update a table if a row with a matching key value exists, or to insert a new row if a matching key value does not exist. In this tutorial, we'll show you how to use the upsert functionality in PostgreSQL.

Syntax

INSERT INTO table (column1, column2, ...) 
VALUES (value1, value2, ...)
ON CONFLICT (column_name) 
DO 
  UPDATE SET 
    column_name_1 = value_1, 
    column_name_2 = value_2, ...
  WHERE condition;
  • table: The name of the table to insert the row into.
  • column1, column2, ...: The names of the columns in the table that you want to insert data into.
  • value1, value2, ...: The values to be inserted into the specified columns.
  • column_name: The column(s) that should trigger an update on conflict.
  • column_name_1, column_name_2, ...: The columns to update in the case of a conflict.
  • value_1, value_2, ...: The new values for the columns to be updated.
  • condition: The condition that must be met for the update to occur.

Example

Let's take a look at an example of using the upsert functionality in PostgreSQL.

Consider an employee table with the following columns:

employee_id  employee_name  employee_salary  employee_department
---------------------------------------------------------------
1            John           50000           Sales
2            Jane           60000           Marketing

INSERT INTO employee (employee_id, employee_name, employee_salary, employee_department) 
VALUES (3, 'Joe', 45000, 'Sales') 
ON CONFLICT (employee_id) DO UPDATE 
  SET employee_salary = 45000 
  WHERE employee_department = 'Sales';

After the above query is executed, the employee table would look like this:

employee_id  employee_name  employee_salary  employee_department
---------------------------------------------------------------
1            John           50000           Sales
2            Jane           60000           Marketing
3            Joe            45000           Sales

In this example, we inserted a new row into the employee table with employee_id = 3 and employee_name = 'Joe'. Since a row with employee_id = 3 did not exist in the table, a new row was inserted with the specified values.

If a row with the specified employee_id already existed in the table, the ON CONFLICT clause is triggered. In this case, we use the DO UPDATE clause to update the salary of the existing employee to 45000, where the department is Sales.

Explanation

In this example, we insert a row into the employee table with values for employee_id, employee_name, employee_salary and employee_department. If a row with the same employee_id already exists, the ON CONFLICT clause is triggered.

In this case, we use the DO UPDATE clause to update the salary of the employee where the department is Sales. Note that we could have used any conditional statement in the WHERE clause to specify the condition for the update.

Use

Upserts are useful when you need to update a row if it exists and insert a new row if it does not. This feature is useful in situations where you need to guarantee the uniqueness of rows in a table.

Important Points

  • The ON CONFLICT clause is only available in PostgreSQL 9.5 and later.
  • The DO UPDATE clause can be used to specify the update action to perform.
  • The WHERE clause is optional and it is used to specify a condition for the update.

Summary

In this tutorial, we showed you how to use the upsert functionality in PostgreSQL. We discussed the syntax, example, explanation, use, and important points of using the upsert functionality. With this knowledge, you can now use the upsert functionality in PostgreSQL to update existing rows or insert new rows based on a specified criteria.

Identity ColumnSubquery
Published on: