Practical PostgreSQL
PrevNext

CREATE VIEW

Name

CREATE VIEW  --  Creates a new view on a table.

Synopsis

CREATE VIEW view AS SELECT query

Parameters

view

Use this parameter to provide the name of the new view.

query

Use this parameter to supply a SQL query designed to provide the columns and rows of the view.

Results

CREATE

This message is displayed if the view is successfully created.

ERROR: Relation 'view' already exists

This error is displayed if a view with the name you supplied already exists.

NOTICE create: attribute named "column" has an unknown type

This error is displayed if you did not specify a column type in the view definition.

Description

Use CREATE VIEW to define a new table view within the current database. A query rewrite retrieve rule is generated to support the use of row retrieval with the new view.

NoteRead-only
 

Views are read-only as of PostgreSQL 7.1.x (the most current version as of the writing of this book).

Examples

The following example demonstrates the creation of a view of all publishers whose IDs within the table are above 100. 

CREATE VIEW high_id AS
    SELECT *
    FROM publishers
    WHERE id *gt; 100;

PrevHomeNext
CREATE USERUpcreatedb