UPSERT in PostgreSQL

Understanding UPSERT

In PostgreSQL, an UPSERT operation is a combination of INSERT and UPDATE statements. It allows you to efficiently insert new data into a table while updating existing rows based on specific conditions. This is particularly useful when dealing with data that may already exist in the table.

Methods for UPSERT

PostgreSQL offers two primary methods to achieve UPSERT functionality:

1. Using the ON CONFLICT DO UPDATE Clause:

   • This is the preferred method for PostgreSQL 9.5 and later.
   • It involves using the INSERT statement with the ON CONFLICT DO UPDATE clause.
   • You specify the unique constraint or index that defines the conflict condition.
   • Within the DO UPDATE clause, you provide the UPDATE statement to modify existing rows.

   Example:

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

   • your_table: The name of the table.
   • column1, column2: The columns to insert or update.
   • value1, value2: The values to insert.
   • EXCLUDED: A special alias referring to the values that would have been inserted if there were no conflict.

2. Using a Conditional INSERT and UPDATE:

   • This method is suitable for older PostgreSQL versions or more complex scenarios.
   • It involves using a conditional INSERT statement followed by a conditional UPDATE statement.
   • You check for the existence of the row before performing the appropriate action.

   BEGIN;
   INSERT INTO your_table (column1, column2)
   VALUES (value1, value2)
   WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE unique_constraint = value1);
   
   UPDATE your_table
   SET column2 = value2
   WHERE unique_constraint = value1;
   COMMIT;
   

   • The BEGIN and COMMIT statements ensure that both the INSERT and UPDATE are executed as a single transaction.

Key Considerations

• Performance: Consider performance implications when choosing a method, especially for large datasets or frequent UPSERT operations.
• Transaction Management: If using the conditional INSERT and UPDATE approach, ensure proper transaction management to maintain data consistency.
• UPDATE Statement: The DO UPDATE clause provides flexibility in defining how to update existing rows.
• Unique Constraint or Index: The ON CONFLICT DO UPDATE clause requires a unique constraint or index to identify conflicting rows.

Understanding UPSERT in PostgreSQL with Example Codes

UPSERT is a combination of INSERT and UPDATE operations. It allows you to insert new data or update existing data based on a specific condition, typically a unique constraint or index.

Method 1: Using the ON CONFLICT DO UPDATE Clause (Recommended for PostgreSQL 9.5 and later)

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

Explanation:

• DO UPDATE: Specifies the action to take if a conflict occurs.

INSERT INTO customers (customer_id, name, email)
VALUES (101, 'Alice', '[email protected]')
ON CONFLICT (customer_id) DO UPDATE
  SET email = EXCLUDED.email;

Method 2: Using a Conditional INSERT and UPDATE (For older PostgreSQL versions or more complex scenarios)

BEGIN;
INSERT INTO your_table (column1, column2)
VALUES (value1, value2)
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE unique_constraint = value1);

UPDATE your_table
SET column2 = value2
WHERE unique_constraint = value1;
COMMIT;

• If the row exists, the UPDATE statement is executed to modify it.
• The WHERE NOT EXISTS clause checks if the row already exists before inserting.

BEGIN;
INSERT INTO products (product_id, name, price)
VALUES (1001, 'Laptop', 999.99)
WHERE NOT EXISTS (SELECT 1 FROM products WHERE product_id = 1001);

UPDATE products
SET price = 999.99
WHERE product_id = 1001;
COMMIT;

Alternative Methods for UPSERT in PostgreSQL

While the ON CONFLICT DO UPDATE clause is the preferred method for UPSERT operations in PostgreSQL 9.5 and later, there are alternative approaches that can be considered in certain scenarios:

Using a MERGE Statement (PostgreSQL 14+):

• It combines the INSERT and UPDATE operations into a single statement.
• This method is a more concise and readable alternative to the ON CONFLICT DO UPDATE clause.

MERGE INTO your_table AS t
USING (VALUES (value1, value2)) AS source (column1, column2)
ON t.column1 = source.column1
WHEN MATCHED THEN UPDATE SET t.column2 = source.column2
WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES (source.column1, source.column2);

BEGIN;
INSERT INTO your_table (column1, column2)
VALUES (value1, value2)
WHERE NOT EXISTS (SELECT 1 FROM your_table WHERE unique_constraint = value1);

UPDATE your_table
SET column2 = value2
WHERE unique_constraint = value1;
COMMIT;

Using a Stored Procedure:

• This can improve code readability and maintainability.
• For complex UPSERT logic or performance optimization, you can encapsulate the UPSERT operation in a stored procedure.

CREATE PROCEDURE upsert_data(IN column1_value INT, IN column2_value TEXT)
LANGUAGE sql
AS $$
BEGIN
  INSERT INTO your_table (column1, column2)
  VALUES (column1_value, column2_value)
  ON CONFLICT (column1) DO UPDATE
    SET column2 = column2_value;
END;
$$;

Choosing the Right Method:

• Performance: If performance is a critical factor, consider benchmarking different methods to determine the most efficient approach for your specific use case.
• Complexity: For simple UPSERT operations, the ON CONFLICT DO UPDATE clause is usually sufficient. For more complex scenarios, a stored procedure or conditional INSERT and UPDATE might be better suited.
• PostgreSQL Version: Consider the version of PostgreSQL you're using. The MERGE statement is available from PostgreSQL 14 onwards.