Creating Views

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.

Views can be created using CREATE VIEW command. Views can also be created using CREATE OR REPLACE VIEW command which overwrites the view of the name if it exists already and if the new view definition generates the same columns in the same order but the new view can also have additional columns at the end.

Using * for the columns list for the view creation will add only current columns of the table to the view and future columns of the table will not be part of the view.

Using TEMPORARY or TEMP option creates a temporary view that gets automatically dropped at the end of the current session. Also, if any of the tables referenced by the view are temporary, the view is created as a temporary view (whether TEMPORARY is specified or not).

Creating a simple view

# CREATE VIEW city_high as
    SELECT * from city WHERE pop > 10000;

# CREATE INDEX idx_city_high_name ON city(name);

# \dv

# \dv city_high

# \d+ city_high

appdb=> CREATE VIEW city_high as
appdb->     SELECT * from city WHERE pop > 10000;
CREATE VIEW
appdb=> CREATE INDEX idx_city_high_name ON city(name);
CREATE INDEX
appdb=> \dv
         List of relations
 Schema |   Name    | Type | Owner
--------+-----------+------+-------
 public | city_high | view | manoj
(1 row)

appdb=> \dv city_high
         List of relations
 Schema |   Name    | Type | Owner
--------+-----------+------+-------
 public | city_high | view | manoj
(1 row)

appdb=> \d+ city_high
                           View "public.city_high"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description
---------+---------+-----------+----------+---------+----------+-------------
 city_id | integer |           |          |         | plain    |
 name    | text    |           |          |         | extended |
 pop     | integer |           |          |         | plain    |
View definition:
 SELECT city.city_id,
    city.name,
    city.pop
   FROM city
  WHERE city.pop > 10000;

appdb=>

Creating an updatable view with checks

Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. In effect, the data of the underlying table is impacted. A view is automatically updatable if it satisfies all of the following conditions:

• The view must have exactly one entry in its FROM list, 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.

If we don’t want the views to be updatable, then views can be created with some checks.

Let’s insert data into the view created earlier. Here, it will allow inserting any data into the view and the data gets inserted into the underlying table. But, selecting the data from the view will return only those rows which are satisfied by the view definition.

Into a simple view, any rows can be INSERTed. However, only the rows satisfied by the view definition will be UPDATEd and DELETEd. If not satisfied, the operation will not have any impact on the table of the data and will not give any error either.

# INSERT INTO city_high VALUES(101, 'Blore', 100);

# INSERT INTO city_high VALUES(102, 'Mum', 1000001);

# SELECT * from city;

# SELECT * FROM city_high;

appdb=> INSERT INTO city_high VALUES(101, 'Blore', 100);
INSERT 0 1
appdb=> INSERT INTO city_high VALUES(102, 'Mum', 1000001);
INSERT 0 1
appdb=> select * from city;
 city_id | name  |   pop
---------+-------+---------
     101 | Blore |     100
     102 | Mum   | 1000001
(2 rows)

appdb=> select * from city_high;
 city_id | name |   pop
---------+------+---------
     102 | Mum  | 1000001
(1 row)

appdb=>

Let’s create another view with LOCAL CHECK OPTION so that only the rows satisfying the current view definition can be inserted or updated.

# CREATE VIEW city_high_check as
    SELECT * from city WHERE pop > 10000
    WITH LOCAL CHECK OPTION;

# \d+ city_high_check

# INSERT INTO city_high_check VALUES(103, 'Blore', 200);

# INSERT INTO city_high_check VALUES(104, 'Mum', 1000002);

# SELECT * from city;

# SELECT * FROM city_high_check;

appdb=> CREATE VIEW city_high_check as
appdb->     SELECT * from city WHERE pop > 10000
appdb->     WITH LOCAL CHECK OPTION;
CREATE VIEW
appdb=> \d+ city_high_check
                        View "public.city_high_check"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description
---------+---------+-----------+----------+---------+----------+-------------
 city_id | integer |           |          |         | plain    |
 name    | text    |           |          |         | extended |
 pop     | integer |           |          |         | plain    |
