ALTER VIEW

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 owner of a view can use ALTER VIEW.
• To change the schema of a view, you must have the CREATE permission on the new schema.
• To change the owner of a view, you must be a direct or indirect member of the new owning role, and the member must have the CREATE permission on the view's schema.
• An administrator can change the owner relationship of any view.

Syntax

• Set the default value of a view column.

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

• Remove the default value of a view column.

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

• Change the owner of a view.

  ALTER VIEW [ IF EXISTS ] view_name 
      OWNER TO new_owner;
  

• Rename a view.

  ALTER VIEW [ IF EXISTS ] view_name 
      RENAME TO new_name;
  

• Set the schema of a view.

  ALTER VIEW [ IF EXISTS ] view_name 
      SET SCHEMA new_schema;
  

• Set the options of a view.

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

• Reset the options of a view.

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

Parameter Description

• IF EXISTS

  If this option is used, no error is generated when the view does not exist, and 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

  Specifies 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. 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 ]

  Specifies an optional parameter for a view.

  Currently, view_option_name supports only the security_barrier parameter. This parameter is used when the view attempts to provide row-level security.

  Value range: Boolean type, TRUE, and FALSE.

Examples

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

-- Rename a view.
postgres=# ALTER VIEW tpcds.customer_details_view_v1 RENAME TO customer_details_view_v2;

-- Change the schema of a view.
postgres=# ALTER VIEW tpcds.customer_details_view_v2 SET schema public;

-- Delete a view.
postgres=# DROP VIEW public.customer_details_view_v2;

Helpful Links

CREATE VIEW and DROP VIEW