PostgreSQL Create View with Example

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.

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:

Creating PostgreSQL Views

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:

Creating PostgreSQL Views

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:

Creating PostgreSQL Views

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:

Changing PostgreSQL Views

The Book table is as follows:

Book:

Changing PostgreSQL Views

The Price table is as follows:

Price:

Changing PostgreSQL Views

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:

Changing PostgreSQL Views

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.

Creating PostgreSQL Views Using pgAdmin

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.

Creating PostgreSQL Views Using pgAdmin

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;
    
  3. Click the Execute button.

Creating PostgreSQL Views Using pgAdmin

This will return the following:

Creating PostgreSQL Views Using pgAdmin

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.

Creating PostgreSQL Views Using pgAdmin

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;
    
  3. Click the Execute button.

Creating PostgreSQL Views Using pgAdmin

This will return the following:

Creating PostgreSQL Views Using pgAdmin

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.

Changing PostgreSQL Views Using pgAdmin

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.

Changing PostgreSQL Views Using pgAdmin

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

SELECT * 
FROM Price_View2;

This will return the following:

Changing PostgreSQL Views Using pgAdmin

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.

Deleting PostgreSQL Views Using pgAdmin

Step 3) Type the query in the query editor:

DROP VIEW Price_View2;

Step 4) Click the Execute button.

Deleting PostgreSQL Views Using pgAdmin

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