View definition:
 SELECT city.city_id,
    city.name,
    city.pop
   FROM city
  WHERE city.pop > 10000;
Options: check_option=local

appdb=> INSERT INTO city_high_check VALUES(103, 'Blore', 200);
ERROR:  new row violates check option for view "city_high_check"
DETAIL:  Failing row contains (103, Blore, 200).
appdb=> INSERT INTO city_high_check VALUES(104, 'Mum', 1000002);
INSERT 0 1
appdb=> SELECT * from city;
 city_id | name  |   pop
---------+-------+---------
     101 | Blore |     100
     104 | Mum   | 1000002
     102 | Mum   | 1000001
(3 rows)

appdb=> SELECT * from city_high;
 city_id | name |   pop
---------+------+---------
     104 | Mum  | 1000002
     102 | Mum  | 1000001
(2 rows)

appdb=>

Let’s create another view with CASACADED CHECK OPTION so that only the rows satisfying the current view definition and all underlying base views definitions can be inserted or updated.

# CREATE VIEW city_high_check_new as
    SELECT * from city_high_check WHERE pop < 20000
    WITH LOCAL CHECK OPTION;

# \d+ city_high_check_new

# INSERT INTO city_high_check_new VALUES(105, 'Chen', 200);

# INSERT INTO city_high_check_new VALUES(106, 'Del', 11000);

# INSERT INTO city_high_check_new VALUES(107, 'Kan', 21000);

# SELECT * from city;

# SELECT * FROM city_high_check_new;

appdb=> CREATE VIEW city_high_check_new as
appdb->     SELECT * from city_high_check WHERE pop < 20000
appdb->     WITH LOCAL CHECK OPTION;
CREATE VIEW
appdb=> \d+ city_high_check_new
                      View "public.city_high_check_new"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Description
---------+---------+-----------+----------+---------+----------+-------------
 city_id | integer |           |          |         | plain    |
 name    | text    |           |          |         | extended |
 pop     | integer |           |          |         | plain    |
View definition:
 SELECT city_high_check.city_id,
    city_high_check.name,
    city_high_check.pop
   FROM city_high_check
  WHERE city_high_check.pop < 20000;
Options: check_option=local

appdb=> INSERT INTO city_high_check_new VALUES(105, 'Chen', 200);
ERROR:  new row violates check option for view "city_high_check"
DETAIL:  Failing row contains (105, Chen, 200).
appdb=> INSERT INTO city_high_check_new VALUES(106, 'Del', 11000);
INSERT 0 1
appdb=> INSERT INTO city_high_check_new VALUES(107, 'Kan', 21000);
ERROR:  new row violates check option for view "city_high_check_new"
DETAIL:  Failing row contains (107, Kan, 21000).
appdb=> SELECT * from city;
 city_id | name  |   pop
---------+-------+---------
     101 | Blore |     100
     104 | Mum   | 1000002
     102 | Mum   | 1000001
     106 | Del   |   11000
(4 rows)

appdb=> SELECT * FROM city_high_check_new;
 city_id | name |  pop
---------+------+-------
     106 | Del  | 11000
(1 row)

appdb=>

Creating a recursive view

# CREATE RECURSIVE VIEW num_view (n) AS
    VALUES (1)
UNION ALL
    SELECT n+1 FROM num_view WHERE n < 10;

# \d+ NUM_VIEW

# SELECT * FROM NUM_VIEW;

appdb=> CREATE RECURSIVE VIEW num_view (n) AS
appdb->     VALUES (1)
appdb-> UNION ALL
appdb->     SELECT n+1 FROM num_view WHERE n < 10;
CREATE VIEW
appdb=> \d+ num_view
                          View "public.num_view"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 n      | integer |           |          |         | plain   |
View definition:
 WITH RECURSIVE num_view(n) AS (
         VALUES (1)
        UNION ALL
         SELECT num_view_1.n + 1
           FROM num_view num_view_1
          WHERE num_view_1.n < 10
        )
 SELECT num_view.n
   FROM num_view;

appdb=> SELECT * from NUM_VIEW;
 n
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

appdb=>