How to Insert or Update if Exists in PostgreSQL

1. Use Subquery to Insert or Update a Row if It Exists
2. Use INSERT ... ON CONFLICT (UPSERT) to Insert or Update a Row if It Exists
3. Use Common Table Expression (CTE) to Insert or Update a Row if It Exists
4. Use PL/pgSQL Function to Insert or Update a Row if It Exists
5. Conclusion

In database management, efficiently handling conditional data insertion or updating is a crucial task. This article explores multiple methods to achieve this in PostgreSQL.

One of the methods we will discuss is using subqueries, INSERT ... ON CONFLICT (UPSERT), Common Table Expression (CTE), and PL/pgSQL functions. Each approach offers a distinct way to enhance data management within a PostgreSQL environment.

Use Subquery to Insert or Update a Row if It Exists

The subquery approach involves using a subquery within an SQL statement to conditionally perform an INSERT or UPDATE operation based on the existence of a record with a specific identifier (in this case, the id column).

The subquery checks for the existence of a record with the given identifier, and if it doesn’t exist, an INSERT statement is executed; otherwise, an UPDATE statement is executed.

Let’s assume we have a table like the following, where the id is the primary key.

postgres=# SELECT * FROM users;
 id |        name        |   gender   | age
----+--------------------+------------+-----
  1 | Tymothy Blezard    | Polygender |  21
  2 | Carlynne Renak     | Female     |  30
  3 | Hewitt Mylechreest | Male       |  26
  4 | Griswold Geer      | Male       |  30
  5 | Kelley Vines       | Female     |  22
  6 | Ted Haizelden      | Male       |  27
  7 | Bonita Glewe       | Female     |  27
  8 | Edgar Hambers      | Male       |  22
  9 | Johnathan Dwelley  | Male       |  22
 10 | Fritz Rylance      | Male       |  22
(10 rows)

Let’s add a new row like the following:

postgres=# UPDATE users SET name='Tymothy', gender='Male', age=21 WHERE id=1;
UPDATE 1
postgres=# SELECT * FROM users ORDER BY id;
 id |        name        | gender | age
----+--------------------+--------+-----
  1 | Tymothy            | Male   |  21
  2 | Carlynne Renak     | Female |  30
  3 | Hewitt Mylechreest | Male   |  26
  .
  .

Now, we can see that it successfully updated the row with an id equal to 1. However, UPDATE 0 will occur if we want to update a row with the id equal to 11 or another id that is not present in the table.

postgres=# UPDATE users SET name='Tymothy', gender='Male', age=21 WHERE id=11;
UPDATE 0

Let’s create a subquery that will insert the row if it is not present in the table.

For example, suppose we want to update the information for a user with id=12, and if the user doesn’t exist, we want to insert a new row.

UPDATE users SET name='John Kane', gender='Male', age=21 WHERE id=12;

So, our solution will be:

UPDATE users SET name='John Kane', gender='Male', age=21 WHERE id=12;

INSERT INTO users (id, name, gender, age)
SELECT 12, 'John Kane', 'Male', 21
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id=12);

In the provided example, the UPDATE statement is first attempted. If a row with id=12 exists, it updates the values.

If the UPDATE doesn’t affect any rows (meaning the user doesn’t exist), the subsequent INSERT statement is executed to add a new row.

Here’s the output:

postgres=# SELECT * FROM users ORDER BY id DESC;
 id |        name        | gender | age
----+--------------------+--------+-----
 12 | John Kane          | Male   |  21
 10 | Fritz Rylance      | Male   |  22

We have successfully inserted a new row into the table.

Use INSERT ... ON CONFLICT (UPSERT) to Insert or Update a Row if It Exists

UPSERT, a combination of “update” and “insert,” is a feature in PostgreSQL that allows us to perform an INSERT operation, and if a conflict (usually on a unique constraint) occurs, it updates the conflicting row instead.

This is achieved using the ON CONFLICT clause, specifying the unique field(s) and defining the action to be taken in case of a conflict (e.g., performing an update).

Here’s the general way to use UPSERT.

INSERT INTO customers (name, email)
VALUES ('field1_value', 'field2_value')
ON CONFLICT (unique_field)
DO UPDATE SET field2 = 'new_field_value';

Suppose we want to update the age for a user with id 12, and if the user doesn’t exist, we want to insert a new row.

INSERT INTO users (id, name, gender, age)
VALUES (12, 'John Kane', 'Male', 23)
ON CONFLICT (id)
DO UPDATE SET age = 24;

