PostgreSQL ViewIn 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 viewA 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:
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 ViewsTo 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:
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(javatpoint). Once we execute the above command, we will be connected to the javatpoint 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 viewTo 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 viewTo 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 ViewsTo 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:
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. Next TopicPostgreSQL Join |