> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Reusable Views

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

A view is a stored and named <InternalLink path="selection-queries">selection query</InternalLink>. By default, CockroachDB's views are **dematerialized**: they do not store the results of the underlying queries. Instead, the underlying query is executed anew every time the view is used.

CockroachDB also supports [**materialized views**](#materialized-views). Materialized views are views that store their selection query results.

<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` <InternalLink path="cluster-settings">cluster setting</InternalLink> to `true`.
</Note>

## Why use views?

There are various reasons to use views, including:

* [Hide query complexity](#hide-query-complexity)
* [Limit access to underlying data](#limit-access-to-underlying-data)

### Hide query complexity

When you have a complex query that, for example, joins several tables, or performs complex calculations, you can store the query as a view and then select from the view as you would from a standard table.

#### Example

Let's say you're using our <InternalLink path="cockroach-gen#generate-example-data">sample `startrek` database</InternalLink>, which contains two tables, `episodes` and `quotes`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> USE startrek;
```

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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT episodes.season, count(*)
  FROM quotes
  JOIN episodes
  ON quotes.episode = episodes.id
  GROUP BY 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE VIEW quotes_per_season (season, quotes)
  AS SELECT episodes.season, count(*)
  FROM quotes
  JOIN episodes
  ON quotes.episode = episodes.id
  GROUP BY episodes.season
  ORDER BY episodes.season;
```

Then, executing the query is as easy as `SELECT`ing from the view:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM quotes_per_season;
```

```
  season | quotes
---------+---------
       1 |     78
       2 |     76
       3 |     46
(3 rows)
```

### Limit access to underlying data

When you do not want to grant a user access to all the data in one or more standard tables, you can create a view that contains only the columns and/or rows that the user should have access to and then grant the user permissions on the view.

#### Example

Let's say you have a `bank` database containing an `accounts` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> USE bank;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
  id |   type   | balance |      email
-----+----------+---------+------------------
   1 | checking |    1000 | max@roach.com
   2 | savings  |   10000 | max@roach.com
   3 | checking |   15000 | betsy@roach.com
   4 | checking |    5000 | lilly@roach.com
   5 | savings  |   50000 | ben@roach.com
(5 rows)
```

You want a particular user, `bob`, to be able to see the types of accounts each user has without seeing the balance in each account, so you create a view to expose just the `type` and `email` columns:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE VIEW user_accounts
  AS SELECT type, email
  FROM accounts;
```

You then make sure `bob` does not have privileges on the underlying `accounts` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW GRANTS ON accounts;
```

```
  database_name | schema_name | table_name | grantee | privilege_type
----------------+-------------+------------+---------+-----------------
  bank          | public      | accounts   | admin   | ALL
  bank          | public      | accounts   | root    | ALL
(2 rows)
```

Finally, you grant `bob` privileges on the `user_accounts` view:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> GRANT SELECT ON user_accounts TO bob;
```

Now, `bob` will get a permissions error when trying to access the underlying `accounts` table but will be allowed to query the `user_accounts` view:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
pq: user bob does not have SELECT privilege on table accounts
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM user_accounts;
```

```
    type   |      email
-----------+------------------
  checking | max@roach.com
  savings  | max@roach.com
  checking | betsy@roach.com
  checking | lilly@roach.com
  savings  | ben@roach.com
(5 rows)
```

## How views work

### Creating views

To create a view, use the <InternalLink path="create-view">`CREATE VIEW`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE VIEW quotes_per_season (season, quotes)
  AS SELECT episodes.season, count(*)
  FROM quotes
  JOIN episodes
  ON quotes.episode = episodes.id
  GROUP BY episodes.season
  ORDER BY episodes.season;
```

<Note>
  Any <InternalLink path="selection-queries">selection query</InternalLink> is valid as operand to `CREATE VIEW`, not just <InternalLink path="select-clause">simple `SELECT` clauses</InternalLink>.
</Note>

### Listing views

Once created, views are listed alongside regular tables in the database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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  |                   0
(3 rows)
```

To list just views, you can query the `views` table in the <InternalLink path="information-schema">Information Schema</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM information_schema.views;
```

```
  table_catalog | table_schema |    table_name     |                                                                               view_definition                                                                                | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
----------------+--------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+--------------+--------------------+----------------------+----------------------+-----------------------------
  startrek      | public       | quotes_per_season | SELECT episodes.season, count(*) FROM startrek.public.quotes JOIN startrek.public.episodes ON quotes.episode = episodes.id GROUP BY episodes.season ORDER BY episodes.season | NULL         | NO           | NO                 | NO                   | NO                   | NO
(1 row)
```

### Query a view

To query a view, target it with a <InternalLink path="table-expressions#table-and-view-names">table expression</InternalLink>, for example using a <InternalLink path="select-clause">`SELECT` clause</InternalLink>, just as you would with a stored table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM quotes_per_season;
```

```
  season | quotes
---------+---------
       1 |     78
       2 |     76
       3 |     46
(3 rows)
```

`SELECT`ing a view executes the view's stored `SELECT` statement, which returns the relevant data from the underlying table(s). To inspect the `SELECT` statement executed by the view, use the <InternalLink path="show-create">`SHOW CREATE`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW CREATE quotes_per_season;
```

```
     table_name     |                                                                                                           create_statement
--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  quotes_per_season | CREATE VIEW public.quotes_per_season (season, quotes) AS SELECT episodes.season, count(*) FROM startrek.public.quotes JOIN startrek.public.episodes ON quotes.episode = episodes.id GROUP BY episodes.season ORDER BY episodes.season
(1 row)
```

You can also inspect the `SELECT` statement executed by a view by querying the `views` table in the <InternalLink path="information-schema">Information Schema</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT view_definition FROM information_schema.views WHERE table_name = 'quotes_per_season';
```

```
                                                                                view_definition
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  SELECT episodes.season, count(*) FROM startrek.public.quotes JOIN startrek.public.episodes ON quotes.episode = episodes.id GROUP BY episodes.season ORDER BY episodes.season
(1 row)
```

### View dependencies

A view depends on the objects targeted by its underlying query. Attempting to <InternalLink path="alter-table#rename-to">rename an object</InternalLink> referenced in a view's stored query therefore results in an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE quotes RENAME TO sayings;
```

```
ERROR: cannot rename relation "startrek.public.quotes" because view "quotes_per_season" depends on it
SQLSTATE: 2BP01
HINT: you can drop quotes_per_season instead.
```

Likewise, attempting to <InternalLink path="drop-table">drop an object</InternalLink> referenced in a view's stored query results in an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE quotes;
```

```
ERROR: cannot drop relation "quotes" because view "quotes_per_season" depends on it
SQLSTATE: 2BP01
HINT: you can drop quotes_per_season instead.
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE episodes DROP COLUMN season;
```

```
ERROR: cannot drop column "season" because view "quotes_per_season" depends on it
SQLSTATE: 2BP01
HINT: you can drop quotes_per_season instead.
```

You can <InternalLink path="alter-table#drop-column">drop</InternalLink> or <InternalLink path="alter-table#rename-column">rename columns</InternalLink> from a table on which a view is dependent, as long as the view does not depend on that column of the table. For example, because there is no view that depends on the `num` column of the `episodes` table, you can rename it to `number`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE startrek.episodes RENAME COLUMN num TO number;
```

Similarly, because no view depends on the `title` column of the `episodes` table, you can drop it. Note that to drop a column with data in it, you must first set `sql_safe_updates = false`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET sql_safe_updates = false;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER TABLE startrek.episodes DROP COLUMN title;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM startrek.episodes;
```

```
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  id          | INT8      |    false    | NULL           |                       | {primary} |   false
  season      | INT8      |    true     | NULL           |                       | {primary} |   false
  number      | INT8      |    true     | NULL           |                       | {primary} |   false
  stardate    | DECIMAL   |    true     | NULL           |                       | {primary} |   false
(4 rows)
```

When <InternalLink path="drop-table">dropping a table</InternalLink> or <InternalLink path="drop-view">dropping a view</InternalLink>, you can use the `CASCADE` keyword to drop all dependent objects as well:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE quotes CASCADE;
```

```
DROP TABLE
```

<Danger>
  `CASCADE` drops **all** dependent objects without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.
</Danger>

### Renaming views

To rename a view, use the <InternalLink path="alter-view">`ALTER VIEW`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER VIEW quotes_per_season RENAME TO quotes_count;
```

```
RENAME VIEW
```

It is not possible to change the stored query executed by the view. Instead, you must drop the existing view and create a new view.

### Replace a view

To replace a view, use <InternalLink path="create-view">`CREATE OR REPLACE VIEW`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE OR REPLACE VIEW quotes_count (season, quotes, stardate)
  AS SELECT episodes.season, count(*), episodes.stardate
  FROM quotes
  JOIN episodes
  ON quotes.episode = episodes.id
  GROUP BY episodes.season, episodes.stardate;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM quotes_count LIMIT 10;
```

```
  season | quotes | stardate
---------+--------+-----------
       1 |      5 |   1709.2
       1 |      7 |   2821.5
       1 |      2 |   3113.2
       1 |      6 |     3134
       1 |      3 |   2715.1
       1 |      7 |   3012.4
       1 |      2 |   3196.1
       2 |      4 |   3468.1
       2 |      1 |   3541.9
       2 |      5 |   4211.4
(10 rows)
```

### Remove a view

To remove a view, use the <InternalLink path="drop-view">`DROP VIEW`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP VIEW quotes_count;
```

```
DROP VIEW
```

## Materialized views

CockroachDB supports [materialized views](https://wikipedia.org/wiki/Materialized_view). A *materialized view* is a view that stores the results of its underlying query.

When you <InternalLink path="selection-queries">select</InternalLink> from a materialized view, the stored query data that is returned might be out-of-date. This contrasts with a standard (i.e., "dematerialized") view, which runs its underlying query every time it is used, returning the latest results. In order to get the latest results from a materialized view, you must <InternalLink path="refresh">refresh the view</InternalLink>, and then select from it.

Because materialized views store query results, they offer better performance than standard views, at the expense of the additional storage required to store query results and the guarantee that the results are up-to-date.

### Usage

Materialized views and standard views share similar syntax for <InternalLink path="create-view">creating</InternalLink>, <InternalLink path="show-tables">showing</InternalLink>, <InternalLink path="alter-view">renaming</InternalLink>, and <InternalLink path="drop-view">dropping</InternalLink>.

To create a materialized view, use a <InternalLink path="create-view">`CREATE MATERIALIZED VIEW`</InternalLink> statement.

For example, suppose that you have the <InternalLink path="cockroach-workload#bank-workload">sample `bank` database</InternalLink> loaded to a CockroachDB cluster, and populated with some workload values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE MATERIALIZED VIEW overdrawn_accounts
  AS SELECT id, balance
  FROM bank
  WHERE balance < 0;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id  | balance
------+----------
    1 |  -17643
    3 |   -5928
   13 |   -3700
...
(402 rows)
```

To show existing materialized views, use a <InternalLink path="show-tables">`SHOW TABLES`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES;
```

```
  schema_name |     table_name     |       type        | estimated_row_count
--------------+--------------------+-------------------+----------------------
  public      | bank               | table             |                1000
  public      | overdrawn_accounts | materialized view |                   0
(2 rows)
```

Now suppose you update the `balance` values of the `bank` table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPDATE bank SET balance = 0 WHERE balance < 0;
```

```
UPDATE 402
```

The changes can be seen in the table with a simple `SELECT` statement against the table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT id, balance
FROM bank
WHERE balance < 0;
```

```
  id | balance
-----+----------
(0 rows)
```

Recall that materialized views do not automatically update their stored results. Selecting from `overdrawn_accounts` returns stored results, which are outdated:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id  | balance
------+----------
    1 |  -17643
    3 |   -5928
   13 |   -3700
...
(402 rows)
```

To update the materialized view's results, use a <InternalLink path="refresh">`REFRESH`</InternalLink> statement:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> REFRESH MATERIALIZED VIEW overdrawn_accounts;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM overdrawn_accounts;
```

```
  id | balance
-----+----------
(0 rows)
```

You can also create or refresh materialized views using historical data with the <InternalLink path="as-of-system-time">`AS OF SYSTEM TIME`</InternalLink> clause. This is useful for reducing <InternalLink path="performance-best-practices-overview#transaction-contention">contention</InternalLink> by leveraging <InternalLink path="follower-reads">follower reads</InternalLink>.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE MATERIALIZED VIEW overdrawn_accounts
  AS SELECT id, balance
  FROM bank
  WHERE balance < 0
  AS OF SYSTEM TIME follower_read_timestamp();
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> REFRESH MATERIALIZED VIEW overdrawn_accounts
  AS OF SYSTEM TIME follower_read_timestamp();
```

For more information, see <InternalLink path="create-view#create-a-materialized-view-with-historical-data-using-as-of-system-time">`CREATE VIEW`</InternalLink> and <InternalLink path="refresh#refresh-a-materialized-view-with-historical-data-using-as-of-system-time">`REFRESH`</InternalLink>.

To rename the materialized view, use <InternalLink path="alter-view">`ALTER MATERIALIZED VIEW`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER MATERIALIZED VIEW overdrawn_accounts RENAME TO forgiven_accounts;
```

```
RENAME VIEW
```

To remove the materialized view, use <InternalLink path="drop-view">`DROP MATERIALIZED VIEW`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP MATERIALIZED VIEW forgiven_accounts;
```

```
DROP VIEW
```

### Add an index to a materialized view

To speed up queries on materialized views, you can add an <InternalLink path="schema-design-indexes">index</InternalLink> to the view.

1. Create a materialized view of the <InternalLink path="movr">MovR</InternalLink> rides table where the revenue is less than \$20.00:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE MATERIALIZED VIEW low_rev_rides AS SELECT city, vehicle_id, revenue from rides WHERE revenue < 20.00;
   ```

2. To see the plan for a select on this view, run:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   EXPLAIN SELECT vehicle_id, revenue FROM low_rev_rides WHERE city = 'seattle';
   ```

   ```
                                            info
   ---------------------------------------------------------------------------------------
     distribution: full
     vectorized: true

     • filter
     │ filter: city = 'seattle'
     │
     └── • scan
           missing stats
           table: low_rev_rides@low_rev_rides_pkey
           spans: FULL SCAN

     index recommendations: 1
     1. type: index creation
        SQL command: CREATE INDEX ON low_rev_rides (city) STORING (vehicle_id, revenue);
   (14 rows)
   ```

   Notice that there are [no statistics](#known-limitations) collected on the view.

3. Create an index on the `city` column:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   CREATE INDEX ON low_rev_rides (city) STORING (vehicle_id, revenue);
   ```

4. To see the change in the plan after adding the index, run:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   EXPLAIN SELECT vehicle_id, revenue FROM low_rev_rides@low_rev_rides_city_idx WHERE city = 'seattle';
   ```

   ```
                         info
   -------------------------------------------------
     distribution: local
     vectorized: true

     • scan
       missing stats
       table: low_rev_rides@low_rev_rides_city_idx
       spans: [/'seattle' - /'seattle']
   (7 rows)
   ```

### Known limitations

* The optimizer may not select the most optimal query plan when querying materialized views because CockroachDB does not <InternalLink path="cost-based-optimizer#table-statistics">collect statistics</InternalLink> on materialized views..

* CockroachDB cannot refresh  materialized views  <InternalLink path="views#materialized-views">materialized views</InternalLink>  inside <InternalLink path="begin-transaction">explicit transactions</InternalLink>. Trying to refresh a materialized view inside an explicit transaction will result in an error.
  1. Start <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> with the sample `bank` data set:

     ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     cockroach demo bank
     ```
  2. Create the materialized view described in <InternalLink path="views#usage">Usage</InternalLink>.
  3. Start a new multi-statement transaction with <InternalLink path="begin-transaction">`BEGIN TRANSACTION`</InternalLink>:

     ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     BEGIN TRANSACTION;
     ```
  4. Inside the open transaction, attempt to <InternalLink path="refresh">refresh the view</InternalLink>. This will result in an error.

     ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     REFRESH MATERIALIZED VIEW overdrawn_accounts;
     ```

     ```
     ERROR: cannot refresh view in an explicit transaction
     SQLSTATE: 25000
     ```

## Temporary views

CockroachDB supports session-scoped temporary views. Unlike persistent views, temporary views can only be accessed from the session in which they were created, and they are dropped at the end of the session. You can create temporary views on both persistent tables and <InternalLink path="temporary-tables">temporary tables</InternalLink>.

<Note>
  **This feature is in <InternalLink path="cockroachdb-feature-availability">preview</InternalLink>** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).
</Note>

<Note>
  Temporary tables must be enabled in order to use temporary views. By default, temporary tables are disabled in CockroachDB. To enable temporary tables, set the `experimental_enable_temp_tables` <InternalLink path="set-vars">session variable</InternalLink> to `on`.
</Note>

### Details

* Temporary views are automatically dropped at the end of the session.
* A temporary view can only be accessed from the session in which it was created.
* Temporary views persist across transactions in the same session.
* Temporary views cannot be converted to persistent views.
* Temporary views can be created on both persistent tables and <InternalLink path="temporary-tables">temporary tables</InternalLink>.
* When you create a view on a temporary table, the view automatically becomes temporary.

<Note>
  Like <InternalLink path="temporary-tables">temporary tables</InternalLink>, temporary views are not in the `public` schema. Instead, when you create the first temporary table, view, or sequence for a session, CockroachDB generates a single temporary schema (`pg_temp_<id`) for all of the temporary objects in the current session for a database.
</Note>

### Usage

To create a temporary view, add <InternalLink path="sql-grammar">`TEMP`/`TEMPORARY`</InternalLink> to a <InternalLink path="create-view">`CREATE VIEW`</InternalLink> statement.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TEMP VIEW temp_view (season, quotes)
  AS SELECT episodes.season, count(*)
  FROM quotes
  JOIN episodes
  ON quotes.episode = episodes.id
  GROUP BY episodes.season;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM temp_view;
```

```
  season | quotes
---------+---------
       1 |     78
       2 |     76
       3 |     46
(3 rows)
```

## See also

* <InternalLink path="selection-queries">Selection Queries</InternalLink>
* <InternalLink path="select-clause">Simple `SELECT` Clauses</InternalLink>
* <InternalLink path="create-view">`CREATE VIEW`</InternalLink>
* <InternalLink path="show-create">`SHOW CREATE`</InternalLink>
* <InternalLink path="grant">`GRANT`</InternalLink>
* <InternalLink path="alter-view">`ALTER VIEW`</InternalLink>
* <InternalLink path="drop-view">`DROP VIEW`</InternalLink>