PostgreSQL Views and Materilized Views

-

View:

                  A view is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. In other words, a PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.
                 CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

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.

Examples:

pearl=# select a.id,a.name,b.place from emp a inner join company b on a.id=b.id;
 id |   name   |  place  
----+----------+---------
  1 | muthu    | theni
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | tvm
(6 rows)

Create Temporary View:

pearl=# 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
pearl=# \dv+
                      List of relations
  Schema   |  Name  | Type |  Owner   |  Size   | Description 
-----------+--------+------+----------+---------+-------------
 pg_temp_4 | v1_tmp | view | postgres | 0 bytes | 
(1 row)

pearl=# \c pearl
You are now connected to database "pearl" as user "postgres".
pearl=# \dv+
Did not find any relations.

Create View:

pearl=# 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
pearl=# select * from v1;
 id |   name   |  place  
----+----------+---------
  1 | muthu    | theni
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | tvm
(6 rows)

List of Views:

pearl=# \dv+
                    List of relations
 Schema | Name | Type |  Owner   |  Size   | Description 
--------+------+------+----------+---------+-------------
 public | v1   | view | postgres | 0 bytes | 
(1 row)

pearl=# 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)));
(1 row)

Updatable Views:

pearl=# update company SET place ='madurai' where id=1;
UPDATE 1

pearl=# select * from v1;
 id |   name   |  place  
----+----------+---------
  1 | muthu    | madurai
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | tvm
(6 rows)
pearl=# create or replace view v1 as select * from emp;
ERROR:  cannot change name of view column "place" to "age"

Drop Views:

pearl=# drop view v1 ;
DROP VIEW

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  NALYZEcommands

Examples:

Create Materialized View:

pearl=# create materialized view mv as select a.id,a.name,b.place from emp a inner join company b on a.id=b.id;
SELECT 6

List Of Materialized Views:

pearl=# \dm+
                         List of relations
 Schema | Name |       Type        |  Owner   | Size  | Description 
--------+------+-------------------+----------+-------+-------------
 public | mv   | materialized view | postgres | 16 kB | 
(1 row)
pearl=# select * from pg_matviews ;
 schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated |                definition                
------------+-------------+--------------+------------+------------+-------------+------------------------------------------
 public     | mv          | postgres     |            | f          | t           |  SELECT a.id,                           +
            |             |              |            |            |             |     a.name,                             +
            |             |              |            |            |             |     b.place                             +
            |             |              |            |            |             |    FROM (emp a                          +
            |             |              |            |            |             |      JOIN company b ON ((a.id = b.id)));
(1 row)
pearl=# select * from mv;
 id |   name   |  place  
----+----------+---------
  1 | muthu    | madurai
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | tvm
(6 rows)

pearl=# update company set place ='thiruvannamalai' where id=6;
UPDATE 1
pearl=# select * from mv;
 id |   name   |  place  
----+----------+---------
  1 | muthu    | madurai
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | tvm
(6 rows)

Refresh Materialized Views:

pearl=# refresh materialized view mv ;
REFRESH MATERIALIZED VIEW
pearl=# select * from mv;
 id |   name   |      place      
----+----------+-----------------
  1 | muthu    | madurai
  2 | siva     | theni
  3 | chandru  | madurai
  4 | thanigai | chennai
  5 | kathir   | covai
  6 | jana     | thiruvannamalai
(6 rows)

Vacuum Materialized View:

pearl=# vacuum ANALYZE mv;
VACUUM

Drop Materialized View:

pearl=# drop materialized view mv;
DROP MATERIALIZED VIEW

Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportionally to the number of PostgreSQ
Read more

PostgreSQL Migration Using MTK

-
Enterprisedb Migration Toolkit: EDB Migration Toolkit is a powerful command-line tool that offers granular control of the migration process. Migration Toolkit facilitates migration of database objects and data to an EDB Postgres Advanced Server (Advanced Server) or PostgreSQL database from: Oracle MySQL Microsoft SQL Server Sybase which objects migrate to postgres Oracle Objects: Schemas   Tables    List-Partitioned Tables         Range-Partitioned Table         Constraints   Indexes   Triggers         Table Data   Views     Materialized Views         Packages         Procedures   Functions       Sequences       Users/Roles   Profiles   Object Types   Object Type Methods   Database Links     Queues       MySQL Objects: Schemas Tables Constraints Indexes Table Data Microsoft SQL Server Objects: Schemas Tables Constraints Indexes Table Data Sybase Obj
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index, as well as the free space ma
Read more