Create table view, alter and drop view

PostreSQL tutorial – Create/Alter/Drop View

Create postgresql view

Create table view, alter and drop view. Syntax and examples.

Syntax

CREATE [OR REPLACE] 
[TEMP|TEMPORARY] 
VIEW view_name 
[(column_name [, ...])]
 [WITH(view_option_name 
 [= view_option_value] 
 [, ... ])]
 AS view_query;

Example

CREATE VIEW test_view 
AS SELECT * FROM customers;

Alter postgresql view

Syntax

ALTER VIEW [ IF EXISTS ] 
view_name RENAME TO new_view_name;

ALTER VIEW [ IF EXISTS ] 
view_name OWNER TO new_owner;

ALTER VIEW [ IF EXISTS ] 
view_name SET SCHEMA new_schema;

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

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

Example

ALTER VIEW test_view 
RENAME TO test_view2;

Drop postgresql view

Syntax

DROP VIEW [ IF EXISTS ] 
view_name [CASCADE|RESTRICT];

Example

DROP VIEW test_view;