The tables are referred to as base tables. When creating a view, you just need to create a query then give it a name, making it a useful tool for wrapping complex and commonly used queries. In this PostgreSQL Tutorial, you will learn the following:
What is a Postgresql View?
Creating PostgreSQL Views
Changing PostgreSQL Views
Deleting PostgreSQL Views
Using pgAdmin
Creating PostgreSQL Views
To create a PostgreSQL view, we use the CREATE VIEW statement. Here is the syntax for this statement: The OR REPLACE parameter will replace the view if it already exists. If omitted and the view already exists, an error will be returned. The view-name parameter is the name of the view that you need to create. The WHERE condition(s) are options, and they must be satisfied for any record to be added to the view. Consider the Price table given below: Price:
Let us create a view from the above table: The above command will create a view based on the SELECT statement. Only the records where the price is greater than 200 will be added to the view. The view has been given the name Price_View. Let us query it to see its contents: This returns the following:
Even though the base table has 4 records, only 2 were added to the view. Here, we can add only one column to the view. Let us create a view that included only one column of the Price table: The view has been given the name Price_View2 and includes only the price column of the Price table. Let us query the view to see its contents: This returns the following:
Changing PostgreSQL Views
The definition of a view can be changed without having to drop it. This is done using the CREATE OR REPLACE VIEW statement. Let us demonstrate this by updating the view named Price_View2. Price_View2:
The Book table is as follows: Book:
The Price table is as follows: Price:
The following query will help us update the view Price_View2: Let us now query the view to see its contents:
The view has been changed, and now we have two columns from two different tables. This has been achieved using a JOIN statement.
Deleting PostgreSQL Views
Anytime you need to delete a PostgreSQL view. You can use the DROP VIEW statement. Here is the syntax for the statement: The parameter view-name is the name of the view that is to be deleted. In this syntax, IF EXISTS is optional. It is only required. If you don’t specify it and attempt to delete a view that does not exist, you will get an error. For example, to drop the view named Price_View2, we can run the following statement: The view will be deleted.
Using pgAdmin
Now let’s see how these actions can be performed using pgAdmin.
Creating PostgreSQL Views
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
Step 5) To view the contents of the view, do the following:
Type the following command in the query editor: SELECT * FROM Price_View;
Click the Execute button.
This will return the following:
To create the view Price_View2, do the following: Step 1) Type the following query in the query editor: Step 2) Click the Execute button.
Step 3) To see the contents of the view, do the following:
Type the following query in the query editor: SELECT * FROM Price_View2;
Click the Execute button.
This will return the following:
Changing PostgreSQL Views
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
Step 5) Type the following query in the query editor: This will return the following:
Deleting PostgreSQL Views
To accomplish the same through pgAdmin, do this: Step 1) Login to your pgAdmin account. Step 2)
From the navigation bar on the left- Click Databases. Click Demo.
Step 3) Type the query in the query editor: Step 4) Click the Execute button.
The view will be deleted.
Summary:
A PostgreSQL view is a pseudo-table, meaning that it is not a real table. A view can be create from one or more tables. The tables from which a view is created are known as base tables. To create a view, we use the CREATE OR REPLACE VIEW statement. To change the definition of a view, we use the CREATE OR REPLACE VIEW statement. To delete a view, we use the DROP VIEW statement.
Download the Database used in this Tutorial
