CREATE VIEW

CREATE VIEW defines a non-materialized view, which simply provides an alias for the embedded SELECT statement.

The results of a view can be incrementally maintained in memory within a cluster by creating an index. This allows you to serve queries without the overhead of materializing the view.

Syntax

Field	Use
TEMP / TEMPORARY	Mark the view as temporary.
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 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.
( col_ident… )	Rename the `SELECT` statement’s columns to the list of identifiers, both of which must be the same length. Note that this is required for statements that return multiple columns with the same identifier.
select_stmt	The `SELECT` statement to embed in the view.

Details

Temporary views

The TEMP/TEMPORARY keyword creates a temporary view. Temporary views are automatically dropped at the end of the SQL session and are not visible to other connections. They are always created in the special mz_temp schema.

Temporary views may depend upon other temporary database objects, but non-temporary views may not depend on temporary objects.

Examples

Creating a view

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;