Welcome to Part 4 of our series exploring the exciting new features anticipated in the official PostgreSQL 17 release. In this series, we delve into newly added features and discuss how they can benefit database developers and migration engineers transitioning to PostgreSQL 17 in future.

In case you want to explore the other parts:

Login Event Trigger
PL/pgSQL – the procedural language addition in PostgreSQL 17.
Null constraints and performance improvements
COPY command has become more user-friendly
pg_maintain role and maintain grant
Correlated-in-clause-join-transformation.

Ready to enhance your PostgreSQL development skills? My course on PostgreSQL and PL/pgSQL will help you master database development. Click here to start your journey, use DBGRP30 to avail 30% off.

Merge Statement in PostgreSQL 17

The MERGE statement allows us to write single DML statements with varying conditions to perform INSERT, UPDATE, or DELETE operations on a target table based on a data source. It offers multiple performance benefits and does not require an exclusion or unique constraint, unlike the INSERT ON CONFLICT statement.

Let’s explore an example involving the current_inventory table, where we want to update it based on transactions listed in the daily_updates table. We need to account for all statuses (sale, new, remove, restock) from daily_updates and perform the necessary operations on current_inventory.

Additionally, we’ll implement functionality to update the addinfo field in current_inventory to “no sale” if an item is not included in daily_updates.

CREATE TABLE current_inventory (
    product_id BIGINT ,product_name VARCHAR(100),quantity INT,last_updated TIMESTAMP,addinfo text
);
CREATE TABLE daily_updates (
    product_id BIGINT,product_name VARCHAR(100),quantity_change INT,update_type VARCHAR(10)
);

INSERT INTO current_inventory (product_id, product_name, quantity, last_updated)
VALUES (1, 'Laptop', 50, '2024-08-08 10:00:00'),(2, 'Smartphone', 100, '2024-08-08 10:00:00'),(3, 'Tablet', 30, '2024-08-08 10:00:00'),(4, 'Camera', 5, '2024-08-07 10:00:00'),(5, 'DVD Player', 5, '2024-08-07 10:00:00');

INSERT INTO daily_updates (product_id, product_name, quantity_change, update_type)
VALUES (1, 'Laptop', -5, 'sale'),(2, 'Smartphone', -10, 'sale'),(6, 'Smartwatch', 25, 'new'),(7, 'GoPro', 25, 'new'),(3, 'Tablet', 1, 'restock'),(2, 'Smartphone', 15, 'restock'),(5, 'DVD Player', 0, 'remove');

Let’s dive into each feature and uncover some fun facts about the internal join operations used in the MERGE statement.

PostgreSQL 17 MERGE – WHEN NOT MATCHED BY SOURCE

In the example shared earlier, we need to update the current_inventory table with additional information, specifically marking items as no sale if they are not included in the daily_updates dataset.

Before PostgreSQL 17, we could only perform actions on rows that matched conditions from the data source. This approach only covered rows that matched or did not match as per data source.

With PostgreSQL 17, we can now perform DML operations on rows that do not match conditions from the data source. The diagram below illustrates how different match conditions are handled within this updated functionality.

Fun Fact: Internal Join Strategies in PostgreSQL 17 MERGE

Internally, PostgreSQL changes its internal join strategy as part of the join methods based on the specified WHEN clause. The table below highlights the join_method used for each WHEN clause.

Referencing the above pictorial representation, different join methods make sense depending on the WHEN_CLAUSE used.

PostgreSQL 17 MERGE – RETURNING CLAUSE WITH merge_action()

The RETURNING clause is one of my favorite features. It allows integration with Common Table Expressions (CTEs) to build more complex functionality with SQL. Check out this use case of DML error logging with a CTE.

With PostgreSQL 17, the MERGE statement also supports the RETURNING clause, covering all rows affected by MERGE actions and operations. The special function merge_action() highlights the DML operation performed on the target table. This feature is valuable when using MERGE within a CTE to utilize processed rows’ information.

Read an interesting blog on how to implement “Get or Create” functionality that also highlight’s usage of RETURNING clause in MERGE. Check it out here.

Below is our MERGE SQL example, showcasing the new additions in PostgreSQL 17 with the samples we discussed earlier in the blog.

WITH merge as 
(MERGE INTO current_inventory as trg using 
    (select product_id,product_name, 
            string_agg(update_type,'+') as
            update_type,
            sum(quantity_change) as quantity_change 
    from daily_updates 
    group by product_id,product_name) as src 
on src.product_id = trg.product_id
WHEN MATCHED AND src.update_type <> 'remove' 
THEN UPDATE 
    SET quantity = trg.quantity + src.quantity_change, 
    addinfo = src.update_type , 
    last_updated = CURRENT_TIMESTAMP
WHEN MATCHED AND src.update_type = 'remove' 
THEN DELETE  
WHEN NOT MATCHED BY TARGET AND src.update_type <> 'remove' 
THEN INSERT 
    (product_id, product_name, quantity, last_updated,addinfo) 
     VALUES 
     (src.product_id, src.product_name, src.quantity_change,
CURRENT_TIMESTAMP,src.update_type)
WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET addinfo = 'No Sale', 
last_updated = CURRENT_TIMESTAMP
returning merge_action() , trg.*)
select * from merge;

PostgreSQL 17 MERGE – Modify Updatable View

With PostgreSQL 17, the MERGE command can now be used with updatable views. This means you can use MERGE to modify data through views, whether they update automatically or use triggers.

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.

Conclusion

PostgreSQL 17 brings some exciting enhancements with the MERGE statement, making it a more powerful and flexible tool for managing your data.

From handling rows not matched by the source to leveraging the RETURNING clause with merge_action(), these new features open up new possibilities for efficient and sophisticated data manipulation with pure SQL and avoid complex procedural logic.