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:
In the above syntax, we have the following parameters:
| Parameters | Description |
|---|---|
| OR REPLACE | The OR REPLACE parameter will replace the view if it already exists. |
| The view name | It is the name of the view that we wanted to create. |
| WHERE conditions | These conditions are options, and they must be fulfilled for any values which have to be added in the view. |
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(tutoraspire).
Once we execute the above command, we will be connected to the tutoraspire 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:
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:
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.
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:
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:
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:
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:
The Book table is as below with the help of the Select command:
We can see the existing data available in the User1 table with the help of the Select command:
For example, in the below command, we will use the Create or Replace view command to update the view book_view2:
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:
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:
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:
In the above syntax, we have the following parameters:
| Parameters | Description |
|---|---|
| View-name | The view-name parameter is used to specify the name of the view, which we wanted to drop. |
| If Exists | It is an optional parameter, and it is only necessary when we don’t define it and try to drop a view which does not exist, we will get an error. |
Here we are dropping the book_info, which we have created in the above section by using the drop view command:
Output
After executing the above command, we will get the below message, which displays that the particular view has been dropped successfully.
