CREATE VIEW
CREATE VIEW — Define a new view
Synopsis
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name
[ ( column_name [, ...] ) ]
[ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
AS query
[ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
Description
CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.
CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must produce the same columns as the existing view query (i.e., the same column order, same column names, same data types), but it may add extra columns at the end of the list. The expressions producing the output columns can be entirely different.
If a schema name is given (for example, CREATE VIEW myschema.myview ...), the view is created in the specified schema. Otherwise, it is created in the current schema. Temporary views exist in a special schema, so a schema name cannot be given when creating a temporary view. The name of the view must be distinct from the name of any other view, table, sequence, index, or foreign table in the same schema.
Parameters
TEMPORARY or TEMP
If specified, the view is created as a temporary view. Temporary views are automatically dropped at the end of the current session. While a temporary view exists, existing permanent views with the same name are invisible to the current session unless referenced using a schema-qualified name.
If any of the tables referenced by the view are temporary, the view will be created as a temporary view (regardless of whether TEMPORARY is specified).
RECURSIVE
Creates a recursive view. The syntax
CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;
is equivalent to
CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names)
AS (SELECT ...) SELECT column_names FROM view_name;
A view column name list must be specified for a recursive view.
name
The name (optionally schema-qualified) of the view to be created.
column_name
An optional list of names to be used for the view columns. If not given, the column names are deduced from the query.
WITH ( view_option_name [= view_option_value] [, ... ] )
This clause specifies optional parameters for the view. The following parameters are supported:
check_option (enum)
This parameter can be local or cascaded, and it is equivalent to specifying WITH [ CASCADED | LOCAL ] CHECK OPTION (see below). This option can be modified on an existing view using ALTER VIEW.
security_barrier (boolean)
This parameter should be used if the view is intended to provide row-level security.
query
A SELECT or VALUES command that provides the rows and columns of the view.
WITH [ CASCADED | LOCAL ] CHECK OPTION
This option controls the behavior of automatically updatable views. When this option is specified, INSERT and UPDATE commands on the view will be checked to ensure that new rows satisfy the view's defining conditions (i.e., new rows will be checked to ensure they are visible through the view). If the new rows do not satisfy the conditions, the update will be rejected. If CHECK OPTION is not specified, INSERT and UPDATE commands on the view are allowed to create rows that are not visible through the view. The following check options are supported:
LOCAL
New rows are only checked against conditions defined directly on the view itself. Any conditions defined on underlying base views are not checked (unless they also specify CHECK OPTION).
CASCADED
New rows are checked against conditions on the view and all underlying base views. If CHECK OPTION is specified without specifying LOCAL or CASCADED, CASCADED is assumed.
CHECK OPTION should not be used with RECURSIVE views.
Note that CHECK OPTION is only supported on automatically updatable views that do not have INSTEAD OF triggers or INSTEAD rules. If an automatically updatable view is defined on top of a base view that has an INSTEAD OF trigger, LOCAL CHECK OPTION can be used to check the conditions on the automatically updatable view, but conditions on the base view with the INSTEAD OF trigger will not be checked (a cascaded check option will not cascade to a trigger-updatable view, and any check options defined directly on a trigger-updatable view will be ignored). If the view or any base relation has INSTEAD rules that cause INSERT or UPDATE commands to be rewritten, all check options will be ignored in the rewritten query, including any checks from automatically updatable views defined on top of relations with INSTEAD rules.
Notes
Use the DROP VIEW statement to remove a view.
Be careful about the names and types of view columns, which will be specified in the way you want. For example:
CREATE VIEW vista AS SELECT 'Hello World';
is bad practice, because the column name defaults to ?column? and the column data type defaults to text, which may not be what the user wants. A better style for a string in a view result is something like:
CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
Access to tables referenced in the view is determined by the permissions of the view's owner. In some cases, this can be used to provide secure but limited access to underlying tables. However, not all views are safe against tampering. Functions called in the view are treated the same way as if they were called directly in the query using the view. Therefore, a user of a view must have the privileges to call all functions used by the view.
When CREATE OR REPLACE VIEW is used on an existing view, only the view's defining SELECT rule is changed. Other view properties, including ownership, permissions, and non-SELECT rules, remain unchanged. To replace a view, you must own it (including being a member of the owning role).
Updatable Views
Simple views are automatically updatable: the system will allow INSERT, UPDATE, and DELETE statements to be used on such views in the same way as on regular tables. A view is automatically updatable if it satisfies the following conditions:
-
The view's FROM list contains exactly one item, which must be a table or another updatable view.
-
The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
-
The view definition must not contain set operations (UNION, INTERSECT, or EXCEPT) at the top level.
-
The view's select list must not contain any aggregates, window functions, or set-returning functions.
An automatically updatable view can mix updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column in the underlying base relation. Otherwise, the column is read-only, and an error will be raised if an INSERT or UPDATE statement attempts to assign a value to it.
If a view is automatically updatable, the system will convert any INSERT, UPDATE, or DELETE statement on the view into the corresponding statement on the underlying base relation. INSERT statements with an ON CONFLICT UPDATE clause are fully supported.
If an automatically updatable view contains a WHERE condition, that condition restricts which rows of the base relation can be modified by UPDATE and DELETE statements on the view. However, an UPDATE-allowed row may cause the row to no longer satisfy the WHERE condition, and thus no longer be visible through the view. Similarly, an INSERT command may insert a base relation row that does not satisfy the WHERE condition, and thus such rows will not be visible through the view (ON CONFLICT UPDATE may similarly affect existing rows that are not visible through the view). CHECK OPTION can be used to prevent INSERT and UPDATE commands from creating rows that cannot be seen through the view.
If an automatically updatable view is marked with the security_barrier attribute, all WHERE conditions of that attribute (and any conditions using operators marked as LEAKPROOF) will be evaluated before any conditions from the view user. Note that because of this, rows that will not ultimately be returned (because they fail the user's WHERE condition) may still end up being locked. You can use EXPLAIN to see which conditions are applied at the relation level (and therefore do not lock rows) and which are not. A more complex view that does not satisfy all these conditions is read-only by default: the system will not allow inserts, updates, or deletes on the view. You can achieve the effect of an updatable view by creating an INSTEAD OF trigger on the view, which must translate attempted inserts, etc., on the view into appropriate actions on other tables. For more information, see CREATE TRIGGER. Another possibility is to create rules (see CREATE RULE), but in practice triggers are easier to understand and use correctly.
Note that a user executing inserts, updates, or deletes on a view must have the corresponding insert, update, or delete privilege on the view. Additionally, the view's owner must have the relevant privileges on the underlying base relation, but the user performing the update does not need any permissions on the underlying base relation.
Examples
# Create a view consisting of all comedy films:
CREATE VIEW comedies AS
SELECT * FROM films
WHERE kind = 'Comedy';
# The created view contains the columns from the films table at the time of creation. Although * was used to create the view, columns added to the table later will not become part of the view.
# Create a view with LOCAL CHECK OPTION:
CREATE VIEW universal_comedies AS
SELECT * FROM comedies
WHERE classification = 'U'
WITH LOCAL CHECK OPTION;
# This creates a view based on the comedies view, showing only films with kind = 'Comedy' and classification = 'U'. Any INSERT or UPDATE attempt on this view will be rejected if the new row does not have classification = 'U', but the film's kind will not be checked.
# Create a view with CASCADED CHECK OPTION:
CREATE VIEW pg_comedies AS
SELECT * FROM comedies
WHERE classification = 'PG'
WITH CASCADED CHECK OPTION;
# This creates a view that checks both kind and classification of new rows.
# Create a view with a mix of updatable and non-updatable columns:
CREATE VIEW comedies AS
SELECT f.* country_code_to_name(f.country_code) AS country,
(SELECT avg(r.rating)
FROM user_ratings r
WHERE r.film_id = f.id) AS avg_rating
FROM films f
WHERE f.kind = 'Comedy';
# This view will support INSERT, UPDATE, and DELETE. All columns from the films table will be updatable, while the computed columns country and avg_rating will be read-only.
# Create a recursive view consisting of the numbers 1 through 100:
CREATE RECURSIVE VIEW public.nums_1_100 (n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM nums_1_100 WHERE n < 100;
# Note that in this CREATE, although the recursive view name is schema-qualified, its internal self-reference is not schema-qualified. This is because the name of the implicitly created CTE cannot be schema-qualified.