CREATE VIEW

Description

Creates a view. A view is a virtual table, not a base table. The database stores only the view definition, not the view data itself. Instead, the view data is stored in the base table. If data in the base table changes, the data queried from the view changes accordingly. In this sense, a view is like a window through which users can know their interested data and data changes in the database.

• You can define frequently used data as views to encapsulate complex query SQL statements. This simplifies operations.
• You can query only data defined in a view, which is secure. Columns in a base table are hidden to protect the data structure of the database.
• User permission management is simplified. Users are granted only the permission to use views.

Precautions

Users with the CREATE ANY TABLE permission can create views in the public and user schemas.

Syntax

CREATE [ OR REPLACE ] [ TEMPORARY ] VIEW view_name [ ( column_name [, ...] ) ]
    [ WITH ( {view_option_name [= view_option_value]} [, ... ] ) ]
    AS query;

You can use WITH(security_barrier) to create a relatively secure view. This prevents attackers from printing hidden base table data by using the RAISE statement of low-cost functions.

After a view is created, you are not allowed to use REPLACE to modify column names in the view or delete the columns.

Calculation operations (such as the function calls and operator calculation) are not allowed in a query for fields of the following data types:

• BINARY[(n)]
• BOOLEAN/BOOL
• VARBINARY(n)
• CHAR[(n)]
• VARCHAR(n)
• TIME[(p)]
• DATETIME[(p)]
• TIMESTAMP[(p)]
• BIT[(n)]
• NUMERIC[(p[,s])]
• DECIMAL[(p[,s])]
• DEC[(p[,s])]
• FIXED[(p[,s])]
• FLOAT4[(p, s)]
• FLOAT8[(p,s)]
• FLOAT[(p)]
• REAL[(p, s)]
• FLOAT[(p, s)]
• DOUBLE[(p,s)]
• DOUBLE PRECISION[(p,s)]
• TEXT
• TINYTEXT
• MEDIUMTEXT
• LONGTEXT
• BLOB
• TINYBLOB
• MEDIUMBLOB
• LONGBLOB

The concepts involved in the constraints on updating views and subqueries, and inserting and deleting views are described as follows:

• Join view: view created with multiple tables using JOIN.
• Key-preserved table: Insertion, update, and deletion of multi-table join views are restricted by key-preserved tables. In a multi-table view, if each row in the source table corresponds to each row in the view, and a row in the source table does not correspond to multiple rows in the view after the JOIN, the source table is a key-preserved table.
• Relationship between the top layer and the bottom layer: A view may have multiple nested layers. For example, a view consists of one or more views or subqueries. The view that is directly operated by the DML is called the top layer. The tables and views that form the view and the tables and views in the WITH clause are called the corresponding bottom layer.
• Updatable columns: user columns that are directly referenced from the base table. System columns and whole-row references are not considered updatable.
• Updatable views: views that support insert, update, and delete operations. To be updatable, views must not contain the DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clause, set operations (UNION and EXCEPT), aggregate functions, window functions, or return set functions (such as array_agg, json_agg, and generate_series).

Parameters

• OR REPLACE

  (Optional) Redefines the view if it already exists.

• TEMPORARY

  (Optional) Creates a temporary view. The view is automatically deleted when the current session ends. If any table referenced by a view is a temporary table, the view is created as a temporary view (regardless of whether TEMPORARY is specified in the SQL statement).

• view_name

  Specifies the name of the view to be created. It is optionally schema-qualified.

  Value range: character strings complying with Identifier Description.

• column_name

  Specifies a list of names to be used as the column names of the view. If not specified, the column names will be automatically derived from the query.

  Value range: character strings complying with Identifier Description.

• view_option_name [= view_option_value]

  Specifies an optional parameter for a view.

  Currently, view_option_name supports only the security_barrier parameter. This parameter is used when the view attempts to provide row-level security.

  Value range: true and false (Boolean).

• query

  Specifies a SELECT or VALUES statement that will provide the columns and rows of the view.

  

  If query contains a clause specifying the partition of a partitioned table, the OID of the specified partition is fixed to the system catalog when the view is created. If a DDL operation that will change the OID of the specified partition is used, for example, DROP, SPLIT, or MERGE, the view is unavailable. In this case, you need to create a view.

Example

• Common view:

  -- Create the test_tb1 table and insert 100 data records into the table.
  m_db=# CREATE TABLE test_tb1(col1 int, col2 int);
  m_db=# INSERT INTO test_tb1 VALUES (generate_series(1,100),generate_series(1,100));
  -- Create a view whose value of col1 is less than 5.
  m_db=# CREATE VIEW test_v1 AS SELECT * FROM test_tb1 WHERE col1 < 3;
  -- Query a view.
  m_db=# SELECT * FROM test_v1;
   col1 | col2 
  ------+------
      1 |    1
      2 |    2
  (2 rows)
  
  -- Delete the table and the view.
  m_db=# DROP VIEW test_v1;
  m_db=# DROP TABLE test_tb1;

• Temporary view:

  -- Create a table and a temporary view.
  m_db=# CREATE TABLE test_tb2(c1 int, c2 int);
  m_db=# CREATE TEMPORARY VIEW test_v2 AS SELECT * FROM test_tb2;
  -- Query the table and view information. (The temporary table belongs to a schema starting with pg_temp instead of public.)
  m_db=# \d
                                               List of relations
            Schema          |   Name   | Type  | Owner |                       Storage                        
  --------------------------+----------+-------+-------+------------------------------------------------------
   pg_temp_dn_6001_1_1_9473 | test_v2  | view  | omm   | 
   public                   | test_tb2 | table | omm   | {orientation=row,compression=no,storage_type=USTORE}
  (2 rows)
  
  -- Exit the current session and log in again. Check whether the temporary view is deleted.
  m_db=# \d
                                      List of relations
   Schema |   Name   | Type  | Owner |                       Storage                        
  --------+----------+-------+-------+------------------------------------------------------
   public | test_tb2 | table | omm   | {orientation=row,compression=no,storage_type=USTORE}
  (1 row)
  
  -- Delete the view and the table.
  m_db=# DROP VIEW test_v2 ;
  m_db=# DROP TABLE test_tb2 CASCADE;

Helpful Links

ALTER VIEW and DROP VIEW