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.

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.

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.

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.