How to Use the PostgreSQL Create View Statement

Introduction

In PostgreSQL, a view can be defined as a virtual table that represents a stored query. Using a view can help simplify complex queries, because users can query the view with a relatively simple SELECT statement. Views are also useful because they can combine and simplify multiple tables into one virtual table. In this article, we’ll show you how to use the PostgreSQL CREATE VIEW statement to create a new view.

Prerequisites

In order to get the most out of this tutorial, you’ll need to have some working knowledge of SQL commands. You’ll also need to have PostgreSQL installed on your machine. You can check your PostgreSQL client server version by using the command psql -V. This will confirm that Postgres is installed and working properly.

Access the psql command-line console

The psql interactive terminal can be used to execute SQL commands in PostgreSQL. To access the psql command-line interface, first use the following command to log in as the postgres superuser:

After entering the password for postgres. you can use the psql command to enter the interface.

The PostgreSQL CREATE VIEW statement

As we mentioned earlier, a VIEW is a virtual table in a PostgreSQL database that represents a stored object query. You can access data in a view using a SELECT statement and choosing what columns you want to display.

We use the CREATE VIEW statement to create a view in PostgreSQL.

The basic syntax for this statement is shown below:

Let’s discuss each component of this syntax:

• REPLACE – This optional syntax is used to replace the existing name of the view.

• TEMPORARY – This optional syntax is used to create a temporary view.

• SELECT – This statement represents the query on which the view is based.

PostgreSQL Create View Examples

Let’s look at an example of the PostgreSQL CREATE VIEW statement:

To display the created view, use the command SELECT * FROM example :

Create a Temporary View

In our next example, we’ll create a view that’s only temporary and will be removed when the session ends. You can use the following query to create a temporary view:

We can then use the SELECT statement again:

The output should look like this:

NOTE: If you end the session and start again, you’d get the following error when executing the CREATE VIEW TEMP on alphabet— ERROR: relation "alphabet" does not exist. This occurs because the relation is now removed on the database.

Use the PostgreSQL CREATE OR REPLACE VIEW

Let’s look at an example that uses the CREATE OR REPLACE VIEW statement:

The output from this example will look like this:

Create a PostgreSQL table

Views are created based on a query of one or more tables, so let’s review the process of creating a table in PostgreSQL.

The syntax for creating a table is shown below:

We can then add some records using the INSERT statement:

To view a list of all tables that are available in our database, use the command shown below:

Perform a PostgreSQL CREATE VIEW Example

Let’s check out another example that uses the CREATE VIEW statement:

We’ll use a SELECT statement to verify that our view is working correctly:

Here’s another example:

This time, the output will look like the following:

Conclusion

PostgreSQL views provide many key advantages over tables– they can represent just a subset of the total data contained in a table, they can join multiple tables to create a simpler virtual table, and they can simplify the complexity of your stored data. In this article, we showed you how to set up your own view using the PostgreSQL CREATE VIEW statement. With our step-by-step instructions, you’ll be able to create new views in your own PostgreSQL environment.