Mastering Data Manipulation with MERGE Command in PostgreSQL 15

Two years back, I wrote a blog post titled “PostgreSQL – Mastering UPSERT“, in which I explored the nuances of the INSERT ON CONFLICT command that allows conditional inserts or updates of rows.

Continuing its database technology innovation, PostgreSQL 15 has introduced an exciting new feature – the MERGE command. This command offers a more versatile approach to INSERT, UPDATE, or even DELETE rows in a table based on specific conditions. In this post, we’ll delve into the intricacies of this new MERGE command.

What is MERGE?

The MERGE command, also known as UPSERT, is a powerful tool that allows you to perform conditional operations on your data. If you’re familiar with the INSERT ON CONFLICT command introduced in PostgreSQL 9.5, you’ll find that MERGE can do everything it can and more.

Let’s revisit our coffee shop scenario from the previous post. As a coffee shop owner, you want to keep track of your customers’ changing drink preferences. With the MERGE command, you can not only update existing records or insert new ones, but also delete records if necessary.

Coffee Shop Scenario ☕

I own a coffee shop, and I want to keep a record of drink preferences for all my customers. There’s one problem though, the preferences change over time. So how can I record all drink preference for my customer in one place, without creating a duplicate row in database. In my previous post, we solved this using INSERT ON CONFLICT statement. In PostgreSQL 15 onwards, we can now solve this using MERGE.

Step 1. Create a test table

CREATE TABLE myCoffee (
	id serial PRIMARY KEY,
	name VARCHAR UNIQUE,
	preference VARCHAR NOT NULL);

myCoffee table consists of three columns: id, name, and preference. The name column has a unique constraint to guarantee the uniqueness of each customer’s name.

Step 2. INSERT sample rows

INSERT INTO 
    myCoffee (name, preference)
VALUES 
    ('Tom', 'Cappuccino'),
    ('Joe', 'Espresso'),
    ('Varun', 'Frappé');

name	preference
Tom	Cappuccino
Joe	Espresso
Varun	Frappé

Step 3. MERGE in action

Option A. In this example, the MERGE command checks if a record with the name ‘Joe’ exists in the myCoffee table.

• If it does (WHEN MATCHED), it updates Joe’s preference by appending the new preference to the existing one.
• If it doesn’t (WHEN NOT MATCHED), it inserts a new record with Joe’s name and preference.

MERGE INTO mycoffee AS target
using (VALUES ('Joe',
      'Mojito')) AS source (NAME, preference)
ON target.NAME = source.NAME
WHEN matched THEN
  UPDATE SET preference = source.preference
                          || ';'
                          || target.preference
WHEN NOT matched THEN
  INSERT (NAME,
          preference)
  VALUES (source.NAME,
          source.preference); 

name	preference
Tom	Cappuccino
Varun	Frappé
Joe	Mojito; Espresso

Option B. What if a customer hasn’t visited your coffee shop for a long time and you want to remove their record from your database? With the MERGE command, you can do this easily.

• In this example, if a record with the name ‘Joe’ exists (WHEN MATCHED) and Joe’s preference is ‘Espresso’, the record is deleted. If no such record exists (WHEN NOT MATCHED), a new one is inserted.
  
  Note: This operation assumes that it is executed independently, working with the initial state of the mycoffee table where Joe’s current preference is ‘Espresso’.

MERGE INTO mycoffee AS target
using (VALUES ('Joe',
      'Iced Tea')) AS source (NAME, preference)
ON target.NAME = source.NAME
WHEN matched AND target.preference = 'Espresso' THEN
  DELETE
WHEN NOT matched THEN
  INSERT (NAME,
          preference)
  VALUES (source.NAME,
          source.preference); 

name	preference
Tom	Cappuccino
Varun	Frappé

Conclusion

In conclusion, the MERGE command in PostgreSQL 15 provides a powerful and flexible way to manipulate your data. It can replace the INSERT ON CONFLICT command in scenarios where you need more control over your operations, such as when you need to conditionally delete records. With the MERGE command, managing your data in PostgreSQL has never been easier.

For more details, refer PostgreSQL 15 MERGE documentation.

Want to stay updated with the latest in PostgreSQL? Join my mailing list for more insightful posts. No spam, guaranteed!👨‍💻