Postgres Views

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.

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.

Postgres views & Hasura​

After creating a view, you can expose it over your GraphQL API and query it like a normal table.

See this page for more info on how to create and expose views in Hasura.