CREATE VIEW

The CREATE VIEW statement defines a new view in the catalog.

Examples

Create a simple view:

Query

CREATE VIEW view1 AS SELECT * FROM tbl;

Create a view or replace it if a view with that name already exists:

Query

CREATE OR REPLACE VIEW view1 AS SELECT 42;

Create a view and replace the column names:

Query

CREATE VIEW view1(a) AS SELECT 42;

The SQL query behind an existing view can be read from the PostgreSQL-compatible pg_views catalog:

Query

SELECT definitionFROM pg_viewsWHERE viewname = 'view1'  AND schemaname = 'public';

Result

 definition------------ SELECT 42;

Syntax

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 then the view is created in the specified schema. Otherwise, it is created in the current schema. Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. The name of the view must be distinct from the name of any other view or table in the same schema.