CREATE VIEW

CREATE VIEW creates a non-materialized view, which only provides an alias for the SELECT statement it includes.

Note that this is very different from Materialize’s main type of view, materialized views, which you can create with CREATE MATERIALIZED VIEW.

Conceptual framework 

CREATE VIEW simply stores the verbatim SELECT query, and provides a shorthand for performing the query. For more information, see API Components: Sources.

Syntax 

CREATE VIEW IF NOT EXISTS OR REPLACE VIEW view_name AS select_stmt
Field Use
OR REPLACE If a view exists with the same name, replace it with the view defined in this statement. You cannot replace views that other views or sinks depend on, nor can you replace a non-view object with a view.
IF NOT EXISTS If specified, do not generate an error if a view of the same name already exists.

If not specified, throw an error if a view of the same name already exists. (Default)
view_name A name for the view.
select_stmt The SELECT statement whose output you want to materialize and maintain.

Details 

Querying non-materialized views 

You can only directly SELECT from a non-materialized view if all of the sources it depends on (i.e. views and sources in its FROM clause) have access to materialized data (i.e. indexes). That is to say that all of a non-materialized view’s data must exist somewhere in memory for it to process SELECT statements. For those inclined toward mathematics, it’s possible to think of this as “transitive materialization.”

If non-materialized views can process SELECT statements, we call them “queryable.”

However, this limitation does not apply to creating materialized views. Materialized view definitions can SELECT from non-materialized view, irrespective of the non-materialized view’s dependencies. This is done by essentially inlining the definition of the non-materialized view into the materialized view’s definition.

The diagram below demonstrates this restriction using a number of views (a-h) with a complex set of interdependencies.

transitive materialization diagram

A few things to note from this example:

Memory 

Non-materialized views do not store the results of the query. Instead, they simply store the verbatim of the included SELECT. This means they take up very little memory, but also provide very little benefit in terms of reducing the latency and computation needed to answer queries.

Converting to materialized view 

You can convert a non-materialized view into a materialized view by adding an index.

Examples 

CREATE VIEW purchase_sum_by_region
AS
    SELECT sum(purchase.amount) AS region_sum,
           region.id AS region_id
    FROM region
    INNER JOIN user
        ON region.id = user.region_id
    INNER JOIN purchase
        ON purchase.user_id = user.id
    GROUP BY region.id;

This view is useful only in as much as it is easier to type purchase_sum_by_region than the entire SELECT statement.

However, it’s important to note that you could only SELECT from this view: