CREATE VIEW

On this page Carat arrow pointing down
Warning:
CockroachDB v2.0 is no longer supported as of October 4, 2019. For more details, refer to the Release Support Policy.

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

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

Parameters

Parameter Description
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.

Example

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

Let's say you're using our sample startrek database, which contains two tables, episodes and quotes. There's 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:

> 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(*) |
+--------+----------+
|      2 |       76 |
|      3 |       46 |
|      1 |       78 |
+--------+----------+
(3 rows)

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

> 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;
CREATE VIEW

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

> SHOW TABLES FROM startrek;
+-------------------+
|       Table       |
+-------------------+
| episodes          |
| quotes            |
| quotes_per_season |
+-------------------+
(4 rows)

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

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

Known Limitations

Note:
The following limitations may be lifted in a future version of CockroachDB.
Note:
Resolved as of v2.1.

It is not yet possible to use a common table expression inside the selection query used to define a view.

See Also


Yes No
On this page

Yes No