Last week's tutorial guided us through the creation of Materialized Views in PostgreSQL, using the DVD Rental Database as a practical example. As we learned there, PostgreSQL Materialized Views provide a powerful mechanism to enhance query performance by precomputing and storing the result set of a query as a physical table. Today's follow-up will cover other pertinent Materialized View operations such as refreshing a view, executing queries against it, as well as deleting a view should you no longer require it. As with the last blog article, we'll go over both DML statements as well as how to achieve the same result via the Navicat GUI.
Refreshing a Materialized View
The data in a Materialized View needs to be refreshed periodically to reflect any changes in the underlying tables. You can use the following command to refresh the Materialized View:
REFRESH MATERIALIZED VIEW mv_category_revenue;In Navicat, we can refresh and completely replace the contents of a materialized view by right-clicking (or control-click on macOS) it in the Objects tab and selecting "Refresh Materialized View With" -> "Data" or "No Data" from the pop-up menu:
Querying a Materialized View
Now that we have our Materialized View, we can query it just like any other table:
SELECT * FROM mv_category_revenue;This query will return the film categories along with their total revenue, providing a quick and efficient way to retrieve this information without repeatedly joining multiple tables.
In Navicat, you can write a query in the Query Editor or using the Query Builder tool. In the case of the Query Editor, the autocomplete feature will recognize the Materialized View with just a couple of key strokes!
Materialized Views are also included in the Object pane of the Query Builder. You can add a Materialized View to the query by dragging it from the Object pane to the Diagram pane or by double-clicking it on the Object pane:
Deleting a Materialized View
Should you no longer require a Materialized View, you can delete it using the DROP MATERIALIZED VIEW command. Here's the statement to drop the mv_category_revenue view:
DROP MATERIALIZED VIEW mv_category_revenue;There are a couple of ways to delete a Materialized View in Navicat. The first is to:
- Select "Materialized View" in the Main Window's toolbar.
- Then select the Materialized View that you want to delete in the Objects list. That will enable several buttons on the Objects toolbar, including the Delete Materialized View button:
- Clicking the Delete Materialized View button will present a dialog prompt where you can confirm that you really do wish to delete the selected Materialized View.
The second way to delete a Materialized View in Navicat is to right-click it (or control-click on macOS) in either the Main Window's Navigation pane or Objects list and select "Delete Materialized View" from the context menu:
Conclusion
In this tutorial, we learned how to execute some pertinent Materialized View operations including refreshing a view, executing queries against it, as well as deleting a view. In each case, we covered both DML statements as well as how to achieve the same result via the Navicat GUI.
