> This page location: Modifying Data > MERGE
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL MERGE Statement

**Summary**: In this tutorial, you will learn how to use the PostgreSQL `MERGE` statement to conditionally insert, update, and delete rows of a table.

## Introduction to the PostgreSQL MERGE statement

Have you ever needed to update a table but weren't sure whether to insert new records or update existing ones? PostgreSQL's `MERGE` command solves this common problem. Think of `MERGE` as a smart helper that can look at your data and decide whether to add new records, update existing ones, or even delete records, all in a single command.

## Basic Concepts

Before we dive into `MERGE`, let's understand some basic terms:

- **Target Table**: The table you want to modify
- **Source Table**: The table containing your new or updated data
- **Match Condition**: The rule that determines if records match between your tables

## Basic MERGE Syntax

Here's the basic structure of a `MERGE` command:

```sql
MERGE INTO target_table
USING source_table
ON match_condition
WHEN MATCHED AND condition THEN
    UPDATE SET column1 = value1, column2 = value2
WHEN MATCHED AND NOT condition THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (column1, column2) VALUES (value1, value2)
RETURNING merge_action(), target_table.*;
```

This `MERGE` statement performs three conditional actions on `target_table` based on rows from `source_table`:

1. **Update rows**: If a match is found (`ON match_condition`) and `condition` is true, it updates `column1` and `column2` in `target_table`.
2. **Delete rows**: If a match is found but `condition` is false, it deletes the matching rows in `target_table`.
3. **Insert rows**: If no match is found, it inserts new rows into `target_table` using values from `source_table`.
4. The `RETURNING` clause provides details of the operation (`merge_action()`) and the affected rows.

## Key Features in PostgreSQL 17

The new RETURNING clause support in PostgreSQL 17 offers several advantages:

1. **Action Tracking**: The `merge_action()` function tells you exactly what happened to each row
2. **Complete Row Access**: You can return both old and new values for affected rows
3. **Immediate Feedback**: No need for separate queries to verify the results

## Setting Up Our Example

Let's create a sample database tracking a company's products and their inventory status:

```sql
-- Create the main products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT UNIQUE,
    price DECIMAL(10,2),
    stock INTEGER,
    status TEXT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert some initial data
INSERT INTO products (name, price, stock, status) VALUES
    ('Laptop', 999.99, 50, 'active'),
    ('Keyboard', 79.99, 100, 'active'),
    ('Mouse', 29.99, 200, 'active');

-- Create a table for our updates
CREATE TABLE product_updates (
    name TEXT,
    price DECIMAL(10,2),
    stock INTEGER,
    status TEXT
);

-- Insert mixed update data (new products, updates, and discontinuations)
INSERT INTO product_updates VALUES
    ('Laptop', 1099.99, 75, 'active'),      -- Update: price and stock change
    ('Monitor', 299.99, 30, 'active'),      -- Insert: new product
    ('Keyboard', NULL, 0, 'discontinued'),  -- Delete: mark as discontinued
    ('Headphones', 89.99, 50, 'active');    -- Insert: another new product
```

## Using MERGE with RETURNING

Now let's see how PostgreSQL 17's enhanced `MERGE` command can handle all three operations (`INSERT`, `UPDATE`, `DELETE`) while providing detailed feedback through the `RETURNING` clause:

```sql
MERGE INTO products p
USING product_updates u
ON p.name = u.name
WHEN MATCHED AND u.status = 'discontinued' THEN
    DELETE
WHEN MATCHED AND u.status = 'active' THEN
    UPDATE SET
        price = COALESCE(u.price, p.price),
        stock = u.stock,
        status = u.status,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED AND u.status = 'active' THEN
    INSERT (name, price, stock, status)
    VALUES (u.name, u.price, u.stock, u.status)
RETURNING
    merge_action() as action,
    p.product_id,
    p.name,
    p.price,
    p.stock,
    p.status,
    p.last_updated;
```

## Understanding the Output

The `RETURNING` clause will provide detailed information about each operation:

```
 action  | product_id |    name    |  price   | stock |   status    |      last_updated
---------+------------+------------+----------+-------+-------------+------------------------
 UPDATE  |     1      | Laptop     | 1099.99  |   75  | active      | 2024-12-04 17:41:58.226807
 INSERT  |     4      | Monitor    |  299.99  |   30  | active      | 2024-12-04 17:41:58.226807
 DELETE  |     2      | Keyboard   |   79.99  |  100  | active      | 2024-12-04 17:41:47.816064
 INSERT  |     5      | Headphones |   89.99  |   50  | active      | 2024-12-04 17:41:58.226807
```

Let's break down what happened:

1. **`UPDATE`**: The Laptop's price and stock were updated
2. **`DELETE`**: The Keyboard is deleted from the products table
3. **`INSERT`**: New Monitor and Headphones products were added

We can confirm the changes by querying the products table:

```sql
SELECT * FROM products
ORDER BY product_id;
```

```
 product_id |    name    |  price   | stock |   status    |      last_updated
------------+------------+----------+-------+-------------+------------------------
          1 | Laptop     | 1099.99  |   75  | active      | 2024-12-04 17:41:58.226807
          3 | Mouse      |   29.99  |  200  | active      | 2024-12-04 17:41:47.816064
          4 | Monitor    |  299.99  |   30  | active      | 2024-12-04 17:41:58.226807
          5 | Headphones |   89.99  |   50  | active      | 2024-12-04 17:41:58.226807
```

## Advanced Usage with Conditions

You can add more complex conditions to your `MERGE` statement:

