ALTER VIEW

On this page Carat arrow pointing down
Warning:
GA releases for CockroachDB v23.1 are no longer supported. Cockroach Labs will stop providing LTS Assistance Support for v23.1 LTS releases on November 13, 2025. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, refer to the Release Support Policy.

The ALTER VIEW statement applies a schema change to a view.

Note:

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

Required privileges

  • To alter a view, the user must have the CREATE privilege on the parent database.
  • To change the schema of a view with ALTER VIEW ... SET SCHEMA, or to change the name of a view with ALTER VIEW ... RENAME TO, the user must also have the DROP privilege on the view.

Syntax

ALTER MATERIALIZED VIEW IF EXISTS view_name RENAME TO view_new_name SET SCHEMA schema_name OWNER TO role_spec

Parameters

Parameter Description
MATERIALIZED Rename a materialized view.
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. 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.

Limitations

CockroachDB does not currently support:

  • Changing the SELECT 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 (see tracking issue).

Examples

Rename a view

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

icon/buttons/copy
CREATE VIEW money_rides (id, revenue) AS SELECT id, revenue FROM rides WHERE revenue > 50;
icon/buttons/copy
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)
icon/buttons/copy
ALTER VIEW money_rides RENAME TO expensive_rides;
RENAME VIEW
icon/buttons/copy
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 can be used to move a view from one database to another, but it cannot be used to move a view from one schema to another. To change a view's schema, use the SET SCHEMA clause. In a future release, RENAME TO will be limited to changing the name of a view, and will not have the ability to change a view's database.

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 created in the database belong to the public schema:

icon/buttons/copy
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:

icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the view's schema:

icon/buttons/copy
ALTER VIEW expensive_rides SET SCHEMA cockroach_labs;
icon/buttons/copy
SHOW CREATE public.expensive_rides;
ERROR: relation "public.expensive_rides" does not exist
SQLSTATE: 42P01
icon/buttons/copy
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)

See also


Yes No
On this page

Yes No