Views and materialized views

Eugenia Kuzina

Views

A view is a named query stored in a database. PostgreSQL runs the query that defines the view every time the view is referenced. You can create a view based on one or more tables and other views. It is possible to utilize a view in almost any place an ordinary table can be used. Views allow you to encapsulate the structure details of your tables, which might change as your application evolves, behind consistent interfaces.

Views in PostgreSQL are implemented using the rule system. There is essentially no difference between the single statement and set of commands displayed below.

CREATE VIEW view1 AS SELECT * FROM table1;
CREATE TABLE view1 (<the same column list as table1>);
CREATE RULE "_RETURN" AS ON SELECT TO view1 DO INSTEAD
    SELECT * FROM table1;

The second code snippet is exactly what the CREATE VIEW command does internally. The information about a view in the PostgreSQL system catalogs is the same as for a table. For the parser, there is no difference between a table and a view. They are the same thing — relations. The benefit of implementing views with the rule system is that the planner has all the information about which tables have to be scanned, the relationships between these tables, the restrictive qualifications from the views, and the qualifications from the original query in one single query tree. The planner should decide which path is the best to execute the query, and the more information the planner has, the better this decision can be. For details, see Views and the Rule System.

Create a view

Utilize the CREATE VIEW command to create a view.

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW <name> [ ( <column_name> [, ...] ) ]
    [ WITH ( <view_option_name> [= <view_option_value>] [, ... ] ) ]
    AS <query>
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Parameters of the CREATE VIEW statement

TEMPORARY or TEMP

If specified, the view is created as a temporary view. PostgreSQL drops temporary views at the end of the current session. Existing permanent relations with the same name are not visible to the current session while the temporary view exists unless they are referenced with schema-qualified names.

If the view query references a temporary table, the view is created as a temporary view (whether TEMPORARY is specified or not)

RECURSIVE

Creates a recursive view. A view column name list must be specified for a recursive view

name

The view name (optionally, schema-qualified)

column_name

An optional list of names to be used for view columns. If not specified, the column names are taken from the query

WITH ( <view_option_name> [= <view_option_value>] [, …​ ] )

This clause specifies optional parameters for a view. The following parameters are supported:

  • check_option (enum) — can be local or cascaded, and is equivalent to WITH [ CASCADED | LOCAL ] CHECK OPTION (see below). This option can be changed on existing views with the ALTER VIEW command.

  • security_barrier (boolean) — is used if the view provides row-level security. For more information, see Rules and Privileges.

query

The SELECT or VALUES command that returns view columns and rows

WITH [CASCADED | LOCAL] CHECK OPTION

This option controls the behavior of updatable views. When this option is specified, the INSERT and UPDATE commands on the view are checked to ensure that new rows satisfy the view-defining condition. The new rows are checked to ensure that they are visible through the view. If they are not, the update will be rejected. If the CHECK OPTION is not specified, the INSERT and UPDATE commands are allowed to create rows that are not visible through the view. The following check options are supported:

  • LOCAL — new rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify CHECK OPTION).

  • CASCADED — new rows are checked against the conditions of the view and all underlying base views.

If the CHECK OPTION is specified, and neither LOCAL nor CASCADED is defined, the CASCADED is used.

CHECK OPTION is only supported on the views that are automatically updatable and do not have INSTEAD OF triggers or INSTEAD rules. If an updatable view is defined on top of a base view that has INSTEAD OF triggers, the LOCAL CHECK OPTION can be used to check the conditions on the automatically updatable view, but the conditions on the base view with INSTEAD OF triggers are not checked. If the view or any of its base relations has an INSTEAD rule that causes the INSERT or UPDATE command to be rewritten, all check options will be ignored in the rewritten query

The CREATE OR REPLACE form of the command allows you to replace a view. If a view with the same name already exists, it is replaced. The new query must generate the same columns that are generated by the existing view query (the same column names, in the same order, and with the same data types), but it can add new columns to the end of the list. The calculations that generate the view columns can be different.

