Views and materialized views

Andrey Aksenov

Contents

Prerequisites
Views
Materialized views

A view is a named SQL query stored in the database. Views allow you to encapsulate frequently used or complex queries and reference them in SELECT statements as if they were regular tables. They can be used as an abstraction layer over tables: even if the table structure changes, applications and users continue to work through the view without changes. You can create a view based on one or more tables and other views.

A materialized view stores both the query and its results in a physical, table-like structure. This can significantly improve query performance, especially for expensive operations. However, unlike regular views, materialized views do not automatically reflect changes in the underlying data — they must be refreshed manually to stay up to date.

NOTE

Greengage DB includes built-in system views. For example, the gp_toolkit.gp_log_* views allow you to execute SQL queries against the server logs, and pg_catalog.pg_settings displays configuration parameters.

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE media_library;
\c media_library

Then, create a table and populate it with data:

Create a table

CREATE TABLE movies
(
    id     INTEGER,
    title  TEXT NOT NULL,
    genre  TEXT NOT NULL,
    year   INT,
    rating NUMERIC(3, 1)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED BY (id);

INSERT INTO movies (id, title, genre, year, rating)
SELECT gs                                                                             AS id,
       'Movie ' || gs                                                                 AS title,
       (ARRAY ['Sci-Fi','Crime','Romance','Action','Drama'])[(random() * 4)::int + 1] AS genre,
       (1925 + (random() * 100)::int)                                                 AS year,
       round((7.0 + random() * 3.0)::numeric, 1)                                      AS rating
FROM generate_series(1, 1000) gs;

To create a view, use the CREATE VIEW command. To modify the definition of an existing view, use ALTER VIEW.

The following is a simplified syntax of the CREATE VIEW command:

CREATE [ TEMP ] VIEW <name>
    AS <query>

The main clauses and arguments for defining a view are the following:

TEMP

Create a temporary view that exists only for the duration of the session. If any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMP is specified or not).
<name>

The name (optionally schema-qualified) of a view to be created.
<query>

A SELECT or VALUES statement that defines the rows and columns of the view. This query determines the data the view returns when queried.

This command creates a view that shows the total number of movies and the average rating for each movie’s genre:

CREATE VIEW avg_rating_by_genre AS
SELECT genre,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre;

You can query this view as a regular table by using the SELECT statement:

SELECT *
FROM avg_rating_by_genre;

The result should look like this:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
(5 rows)

This command creates a view using the same movies table that shows, for each decade, the total number of movies and the average rating:

CREATE VIEW movie_stats_by_decade AS
SELECT (year / 10) * 10      AS decade,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY decade
ORDER BY decade;

NOTE

When both CREATE VIEW <view> … and SELECT … FROM <view> specify an ORDER BY clause, Greengage DB ignores the ORDER BY clause in the CREATE VIEW statement.

The command below creates a nested view that selects the top three genres by an average rating based on the previously defined avg_rating_by_genre view:

CREATE VIEW top_3_genres AS
SELECT genre, avg_rating
FROM (SELECT genre,
             avg_rating,
             RANK() OVER (ORDER BY avg_rating DESC) AS genre_rank
      FROM avg_rating_by_genre) ranked
WHERE genre_rank <= 3;

The SELECT statement below retrieves all rows and columns from the created view:

SELECT *
FROM top_3_genres;

The result should look as follows:

  genre  | avg_rating
---------+------------
 Action  |       8.59
 Crime   |       8.51
 Romance |       8.49
(3 rows)

You can use the \dv or \dv+ meta-command to get the list of views, for example:

\dv

The result looks like this:

                List of relations
 Schema |         Name          | Type |  Owner
--------+-----------------------+------+---------
 public | avg_rating_by_genre   | view | gpadmin
 public | movie_stats_by_decade | view | gpadmin
 public | top_3_genres          | view | gpadmin
(3 rows)

The \d+ meta-command below shows information about the avg_rating_by_genre view:

\d+ avg_rating_by_genre

The result should include the definition of the view:

              View "public.avg_rating_by_genre"
    Column    |  Type   | Modifiers | Storage  | Description
--------------+---------+-----------+----------+-------------
 genre        | text    |           | extended |
 total_movies | bigint  |           | plain    |
 avg_rating   | numeric |           | main     |
View definition:
 SELECT movies.genre,
    count(*) AS total_movies,
    round(avg(movies.rating), 2) AS avg_rating
   FROM movies
  GROUP BY movies.genre;

You can query the pg_views view to get the list of user-defined views:

SELECT schemaname, viewname, definition
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
ORDER BY schemaname, viewname;

The result of the query should include the definition of each view:

 schemaname |       viewname        |                                      definition
