Drop View If Exists postgres

As the name suggests, a view can be a glance at a database record from a different site. A view in PostgreSQL is a subset of one or more tables to see the important data. We mostly create views to execute the most-used query in the database. Those, who don’t have any understanding of Database views, will get some knowledge today. Therefore, we will be discussing a method to create, use and drop the views in the PostgreSQL database.

Using PgAdmin 4 GUI:

Let’s start with the opening of PostgreSQL PgAdmin Graphical User Interface from the search bar. Write “pgadmin” and tap on the application that appeared on the screen. Add your server and database password in the dialog that appeared on your pgAdmin screen. Now, expand the database section and tap on the specific database. Tap on the query tool icon. To create and drop a view, we must have some table and data in it within the database. Therefore, using the “SELECT” instruction within the query tool, we have displayed table “Brand” data as below. This table contains 4 columns of id, text, and an array of integer and text types.

Let’s create a new view with the help of a query tool again. The CREATE VIEW instruction will be utilized for this purpose. You have to name the view anything you want, i.e. we are naming it “brand_view” as shown below. The view must contain some query to make transactions from the database. Therefore, we have been utilizing the SELECT instruction to fetch all the records from the table “Brand” only where the index 2 of column “Brand” is empty, i.e. no values. The instruction used for this purpose has been shown in the attached screenshot. You have to execute it with the small triangle run button from the pgAdmin taskbar or tool area. The success message displayed in the output area is proof that the view has been created.

Let’s just create another view, “price_view” for the table “Brand” using the CREATE VIEW command utilizing the “SELECT” instruction to fetch records. This time, this view will be used to fetch all the records from the table “Brand” where the index 3 of the Brand column is NULL. This query has also been successful after executing the “run” button.

Moving on further, let’s see if the views we have created work or not. Therefore, SELECT instruction will be cast off for this need using the name of a view, i.e. “brand_view”. The output shows 0 records because there are no records in “Brand[2]” that are empty. Although, there are records in the brand column where the whole array is empty.

Let’s see the “price_view” now. Use the same SELECT instruction to fetch the records from the table “Brand” using the “price_view” where the 3^rd index of the brand column is NULL. This query is returning us a total of 4 records out of 5. All the resulted records are NULL in index 3 of column “brand”. So, this was all about the illustration to create views and fetch records through them. Let’s move further to see how this newly made view will work.

Let’s just delete or Drop the view “brand” from our system. Therefore, we have been using the DROP VIEW command along with the name of a view, i.e. “brand”. We have encountered an error on execution saying that this view is not found so far. This means we are using the wrong name for the view. This is clear that we will encounter an error upon using the wrong name for any view. Let’s correct our name-mistake to avoid this error by an update.

Now, we will be using the correct name of the view to drop it, i.e. “brand_view”. Thus the DROP VIEW instruction utilizes the correct view name to drop it. The execution has been prosperous, and the view is finally deleted from our system and cannot be used further.

To avoid the error when executing the DROP VIEW command and using the wrong name for a view, we must use the condition “IF EXISTS” in it. It will not let our system throw an exception. Therefore, we used the wrong view name again to test this “IF EXISTS” condition in the DROP VIEW instruction. On execution of the command, we have got a notice that this view does not exist, and the system is skipping the process of dropping. The query was successful.

Let’s use the same DROP VIEW command with IF EXISTS condition to drop a correct name view, i.e. “price_view” as below. The dropping of view this time will be fruitful.

Using PostgreSQL Shell:

Let’s use the postgresql shell to create and drop views. Open PostgreSQL shell on your system and add your database name, port number, username, and password to use it.

Firstly, we will see a table “Ftest” all records in our database “aqsayasin” using the SELECT instruction. You can see it has a total of 3 columns. We will use the Number column in our newly added view.

You have to use the CREATE VIEW keyword to create a new view named “ftest_view” on our “aqsayasin” database using the “SELECT” instruction to fetch records from the “ftest” table. It is not only fetching all records from the “Ftest” table but also displays the “floor” of a whole column “Number” from the same table to display. The view is now created and ready to use.

On using the Ftest_view in the SELECT instruction, we have got all the records from the table “Ftest”. Here comes another column, “floor” using the “Ftest_view”. In the SELECT instruction showing the floor of values in the column “Number” of the table “Ftest”.

Using the DROP VIEW command in the PostgreSQL Shell, let’s drop this view. The IF EXISTS condition will be applied to this query to avoid any errors. On executing this DROP VIEW query for the “Ftest_view” view, we got successful, and the view is gone.

Let’s use the already dropped view to select the data from the “Ftest” table. Using this SELECT instruction leads us to an error, i.e. “View doesn’t exist”.

Let’s create another view named “fview” to select the column “Number” from the “Ftest” table after being multiplied by 12. Thus, CREATE VIEW command has been here again.

On utilizing the “SELECT” query to fetch the records by “fview” view, it returns the below-shown output. The column displays the values of column “Number” multiplied by 12 from the table “Ftest” as beneath.

Let’s just drop this newly made view “Ftest” using the DROP VIEW command along with the IF EXISTS command.

Here, it is gone already.

Conclusion:

The discussion was about creating views and dropping them from the database permanently to have a different look at your database in 2 seconds. This whole discussion covers the creation and deletion of views on PostgreSQL Shell and pgAdmin. All the queries specified in this guide can be utilized in any other SQL database version. Feel free and easy to use these queries anywhere to learn more and more.