Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

CREATE VIEW

Creates a view in a database. The view is not physically materialized; the query that defines the view is run every time the view is referenced in a query. You can't create a view with an external table.

Syntax

Copy
CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

Parameters

OR REPLACE

If a view of the same name already exists, the view is replaced. You can only replace a view with a new query that generates the identical set of columns, using the same column names and data types. CREATE OR REPLACE VIEW locks the view for reads and writes until the operation completes.

name

The name of the view. If a schema name is given (such as myschema.myview) the view is created using the specified schema. Otherwise, the view is created in the current schema. The view name must be different from the name of any other view or table in the same schema.

If you specify a view name that begins with '# ', the view will be created as a temporary view that is visible only in the current session.

For more information about valid names, see Names and Identifiers. You cannot create tables or views in the system databases template0, template1, and padb_harvest.

column_name

Optional list of names to be used for the columns in the view. If no column names are given, the column names are derived from the query. The maximum number of columns you can define in a single view is 1,600.

query

A query (in the form of a SELECT statement) that evaluates to a table. This table defines the columns and rows in the view.

Note

You cannot update, insert into, or delete from a view.

Usage Notes

Having ownership of a view, or having privileges granted on a view, does not imply access to the underlying tables. You need to grant access to the underlying tables explicitly.

Examples

The following command creates a view called myevent from a table called EVENT:

Copy
create view myevent as select eventname from event where eventname = 'LeAnn Rimes';

The following command creates a view called myuser from a table called USERS:

Copy
create view myuser as select lastname from users;