If a schema name is given, for example, CREATE VIEW schema1.view1 …​, PostgreSQL creates the view in the specified schema, otherwise in the current one. Temporary views exist in a special schema, so the schema name cannot be specified when creating temporary views. The view name must be different from the names of other views, tables, sequences, indexes, or third-party tables in the schema.

For example, we have the following table books:

 book_id |                title                | author_id | public_year |  genre  | evaluation | price
---------+-------------------------------------+-----------+-------------+---------+------------+-------
       1 | Mrs. Dalloway                       |         1 |        1925 | novel   |       7.82 |   360
       2 | To the Lighthouse                   |         1 |        1927 | novel   |       8.45 |   440
       3 | To Kill a Mockingbird               |         2 |        1960 | novel   |       7.48 |   750
       4 | The Great Gatsby                    |         3 |        1925 | novel   |       9.23 |   900
       5 | The Lord of the Rings               |         4 |        1955 | fantasy |       9.49 |  1200
       6 | 1984                                |         5 |        1949 | sci-fi  |       8.17 |   520
       7 | The Hobbit, or There and Back Again |         4 |        1937 | fantasy |       9.32 |  1100
       8 | War and Peace                       |         6 |        1869 | novel   |       9.69 |  1500
       9 | Hyperion                            |         7 |        1989 | sci-fi  |       9.46 |   610
      10 | The Time Machine                    |         8 |        1895 | sci-fi  |       8.12 |   450

Create a view that contain all novels.

CREATE VIEW novels AS
    SELECT *
    FROM books
    WHERE genre = 'novel';

Display the contents of the view.

SELECT * FROM novels;

The result:

 book_id |         title         | author_id | public_year | genre | evaluation | price
---------+-----------------------+-----------+-------------+-------+------------+-------
       1 | Mrs. Dalloway         |         1 |        1925 | novel |       7.82 |   360
       2 | To the Lighthouse     |         1 |        1927 | novel |       8.45 |   440
       3 | To Kill a Mockingbird |         2 |        1960 | novel |       7.48 |   750
       4 | The Great Gatsby      |         3 |        1925 | novel |       9.23 |   900
       8 | War and Peace         |         6 |        1869 | novel |       9.69 |  1500

If the column name and type are not specified in the view and cannot be taken from the underlying relation, PostgreSQL assigns the name ?column? and uses text as type by default. For example:

CREATE VIEW test AS SELECT 'Test';

Check the result.

SELECT * from test;
 ?column?
----------
 Test

In such cases, the best practice is to explicitly specify the column name and type.

CREATE VIEW test1 AS SELECT text 'Test' AS column1;

Permissions of the view owner determine access to the tables referenced in the view. In some cases, this can be used to provide secure but restricted access to the underlying tables. See Column-level security.

Updatable views

Simple views can be updated — PostgreSQL executes INSERT, UPDATE, and DELETE statements on a view in the same way as on a regular table. A view is updatable if it satisfies all the following conditions:

  • The view must have a single entry in its FROM list, which references a table or another updatable view.

  • The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.

  • The view definition must not contain set operations (UNION, INTERSECT, or EXCEPT) at the top level.

  • The view SELECT list must not contain any aggregates, window functions, or set-returning functions.

An updatable view may contain updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation, otherwise, the column is read-only. If an INSERT or UPDATE statement attempts to assign a value to it, an error occurs.

If the view is updatable, the system converts any INSERT, UPDATE, or DELETE statement on the view into the corresponding statement on the underlying base relation. INSERT statements with the ON CONFLICT UPDATE clause are fully supported.

If an updatable view contains a WHERE condition, the condition restricts which rows of the base relation are available to be modified by the UPDATE and DELETE statements on the view. However, UPDATE is allowed to change a row so that it no longer satisfies the WHERE condition, and is no longer visible through the view. Similarly, an INSERT command can insert base-relation rows that do not satisfy the WHERE condition and are not visible through the view. CHECK OPTION may be used to prevent INSERT and UPDATE commands from creating such rows.

