Vertica Analytics Platform Version 9.2.x Documentation

CREATE VIEW

Defines a view. Views are read only, so they do not support insert, update, delete, or copy operations.

Syntax

CREATE [ OR REPLACE ] VIEW [[database.]schema.]view [ (column[,…]) ] 
  [ {INCLUDE|EXCLUDE} [SCHEMA] PRIVILEGES ] AS query

Parameters

OR REPLACE

Specifies to overwrite the existing view view‑name. If you omit this option and view‑name already exists, CREATE VIEW returns an error.

Any grants assigned to the view before you execute a CREATE OR REPLACE remain on the updated view. See GRANT (View).

[database]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

If you specify a database, it must be the current database.

view

Identifies the view to create, where view conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

column[,…] A list of names to use as view column names. Vertica maps view column names to query columns according to the order of their respective lists. By default, the view uses column names as they are specified in the query. Each view can contain up to 1600 columns.
query

A SELECT statement that the temporary view executes. The SELECT statement can reference tables, temporary tables, and other views.

{INCLUDE|EXCLUDE}[SCHEMA] PRIVILEGES

Specifies whether this view inherits schema privileges:

  • INCLUDE PRIVILEGES specifies that the view inherits privileges that are set on its schema. This is the default behavior if privileges inheritance is enabled for the schema.
  • EXCLUDE PRIVILEGES disables inheritance of privileges from the schema.

For details, see Inherited Privileges in the Administrator's Guide.

Privileges

See Creating Views

Examples

The following example shows how to create a view that contains data from multiple tables.

=> CREATE VIEW temp_t0 AS SELECT * from t0_p1 UNION ALL 
     SELECT * from t0_p2 UNION ALL 
       SELECT * from t0_p3 UNION ALL 
         SELECT * from t0_p4 UNION ALL 
           SELECT * from t0_p5;			

See Also