CREATE VIEW

CREATE VIEW creates a new view in an existing database. A view is a virtual table.

Tip

• Use views to simplify complex queries or present only partial data to specific roles.
• If an underlying table has changed (new columns, changed names, etc.) - a view may be invalidated. To recompile the view, see SELECT RECOMPILE_VIEW(<view name>)

Permissions

The role must have the CREATE permission at the database level, as well as SELECT permissions for any tables referenced by the view.

Syntax

create_view_statement ::=
    CREATE VIEW [schema_name].view_name [ column_list ]
    AS
    query
    ;

schema_name ::= identifier

view_name ::= identifier

column_list ::= ( { column_name [, ...] } )

column_name ::= identifier

Parameters

Parameter	Description
schema_name	The name of the schema in which to create the view.
view_name	The name of the view to create, which must be unique inside the schema.
column_list	An optional comma separated list of column names for the view. If specified, these column names will override the column names in the response of the query in the AS query statement.
AS query	The select query to execute when the view is referenced.

Examples

A simple view

CREATE VIEW only_agressive_animals AS
  SELECT * FROM cool_animals WHERE is_agressive=true;

SELECT * FROM only_agressive_animals;

Overriding default column names

CREATE VIEW only_relaxed_animals (animal_id, animal_name, should_i_worry) AS
  SELECT id, name, is_agressive FROM cool_animals WHERE is_agressive=false;