How to Create View in PostgreSQL

In this PostgreSQL tutorial, I will show you how to create a view in Postgres. You will learn “what is the view?” and the syntax for creating the view and its execution process.

How to Create View in PostgreSQL

Before diving deep into the topic, let me understand what views are.

What Are PostgreSQL Views?

In PostgreSQL, a view is a named query stored in the database server. Think of a view as a virtual table that doesn’t store data itself but rather presents data stored in other tables.

Views offer several advantages:

• Simplification: Complex queries can be encapsulated into simple views
• Security: Access to sensitive data can be restricted by exposing only certain columns
• Consistency: Business logic can be centralized, ensuring consistent data presentation
• Performance: When used correctly, views can improve query performance

Now that we understand what views are, let’s learn how to create them.

Let us discuss all the approaches individually.

Approach 1: Using Single Table

A view in PostgreSQL is not a real table and is called a Virtual table. Some database users think a view is a real table, but that is untrue. 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).

Syntax

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 that after this keyword, the SELECT statement is used to get the data from the base table that is shown by the view.

Example

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 a view named ‘customer_status’ that shows the account status of customers in the USA. So use the query below.

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

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

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

SELECT * FROM customer_status;

As you can see, the view ‘customer_status’ shows information about the four customers: Olivia, David, Michael, and Robert. All four customers belong to the USA; two are active, and the 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 the view “SELECT * FROM customer_status”.

Here, the view name ‘customer_status’ doesn’t contain the rows or columns from the ‘customers’ table specified after the AS keyword, but it does contain the SQL query 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, which is then shown by that view.

Now you know about creating views and how they work internally.

Approach 2: Using Multiple Tables

View creation is not limited to a single table; it can also be created using multiple tables. Sometimes, you need information from different tables in the database. Instead of querying each table separately, you can create a view showing all the tables’ information.

Example

Let’s see how to create the view from 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 following 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.

Approach 3: Using pgAdmin

You can create the view using the pgAdmin of PostgreSQL, the graphical user interface.

To create View in PostgreSQL, follow the steps below.

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

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

3. The Create-View dialog opens for creating a view. Then switch to the ‘General’ Section and specify the view’s name, as shown in the picture below.

4. 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.

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

Right-click the view and open the Query tool. Run the query below to show the view ‘author_books’ data.

SELECT * FROM author_books;

The above picture shows that the view ‘author_books’ contains information about the book title, publication year, and author. Now you know how to create a view in PostgreSQL using pgAdmin.

Conclusion

This PostgreSQL tutorial taught you how to create a view using single and multiple tables and pgAdmin. You also learned how to fetch and show information from the base tables. Then, you realized that a view doesn’t store the rows and column data. Instead, it stores the SQL query.

You may like to read:

• PostgreSQL CASE Multiple Columns
• PostgreSQL WHERE IN Condition