------------+-----------------------+--------------------------------------------------------------------------------------
 public     | avg_rating_by_genre   |  SELECT movies.genre,                                                               +
            |                       |     count(*) AS total_movies,                                                       +
            |                       |     round(avg(movies.rating), 2) AS avg_rating                                      +
            |                       |    FROM movies                                                                      +
            |                       |   GROUP BY movies.genre;
 public     | movie_stats_by_decade |  SELECT ((movies.year / 10) * 10) AS decade,                                        +
            |                       |     count(*) AS total_movies,                                                       +
            |                       |     round(avg(movies.rating), 2) AS avg_rating                                      +
            |                       |    FROM movies                                                                      +
            |                       |   GROUP BY ((movies.year / 10) * 10)                                                +
            |                       |   ORDER BY ((movies.year / 10) * 10);
 public     | top_3_genres          |  SELECT ranked.genre,                                                               +
            |                       |     ranked.avg_rating                                                               +
            |                       |    FROM ( SELECT avg_rating_by_genre.genre,                                         +
            |                       |             avg_rating_by_genre.avg_rating,                                         +
            |                       |             rank() OVER (ORDER BY avg_rating_by_genre.avg_rating DESC) AS genre_rank+
            |                       |            FROM avg_rating_by_genre) ranked                                         +
            |                       |   WHERE (ranked.genre_rank <= 3);
(3 rows)

To see which views directly depend on the movies table, use a query that joins the system tables holding dependency info and filters for views only (relkind = 'v'):

SELECT DISTINCT v.oid::regclass      AS view,
                d.refobjid::regclass AS ref_object
FROM pg_depend AS d
         JOIN pg_rewrite AS r ON r.oid = d.objid
         JOIN pg_class AS v ON v.oid = r.ev_class
WHERE v.relkind = 'v'
  AND d.classid = 'pg_rewrite'::regclass
  AND d.deptype = 'n'
  AND d.refclassid = 'pg_class'::regclass
  AND d.refobjid = 'movies'::regclass;

DISTINCT is used to avoid duplicate rows when a view references multiple columns of the same table, since pg_depend records a separate dependency for each column. The result looks like this:

         view          | ref_object
-----------------------+------------
 movie_stats_by_decade | movies
 avg_rating_by_genre   | movies
(2 rows)

You can update the previous query to find views that depend on a specific column, which is helpful if you plan to drop that column:

SELECT v.oid::regclass      AS view,
       d.refobjid::regclass AS ref_object,
       a.attname            AS col_name
FROM pg_attribute AS a
         JOIN pg_depend AS d
              ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
         JOIN pg_rewrite AS r
              ON r.oid = d.objid
         JOIN pg_class AS v
              ON v.oid = r.ev_class
WHERE v.relkind = 'v'
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'
  AND a.attrelid = 'movies'::regclass
  AND a.attname = 'genre';

The result shows that only one view depends on the genre column in the movies table:

        view         | ref_object | col_name
---------------------+------------+----------
 avg_rating_by_genre | movies     | genre
(1 row)

The query below lists information about views that reference another view. The WITH clause in the CTE query selects all views from user schemas, while the main SELECT identifies views that reference other views:

WITH views AS (SELECT v.relname  AS view,
                      d.refobjid AS ref_object,
                      v.oid      AS view_oid,
                      ns.nspname AS namespace
               FROM pg_depend AS d
                        JOIN pg_rewrite AS r
                             ON r.oid = d.objid
                        JOIN pg_class AS v
                             ON v.oid = r.ev_class
                        JOIN pg_namespace AS ns
                             ON ns.oid = v.relnamespace
               WHERE v.relkind = 'v'
                 AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
                 AND d.deptype = 'n'
                 AND NOT (v.oid = d.refobjid))
SELECT DISTINCT views.view,
                views.namespace            AS schema,
                views.ref_object::regclass AS ref_view,
                ref_nspace.nspname         AS ref_schema
FROM views
         JOIN pg_depend as dep
              ON dep.refobjid = views.view_oid
         JOIN pg_class AS class
              ON views.ref_object = class.oid
         JOIN pg_namespace AS ref_nspace
              ON class.relnamespace = ref_nspace.oid
WHERE class.relkind = 'v'
  AND dep.deptype = 'n';

The result should include the top_3_genres view:

     view     | schema |      ref_view       | ref_schema
--------------+--------+---------------------+------------
 top_3_genres | public | avg_rating_by_genre | public
(1 row)

To drop a view, use the DROP VIEW command:

DROP VIEW movie_stats_by_decade;

To drop a view along with related objects, such as dependent views, add the CASCADE option:

