CREATE VIEW

Define a new view.

Table of contents

Synopsis

CREATE [ OR REPLACE ] VIEW view_ident AS query

Or

CREATE [ OR REPLACE ] VIEW view_ident AS (query)

Where query is a SELECT statement.

Description

CREATE VIEW creates a named definition of a query. This name can be used in other statements instead of a table name to reference the saved query definition. A view is not materialized, instead the query is run every time a view is referenced in a query.

If OR REPLACE is used, an already existing view with the same name will be replaced.

If a schema name is given in the view_ident (some_schema.view_name), the view will be created in the specified schema.

Table and view names must be unique within a schema. A view cannot have the name of an already existing table.

Views are read-only. They cannot be used as a target relation in write operations.

Note

If a * is used to select the columns within the views query definition, this * will be resolved at query time like the rest of the query definition. This means if columns are added to the table after the view had been created, these columns will show up in subsequent queries on the view. It is generally recommended to avoid using * in view definitions.

Note

If an object column is selected, sub-columns are visible in the information_schema.columns table. Added sub-columns at the source relation are visible there while dropped columns won’t show up there anymore as well. This applies both to views where * is used to select columns and to views with a static selection of top level columns.

Privileges

Regular users need to have DDL permissions on the schema in which the view is being created. In addition the user creating the view requires DQL permissions on all relations that occur within the views query definition.