Function

ALTER VIEW modifies all auxiliary attributes of a view. (To modify the query definition of a view, use CREATE OR REPLACE VIEW.)

Precautions

• Only the view owner can modify a view by running ALTER VIEW.
• To change a view's schema, you must also have the CREATE permission on the new schema.
• To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the view's schema.
• An administrator can change the owner relationship of any view.

Syntax

• Set the default value of the view column.

  1 2	ALTER VIEW [ IF EXISTS ] view_name ALTER [ COLUMN ] column_name SET DEFAULT expression;

• Remove the default value of the view column.

  1 2	ALTER VIEW [ IF EXISTS ] view_name ALTER [ COLUMN ] column_name DROP DEFAULT;

• Change the owner of a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name OWNER TO new_owner;

• Rename a view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name RENAME TO new_name;

• Set the schema of the view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name SET SCHEMA new_schema;

• Set the options of the view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name SET ( { view_option_name [ = view_option_value ] } [, ... ] );

• Reset the options of the view.

  1 2	ALTER VIEW [ IF EXISTS ] view_name RESET ( view_option_name [, ... ] );

Parameter Description

• IF EXISTS

  If this option is specified, no error is reported if the view does not exist. Only a message is displayed.

• view_name

  Specifies the view name, which can be schema-qualified.

  Value range: A string. It must comply with the naming convention rule.

• column_name

  Indicates an optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.

  Value range: A string. It must comply with the naming convention rule.

• SET/DROP DEFAULT

  Sets or deletes the default value of a column. Currently, this parameter does not take effect.

• new_owner

  Specifies the new owner of a view.

• new_name

  Specifies the new view name.

• new_schema

  Specifies the new schema of the view.

• view_option_name [ = view_option_value ]

  This clause specifies optional parameters for a view.

  Currently, the only parameter supported by view_option_name is security_barrier, which should be enabled when a view is intended to provide row-level security.

  Value range: Boolean type. It can be TRUE or FALSE.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- Create a view consisting of rows with c_customer_sk smaller than 150:
CREATE VIEW tpcds.customer_details_view_v1 AS
    SELECT * FROM tpcds.customer
    WHERE c_customer_sk < 150;

-- Rename the view:
ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Change the schema of the view:
ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete the view:
DROP VIEW public.customer_details_view_v2;

Helpful Links

CREATE VIEW, DROP VIEW