CREATE VIEW ​

Create a new view or replace an existing view, based on the results of a specific query.

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
   AS query

OR REPLACE

Create a new view, or replace an existing view if a view with the same name exists in the specified (or current) schema and database.

Note: When you intend to replace a view, you cannot drop columns, change the order of columns, change the names of columns, or change the data types of columns. However, you can add new columns to the view.

TEMP | TEMPORARY

Create a view in a temporary schema and remove it when the current session ends.

VIEW name

Specify a view name, optionally qualified with a schema name. You cannot create views in remote databases.

column_name

Provide an optional list of column names for the view definition. By default, the view assumes the column names that derive from the query. Make sure the query for the view defines a set of uniquely named columns.

AS query

Define the view in terms of a valid query (a SELECT statement). The query cannot change the names or data types of columns in the source tables.

Yellowbrick views are persistent objects. They are not dependent on the tables that they reference; therefore, you can drop or rename a table and its associated views persist in the database. For example, you can create table team, then create view teamview by selecting from team. If you drop team with CASCADE, the view remains in the database. If table team is re-created, you can still select from teamview as long as the columns that originally existed in the table also exist in the new instance of the table and have the same data types.

Restriction: You cannot create views by selecting from external tables.

See also User-Defined Views.

Examples ​

For example, create a view in the premdb schema, based on a query against the match table in the public schema:

premdb=# create or replace view premdb.matchview as select * from public.match where seasonid between 5 and 15;
CREATE VIEW
premdb=# \d premdb.matchview
             View "premdb.matchview"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 ftscore  | character(3)                | 
 htscore  | character(3)                |

The following CREATE OR REPLACE VIEW statement fails because it attempts to change the column names of an existing view:

premdb=# create or replace view matchview(mv1,mv2,mv3,mv4,mv5,mv6) as 
select * from public.match where seasonid between 5 and 15;
ERROR:  cannot change name of view column "seasonid" to "mv1"

The following CREATE OR REPLACE VIEW statement fails because it attempts to change the data type of a column in an existing view:

premdb=# create or replace view matchv as select seasonid::int,matchday,htid,atid,ftscore,htscore from match;
ERROR:  cannot change data type of view column "seasonid" from smallint to integer

The following example demonstrates the case where you use CREATE OR REPLACE VIEW to add columns to an existing view. The following initial version of the view contains five columns. The last column is created by concatenating two columns from the match table, but those columns are not included in their original form.

premdb=# create view matchv as select seasonid, matchday, htid, atid, 
concat(ftscore, ' (HT:', htscore,')') score from match;
CREATE VIEW
premdb=# \d matchv
               View "public.matchv"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 score    | character varying(12)       |

If you later decide that you want to add columns to the view, such as ftscore and htscore in this case, you can use CREATE OR REPLACE VIEW and put them at the end of the original column list:

premdb=# create or replace view matchv as select seasonid, matchday, htid, atid, 
concat(ftscore, ' (HT:', htscore,')') score, ftscore, htscore from match;
CREATE VIEW
premdb=# \d matchv
               View "public.matchv"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 seasonid | smallint                    | 
 matchday | timestamp without time zone | 
 htid     | smallint                    | 
 atid     | smallint                    | 
 score    | character varying(12)       | 
 ftscore  | character(3)                | 
 htscore  | character(3)                |

However, note that you cannot rearrange the columns in the view:

premdb=# create or replace view matchv as select seasonid, matchday, htid, atid, ftscore, htscore, 
concat(ftscore, ' (HT:', htscore,')') score from match;
ERROR:  cannot change name of view column "score" to "ftscore"

To change column names, column order, or data types for columns, you have to drop the view, then create it again.

Parent topic:SQL Commands