CREATE VIEW

On this page Carat arrow pointing down

The CREATE VIEW statement creates a new view, which is a stored query represented as a virtual table.

Note:

By default, views created in a database cannot reference objects in a different database. To enable cross-database references for views, set the sql.cross_db_views.enabled cluster setting to true.

Note:

The CREATE VIEW statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Required privileges

The user must have the CREATE privilege on the parent database and the SELECT privilege on any table(s) referenced by the view.

Synopsis

CREATE opt_temp VIEW IF NOT EXISTS OR REPLACE opt_temp VIEW view_name ( name_list ) AS select_stmt MATERIALIZED VIEW IF NOT EXISTS view_name ( name_list ) AS select_stmt opt_with_data

Parameters

Parameter Description
MATERIALIZED Create a materialized view.
IF NOT EXISTS Create a new view only if a view of the same name does not already exist. If one does exist, do not return an error.

Note that IF NOT EXISTS checks the view name only. It does not check if an existing view has the same columns as the new view.
OR REPLACE Create a new view if a view of the same name does not already exist. If a view of the same name already exists, replace that view.

In order to replace an existing view, the new view must have the same columns as the existing view, or more. If the new view has additional columns, the old columns must be a prefix of the new columns. For example, if the existing view has columns a, b, the new view can have an additional column c, but must have columns a, b as a prefix. In this case, CREATE OR REPLACE VIEW myview (a, b, c) would be allowed, but CREATE OR REPLACE VIEW myview (b, a, c) would not.
view_name The name of the view to create, which must be unique within its database and follow these identifier rules. When the parent database is not set as the default, the name must be formatted as database.name.
name_list An optional, comma-separated list of column names for the view. If specified, these names will be used in the response instead of the columns specified in AS select_stmt.
AS select_stmt The selection query to execute when the view is requested.

Note that it is not currently possible to use * to select all columns from a referenced table or view; instead, you must specify specific columns.
opt_temp Defines the view as a session-scoped temporary view. For more information, see Temporary Views.

Support for temporary views is in preview.

Example

Tip:

This example highlights one key benefit to using views: simplifying complex queries. For additional benefits and examples, see Views.

Setup

The following examples use the startrek demo database schema.

To follow along, run cockroach demo startrek to start a temporary, in-memory cluster with the startrek schema and dataset preloaded:

icon/buttons/copy
$ cockroach demo startrek

Create a view

The sample startrek database contains two tables, episodes and quotes. The table also contains a foreign key constraint, between the episodes.id column and the quotes.episode column. To count the number of famous quotes per season, you could run the following join:

icon/buttons/copy
> SELECT startrek.episodes.season, count(*)
  FROM startrek.quotes
  JOIN startrek.episodes
  ON startrek.quotes.episode = startrek.episodes.id
  GROUP BY startrek.episodes.season;
  season | count
---------+--------
       1 |    78
       2 |    76
       3 |    46
(3 rows)

Alternatively, to make it much easier to run this complex query, you could create a view:

icon/buttons/copy
> CREATE VIEW startrek.quotes_per_season (season, quotes)
  AS SELECT startrek.episodes.season, count(*)
  FROM startrek.quotes
  JOIN startrek.episodes
  ON startrek.quotes.episode = startrek.episodes.id
  GROUP BY startrek.episodes.season;

The view is then represented as a virtual table alongside other tables in the database:

icon/buttons/copy
> SHOW TABLES FROM startrek;
  schema_name |    table_name     | type  | estimated_row_count
--------------+-------------------+-------+----------------------
  public      | episodes          | table |                  79
  public      | quotes            | table |                 200
  public      | quotes_per_season | view  |                   3
(3 rows)

Executing the query is as easy as SELECTing from the view, as you would from a standard table:

icon/buttons/copy
> SELECT * FROM startrek.quotes_per_season;
  season | quotes
---------+---------
       3 |     46
       1 |     78
       2 |     76
(3 rows)

Replace an existing view

You can create a new view, or replace an existing view, with CREATE OR REPLACE VIEW:

icon/buttons/copy
> CREATE OR REPLACE VIEW startrek.quotes_per_season (season, quotes)
  AS SELECT startrek.episodes.season, count(*)
  FROM startrek.quotes
  JOIN startrek.episodes
  ON startrek.quotes.episode = startrek.episodes.id
  GROUP BY startrek.episodes.season
  ORDER BY startrek.episodes.season;
icon/buttons/copy
> SELECT * FROM startrek.quotes_per_season;
  season | quotes
---------+---------
       3 |     46
       1 |     78
       2 |     76
(3 rows)

See also


Yes No
On this page

Yes No