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'sMERGE command solves this common problem. Think ofMERGE 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 intoMERGE, 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 aMERGE command:

MERGE INTO target_tableUSING source_tableON match_conditionWHEN MATCHED AND conditionTHEN    UPDATE SET column1= value1, column2= value2WHEN MATCHED AND NOT conditionTHEN    DELETEWHEN NOT MATCHED THEN    INSERT (column1, column2)VALUES (value1, value2)RETURNING merge_action(), target_table.*;

ThisMERGE statement performs three conditional actions ontarget_table based on rows fromsource_table:

1. Update rows: If a match is found (ON match_condition) andcondition is true, it updatescolumn1 andcolumn2 intarget_table.
2. Delete rows: If a match is found butcondition is false, it deletes the matching rows intarget_table.
3. Insert rows: If no match is found, it inserts new rows intotarget_table using values fromsource_table.
4. TheRETURNING 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: Themerge_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:

-- Create the main products tableCREATE TABLE products (    product_idSERIAL PRIMARY KEY,    name TEXT UNIQUE,    priceDECIMAL(10,2),    stockINTEGER,    status TEXT,    last_updatedTIMESTAMP DEFAULT CURRENT_TIMESTAMP);-- Insert some initial dataINSERT 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 updatesCREATE TABLE product_updates (    name TEXT,    priceDECIMAL(10,2),    stockINTEGER,    status TEXT);-- Insert mixed update data (new products, updates, and discontinuations)INSERT INTO product_updatesVALUES    ('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 enhancedMERGE command can handle all three operations (INSERT,UPDATE,DELETE) while providing detailed feedback through theRETURNING clause:

MERGE INTO products pUSING product_updates uON p.name= u.nameWHEN MATCHED AND u.status= 'discontinued' THEN    DELETEWHEN MATCHED AND u.status= 'active' THEN    UPDATE SET        price= COALESCE(u.price, p.price),        stock= u.stock,        status = u.status,        last_updated= CURRENT_TIMESTAMPWHEN 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

TheRETURNING 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:

SELECT * FROM productsORDER 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 yourMERGE statement:

MERGE INTO products pUSING (    SELECT        name,        price,        stock,        status,        CASE            WHEN priceIS NULL AND status = 'discontinued' THEN 'DELETE'            WHEN stock= 0 THEN 'OUT_OF_STOCK'            ELSE status        END as action_type    FROM product_updates) uON p.name= u.nameWHEN MATCHED AND u.action_type= 'DELETE' THEN    DELETEWHEN MATCHED AND u.action_type= 'OUT_OF_STOCK' THEN    UPDATE SET        status = 'inactive',        stock= 0,        last_updated= CURRENT_TIMESTAMPWHEN MATCHED THEN    UPDATE SET        price= COALESCE(u.price, p.price),        stock= u.stock,        status = u.status,        last_updated= CURRENT_TIMESTAMPWHEN 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 theMERGE
   • Use subqueries to transform or clean data
   • Consider using CTEs for complex data preparation

2. Leverage RETURNING for Validation:

   • Include themerge_action() for operation tracking
   • Consider returning both old and new values for logging purposes and validation

Common Pitfalls to Avoid

1. Ambiguous Matches: Ensure yourON clause creates unique matches
2. NULL Handling: UseCOALESCE orIS NOT DISTINCT FROM forNULL values
3. Missing Conditions: Always handle all possible cases in yourWHEN clauses

Conclusion

PostgreSQL 17's enhancedMERGE command withRETURNING 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 theMERGE statement in PostgreSQL?

TheMERGE statement allows you to conditionallyINSERT,UPDATE, orDELETE 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 theMERGE statement introduced in PostgreSQL?

TheMERGE statement was officially introduced in PostgreSQL version 15, released in October 2022.

—How does theMERGE statement determine which operation to perform?

TheMERGE statement uses a specifiedON 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 correspondingINSERT,UPDATE,DELETE, orDO NOTHING actions.

—Can I use theMERGE statement with views in PostgreSQL?

Yes, starting from PostgreSQL 17, theMERGE command can be used with updatable views. ForMERGE to work with views, the views must be consistent:

Trigger-updatable views needINSTEAD 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 aMERGE statement?

To execute aMERGE 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 theMERGE statement atomic in PostgreSQL?

Yes, theMERGE 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 theRETURNING clause with theMERGE statement?

Yes, starting from PostgreSQL 17, theMERGE statement supports theRETURNING clause. This allows you to retrieve information about the rows affected by theMERGE operation, including the specific action performed (INSERT,UPDATE, orDELETE) on each row.

—How does theMERGE statement handle concurrent data modifications?

TheMERGE 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 theMERGE statement?

While theMERGE statement simplifies complex operations into a single command, it's essential to ensure that theON 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 theMERGE statement?

Yes, theMERGE statement allows for multipleWHEN clauses with additional conditions. This enables you to specify different actions (INSERT,UPDATE,DELETE, orDO NOTHING) based on various criteria, providing fine-grained control over the operation.

Last updated onMarch 27, 2024

On this page

• Introduction to the PostgreSQL MERGE statement
• Basic Concepts
• Basic MERGE Syntax
• Key Features in PostgreSQL 17
• Setting Up Our Example
• Using MERGE with RETURNING
• Understanding the Output
• Advanced Usage with Conditions
• Best Practices
• Common Pitfalls to Avoid
• Conclusion
• Frequently Asked Questions (FAQ)