ALTER VIEW

Change the definition of a view.

Summary

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression

ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT

ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner

ALTER VIEW [ IF EXISTS ] name RENAME TO new_name

ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema

ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )

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

describe

ALTER VIEW Changes the various auxiliary properties of the view. (If you want to modify the view's definition query, use CREATE OR REPLACE VIEW.)

To execute this command, you must be the owner of the view. To change the schema of the view, you must also have CREATE privileges for the new schema. To change the owner, you must also be a direct or indirect member of the newly owned role, and that role must have CREATE privileges on the view's schema. These restrictions force change owners to do anything that cannot be done by deleting and recreating views. However, superusers can change ownership of any view.

Parameters

name

The name of the existing view (optional, schema-qualified).

IF EXISTS

If the view does not exist, no error is thrown, but a notification is issued.

SET/DROP DEFAULT

These tables set or delete the default values for columns. Replace the default value of the view column with any INSERT or UPDATE command targeting the view before applying any rules or triggers to the view. Therefore, the default value of the view will take precedence over any default value in the underlying relationship.

new_owner

New owner of the view.

new_name

The new name of the view.

new_schema

New architecture of the view.

SET ( view_option_name [= view_option_value] [, ... ] )

RESET ( view_option_name [, ... ] )

Set or reset view options. The currently supported options are:
- check_option (string): Change the check options for the view. The value must be local or cascaded.
- security_barrier (boolean): Changes the security barrier properties of the view. The value must be a Boolean value, such as true or false.

Notice

ALTER TABLE can also be used with views for historical reasons; however, the only variant of ALTER TABLE allowed by the view is equivalent to the statement shown above.

Rename the view myview to newview: Rename the view myview to newview:

ALTER VIEW myview RENAME TO newview;

Example

To rename view foo to bar:

ALTER VIEW foo RENAME TO bar;

To append the default column value to the updateable view:

CREATE TABLE base_table (id int, ts timestamptz);
CREATE VIEW a_view AS SELECT * FROM base_table;
ALTER VIEW a_view ALTER COLUMN ts SET DEFAULT now();
INSERT INTO base_table(id) VALUES(1);  -- ts will receive a NULL
INSERT INTO a_view(id) VALUES(2);  -- ts will receive the current time

compatibility

ALTER VIEW is a SQL standard YMatrix database extension.