Postgresql - Views

View :
A view is a representation of a stored query in a database. In PostgreSQL, a view can be accessible as a virtual table. Put differently, a PostgreSQL view is a logical table that uses a SELECT statement to represent the data from one or more underlying tables.

Similar in function, CREATE OR REPLACE VIEW replaces an existing view with the same name if one already exists. The new query may add more columns to the end of the list, but it must provide the same columns that the previous view query produced—that is, the same column names in the same order and with the same data types.

Temporary View :
These are session based views. Consider we have two table as below

  create table company(id int, name varchar, place varchar);
  postgres=# Insert into company values(1,'babu','chennai');
  postgres=# Insert into company values(2,'pappu','avadi');
  postgres=# Insert into company values(3,'rosh','chepauk');
  postgres=# Insert into company values(4,'div','chepauk');
  postgres=# Insert into company values(5,'divya','chetpet');

  postgres=# select * from company;
  id | name  |  place   
  ----+-------+----------
    1 | babu  | chennai
    1 | pappu | mylapore 
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | divya | chetpet
  (6 rows)
  postgres=# create table emp(id int, name varchar,sal int);
  postgres=# Insert into emp values(1,'babu',2000)
  postgres=# Insert into emp values(2,'pappu',4000);
  postgres=# Insert into emp values(3,'rosh',5000);
  postgres=# Insert into emp values(4,'div',6000);
  postgres=# Insert into emp values(5,'divya',8000);

  postgres=# select * from emp;
  id | name  | sal  
  ----+-------+------
    1 | babu  | 2000
    2 | pappu | 4000
    3 | rosh  | 5000
    4 | div   | 6000
    5 | vidya | 8000

Creating temporary view :

  postgres=# create temporary view v1_tmp as select a.id, a.name, b.place from emp a inner join company b on a.id=b.id;
  CREATE VIEW
  postgres=# select * from v1_tmp;
  id | name  |  place   
  ----+-------+----------
    1 | babu  | chennai
    1 | babu  | mylapore
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | chetpet
  (6 rows)

List of views :

  postgres=# \dv
            List of relations
    Schema   |  Name  | Type |  Owner   
  -----------+--------+------+----------
  pg_temp_3 | v1_tmp | view | postgres

Updatable Views :
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. 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.

  postgres=# create view v1 as select a.id, a.name, b.place from emp a inner join company b on a.id=b.id;
  CREATE VIEW
  postgres=# \dv
            List of relations
    Schema   |  Name  | Type |  Owner   
  -----------+--------+------+----------
  public    | v1     | view | postgres
  (2 rows)

  postgres=# select * from v1;
  id | name  |  place   
  ----+-------+----------
    1 | babu  | chennai
    1 | babu  | mylapore
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | chetpet
  (6 rows)
  postgres=# select * from pg_views where schemaname='public';
  schemaname     | viewname      | viewowner            |  definition                
  ------------+----------+-----------+------------------------------------------
  public     | v1       | postgres  |  SELECT a.id, a.name,  b.place   FROM (emp a  JOIN company b ON                         
                                                        ((a.id = b.id)));

Update the table will automatically update view :

  postgres=# update company set place ='madurai' where id=1;
  UPDATE 2
  postgres=# select * from company;
  id | name  |  place  
  ----+-------+---------
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | divya | chetpet
    1 | babu  | madurai
    1 | pappu | madurai
  (6 rows)

  postgres=# select * from v1;
  id | name  |  place  
  ----+-------+---------
    1 | babu  | madurai
    1 | babu  | madurai
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | chetpet
  (6 rows)

Drop view :

  postgres=# drop view v1;
  DROP VIEW
  postgres=# select * from pg_views where schemaname='public';
  schemaname | viewname | viewowner | definition 
  ------------+----------+-----------+------------
  (0 rows)

Materialized Views :
Materialized views are disc-stored views that can be refreshed. It can be used like a regular table, for example, you can add indexes or primary key on it, it supports VACUUM and ANALYZE commands.

  postgres=# create materialized  view v1 as select a.id, a.name, b.place from emp a inner join company b on a.id=b.id;
  SELECT 6
  postgres=# select * from v1;
  id | name  |  place  
  ----+-------+---------
    1 | babu  | madurai
    1 | babu  | madurai
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | chetpet
  (6 rows)

  postgres=# 
  postgres=# select * from pg_matviews;
  schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |                definition                
  ------------+-------------+--------------+------------+------------+-------------+------------------------------------------
  public     | v1          | postgres     |            | f          | t           |  SELECT a.id,                           +
              |             |              |            |            |             |     a.name,                             +
              |             |              |            |            |             |     b.place                             +
              |             |              |            |            |             |    FROM (emp a                          +
              |             |              |            |            |             |      JOIN company b ON ((a.id = b.id)));

  postgres=# update company set place ='kollam' where id=5;
  UPDATE 1
  postgres=# select * from company;
  id | name  |  place  
  ----+-------+---------
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    1 | babu  | madurai
    1 | pappu | madurai
    5 | divya | kollam
  (6 rows)

  postgres=# select * from v1;
  id | name  |  place  
  ----+-------+---------
    1 | babu  | madurai
    1 | babu  | madurai
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | chetpet
  (6 rows)

  postgres=# refresh materialized view v1;
  REFRESH MATERIALIZED VIEW
  postgres=# select * from v1;
  id | name  |  place  
  ----+-------+---------
    1 | babu  | madurai
    1 | babu  | madurai
    2 | pappu | avadi
    3 | rosh  | chepauk
    4 | div   | chepauk
    5 | vidya | kollam
  (6 rows)
  postgres=# vacuum analyze v1;
  VACUUM
  postgres=# drop materialized view v1;
  DROP MATERIALIZED VIEW
  postgres=# \dm+
  Did not find any relations.
  postgres=# select * from pg_matviews;
  schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated | definition 
  ------------+-------------+--------------+------------+------------+-------------+------------
  (0 rows)

« Previous Next Topic » (Postgresql - Sequence)