DROP VIEW avg_rating_by_genre CASCADE;

To create a materialized view, use the CREATE MATERIALIZED VIEW command. To modify the definition of an existing materialized view, use ALTER MATERIALIZED VIEW.

The following is a simplified syntax of the CREATE MATERIALIZED VIEW command:

CREATE MATERIALIZED VIEW <name>
    AS <query>
    [ WITH [ NO ] DATA ]
    [ DISTRIBUTED ... ]

The main clauses and arguments for defining a materialized view are the following:

<name>

The name (optionally schema-qualified) of a materialized view to be created.
<query>

A SELECT or VALUES statement that defines the result set to store in the materialized view.
WITH [ NO ] DATA

Whether the materialized view should be populated with data at creation time. WITH DATA is the default, which populates the materialized view.
DISTRIBUTED

The distribution policy that determines how materialized view rows are distributed across segments in a Greengage DB cluster. For information about distribution policies, see Distribution.

This command creates a materialized view that contains the total number of movies and the average rating for each genre:

CREATE MATERIALIZED VIEW avg_rating_by_genre_mat AS
SELECT genre,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre
DISTRIBUTED BY (genre);

You can query this materialized view as a regular table by using the SELECT statement:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

The result should look like this:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
(5 rows)

To update a materialized view, use the REFRESH MATERIALIZED VIEW statement. You can set up automatic view updates using this command.

The command below inserts additional data into the underlying movies table, specifically movies with ratings between 9.0 and 10.0:

INSERT INTO movies (id, title, genre, year, rating)
SELECT gs                                                                             AS id,
       'Movie ' || gs                                                                 AS title,
       (ARRAY ['Sci-Fi','Crime','Romance','Action','Drama'])[(random() * 4)::int + 1] AS genre,
       (1925 + (random() * 100)::int)                                                 AS year,
       round((7.0 + random() * 3.0)::numeric, 1)                                      AS rating
FROM generate_series(1, 1000) gs;

This is expected to increase the average rating for each genre from approximately 8.5 to around 9.0. Use the SELECT statement to query the materialized view:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

The result should show that the values in the avg_rating column have not changed:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
(5 rows)

Update the materialized view data using REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW avg_rating_by_genre_mat;

Then, query the materialized view again:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

The results indicate that the avg_rating values have been updated and are now close to 9.0:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          483 |       9.05
 Crime   |          517 |       9.02
 Drama   |          262 |       8.96
 Romance |          490 |       8.96
 Sci-Fi  |          248 |       8.94
(5 rows)

Use the \dm or \dm+ meta-command to get the list of materialized views:

\dm+

The result looks like this:

                                   List of relations
 Schema |          Name           |       Type        |  Owner  |  Size  | Description
--------+-------------------------+-------------------+---------+--------+-------------
 public | avg_rating_by_genre_mat | materialized view | gpadmin | 256 kB |
(1 row)

The \d+ meta-command shows information about the avg_rating_by_genre_mat materialized view:

\d+ avg_rating_by_genre_mat;

The result includes the definition of the materialized view:

             Materialized view "public.avg_rating_by_genre_mat"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
 genre        | text    |           | extended |              |
 total_movies | bigint  |           | plain    |              |
 avg_rating   | numeric |           | main     |              |
View definition:
 SELECT movies.genre,
    count(*) AS total_movies,
    round(avg(movies.rating), 2) AS avg_rating
   FROM movies
  GROUP BY movies.genre;
Distributed by: (genre)

Query the pg_matviews view to get the list of materialized views:

SELECT matviewname, definition
FROM pg_matviews;

The result looks as follows:

       matviewname       |                   definition
-------------------------+------------------------------------------------
 avg_rating_by_genre_mat |  SELECT movies.genre,                         +
                         |     count(*) AS total_movies,                 +
                         |     round(avg(movies.rating), 2) AS avg_rating+
                         |    FROM movies                                +
                         |   GROUP BY movies.genre;
(1 row)

NOTE

You can retrieve information about materialized view dependencies just like for regular views; the only difference is that relkind must be 'm'.

To drop a materialized view, use the DROP MATERIALIZED VIEW command:

DROP MATERIALIZED VIEW avg_rating_by_genre_mat;

To drop a materialized view along with related objects, such as dependent materialized views, add the CASCADE option.

Indexes

SELECT command overview

Views and materialized views

Prerequisites

Views

Syntax overview

Create a view

Create a nested view

Get information about views

psql meta-commands

System catalogs

Drop a view

Materialized views

Syntax overview

Create a materialized view

Refresh a materialized view

Get information about materialized views

psql meta-commands

System catalogs

Drop a materialized view