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 must use the same number of columns with the same names and the same data types.
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.
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.
Restriction: You cannot create views by selecting from external tables.

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

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