PostgreSQL View

In this section, we are going to understand the working of PostgreSQL Views, such as how to create PostgreSQL view, change the PostgreSQL view, alter the PostgreSQL view, and drop the PostgreSQL view.

Introduction of PostgreSQL view

A VIEW is a pseudo table in PostgreSQL; it is not a solid table but appears as an ordinary table to select. A view can also represent joined tables. It can contain all rows of a table or selected rows from one or more tables.

A View simplifies users to perform the following aspects:

• It structures data naturally and intuitively and makes it easy to find.
• We can authorize permission to users over a view, which has a complete record that the users are authorized to see.
• It restricts access to the data such that users can only see limited data instead of complete data.
• A view provides a dependable layer, even the columns of necessary table modification.
• It summarizes data from various tables to generate reports.
• A view helps us to describe the difficulty of a statement as we can write a command of view based on a complex query with the help of a SELECT command.

In PostgreSQL, we can perform all the activities of view in SQL shell(psql):

PostgreSQL view by using SQL shell (PSQL)

In this, we are going to create a view, change a view, and deleting a view by using SQL shell(psql). So, for this, we need to follow the below process:

Creating a PostgreSQL Views

To create a PostgreSQL view, we use the CREATE VIEW command.

Syntax

The syntax of Create view command is as follows:

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

In the above syntax, we have the following parameters:

To create a view in psql, we will follow the below steps:

Step 1

Firstly, we will open the psql in our local system, and we will provide the password and login to psql.

Step 2

Now, we will use the below command to select or connect the desired database(TheDeveloperBlog).

postgres-# \c TheDeveloperBlog  

Once we execute the above command, we will be connected to the TheDeveloperBlog database, as shown in the image below:

Step 3

We will be using the Book table, which we have created in the earlier section of the PostgreSQL tutorial.

To see which all columns are present in the Book table, we will use the select command as follows:

Select * public."Book";

Step 4

Now, we are ready to create a view with the help of the Book table by using the CREATE VIEW command as follows:

CREATE VIEW book_View AS SELECT BookID, Book_cost 
FROM public."Book" 
where Book_cost > '200'; 

Output

Once we implement the above command, we will get the below message that the book_view has been created successfully.

Step 5

After that, we will use the Select command to check the records in the book_view, whose value is higher than 200.

SELECT * FROM book_View;

Output

After executing the above command, we will get those records whose book_cost is more than 200.

While the Book table has four records, and here, we will get three records that were added to the particular view.

We are creating a view, which included only one column of the Book table with the help of below command:

CREATE VIEW Book_View2 AS 
SELECT book_cost 
FROM public."Book" 
WHERE book_cost > '225';

Output

After executing the above command, we will get the below message, that the book_ view2 has been created successfully.

The book_view2 contains only the Book_cost column from the Book table. Here we will use the Select command to see the data of the book_view2 view:

SELECT * FROM book_view2;

Output

After executing the above command, we will get those records whose cost is more than 225.

Changing a PostgreSQL view

To change the PostgreSQL view in psql, we will use the CREATE OR REPLACE VIEW command because the definition of a view can be modified without having to drop it.

Syntax of changing a PostgreSQL view

The syntax of changing a view in PostgreSQL is as follows:

CREATE OR REPLACE view_name 
AS 
query

Note: The previous version up to 9.4 of PostgreSQL does not support in deleting a remaining column in the view. If we need to perform it, we will get the following error message "[Err] ERROR: cannot drop columns from view". That's why the query must create similar columns that were created when the view was created.

In detail, the new columns require similar data types, similar names, and in a similar order as they were generated. But PostgreSQL permits us to add further columns at the end of the column list.

So, for this, we required three tables Book, User1, and book_view2 to change the PostgreSQL view.

We will use the SELECT command to check the records present in the Book, User1, and book_view2 tables.

The Book_view2 table is as below by using the select command:

Select * from book_view2;

The Book table is as below with the help of the Select command:

Select * from public."Book";

We can see the existing data available in the User1 table with the help of the Select command:

Select * from User1;

For example, in the below command, we will use the Create or Replace view command to update the view book_view2:

CREATE or REPLACE VIEW book_view2 AS
SELECT book_cost, user_name
FROM public."Book"
INNER JOIN User1
ON public."Book".UserID = User1.UserID
WHERE book_cost > '225';

Output

After executing the above command, we will get the below message window, which displays that the book_view2 has been created successfully.

Now, we will use the Select command to check whether the operation is working fine or not after executing the above command:

Select * from book_view2;

Output

Once we execute the above command, we will get the below result where we can see that the view has been changed as we used a JOIN command, and now we have two columns from two distinct tables.

Alter a PostgreSQL view

To change the definition of a view, we use the ALTER VIEW command.

For example, we can modify the name of the view from book_view2 to book_info by using the following statement:

ALTER VIEW book_view2 RENAME TO book_info;

Output

Once we execute the above command, we will get the below message window, which shows that the book_view2 has been altered successfully.

If we select the data from the book_view2, we will get the below error as we can see in the below screenshot:

Dropping a PostgreSQL Views

To delete a PostgreSQL view, we can use the DROP VIEW command.

Syntax

The syntax of the drop view command is as follows:

DROP VIEW [IF EXISTS] view-name;

In the above syntax, we have the following parameters:

Here we are dropping the book_info, which we have created in the above section by using the drop view command:

DROP VIEW book_info;

Output

After executing the above command, we will get the below message, which displays that the particular view has been dropped successfully.