PostgreSQL Standard View vs Materialized View (with Syntax and Example)

Standard And Materialized Views

PostgreSQL plays a very important role if you want to manage data and majorly when you want to manipulate the data according to your requirements. It provides a large variety of statements, functions and JOINs to reduce the complicated structure of the different models.

Have you ever heard of VIEW in PostgreSQL? VIEW allows users to encapsulate commonly used query logic into reusable virtual tables. In this tutorial, we will learn two prominent categories of Views, which are standard views and materialized views.

PostgreSQL Standard View

Standard view, which is also known as regular view, in PostgreSQL is a virtual representation of data that is retrieved from one or more underlying tables using a defined SQL query. Unlike physical tables, these views don’t store data on their own. It is dynamically retrieved on request.

To understand this better, we can say that VIEW is like having a customized report or dashboard that takes you through the exact data you are interested in, and it eliminates all the extra noise.


SELECT column1, column2
FROM table
[WHERE condition];
Code language: SQL (Structured Query Language) (sql)

In the above syntax,

  • OR REPLACE: If a view already exists, this parameter will replace it.
  • view_name: Name of view which is to be created.

Creating a Standard View

Let’s assume, you are the owner of a bookstore, and you have a record of the book sold with the table name sales_record.

Sales Data Record
Sales Data Record

Now, let’s say you want to create a view where the total_sales value is greater than 1000.

CREATE VIEW sales_over_1000 AS
SELECT customer_id, total_sales
FROM sales_record
WHERE total_sales > 1000;Code language: SQL (Structured Query Language) (sql)

You can then query the Standard view just like you would a regular table:

SELECT * FROM sales_over_1000;Code language: SQL (Structured Query Language) (sql)
Book Sold Over 1000
Book Sold Over 1000

As you can see, a view has been created where the total_sales is more than 1000.

Also Read: PostgreSQL Conditional Select With CASE Statements

PostgreSQL Materialized Views

Materialized views store the result set of the query physically. This pre-calculated data allows for faster access and improved query performance, especially for complex queries that involve aggregates or joins. However, this view requires periodic refreshing to match the underlying data, which makes it suitable for situations where data revision is not necessary.


SELECT column1, column2
FROM table
[WHERE condition]
WITH [NO] DATA;Code language: SQL (Structured Query Language) (sql)

In the above syntax,

  • view_name: Name of view which is to be created.
  • WITH [NO] DATA: Determines whether the materialized view should be populated with data immediately upon creation (WITH DATA), or if it should be created without data (WITH NO DATA).

Refreshing Materialized Views:

If you use WITH NO DATA, the materialized view will be created, but it will initially be empty. So it must be refreshed to reflect changes in the underlying data. This can be done with the REFRESH command:

REFRESH MATERIALIZED VIEW  view_name;Code language: SQL (Structured Query Language) (sql)

Creating a Materialized View

Now let’s go back to the last example, yes, the one where you were the owner of the bookstore, and you need to calculate almost all the money spent by each customer.

You can create a materialized view that calculates this total for you, so you don’t have to do that every time you run a query.

SELECT customer_id, SUM(total_sales) AS total_amount
FROM sales_record
GROUP BY customer_id
WITH DATA;Code language: SQL (Structured Query Language) (sql)

Now, let’s write a query to see the table:

SELECT * FROM order_summary_data ORDER BY customer_id;Code language: SQL (Structured Query Language) (sql)
Total Sales Record
Total Sales Record

As you can see from the above, you have a materialized view of a table which contains the total amount that each customer spent.

Standard Views vs Materialized Views

FeatureStandard ViewsMaterialized Views
Data RepresentationVirtual representation of data which is dynamically generated when the query is executed.Physical storage of pre-calculated data which is stored as a snapshot of data at the time of creation.
Data StorageDo not store data themselves.Reflect changes in underlying data in real time.
Real-time UpdatesIt may have a performance impact for complex queries as they are computed dynamically.Require periodic refreshing to synchronize with underlying data.
Query PerformanceIt may have a performance impact on complex queries as they are computed dynamically.Improved query performance, especially for complex queries, as data is precomputed.
Data FreshnessAlways reflects the latest data from underlying tables.It may not always reflect the latest data, depending on the last refresh time.
Usage ScenarioSuitable for scenarios where real-time data access is essential.Ideal for scenarios where query performance is critical, and data freshness can be compromised.

Read More: Difference Between Simple and Complex View in SQL


Standard and materialized views have their distinct advantages and use cases in PostgreSQL. As we know, standard views provide dynamic representations of data, on the other hand, materialized views offer improved query performance at the cost of data freshness. Understanding these both can help you during the implementation part of your model and allow you to optimize database performance. We hope you enjoyed it.