```sql
MERGE INTO products p
USING (
    SELECT
        name,
        price,
        stock,
        status,
        CASE
            WHEN price IS NULL AND status = 'discontinued' THEN 'DELETE'
            WHEN stock = 0 THEN 'OUT_OF_STOCK'
            ELSE status
        END as action_type
    FROM product_updates
) u
ON p.name = u.name
WHEN MATCHED AND u.action_type = 'DELETE' THEN
    DELETE
WHEN MATCHED AND u.action_type = 'OUT_OF_STOCK' THEN
    UPDATE SET
        status = 'inactive',
        stock = 0,
        last_updated = CURRENT_TIMESTAMP
WHEN MATCHED THEN
    UPDATE SET
        price = COALESCE(u.price, p.price),
        stock = u.stock,
        status = u.status,
        last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED AND u.action_type != 'DELETE' THEN
    INSERT (name, price, stock, status)
    VALUES (u.name, u.price, u.stock, u.status)
RETURNING
    merge_action() as action,
    p.*,
    u.action_type;
```

## Best Practices

1. **Handle Source Data Carefully**:
   - Validate input data before the `MERGE`
   - Use subqueries to transform or clean data
   - Consider using CTEs for complex data preparation

2. **Leverage RETURNING for Validation**:
   - Include the `merge_action()` for operation tracking
   - Consider returning both old and new values for logging purposes and validation

## Common Pitfalls to Avoid

1. **Ambiguous Matches**: Ensure your `ON` clause creates unique matches
2. **NULL Handling**: Use `COALESCE` or `IS NOT DISTINCT FROM` for `NULL` values
3. **Missing Conditions**: Always handle all possible cases in your `WHEN` clauses

## Conclusion

PostgreSQL 17's enhanced `MERGE` command with `RETURNING` clause support provides a powerful tool for data synchronization and maintenance. The ability to perform multiple operations in a single statement while getting immediate feedback makes it an invaluable feature for modern applications.

## Frequently Asked Questions (FAQ)

What is the purpose of the `MERGE` statement in PostgreSQL?
: The `MERGE` statement allows you to conditionally `INSERT`, `UPDATE`, or `DELETE` rows in a target table based on the presence of matching records in a source table. This consolidates multiple operations into a single, efficient command.

When was the `MERGE` statement introduced in PostgreSQL?
: The `MERGE` statement was officially introduced in PostgreSQL version 15, released in October 2022.

How does the `MERGE` statement determine which operation to perform?
: The `MERGE` statement uses a specified `ON` condition to match rows between the source and target tables. Based on whether a match is found (`MATCHED`) or not (`NOT MATCHED`), and any additional conditions, it executes the corresponding `INSERT`, `UPDATE`, `DELETE`, or `DO NOTHING` actions.

Can I use the `MERGE` statement with views in PostgreSQL?
: Yes, starting from PostgreSQL 17, the `MERGE` command can be used with updatable views. For `MERGE` to work with views, the views must be consistent:
: Trigger-updatable views need `INSTEAD OF` triggers for all actions.
: Auto-updatable views cannot have any triggers.
: Mixing types of views or using rule-updatable views is not allowed.

What privileges are required to execute a `MERGE` statement?
: To execute a `MERGE` statement, you need:
: `SELECT` privilege on the source table or query.
: Appropriate privileges on the target table:
: `INSERT` privilege for insert actions.
: `UPDATE` privilege for update actions.
: `DELETE` privilege for delete actions.

Is the `MERGE` statement atomic in PostgreSQL?
: Yes, the `MERGE` statement in PostgreSQL is atomic. This means all specified actions (`INSERT`, `UPDATE`, `DELETE`) are performed as a single unit. If an error occurs during execution, the entire operation is rolled back, ensuring data integrity.

Can I use the `RETURNING` clause with the `MERGE` statement?
: Yes, starting from PostgreSQL 17, the `MERGE` statement supports the `RETURNING` clause. This allows you to retrieve information about the rows affected by the `MERGE` operation, including the specific action performed (`INSERT`, `UPDATE`, or `DELETE`) on each row.

How does the `MERGE` statement handle concurrent data modifications?
: The `MERGE` statement ensures data consistency during concurrent operations by acquiring the necessary locks on the target table. This prevents other transactions from modifying the same rows simultaneously, thereby avoiding conflicts.

Are there any performance considerations when using the `MERGE` statement?
: While the `MERGE` statement simplifies complex operations into a single command, it's essential to ensure that the `ON` condition is well-optimized, typically by indexing the columns involved. Proper indexing can significantly enhance performance.

Can I perform different actions based on additional conditions within the `MERGE` statement?
: Yes, the `MERGE` statement allows for multiple `WHEN` clauses with additional conditions. This enables you to specify different actions (`INSERT`, `UPDATE`, `DELETE`, or `DO NOTHING`) based on various criteria, providing fine-grained control over the operation.

---

## Related docs (Modifying Data)

- [INSERT](https://neon.com/postgresql/postgresql-tutorial/postgresql-insert)
- [INSERT Multiple Rows](https://neon.com/postgresql/postgresql-tutorial/postgresql-insert-multiple-rows)
- [UPDATE](https://neon.com/postgresql/postgresql-tutorial/postgresql-update)
- [UPDATE Join](https://neon.com/postgresql/postgresql-tutorial/postgresql-update-join)
- [DELETE](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete)
- [DELETE Join](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete-join)
- [DELETE CASCADE](https://neon.com/postgresql/postgresql-tutorial/postgresql-delete-cascade)
- [Delete Duplicate Rows](https://neon.com/postgresql/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql)
- [Upsert](https://neon.com/postgresql/postgresql-tutorial/postgresql-upsert)