Upcoming feature in PostgreSQL 15 – MERGE statement

Enterprise PostgreSQL Solutions

Comments are off

Upcoming feature in PostgreSQL 15 – MERGE statement

The MERGE statement is one of the long awaited features and it’s coming in the upcoming Major version of PostgreSQL 15. Although the PostgreSQL 15 release is quite a distance away, the MERGE statement patch has been committed to the development branch and it should become available as early as beta release around May, 2022.

The MERGE statement is mostly used for adding, updating or removing rows from the target tables, based on the information and condition from a source, in a single transaction and using a single statement.

The most notable use is to synchronize two tables by updating, deleting or inserting new rows when there are differences found based on some join conditions.

 

What problem does it solve?

The MERGE statement is used to insert, update or delete rows in a specified table based on one or more source tables. It uses a condition to determine whether to update, delete or insert into the target table.

Let’s consider a small scale setup where customers and their order information is maintained. In this setup, perhaps one would like to perform some analysis of how many orders have been placed by a particular customer or how long has it been since the last placed order etc.

One would have to perform separate INSERT and UPDATE operations to sync up this information from a daily orders table to a consolidated customer’s information tables.

 

Syntax

 
[WITH with_query [, ...]]
MERGE INTO [schema.]table [ [ AS ] alias ]
   USING   {
               [schema.]table |
               [sub-query]
           } [ [ AS ] alias ]
   ON join_condition
   when_clause [...];

when_clause:

{
   WHEN MATCHED [ AND condition ] THEN { merge_update | DELETE | DO NOTHING } |
   WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING }
}

merge_update:
   UPDATE SET { column_name = { expression | DEFAULT } |
            ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

merge_insert:
   INSERT [( column_name [, ...] )]
           [ OVERRIDING { SYSTEM | USER } VALUE ]
           { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

Example

CREATE TABLE daily_orders
(
   order_id        INT,
   customer_id     INT,
   order_center    VARCHAR,
   order_time timestamp
);

CREATE TABLE customer_history
(
   customer_id     INT,
   last_order_id   INT,
   order_center    VARCHAR,
   order_count     INT,
   last_order      timestamp
);

INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (101, 30030, 'WX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (102, 20045, 'CX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (103, 20090, 'JX', now());
INSERT INTO daily_orders (order_id, customer_id, order_center, order_time)
   VALUES (104, 20010, 'AX', now());

INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (40100, 097, 'MK', 10, '2019-09-15 08:13:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20453, 098, 'NU', 17, '2020-07-12 01:05:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20090, 099, 'JX', 03, '2020-09-11 03:23:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (21495, 100, 'DS', 11, '2022-05-21 04:12:00');
INSERT INTO customer_history (customer_id, last_order_id, order_center, order_count, last_order)
   VALUES (20010, 103, 'AX', 05, '2021-01-17 19:53:00');

select * from customer_history;
postgres=# select * from customer_history;
 customer_id | last_order_id | order_center | order_count |     last_order      
-------------+---------------+--------------+-------------+--------------------
       40100 |            97 | MK           |          10 | 2019-09-15 08:13:00
       20453 |            98 | NU           |          17 | 2020-07-12 01:05:00
       20090 |            99 | JX           |           3 | 2020-09-11 03:23:00
       21495 |           100 | DS           |          11 | 2022-05-21 04:12:00
       20010 |           103 | AX           |           5 | 2021-01-17 19:53:00
(5 rows)


MERGE INTO customer_history c
   USING daily_orders d
   ON (c.customer_id = d.customer_id)

   WHEN MATCHED THEN
       UPDATE SET     -- Existing customer, update the order count and the timestamp of order.
           order_count = c.order_count + 1,
           last_order_id = d.order_id

   WHEN NOT MATCHED THEN       -- New entry, record it.
       INSERT (customer_id, last_order_id, order_center, order_count, last_order)
           VALUES (customer_id, d.order_id, d.order_center, 1, d.order_time);

select * from customer_history;

postgres=# select * from customer_history;
 customer_id | last_order_id | order_center | order_count |         last_order         
-------------+---------------+--------------+-------------+--------------------
       40100 |            97 | MK           |          10 | 2019-09-15 08:13:00
       20453 |            98 | NU           |          17 | 2020-07-12 01:05:00
       21495 |           100 | DS           |          11 | 2022-05-21 04:12:00
       30030 |           101 | WX           |           1 | 2022-04-30 18:20:33
       20045 |           102 | CX           |           1 | 2022-04-30 18:20:33
       20090 |           103 | JX           |           4 | 2020-09-11 03:23:00
       20010 |           104 | AX           |           6 | 2021-01-17 19:53:00
(7 rows)

As one can see that two rows have been added to the target table customer_history with customer_id 30030 and 20045. While two other rows with customer_id 20090 and 20010 have been updated with new information.

The MERGE statement feature has been committed and is part of the development branch. Its documentation is available at https://www.postgresql.org/docs/devel/sql-merge.html