create-view.md 2.9 KB
title: "CREATE VIEW"
description: "CREATE VIEW defines view, which provides an alias for the embedded SELECT statement."
menu:
# This should also have a "non-content entry" under Reference, which is
# configured in doc/user/config.toml
main:
parent: 'commands'
CREATE VIEW defines a 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.
Usage patterns
{{% views-indexes/table-usage-pattern-intro %}} {{% views-indexes/table-usage-pattern %}}
Syntax
{{< diagram "create-view.svg" >}}
| 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. |
| ( _colident... ) | 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
[//]: # "TODO(morsapaes) Add short usage patterns section + point to relevant architecture patterns once these exist."
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:
{{< include-md file="shared-content/sql-command-privileges/create-view.md" >}}
Additional information
- Views can be monotonic; that is, views can be recognized as append-only.