ALTER VIEW
ALTER VIEW — Change the definition of a view
Synopsis
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 | CURRENT_USER | SESSION_USER }
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 [, ... ] )
Description
ALTER VIEW changes various auxiliary properties of a view (to modify the query definition of a view, use CREATE OR REPLACE VIEW).
To use ALTER VIEW, you must own the view. To change the schema of a view, you must also have the CREATE privilege on the new schema. To change the owner, you must also be a direct or indirect member of the new owning role, and that role must have the CREATE privilege on the view's schema (these restrictions enforce that changing the owner cannot do anything that could not be accomplished by dropping and recreating the view. However, a superuser can always change the ownership of any view.).
Parameters
name
The name of an existing view (can be schema-qualified).
column_name
The name of an existing column.
new_column_name
The new name of an existing column.
IF EXISTS
Do not throw an error if the view does not exist. A notice is issued in this case.
SET/DROP DEFAULT
These forms set or remove the default value for a column. For any INSERT or UPDATE command on the view, a view column's default value is substituted before any rules or triggers referencing the view are applied. Therefore, the view's default value takes precedence over any default values from the underlying relation.
new_owner
The user name of the new owner of the view.
new_name
The new name for the view.
new_schema
The new schema for the view.
SET ( view_option_name [= view_option_value] [, ... ] )
RESET ( view_option_name [, ... ] )
Set or reset a view option. Currently supported options are:
check_option (enum)
Changes the check option of the view. The value must be local or cascaded.
security_barrier (boolean)
Changes the security barrier attribute of the view. The value must be a boolean, such as true or false.
Notes
For historical reasons, ALTER TABLE can also be used with views, but only the variants of ALTER TABLE that are equivalent to the forms above are allowed with views.
Examples
# Rename view foo to bar:
ALTER VIEW foo RENAME TO bar;
# To attach a default column value to an updatable 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