Create a view with LOCAL CHECK OPTION.

CREATE VIEW novels1 AS
    SELECT *
    FROM novels
    WHERE public_year < 1959
    WITH LOCAL CHECK OPTION;

Display the contents of the view.

SELECT * FROM novels1;

The result:

 book_id |       title       | author_id | public_year | genre | evaluation | price
---------+-------------------+-----------+-------------+-------+------------+-------
       1 | Mrs. Dalloway     |         1 |        1925 | novel |       7.82 |   360
       2 | To the Lighthouse |         1 |        1927 | novel |       8.45 |   440
       4 | The Great Gatsby  |         3 |        1925 | novel |       9.23 |   900
       8 | War and Peace     |         6 |        1869 | novel |       9.69 |  1500

Add a new row to novels1.

INSERT INTO novels1 (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (11, 'The World Set Free', 8, 1914, 'sci-fi', 7.9, 450);

The World Set Free row is added, but it is not displayed in the novels1 view, because the row does not match novels view condition genre = 'novel'. To see this row, execute SELECT on the books table.

Create another view with CASCADED CHECK OPTION and try to add a row that does not meet the novels view condition.

CREATE VIEW novels2 AS
    SELECT *
    FROM novels
    WHERE public_year < 1959
    WITH CASCADED CHECK OPTION;

INSERT INTO novels2 (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (12, 'The Food of the Gods', 8, 1904, 'sci-fi', 8.4, 620);

An error occurs:

ERROR:  new row violates check option for view "novels"
DETAIL:  Failing row contains (12, The Food of the Gods, 8, 1904, sci-fi, 8.4, 620).

The CASCADED CHECK OPTION option checks the conditions of the current view and all underlying base views.

More complex views are read-only by default. The system does not allow insert, update, or delete operations on the view. To perform these operations, you can create INSTEAD OF triggers on the view that convert operations on the view into appropriate actions on other tables. For more information, see CREATE TRIGGER.

Change view properties

You can use the ALTER VIEW statement to change various auxiliary properties of a view. You must own the view to execute ALTER VIEW. If you want to modify the view defining query, use the CREATE OR REPLACE VIEW statement mentioned above.

The ALTER VIEW command has the following syntax:

ALTER VIEW [ IF EXISTS ] <name> ALTER [ COLUMN ] <column_name> SET DEFAULT <expression>
ALTER VIEW [ IF EXISTS ] <name> ALTER [ COLUMN ] <column_name> DROP DEFAULT
ALTER VIEW [ IF EXISTS ] <name> OWNER TO { <new_owner> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER VIEW [ IF EXISTS ] <name> RENAME [ COLUMN ] <column_name> TO <new_column_name>
ALTER VIEW [ IF EXISTS ] <name> RENAME TO <new_name>
ALTER VIEW [ IF EXISTS ] <name> SET SCHEMA <new_schema>
ALTER VIEW [ IF EXISTS ] <name> SET ( <view_option_name> [= <view_option_value>] [, ... ] )
ALTER VIEW [ IF EXISTS ] <name> RESET ( <view_option_name> [, ... ] )
Parameters of the ALTER VIEW statement

name

A name of an existing view (optionally, schema-qualified)

column_name

A name of an existing column

new_column_name

A new name for an existing column

IF EXISTS

Do not throw an error if the view does not exist. A notification is displayed in this case

SET/DROP DEFAULT

These options set or remove the default value for a column. A column default value is substituted into any INSERT or UPDATE command executed on the view, before applying any rules or triggers for the view. The view default values take precedence over any default values from underlying relations

new_owner

A user name of a new view owner

new_name

A new name for the view

new_schema

A new schema for the view

SET ( <view_option_name> [= <view_option_value>] [, …​ ] )

RESET ( <view_option_name> [, …​ ] )

Sets or resets a view option. Currently, supported options are:

  • check_option (enum) — changes the check option of the view. The value must be local or cascaded.

  • security_barrier (boolean) — changes the security-barrier property of the view. The value must be boolean: true or false

For historical reasons, ALTER TABLE can also be used with views. However, only ALTER TABLE options that are equivalent to those shown above are allowed.

You can use ALTER VIEW to rename the view novels to all_novels_from_books:

ALTER VIEW novels RENAME TO all_novels_from_books;

It is also possible to attach a default column value to an updatable view.

ALTER VIEW all_novels_from_books ALTER COLUMN genre SET DEFAULT 'novel';

INSERT INTO all_novels_from_books (book_id, title, author_id, public_year, evaluation, price)
VALUES (12, 'Anna Karenina', 6, 1877, 8.9, 750);

The Anna Karenina row has been inserted with the novel genre. If you try to insert a row directly into the books table, the novel value will not be used for the genre column by default.

Remove a view

You can use the DROP VIEW command to remove a view. To execute this command, you must be the owner of the view.

DROP VIEW [ IF EXISTS ] <name> [, ...] [ CASCADE | RESTRICT ]
Parameters of the DROP VIEW statement

IF EXISTS

Do not throw an error if the view does not exist. A notification is displayed in this case

name

A name of the view to remove (optionally, schema-qualified)

CASCADE

Drop objects that depend on the view (such as other views), and in turn all objects that depend on those objects (see Dependency Tracking)

RESTRICT

Refuse to drop the view if any objects depend on it. This is the default behavior

Example:

DROP VIEW novels1;

Materialized views

Materialized views use the rule system like views, but persist the results in a table-like form. Unlike views, the materialized view stores not only the query but also its results.

The difference between a materialized view and a table is that a materialized view cannot be directly updated. To update a materialized view, use the REFRESH MATERIALIZED VIEW statement.

The information about a materialized view in the PostgreSQL system catalogs is the same as for a table or view. For the parser, a materialized view is a relation. When a query references a materialized view, the data is returned directly from the materialized view, like from a table. The rule system is only used for populating the materialized view.

Although accessing data stored in a materialized view is often much faster than accessing the underlying tables directly or through the view, the data is not always up to date. It is possible to use materialized views when current data is not needed. For example, some aggregated statistics stored in a materialized view can be updated by a timer. A job can be scheduled to update the statistics using the REFRESH MATERIALIZED VIEW statement.

Another use of a materialized view is to provide faster access to data transferred from a remote system through a foreign data wrapper. Even if a data wrapper does not support indexes, in some cases, indexes can be created for the materialized view.

Create a materialized view

The CREATE MATERIALIZED VIEW statement defines a new materialized view of a query. PostgreSQL executes the query and populates the view at the time the command is issued (unless WITH NO DATA is used). Temporary materialized views are not supported. CREATE MATERIALIZED VIEW requires the CREATE privilege on the schema used for the materialized view.

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <name>
    [ (<column_name> [, ...] ) ]
    [ USING <method> ]
    [ WITH ( <storage_parameter> [= <value>] [, ... ] ) ]
    [ TABLESPACE <tablespace_name> ]
    AS <query>
    [ WITH [ NO ] DATA ]
Parameters of the CREATE MATERIALIZED VIEW statement

IF NOT EXISTS

Do not throw an error if a materialized view with the same name already exists. A notification is displayed in this case

name

The name of the materialized view (optionally, schema-qualified). The name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema

column_name

The name of a column in the new materialized view. If column names are not specified, they are taken from the output column names of the query

USING <method>

This optional clause specifies the table access method used to store the contents of the new materialized view. The method needs to be an access method of type TABLE. See Table Access Method Interface Definition. If this option is not specified, the default table access method is chosen for the new materialized view. See default_table_access_method

WITH ( <storage_parameter> [= <value>] [, …​ ] )

This clause specifies optional storage parameters for the new materialized view. All parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED VIEW. See Storage Parameters for more information

TABLESPACE <tablespace_name>

The tablespace_name is the name of the tablespace in which the materialized view should be created. If not specified, default_tablespace is used

query

A SELECT, TABLE, or VALUES command. This query runs within a security-restricted operation. In particular, calls to functions that create temporary tables will fail

WITH [NO] DATA

This clause specifies whether the materialized view should be populated at creation time. If not, the materialized view is marked as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW is executed

Create a materialized view based on the books table mentioned above and the following authors table:

 id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell
  6 | Leo Tolstoy
  7 | Dan Simmons
  8 | Herbert Wells
CREATE MATERIALIZED VIEW bookshelf AS
SELECT
    b.book_id,
    b.title,
    b.public_year,
    a.name,
    b.genre
FROM books b, authors a
WHERE b.author_id = a.id;

Display the contents of bookshelf:

SELECT * FROM bookshelf;

The result:

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel

Add a row to the books table:

INSERT INTO books (book_id, title, author_id, public_year, genre, evaluation, price)
VALUES (13, 'Resurrection', 6, 1899, 'novel', 9.1, 460);

Display the bookshelf materialized view:

SELECT * FROM bookshelf;

The result is the same, a new row is not displayed in the materialized view.

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel

Execute the REFRESH MATERIALIZED VIEW statement to update bookshelf and check the result.

REFRESH MATERIALIZED VIEW bookshelf;

SELECT * FROM bookshelf;

The materialized view is updated.

 book_id |                title                | public_year |        name         |  genre
---------+-------------------------------------+-------------+---------------------+---------
       1 | Mrs. Dalloway                       |        1925 | Virginia Woolf      | novel
       2 | To the Lighthouse                   |        1927 | Virginia Woolf      | novel
       3 | To Kill a Mockingbird               |        1960 | Harper Lee          | novel
       4 | The Great Gatsby                    |        1925 | F. Scott Fitzgerald | novel
       5 | The Lord of the Rings               |        1955 | J.R.R. Tolkien      | fantasy
       6 | 1984                                |        1949 | George Orwell       | sci-fi
       7 | The Hobbit, or There and Back Again |        1937 | J.R.R. Tolkien      | fantasy
       8 | War and Peace                       |        1869 | Leo Tolstoy         | novel
       9 | Hyperion                            |        1989 | Dan Simmons         | sci-fi
      10 | The Time Machine                    |        1895 | Herbert Wells       | sci-fi
      11 | The World Set Free                  |        1914 | Herbert Wells       | sci-fi
      12 | Anna Karenina                       |        1877 | Leo Tolstoy         | novel
      13 | Resurrection                        |        1899 | Leo Tolstoy         | novel

Change materialized view properties

Use the ALTER MATERIALIZED VIEW statement to change various auxiliary properties of a materialized view. The ALTER MATERIALIZED VIEW command has the following syntax:

ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    <action> [, ... ]
ALTER MATERIALIZED VIEW <name>
    [ NO ] DEPENDS ON EXTENSION <extension_name>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    RENAME [ COLUMN ] <column_name> TO <new_column_name>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    RENAME TO <new_name>
ALTER MATERIALIZED VIEW [ IF EXISTS ] <name>
    SET SCHEMA <new_schema>
ALTER MATERIALIZED VIEW ALL IN TABLESPACE <name> [ OWNED BY <role_name> [, ... ] ]
    SET TABLESPACE <new_tablespace> [ NOWAIT ]

where <action> is one of:

    ALTER [ COLUMN ] <column_name> SET STATISTICS integer
    ALTER [ COLUMN ] <column_name> SET ( <attribute_option> = <value> [, ... ] )
    ALTER [ COLUMN ] <column_name> RESET ( <attribute_option> [, ... ] )
    ALTER [ COLUMN ] <column_name> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] <column_name> SET COMPRESSION <compression_method>
    CLUSTER ON <index_name>
    SET WITHOUT CLUSTER
    SET TABLESPACE <new_tablespace>
    SET ( <storage_parameter> [= <value>] [, ... ] )
    RESET ( <storage_parameter> [, ... ] )
    OWNER TO { <new_owner> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
Parameters of the ALTER MATERIALIZED VIEW statement

name

A name of an existing materialized view (optionally, schema-qualified)

column_name

A name of a new or existing column

extension_name

The name of the extension on which the materialized view will depend (or not if NO is specified). A materialized view that is marked as dependent on an extension is deleted when the extension is dropped

new_column_name

A new name for an existing column

new_owner

A user name of the new owner of the materialized view

new_name

A new name for the materialized view

new_schema

A new schema for the materialized view

For example, you can use ALTER MATERIALIZED VIEW to rename the bookshelf view to books_authors.

ALTER MATERIALIZED VIEW bookshelf RENAME TO books_authors;

You must own the materialized view to use ALTER MATERIALIZED VIEW. To change a materialized view schema, you must also have the CREATE privilege on the new schema. To alter the owner, you must also be a member of the new owning role, and that role must have CREATE privilege on the materialized view schema.

The statement forms and actions of ALTER MATERIALIZED VIEW are a subset of the ALTER TABLE forms and actions and have the same meaning when applied to materialized views.

Remove a materialized view

Use the DROP MATERIALIZED VIEW statement to remove a materialized view. To execute this command, you must be the owner of the materialized view.

DROP MATERIALIZED VIEW [ IF EXISTS ] <name> [, ...] [ CASCADE | RESTRICT ]
Parameters of the DROP MATERIALIZED VIEW statement

IF EXISTS

Do not throw an error if the materialized view does not exist. A notification is displayed in this case

name

A name of the materialized view to remove (optionally, schema-qualified)

CASCADE

Drop objects that depend on the materialized view (such as other materialized and regular views), and in turn all objects that depend on those objects (see Dependency Tracking)

RESTRICT

Refuse to drop the materialized view if any objects depend on it. This is the default behavior

Example:

DROP MATERIALIZED VIEW books_authors;

Views vs materialized views

The main differences between a view and materialized view are:

  • The view saves only the defining query. The materialized view stores the defining query and its result.

  • Each time when the regular view is referenced, its defining query is executed. When the materialized view is referenced, it returns a previously stored result. The materialized view may return outdated data if underlining tables or views are changed. You need to execute the REFRESH MATERIALIZED VIEW statement to update the materialized view.

  • The materialized view is often much faster than accessing the underlying tables directly or through a regular view.

Get information about views and materialized views

You can use the \dv or \dv+ meta-command of psql to get the list of views.

$ \dv

The result:

                List of relations
 Schema |         Name          | Type |  Owner
--------+-----------------------+------+----------
 public | all_novels_from_books | view | postgres
 public | novels2               | view | postgres
 public | test                  | view | postgres
(3 rows)

The \dv+ meta-command provides more details.

 Schema |         Name          | Type |  Owner   | Persistence |  Size   | Description
--------+-----------------------+------+----------+-------------+---------+-------------
 public | all_novels_from_books | view | postgres | permanent   | 0 bytes |
 public | novels2               | view | postgres | permanent   | 0 bytes |
 public | test                  | view | postgres | permanent   | 0 bytes |
(3 rows)

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

The \dm+ meta-command provides more details.

 Schema |   Name    |       Type        |  Owner   | Persistence | Access method |    Size    | Description
--------+-----------+-------------------+----------+-------------+---------------+------------+-------------
 public | bookshelf | materialized view | postgres | permanent   | heap          | 8192 bytes |
(1 row)

It is also possible to utilize the Information Schema to get a view name, defining query, check option value, and other information.

SELECT table_catalog, table_schema, table_name, view_definition, check_option
FROM INFORMATION_SCHEMA.views
WHERE table_schema = ANY (current_schemas(false));

The table_schema = ANY (current_schemas(false)) condition excludes system views from the output.

The result:

 table_catalog | table_schema |      table_name       |                   view_definition                   | check_option
---------------+--------------+-----------------------+-----------------------------------------------------+--------------
 bookstore     | public       | test                  |  SELECT 'Test'::text;                               | NONE
 bookstore     | public       | novels2               |  SELECT all_novels_from_books.book_id,             +| CASCADED
               |              |                       |     all_novels_from_books.title,                   +|
               |              |                       |     all_novels_from_books.author_id,               +|
               |              |                       |     all_novels_from_books.public_year,             +|
               |              |                       |     all_novels_from_books.genre,                   +|
               |              |                       |     all_novels_from_books.evaluation,              +|
               |              |                       |     all_novels_from_books.price                    +|
               |              |                       |    FROM all_novels_from_books                      +|
               |              |                       |   WHERE (all_novels_from_books.public_year < 1959); |
 bookstore     | public       | all_novels_from_books |  SELECT books.book_id,                             +| NONE
               |              |                       |     books.title,                                   +|
               |              |                       |     books.author_id,                               +|
               |              |                       |     books.public_year,                             +|
               |              |                       |     books.genre,                                   +|
               |              |                       |     books.evaluation,                              +|
               |              |                       |     books.price                                    +|
               |              |                       |    FROM books                                      +|
               |              |                       |   WHERE ((books.genre)::text = 'novel'::text);      |
(3 rows)

To display materialized views, utilize pg_matviews.

SELECT * from pg_matviews;

The result:

 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |          definition
------------+-------------+--------------+------------+------------+-------------+-------------------------------
 public     | bookshelf   | postgres     |            | f          | t           |  SELECT b.book_id,           +
            |             |              |            |            |             |     b.title,                 +
            |             |              |            |            |             |     b.public_year,           +
            |             |              |            |            |             |     a.name,                  +
            |             |              |            |            |             |     b.genre                  +
            |             |              |            |            |             |    FROM books b,             +
            |             |              |            |            |             |     author a                 +
            |             |              |            |            |             |   WHERE (b.author_id = a.id);
(1 row)

System views

PostgreSQL includes built-in system views. Some views provide access to frequently used queries on system catalogs, while others return parameters of the internal server state. For example, pg_settings displays the runtime parameters of the server, pg_roles contains information about database roles, and pg_matviews — about materialized views.

System views
Name Description

pg_available_extensions

Available extensions

pg_available_extension_versions

Available versions of extensions

pg_backend_memory_contexts

Backend memory contexts

pg_config

Compile-time configuration parameters

pg_cursors

Open cursors

pg_file_settings

Summary of configuration file contents

pg_group

Groups of database users

pg_hba_file_rules

Summary of client authentication configuration file contents

pg_indexes

Indexes

pg_locks

Locks currently held or awaited

pg_matviews

Materialized views

pg_policies

Policies

pg_prepared_statements

Prepared statements

pg_prepared_xacts

Prepared transactions

pg_publication_tables

Publications and their associated tables

pg_replication_origin_status

Information about replication origins, including replication progress

pg_replication_slots

Replication slot information

pg_roles

Database roles

pg_rules

Rules

pg_seclabels

Security labels

pg_sequences

Sequences

pg_settings

Parameter settings

pg_shadow

Database users

pg_shmem_allocations

Shared memory allocations

pg_stats

Planner statistics

pg_stats_ext

Extended planner statistics

pg_stats_ext_exprs

Extended planner statistics for expressions

pg_tables

Tables

pg_timezone_abbrevs

Time zone abbreviations

pg_timezone_names

Time zone names

pg_user

Database users

pg_user_mappings

User mappings

pg_views

Views

PostgreSQL also includes some additional views that provide access to the results of the statistics collector, see Collected statistics views.

Note that the information schema provides an alternative set of views that overlap the functionality of the system views. Since the information schema follows the SQL standard, and the views described above are specific to PostgreSQL, it is usually better to use the information schema if it provides all the information you need.

Found a mistake? Seleсt text and press Ctrl+Enter to report it