How To Create a Materialized View On PostgreSQL

By using Materialized Views in PostgreSQL, you can access data faster by physically holding the data in the view.

I will go over an example and explain the details.

In my example I will use the table I created in the article “How to Create a View in PostgreSQL“.

We create a materialized view with the help of the following script.

With the WITH DATA statement you see at the end of the script, we are doing the loading operation on the materialized view.

If you run the script WITH NO DATA, the view will be read only until you load the data into the view.

 

After creating the Materialized View, we are querying as follows.

 

I will add a record to the original table and query the materialized view again to see what the result is.

 

As you can see, when we query the view, we can not see the recording we just added.

We need to refresh the materialized view as follows to be able to see the changes.

 

We got an error like you saw it.

ERROR:  cannot refresh materialized view “public.materialized_view_example” concurrently

HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

 

To be able to REFRESH the materialized view we need to add a unique index.

Add the unique index to the materialized view with the following script.

 

And let’s refresh the materialized view again.

 

If you do not use the concurrently expression when creating the materialized view, you do not need to create a unique index.

But when you refresh the materialied view, the source table is locked.

You will see Bruce Lee in the results when you query again after you have refreshed the materialized view.

 

You can delete the materialized view as follows.

 

You may want to look at the following articles about Views.

How To Create a View On PostgreSQL“,

How To Create a Recursive View On PostgreSQL“,

How To Create an Updateable View On PostgreSQL“,

How To Create an Updateable View WITH CHECK CONSTRAINT On PostgreSQL

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *