PostgreSQL Views

PostgreSQL Views

With the increase in the size of the database, the number of tables in the database increases up to a great extent and it becomes a very tedious task for the database administrator or the user to retrieve data from the various number of tables present inside the database in an efficient and effective manner. So, for the applications today that we have in our day to day life, it becomes very necessary to have an efficient way to retrieve this vast amount of data from the tables very rapidly.

In the PostgreSQL database, the solution for this problem is PostgreSQL Views. PostgreSQL Views are the named queries that help us to represent data in another form. As told with the bigger databases, the complexity of the queries also increases and it becomes very hard for the programmer or the database administrator to form such complex queries without any mistake. Here the programmer or the database administrator can make use of PostgreSQL views that help them to store complex queries under a named view so that whenever that complex query is needed next time, they can easily use that view instead of running that complex query, this saves their time and helps them to retrieve the data in an efficient manner.

PostgreSQL Views are also known as pseudo tables because they store the result in the table and that result obtains can be used later. We can represent a subset of a real table according to the need of our query with the help of views. The subset that we want to represent depends on the query and we can filter the data with the help of PostgreSQL conditions and PostgreSQL clauses that we morally use in our regular queries.

Depending upon the written PostgreSQL query, a view can be created from one or many tables and that created view can be used for further computation. We can also create views on the joined tables. Views are also known as virtual tables.

Views allow users to do the following tasks ?

• Helps user or classes of users to find a natural or an intuitive way to structure the data.

• PostgreSQL views can be used to provide abstraction in the database tables as it will give access to only the specified data of that table only. We can say that it shows limited data instead of the whole table to be displayed.

• In the case of complex queries, PostgreSQL Views summarize data from various tables that we can use to generate reports for a better understanding of the data present in the database.

How to Create a PostgreSQL View?

We can create PostgreSQL Views in two ways, one by using PostgreSQL CLI, i.e., psql or PostgreSQL Management tool pgAdmin4.

Let us see the creation of PostgreSQL View with the use of PostgreSQL CLI, psql.

We have created an employee table with 3 columns name, age, and salary. And we have inserted 4 rows of data in the employee table for the understanding purpose.

The syntax for a PostgreSQL View creation is:

 CREATE [OR REPLACE] VIEW view-name AS 
  SELECT column(s) 
  FROM table(s) 
  [WHERE condition(s)];  

 As shown in the image, we have created a view named ‘view_age_filter’ which select only those rows from the employee table where the age of the employee is greater than 21 and in the next query, we have used this newly created View to display only those rows of the employee whose salary is greater than 60 and there is already a filter for the age column in the created view that salary should be greater than 21.

How to Alter a PostgreSQL View?

We change the name of the already created view in the PostgreSQL using the alter view command having the syntax as:

ALTER VIEW book_view2 RENAME TO book_info;

As shown in the image with the help of the view alter command, we have renamed the previously created view view_age_filter with the new name age_filter_employee. And in the second command, we have used that renamed view to display the rows of the table employee that are present in that view.

How to Drop a PostgreSQL View?

To delete or drop a PostgreSQL View use the syntax:

DROP VIEW [IF EXISTS] view-name; 

As shown in the image we have deleted the previously created View and in the second command, we tried to use that deleted view and got errors; so it shows that we have successfully deleted the View.

So, with the help of this article, we are able to understand the usage and working of PostgreSQL views.