This topic describes how to create a view.

Syntax

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ]
  AS query

Parameters

Parameter Description
name The name of a view to be created. The name can be schema-qualified.
column_name An optional list of names to be used for columns of the view. If not given, the column names are deduced from the query.
query A SELECT statement provides the columns and rows of the view.

Description

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced.

If a schema name is given by using the CREATE VIEW myschema.myview ... statement, the view is created in the specified schema. Otherwise, it is created in the current schema. The view name must be distinct from the name of any other view, table, sequence, or index in the same schema.

Note
  • Views are read-only. The system will not allow an insert, update, or delete operation on a view. You can get the effect of an updatable view by creating rules such as rewriting inserts on the view into appropriate actions on other tables. For information about the CREATE RULE statement, see the Postgres Plus documentation set.
  • Access to tables referenced in the view is determined by permissions of the view owner. However, functions called in the view are treated the same as if they had been called directly from the query using the view. Therefore, the user of a view must have permissions to call all functions used by the view.

Examples

Create a view consisting of all employees in department 30:

CREATE VIEW dept_30 AS SELECT * FROM emp WHERE deptno = 30;