Home  /  Reference

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

CREATE TEMP TEMPORARY VIEW IF NOT EXISTS OR REPLACE VIEW view_name ( col_ident , ) AS select_stmt
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;

Privileges

The privileges required to execute this statement are:

  • Ownership of existing view_name if OR REPLACE is specified.
  • CREATE privileges on the containing schema.
  • USAGE privileges on all types used in the view definition.
  • USAGE privileges on the schemas that all types in the statement are contained in.
Back to top ↑