In this example, the INSERT statement is attempted. If a conflict on the id occurs (indicating the user already exists), the age is updated using the DO UPDATE SET clause.

Output:

postgres=# SELECT * FROM users ORDER BY id DESC;

 id |        name        |   gender   | age
----+--------------------+------------+-----
 12 | John Kane          | Male       |  24
 10 | Fritz Rylance      | Male       |  22
  9 | Johnathan Dwelley  | Male       |  22

Here, we have successfully inserted a new row into the table.

By using UPSERT, we can update existing records or insert new ones based on whether a conflict occurs on a specified unique field, providing a more concise and efficient approach for conditional insert or update operations.

To learn more about the UPSERT operation, visit the official documentation.

Use Common Table Expression (CTE) to Insert or Update a Row if It Exists

Another method to achieve conditional insert or update in PostgreSQL is by using a Common Table Expression (CTE). A CTE allows us to create temporary result sets that can be referenced within an SQL statement.

Let’s demonstrate how to use a CTE to achieve conditional insert or update based on the id.

WITH Upsert AS (
    UPDATE Users
    SET name = 'John Kane', gender = 'Male', age = 25
    WHERE id = 12
    RETURNING *
)
INSERT INTO Users (id, name, gender, age)
SELECT 12, 'John Kane', 'Male', 25
WHERE NOT EXISTS (SELECT 1 FROM Upsert);

In the above example, we start with a Common Table Expression (CTE) named Upsert that attempts to update the user with id = 12. If the user exists, it returns the updated row.

We then use an INSERT statement to insert a new row with id = 12, name = 'John Kane', gender = 'Male', and age = 25 if the CTE didn’t return any rows (indicating no update occurred, hence the user doesn’t exist).

Output:

postgres=# SELECT * FROM users ORDER BY id DESC;

 id |        name        |   gender   | age
----+--------------------+------------+-----
 12 | John Kane          | Male       |  25
 10 | Fritz Rylance      | Male       |  22
  9 | Johnathan Dwelley  | Male       |  22

As you can see in the output, we have successfully inserted a new row into the table.

By using a CTE, we can achieve conditional insert or update operations based on specified conditions in PostgreSQL, providing a versatile and powerful approach for managing data.

Use PL/pgSQL Function to Insert or Update a Row if It Exists

We can also create a PL/pgSQL function to encapsulate the logic for conditional insert or update. PL/pgSQL is a procedural language for PostgreSQL that allows you to write functions and stored procedures.

Here’s an example of a PL/pgSQL function to achieve conditional insert or update based on the id:

CREATE OR REPLACE FUNCTION UpsertUser(id INT, new_name TEXT, new_gender TEXT, new_age INT)
RETURNS VOID AS $$
BEGIN
    UPDATE Users
    SET name = new_name, gender = new_gender, age = new_age
    WHERE id = id;

    IF NOT FOUND THEN
        INSERT INTO Users (id, name, gender, age)
        VALUES (id, new_name, new_gender, new_age);
    END IF;
END;
$$ LANGUAGE plpgsql;

In the example code, we create a PL/pgSQL function named UpsertUser that takes the id, new_name, new_gender, and new_age as parameters.

The function first attempts to update the user with the provided id. If no rows are updated (indicating the user doesn’t exist), it performs an insert operation.

Usage:

SELECT UpsertUser(13, 'New User', 'Female', 30);

Output:

postgres=# SELECT * FROM users ORDER BY id DESC;

 id |        name        |   gender   | age
----+--------------------+------------+-----
 13 | New User           | Female     |  30
 12 | John Kane          | Male       |  25
 10 | Fritz Rylance      | Male       |  22
  9 | Johnathan Dwelley  | Male       |  22

As you can see, we have successfully inserted a new row into the table.

By using a PL/pgSQL function, we can effectively manage and execute complex conditional insert or update operations in PostgreSQL, enhancing the efficiency and maintainability of our database logic.

Conclusion

In this article, we have explored several approaches to conditionally insert or update records based on certain criteria, each offering its unique advantages.

The subquery method allows us to check for the existence of a record and then choose between an INSERT or UPDATE operation accordingly. On the other hand, the INSERT ... ON CONFLICT statement streamlines the process, simplifying conditional insert or update operations.

The use of Common Table Expressions (CTE) provides a versatile and powerful approach, while PL/pgSQL functions encapsulate the logic for even more complex scenarios.