Postgres views¶
Table of contents
Introduction¶
A Postgres view is a virtual table in Postgres. It represents the result of a query to one or more underlying tables in Postgres. Views are used to simplify complex queries since these queries are defined once in the view, and can then be directly queried via the same.
Note
Please refer to the Postgres documentation for more details on standard views and materialized views.
Standard views¶
Standard views represent the result of a query without actually storing data.
Examples¶
View with authors whose rating is larger than 6:
CREATE VIEW popular_authors AS
SELECT name, rating
FROM authors
WHERE rating > 6;
The created view can now be queried as follows:
SELECT name, rating from popular_authors;
View with authors ordered by their rating:
CREATE VIEW authors_ordered_by_rating AS
SELECT name, rating
FROM authors
ORDER BY rating;
The created view can now be queried as follows:
SELECT name, rating from authors_ordered_by_rating;
Materialized views¶
Compared to the standard view described above, materialized views do store data physically in the database. Materialized views are used if data from complex queries needs to be accessed quickly.
Example¶
Materialized view with authors whose rating is larger than 6 and who are active, ordered by rating:
CREATE MATERIALIZED VIEW popular_active_authors AS
SELECT name, rating
FROM authors
WHERE rating > 6 AND is_active = TRUE
ORDER BY rating;
The created materialized view can now be queried as follows:
SELECT name, rating from popular_active_authors;
Refreshing materialized views¶
Materialized views don’t always have the most recent data. Since the result of a query is stored in a materialized view like in a cache, you need to make sure to refresh it periodically:
REFRESH MATERIALIZED VIEW popular_active_authors;
Materialized views can be refreshed when their underlying source data changes using Postgres triggers.
Thank you for subscribing to our newsletter!
