PostgreSQL UPSERT Statement

PostgreSQL UPSERT Statement

The PostgreSQL UPSERT statement is used to insert a new row into a table or update an existing row if a conflict occurs. This statement is essential for ensuring data integrity and avoiding duplicate entries during insert operations.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column)
DO UPDATE SET column1 = EXCLUDED.column1,
              column2 = EXCLUDED.column2;

The INSERT statement with ON CONFLICT clause has the following components:

  • table_name: The name of the table to insert or update data.
  • column1, column2, ...: The columns in the table where the data will be inserted or updated.
  • value1, value2, ...: The values to be inserted into the specified columns.
  • conflict_column: The column on which the conflict is detected.
  • EXCLUDED: A special table representing the row proposed for insertion that conflicts with an existing row.

Example PostgreSQL UPSERT Statement Queries

Let's look at some examples of PostgreSQL UPSERT statement queries:

1. Basic UPSERT Example

INSERT INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john.doe@example.com')
ON CONFLICT (id)
DO UPDATE SET first_name = EXCLUDED.first_name,
              last_name = EXCLUDED.last_name,
              email = EXCLUDED.email;

This query inserts a new row into the employees table. If a conflict occurs on the id column, it updates the existing row with the values from the proposed insertion.

2. UPSERT with Additional Conditions

INSERT INTO employees (id, first_name, last_name, email, salary)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', 50000)
ON CONFLICT (id)
DO UPDATE SET first_name = EXCLUDED.first_name,
              last_name = EXCLUDED.last_name,
              email = EXCLUDED.email,
              salary = GREATEST(employees.salary, EXCLUDED.salary);

This query inserts a new row into the employees table. If a conflict occurs on the id column, it updates the existing row with the new values, ensuring that the salary column retains the highest value.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the UPSERT statement.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2)
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, and salary.

PostgreSQL UPSERT

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, first_name, last_name, email, salary)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', 50000);

Here, we insert data into the employees table, including values for the id, first_name, last_name, email, and salary columns.

PostgreSQL UPSERT

Step 3: Using the UPSERT Statement

This step involves inserting a new row or updating an existing row if a conflict occurs.

INSERT INTO employees (id, first_name, last_name, email, salary)
VALUES (1, 'John', 'Doe', 'john.doe@example.com', 55000)
ON CONFLICT (id)
DO UPDATE SET first_name = EXCLUDED.first_name,
              last_name = EXCLUDED.last_name,
              email = EXCLUDED.email,
              salary = GREATEST(employees.salary, EXCLUDED.salary);

Here, we attempt to insert a new row into the employees table. If a conflict occurs on the id column, it updates the existing row with the new values, ensuring that the salary column retains the highest value.

PostgreSQL UPSERT

Conclusion

The PostgreSQL UPSERT statement is a powerful tool for managing insertions and updates in a single operation, ensuring data integrity and preventing duplicate entries. Understanding how to use the UPSERT statement and its syntax is essential for effective database management and data manipulation in PostgreSQL.