How to Create View in PostgreSQL

In this PostgreSQL tutorial, I will show you how to create view in Postgres. Basically, you will learn “what is the view?” and the syntax to create the view and the execution process of the view.

Create View in Postgres Using Single Table

View in PostgreSQL is not a real table and is called a Virtual table, some database users think that view is a real table but that is not true. In reality, It is an SQL query stored as a Virtual table, you can derive or create the view from the base table (from the real table).

The syntax is given below.

CREATE VIEW view_name AS 
SELECT column_1, column_2,..
FROM base_table_name
WHERE condtions;

• CREATE VIEW: It is the clause or statement to create the view.
• view_name: This is the name of the view that will be created
• AS: This is used to specify the query, which means after this keyword SELECT statement is used to get the data from the base table that is shown by the view.

Let’s check with a simple example and see how to create a view in PostgreSQL.

Consider you have a table called ‘customers’ with columns ‘first_name’, ‘last_name’, ‘country’, ‘account_status’, and ‘purchase_history’.

Now you want to create the view named ‘customer_status’ that shows the account status of the customers who belong to the country USA. So use the below query.

CREATE VIEW customer_status AS
SELECT first_name, account_status, country
FROM customers
WHERE country = 'USA';

When you execute the above query, the new view is created with the name ‘customer_status’ that shows the information about the customer’s first name, account status, and country whenever this view is queried using the simple SELECT statement.

Now use the below query to show the data of the view ‘customer_status’.

SELECT * FROM customer_status;

As you can see, the view ‘customer_status’ shows the information about the 4 customers Olivia, David, Michael, and Robert. All the customer belongs to the country USA and two of them are active and others are inactive.

But what happens internally, when you show or get the data from the view using the SELECT statement? As you run the query something like this for view “SELECT * FROM customer_status”.

Here the view name ‘customer_status’ doesn’t contain the rows or columns from the ‘customers’ table that is specified after the AS keyword. But it contains the SQL query that is specified after the AS keyword.

So when you call the view, it first executes the SQL query within that view and fetches the result from the database and then this result is shown by that view.

Now you know about creating views and how it works internally.

Create View in Postgres using Multiple Tables

The view creation is not only limited to a single table, it can also be created using multiple tables. Sometimes you need information from the different tables in the database, instead of querying each table separately you can create a view that shows information from all the tables.

Let’s see how to create the view from the multiple tables.

Suppose you have two tables of books and authors. The table books with columns ‘title’, ‘author_id’, ‘publication_year’, and ‘category_id’. The authors table with columns ‘author_id’, ‘author_name’, and ‘books_written’.

You want to create a view named ‘author_books’ that shows the details about the book title, publication year, and the name of the author to which the book belongs. Use the below command to create the view ‘author_books’.

CREATE VIEW author_books AS
SELECT title, publication_year, author_name
FROM books
INNER JOIN authors
on books.author_id = authors.author_id;

After executing the above query, you can show the data of the view ‘author_books’ using the below query.

SELECT * FROM author_books;

In the above picture, you can see that the view ‘author_books’ shows the title and publication year of the books from the table ‘books’ and the author of the books from the table ‘authors’.

The execution process for the view that fetches information from multiple tables is the same as the one you have learned in the previous topic.

Create View in Postgres using pgAdmin

You can create the view using the pgAdmin of PostgreSQL which is called the graphical user interface.

Use the following steps to create the view from the pgAdmin.

First, open the pdAdmin and connect to the PostgreSQL server, after connecting to the server choose ‘Databases’ and select ‘Views’.

Right-click on the selected ‘Views’ and click on the option ‘Create’ and then click on the ‘View’ as shown in the below picture.

Then Create-View dialog opens for view creation. Then switch to the ‘General’ Section and specify the name of the view as shown in the below picture.

Then switch to the ‘Code’ tab, and write the SQL query (for the base table from which you want to get the data) that you use after the AS keyword in the previous topic. To create or save the view click on the ‘Save’ button.

Now go to the ‘Views’ section of ‘Browser’, and you see the newly created view named ‘author_books’.

Open the Query tool by right-clicking on the view and run the below query to show the data of the view ‘author_books’.

SELECT * FROM author_books;

From the above picture, you can see that the view ‘author_books’ contents about the book title, publication year, and the author of that book. Now you know how to create view in PostgreSQL using the pgAdmin of PostgreSQL.

Conclusion

In this PostgreSQL tutorial, you have learned how to create a view using single, multiple tables and pagAdmin. Also learned how to view fetch and show the information from the base tables, then learned view doesn’t store the rows and column data, instead it stored the SQL query.

You may like to read:

• PostgreSQL CASE Multiple Columns
• PostgreSQL WHERE IN Condition