How To Create a Materialized View On PostgreSQL

dbtut

6 years ago

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.

CREATE MATERIALIZED VIEW materialized_view_example
AS select * from table_example WITH DATA;

1 2	CREATE MATERIALIZED VIEW materialized_view_example AS select * from table_example WITH DATA;

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

select * from materialized_view_example;

1	select * from materialized_view_example;

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

INSERT INTO table_example VALUES('Bruce Lee','San Francisco');

1	INSERT INTO table_example VALUES('Bruce Lee','San Francisco');

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.

REFRESH MATERIALIZED VIEW CONCURENTLY materialized_view_example;

1	REFRESH MATERIALIZED VIEW CONCURENTLY materialized_view_example;

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.

CREATE UNIQUE INDEX ON materialized_view_example (name);

1	CREATE UNIQUE INDEX ON materialized_view_example (name);

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.

DROP MATERIALIZED VIEW materialized_view_example;

1	DROP MATERIALIZED VIEW materialized_view_example;

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