PostgreSQL MERGE Statement
Last Updated :
01 Nov, 2024
The MERGE statement in PostgreSQL is a powerful data manipulation tool introduced in PostgreSQL 15, enabling conditional INSERT, UPDATE, and DELETE operations in a single command. This feature streamlines data synchronization between tables, making it ideal for tasks such as upserts and handling table synchronization.
In this article, we will explain the PostgreSQL MERGE statement in depth, covering its syntax and practical examples to demonstrate its functionality.
PostgreSQL MERGE Statement
The MERGE statement can combine the INSERT, UPDATE, and DELETE operations into a single statement, making it easier to manage the records. It can allow the users to conditionally execute these operations based on the results of the JOIN between the target table and the source data.
syntax:
MERGE INTO target_table USING source_table
ON merge_condition
WHEN MATCHED THEN
UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
INSERT (column1, column2, ...) VALUES (value1, value2, ...);
Key Terms
- target_table: The table you want to modify.
- source_table: The table containing the new or updated the data.
- merge_condition: The condition can be used to match the rows from the target_table with rows in the source_table.
- WHEN MATCHED: It can specifies the action to take when the match is found.
- WHEN NOT MATCHED: It can specifies the action to take when no match is found.
Examples of Using the MERGE Statement in PostgreSQL
To illustrate the MERGE statement’s flexibility, let us go through a few practical examples that highlight common scenarios for data synchronization and updates.
Example 1: Merging Data with Update and Insert
This example demonstrates how to use the MERGE statement to update existing records and insert new records if they do not exist.
Step 1: Create employee table and insert Data
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC(10, 2)
);
INSERT INTO employees (name, salary) VALUES
('John Doe', 50000),
('Jane Smith', 60000),
('Alice Johnson', 70000);
select * from employees;
Output
PostgreSQL MERGE Statement Example1 step1Step 2: Create employees_updates Table and Insert Data
CREATE TABLE employees_updates (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC(10, 2)
);
INSERT INTO employees_updates (employee_id, name, salary) VALUES
(1, 'John Doe', 52000), -- Updates the salary of John Doe
(4, 'Bob Brown', 55000); -- New employee Bob Brown
select * from employees_updates;
Output
PostgreSQL MERGE Statement Example1 step2Step 3: Execute MERGE Statement
In this example, we have the two tables, employees and employees_updates. We want to update the salary in the employees table if there is match on the employee_id and insert the new record if there is no match.
MERGE INTO employees AS e
USING employees_updates AS u
ON e.employee_id = u.employee_id
WHEN MATCHED THEN
UPDATE SET salary = u.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, name, salary)
VALUES (u.employee_id, u.name, u.salary);
select * from employees;
Output
PostgreSQL MERGE Statement Example1 step3Explanation:
This statement will update the salary of matching the employees in the employees table and insert the new employees from employees_updates if they don't exist.
Example 2: Using MERGE for Deletion of Stale Data
This example demonstrates how to use MERGE for removing stable or inactive records from an old customer table based on the status in an active customer table.
Step 1: Create old_customers Table and Insert data
CREATE TABLE old_customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO old_customers (name) VALUES
('Customer A'),
('Customer B'),
('Customer C');
select * from old_customers;
Output
PostgreSQL MERGE Statement Example2 step 1Step 2: Create active_customers Table and Insert data
CREATE TABLE active_customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO active_customers (customer_id, name) VALUES
(1, 'Customer A'), -- Customer A is still active
(2, 'Customer D'); -- New active customer
select * from active_customer;
Output
PostgreSQL MERGE Statement Example2 step 2Step 3: Execute MERGE Statement
We can use the MERGE statement to remove the stale records from the old_customers table if they no longer exist in the active_cusomers table.
MERGE INTO old_customers AS oc
USING active_customers AS ac
ON oc.customer_id = ac.customer_id
WHEN MATCHED THEN
UPDATE SET is_active = TRUE
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET is_active = FALSE;
select * from old_cusomters;
Output
PostgreSQL MERGE Statement Example2 step 3Explanation:
The MERGE statement will delete entries in the old_customers that are not present in the active_customers table and it can ensuring the table only contains active records.
Conclusion
The MERGE statement in PostgreSQL is an essential tool for efficient data management, allowing INSERT, UPDATE, and DELETE operations to be combined within a single conditional statement. By understanding and using the MERGE statement effectively, we can streamline data synchronization, ensure accurate upserts, and handle complex transformations with ease. Using MERGE in PostgreSQL 15 and above can significantly optimize database operations, especially in large and dynamic environments.
Similar Reads
SQL Server MERGE Statement
MERGE is a powerful SQL statement that allows you to perform INSERT/UPDATE/DELETE operations depending on the conditions you specify. It makes it easy to synchronize data between source and destination tables. This makes it efficient to handle data changes in one single statement. MERGE makes it eas
6 min read
PostgreSQL UPDATE Statement
The PostgreSQL UPDATE statement is an important SQL command used to modify existing data in one or more rows of a table. It allows users to update specific columns or multiple columns at once, using conditions defined in the WHERE clause. This command is highly flexible, enabling dynamic data manage
5 min read
PL/SQL INSERT Statement
The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table's name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets
3 min read
PL/SQL DELETE Statement
In PL/SQL(Procedural Language/Structured Query Language), the DELETE statement is the powerful command used to remove one or more records from the database table. It is an essential part of database management and enables the users to efficiently manage and maintain the data integrity by selectively
7 min read
PostgreSQL - GROUPING SETS
In PostgreSQL, the GROUPING SETS feature allows users to generate result sets that are equivalent to those produced by the UNION ALL of multiple GROUP BY clauses. This feature is highly useful for creating complex reports with multiple levels of aggregation in a single query. A grouping set is essen
3 min read
PostgreSQL - Comparing Tables
Comparing tables in PostgreSQL is a common task when you need to identify differences between datasets. This can be especially useful when you are merging tables, validating data, or performing quality checks. In this article, we'll explore two of the most commonly used techniques for comparing tabl
3 min read
SQL INSERT INTO Statement
The SQL INSERT INTO statement is one of the most commonly used commands for adding new data into a table in a database. Whether you're working with customer data, products, or user details, mastering this command is crucial for efficient database management. Letâs break down how this command works,
6 min read
SQL INSERT INTO SELECT Statement
In SQL, the INSERT INTO statement is used to add or insert records into the specified table. We use this statement to insert data directly into a table by specifying column names in a specific order. The SELECT statement is used to retrieve data from the table, and it can be used in conjunction with
6 min read
PostgreSQL - Upsert
UPSERT in PostgreSQL is a powerful database operation that merges the functionalities of INSERT and UPDATE into a single command. This operation allows users to either insert a new row into a table or update an existing row if it already exists. Also, making it essential for efficient data managemen
5 min read
What is Nested Select Statement in PostgreSQL?
Nested select statements, also known as subqueries which is a fundamental concept in PostgreSQL and play an important role in data retrieval and manipulation. In PostgreSQL, a powerful relational database management system, the nested select statements allow for complex queries by nesting one query
5 min read