Database Essentials - Writing Advanced SQL Queries - Merging or Upserting Data

Databases Postgres
1

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 with ON CONFLICT (columns) DO UPDATE for a simpler way to handle merge or upsert.
  • Postgres lacks MERGE or UPSERT 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

  1. Develop a SQL script using the two-statement approach for merging or upserting data.
  2. 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.

Watch the video tutorial here