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;
|
Nice but explain about materialized view also it’s possible
ReplyDeleteThank you for complement.
DeleteFollow the link for the materialized view: https://www.codefari.com/2020/04/postgresql-materialized-view.html
Follow the link for the materialized view
ReplyDelete