PostgreSQL Create View with Example

Details

What is PostgreSQL View?

In PostgreSQL, a view is a pseudo-table. This means that a view is not a real table. However, we can SELECT it as an ordinary table. A view can have all or some of the table columns. A view can also be a representation of more than one table.

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:

Creating PostgreSQL Views

To create a PostgreSQL view, we use the CREATE VIEW statement. Here is the syntax for this statement:

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

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: 

CREATE VIEW Price_View AS
  SELECT id, price
  FROM Price
  WHERE price > 200;

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: 

SELECT *
FROM Price_View;

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: 

CREATE VIEW Price_View2 AS
  SELECT price
  FROM Price
  WHERE price > 200;

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: 

SELECT *
FROM Price_View2;

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: 

CREATE or REPLACE VIEW Price_View2 AS
  SELECT price, name
  FROM Book
  INNER JOIN Price
  ON Book.id = Price.id
  WHERE price > 200;

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: 

DROP VIEW [IF EXISTS] view-name;

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: 

DROP VIEW Price_View2;

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)

  1. From the navigation bar on the left- Click Databases.
  2. Click Demo.

Step 3) Type the query in the query editor: 

CREATE VIEW Price_View AS
  SELECT id, price
  FROM Price
  WHERE price > 200;

Step 4) Click the Execute button.

Step 5) To view the contents of the view, do the following:

  1. Type the following command in the query editor:
    2. SELECT *
FROM Price_View;
  2. 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: 

CREATE VIEW Price_View2 AS
  SELECT price
  FROM Price
  WHERE price > 200;

Step 2) Click the Execute button.

Step 3) To see the contents of the view, do the following:

  1. Type the following query in the query editor:
    2. SELECT *
FROM Price_View2;
  2. 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)

  1. From the navigation bar on the left- Click Databases.
  2. Click Demo.

Step 3) Type the query in the query editor: 

CREATE or REPLACE VIEW Price_View2 AS
  SELECT price, name
  FROM Book
  INNER JOIN Price
  ON Book.id = Price.id
  WHERE price > 200;

Step 4) Click the Execute button.

Step 5) Type the following query in the query editor: 

SELECT * 
FROM Price_View2;

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)

  1. From the navigation bar on the left- Click Databases.
  2. Click Demo.

Step 3) Type the query in the query editor: 

DROP VIEW Price_View2;

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

 

YOU MIGHT LIKE: