At times, we need to merge or upsert data, i.e., update existing records and insert new records. The following key concepts cover different approaches to achieve merge or upsert in a database:
Two-Statement Approach
- Develop two statements - one to update and the other to insert.
- The queries in both statements should return mutually exclusive results.
- Executing the update statement followed by the insert statement is usually a more efficient approach.
INSERT INTO customer_order_metrics_dly
SELECT o.order_customer_id,
o.order_date,
count(1) order_count,
NULL
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_item_order_id
WHERE o.order_date BETWEEN '2013-08-01' AND '2013-08-31'
GROUP BY o.order_customer_id, o.order_date
Insert with ON CONFLICT for Merge (Upsert)
- Use
INSERT
withON CONFLICT (columns) DO UPDATE
for a simpler way to handle merge or upsert. - Postgres lacks
MERGE
orUPSERT
in SQL syntax.
INSERT INTO customer_order_metrics_dly
SELECT o.order_customer_id,
o.order_date,
count(1) order_count,
round(sum(order_item_subtotal)::numeric, 2) AS order_revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_item_order_id
WHERE o.order_date BETWEEN '2013-08-01' AND '2013-10-31'
GROUP BY o.order_customer_id, o.order_date
ON CONFLICT (customer_id, order_date) DO UPDATE SET
order_count = EXCLUDED.order_count,
order_revenue = EXCLUDED.order_revenue
Hands-On Tasks
- Develop a SQL script using the two-statement approach for merging or upserting data.
- Execute SQL to merge and upsert data efficiently using the
ON CONFLICT
clause.
Conclusion
In this article, we discussed merging or upserting data using a two-statement approach and the ON CONFLICT
clause. Practice these methods with hands-on tasks and explore further. Enjoy coding!
Click here to watch the full video tutorial on merging or upserting data.