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

# ALTER VIEW

The `ALTER VIEW` [statement](/docs/stable/sql-statements.md) applies a schema change to a [view](/docs/stable/views.md).

> [!NOTE]
>
> The `ALTER VIEW` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## Required privileges

* To set or reset `security_invoker`, the user must own the view.
* To change the schema of a view with `ALTER VIEW ... SET SCHEMA`, the user must have the `DROP` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the view and the `CREATE` privilege on the new schema.
* New in v26.3: To rename a view with `ALTER VIEW ... RENAME TO`, the user must have the `DROP` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the view and the `CREATE` privilege on the view's schema.

## Syntax

![alter\_view syntax diagram](https://mintcdn.com/cockroachlabs/7gzGESYW_aT0XysP/images/sql-diagrams/v26.3/alter_view.svg?fit=max\&auto=format\&n=7gzGESYW_aT0XysP\&q=85\&s=a5b82c55579fdb31b0bad80aafdfd21b)

## Parameters

| Parameter | Description |
| - | - |
| `MATERIALIZED` | Rename a [materialized view](/docs/stable/views.md#materialized-views). |
| `IF EXISTS` | Rename the view only if a view of `view_name` exists; if one does not exist, do not return an error. |
| `view_name` | The name of the view to rename. To find existing view names, use: `SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';` |
| `view_new_name` | The new name of the view. The name of the view must be unique to its database and follow these [identifier rules](/docs/stable/keywords-and-identifiers.md#identifiers). Name changes do not propagate to the table(s) using the view. |
| `schema_name` | The name of the new schema. |
| `role_spec` | The role to set as the owner of the view. |
| `security_invoker` | Sets whether to check privileges on the underlying tables as the querying user (`true`) or the view owner (`false`). `SET (security_invoker)` is equivalent to `SET (security_invoker = true)`. `RESET (security_invoker)` restores the default behavior, where a view checks privileges on the underlying tables as the view owner. This option applies only to non-materialized views. |

## Known limitations

`ALTER VIEW` does not currently support:

* Changing the [`SELECT`](/docs/stable/select-clause) statement executed by a view. Instead, you must drop the existing view and create a new view.
* Renaming a view that other views depend on. This feature may be added in the future.

## Examples

### Rename a view

Suppose you create a new view that you want to rename:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE VIEW money_rides (id, revenue) AS SELECT id, revenue FROM rides WHERE revenue > 50;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';
```

```
  schema_name | table_name  | type | owner | estimated_row_count | locality
--------------+-------------+------+-------+---------------------+-----------
  public      | money_rides | view | demo  |                   0 | NULL
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW money_rides RENAME TO expensive_rides;
```

```
RENAME VIEW
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
WITH x AS (SHOW TABLES) SELECT * FROM x WHERE type = 'view';
```

```
  schema_name |   table_name    | type | owner | estimated_row_count | locality
--------------+-----------------+------+-------+---------------------+-----------
  public      | expensive_rides | view | demo  |                   0 | NULL
(1 row)
```

Note that `RENAME TO` only changes the name of the view; it cannot move the view to a different database or schema. To change a view's schema, [use the `SET SCHEMA` clause](#change-the-schema-of-a-view).

### Change the schema of a view

Suppose you want to add the `expensive_rides` view to a schema called `cockroach_labs`:

By default, [unqualified views](/docs/stable/sql-name-resolution.md#lookup-with-unqualified-names) created in the database belong to the `public` schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE public.expensive_rides;
```

```
        table_name       |                                                 create_statement
-------------------------+-------------------------------------------------------------------------------------------------------------------
  public.expensive_rides | CREATE VIEW public.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)
```

If the new schema does not already exist, [create it](/docs/stable/create-schema.md):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA IF NOT EXISTS cockroach_labs;
```

Then, change the view's schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW expensive_rides SET SCHEMA cockroach_labs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE public.expensive_rides;
```

```
ERROR: relation "public.expensive_rides" does not exist
SQLSTATE: 42P01
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE cockroach_labs.expensive_rides;
```

```
            table_name           |                                                     create_statement
---------------------------------+---------------------------------------------------------------------------------------------------------------------------
  cockroach_labs.expensive_rides | CREATE VIEW cockroach_labs.expensive_rides (id, revenue) AS SELECT id, revenue FROM movr.public.rides WHERE revenue > 50
(1 row)
```

### Set or reset `security_invoker`

By default, a view checks privileges on the underlying tables as the view owner. To change the view to use the querying user's privileges instead, include the [`security_invoker` option](/docs/stable/views#use-invoker-privileges-for-underlying-data):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW user_accounts SET (security_invoker = true);
```

To restore the default behavior:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER VIEW user_accounts RESET (security_invoker);
```

## See also

* [Views](/docs/stable/views.md)
* [`CREATE VIEW`](/docs/stable/create-view)
* [`SHOW CREATE`](/docs/stable/show-create)
* [`DROP VIEW`](/docs/stable/drop-view)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)