PostgreSQL: VIEW



In this article, we will learn about VIEW and how it can be managed in PostgreSQL. Firstly, we should know why we need a VIEW, while it just a group of SQL statements like function and procedure.

View in PostgreSQL


As per PostgreSQL documentation, A VIEW is a database object that is of a stored query. A view can be accessed as a virtual table in PostgreSQL. It means VIEWS is a pseudo-tables that represent the data/subset of selective columns of a table or more joined table through Select Statement;

The concept behind VIEWS is the security of sensitive data, If some tables have sensitive data so we cannot give them permission to access those tables publically, using VIEW we can give the permission specific rows or columns of a table to see.

For example, we have a customer table that has some sensitive information like password, Income, etc. I cannot give the permissions to access this table of our web developer, and then here the best option is VIEW.

Another thing is De-normalization, In relational databases, when we work on database design, the things that remain in your mind are data integrity and data consistency. And RDBMS provides lots of concepts to achieve the best database design, one of them is Normalization. There will a time comes when we work on the SQL statement and at that time we need to de-normalize data, then we have difficulty in writing the statement again and again, here we can avoid writing the SQL statement repeatedly by creating a VIEW.

Now, the conclusion of VIEW is described below.
  • A view helps simplify the complexity of a query because you can query a view, which is based on a complex query, using a simple SELECT statement.
  • Like a table, you can grant permission to users through a view that contains specific data that the users are authorized to see.
  • A view provides a consistent layer even the columns of underlying table changes. 

Create VIEW in PostgreSQL


Syntax:


CREATE VIEW view_name AS query;


view_name is the name of the view
query is the select SQL Statement like SELECT col1, col2.. From Table

Example of VIEW in PostgreSQL


Suppose we have table as below.

Employee table


CREATE TABLE public.employee
(
    empid serial NOT NULL,-- primary key
    fname text,
    lname text,
    username text,
    password text,
    contact text,
    salary numeric NOT NULL DEFAULT 0.0,
    doj date NOT NULL,
    managerid bigint,
    age integer,
    CONSTRAINT employee_primarykey PRIMARY KEY (empid),
    CONSTRAINT "uk_UserName" UNIQUE (username),
    CONSTRAINT chk_employee_age CHECK (age > 18)
);


Employee Address table


CREATE TABLE public.address
(
    id serial NOT NULL,
    empid integer NOT NULL,-- reference of employee
    address1 text,
    address2 text,
    city text,
    state text,
    country text,
    CONSTRAINT pk_address_id PRIMARY KEY (id)
);


I am creating customer VIEW as vw_customer


CREATE VIEW vw_customer AS
SELECT
  e.fname || ' ' || e.lname AS name,
  e.contact,
  a.city,
  a.state,
  a.country
FROM employee e
  INNER JOIN address a
  ON e.empid=a.empid;

 
Now you can fetch the customers needed records using VIEW.


SELECT * FROM vw_customer;


3 comments:

  1. Nice but explain about materialized view also it’s possible

    ReplyDelete
    Replies
    1. Thank you for complement.
      Follow the link for the materialized view: https://www.codefari.com/2020/04/postgresql-materialized-view.html

      Delete

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...