Performing Inserts and Updates in One Go: PostgreSQL Upsert Explained

PostgreSQL doesn't have a built-in UPSERT (UPDATE or INSERT) statement, but it provides two powerful mechanisms to achieve this functionality:

1. INSERT ... ON CONFLICT: This is the most common and versatile approach. It allows you to specify what action to take when a conflict occurs during an insert operation (i.e., trying to insert a row that already exists based on a unique constraint or index).

   • Syntax:

     INSERT INTO table_name (column1, column2, ...)
     VALUES (value1, value2, ...)
     ON CONFLICT ON constraint_name OR index_name
     DO UPDATE SET column1 = value1, column2 = value2, ...
     [WHERE condition];
     

   • Explanation:

     • INSERT INTO table_name: Starts the insert operation.
     • (column1, column2, ...): Specifies the columns to insert values into.
     • VALUES (value1, value2, ...): Provides the values to insert.
     • ON CONFLICT ON constraint_name OR index_name: Identifies the unique constraint or index that defines the conflict scenario.
     • DO UPDATE SET column1 = value1, column2 = value2, ...: Defines the update operation to be performed on conflicting rows. You can specify which columns to update and their new values.
     • [WHERE condition] (optional): Adds an optional WHERE clause to further refine the update behavior within the conflicting rows.

2. MERGE Statement (PostgreSQL 15 and later): This is a newer addition that offers a more concise syntax for upserts:

   • MERGE INTO table_name USING (
       SELECT column1, column2, ... FROM source_table
     ) AS target
     ON target.unique_column = table_name.unique_column
     WHEN MATCHED THEN UPDATE SET column1 = target.column1, column2 = target.column2, ...
     WHEN NOT MATCHED THEN INSERT (column1, column2, ...) VALUES (target.column1, target.column2, ...);
     

   • • USING (SELECT ... FROM source_table) AS target: Defines a virtual table (target) containing the data to be inserted or updated.
     • ON target.unique_column = table_name.unique_column: Specifies the join condition for matching rows.
     • WHEN MATCHED THEN UPDATE ...: Defines the update operation for matching rows.

Choosing the Right Approach:

• Use INSERT ... ON CONFLICT for more granular control over conflict resolution and update behavior.
• If you're using PostgreSQL 15 or later and prefer a more streamlined syntax, MERGE can be a good option.

Example (Using INSERT ... ON CONFLICT):

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE
);

INSERT INTO users (username) VALUES ('john')
ON CONFLICT (username) DO UPDATE SET username = 'john_updated';

In this example:

• If username 'john' doesn't exist, it will be inserted.
• If username 'john' already exists, it will be updated to 'john_updated'.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) UNIQUE,
  price DECIMAL(10,2) NOT NULL
);

-- Insert a new product (assuming 'name' 'Widget' doesn't exist)
INSERT INTO products (name, price) VALUES ('Widget', 19.99)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;  -- Update price using EXCLUDED

-- Insert another product, updating price if 'name' 'Gadget' already exists
INSERT INTO products (name, price) VALUES ('Gadget', 24.50)
ON CONFLICT (name) DO UPDATE SET price = EXCLUDED.price;

This example demonstrates:

• Inserting new products with unique names (name).
• Updating the price of an existing product (Gadget) if the name conflicts.
• Using EXCLUDED to access the values being attempted for insertion during conflict.

Example 2: Upsert with Additional Check (Update Only if Existing Price is Lower)

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  salary DECIMAL(10,2) NOT NULL
);

-- Insert or update employee salary, but only if new salary is higher
INSERT INTO employees (name, salary) VALUES ('Alice', 80000.00)
ON CONFLICT (name) DO UPDATE SET salary = EXCLUDED.salary
WHERE employees.salary < EXCLUDED.salary;  -- Update only if existing < new

This example shows:

• Upserting an employee's salary (Alice).
• Updating the salary only if the new salary (80000.00) is higher than the existing one using an additional WHERE clause.

Using MERGE Statement (PostgreSQL 15 and later):

Example:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(100) UNIQUE,
  name VARCHAR(50) NOT NULL
);

-- Create a temporary table to hold data for upsert
CREATE TEMP TABLE customer_data (
  email VARCHAR(100) PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

-- Insert data into temporary table
INSERT INTO customer_data (email, name) VALUES ('[email protected]', 'John Doe');

MERGE INTO customers USING customer_data AS target
ON target.email = customers.email
WHEN MATCHED THEN UPDATE SET name = target.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (target.email, target.name);

DROP TABLE customer_data;  -- Cleanup temporary table

• Using a temporary table (customer_data) to stage data for upsert.
• Performing a merge operation using MERGE.
• Updating existing customer names (John Doe) if email matches.
• Inserting new customers if email doesn't exist.
• Dropping the temporary table after use.

This method involves attempting an INSERT and then checking if it succeeded (no unique constraint violation). If it fails due to a conflict, you can perform an UPDATE in a loop until successful. However, this is less efficient and can lead to race conditions in high-concurrency scenarios.

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) UNIQUE
);

LOOP
  INSERT INTO accounts (username) VALUES ('new_user') RETURNING id;  -- Check for success
  EXIT WHEN FOUND;  -- Exit if insert succeeded
END LOOP;

IF NOT FOUND THEN  -- Update if insert failed (assuming unique username conflict)
  UPDATE accounts SET username = 'new_user_updated'
  WHERE username = 'new_user';
END IF;

User-Defined Functions (UDFs):

You can create a PL/pgSQL function that encapsulates the upsert logic using INSERT ... ON CONFLICT or a similar approach internally. This can improve code organization, but the underlying functionality remains the same as INSERT ... ON CONFLICT.

ORM or Object-Relational Mapper Tools:

Many ORMs (Object-Relational Mappers) like SQLAlchemy or Django's ORM provide built-in methods for upserts. These methods typically translate to INSERT ... ON CONFLICT under the hood, offering a more convenient API for your application code.

Choosing the Best Method:

• For most cases, INSERT ... ON CONFLICT is the preferred approach due to its efficiency and built-in conflict resolution handling.
• Use MERGE (if your PostgreSQL version supports it) for a more concise syntax.
• Consider separate INSERT and UPDATE statements only if you have very specific requirements or cannot use the recommended methods.
• If you're using an ORM, leverage its upsert functionality for a higher-level abstraction.