# How to Create a View in PostgreSQL

> In Postgres, the CREATE VIEW statement defines a new view based on the selected table(s). To create a view from several tables, use the CREATE VIEW statement w…

In Postgres, a view is not a real table(not physically materialized) but a pseudo-table. However, it can be accessed as an ordinary/real table using a SELECT statement. In PostgreSQL, a VIEW can be defined/created based on single or multiple tables or from other views. For this purpose, the **CREATE VIEW** statement is used in Postgres.

This post demonstrates how to create a view in Postgres using suitable examples.

 **How to Create a View in Postgres?**

Use the below-provided syntax to create a new view in Postgres:
    
    
    CREATE[OR REPLACE] VIEW viewName AS
    SELECT col_list
    FROM tab_name
    [WHERE condition];

Let’s comprehend the above syntax line-by-line:

\- OR REPLACE is an optional clause/parameter that replaces the already existing view.  
\- Skipping the OR REPLACE parameter may cause an error if the view already exists.  
\- Specify the column names in place of the “col_list” parameter to add the columns of your choice to the view.  
\- tab_name represents a table based on which the view will be created.  
\- WHERE is an optional clause that specifies a particular condition(s).

Let’s put these concepts into practice.

 **Example 1: Creating a View**

The below snippet shows the content of the base table:

Suppose we want to create a view from the “staff_info” table. For this purpose, we will use the following statement:
    
    
    CREATE VIEW staff_view AS
    SELECT staff_id, staff_name, staff_designation
    FROM staff_info
    WHERE staff_id <= 5;

The “ **CREATE VIEW** ” message in the output window demonstrates that the “staff_view” has been created. Let’s verify it via the “SELECT *” command:
    
    
    SELECT * FROM staff_view;

 **Example 2: Creating an Already Existing View**

Trying to create an already existing view will throw an error, as shown in the following snippet:

To avoid this error, you need to execute the “CREATE VIEW” statement with the “OR REPLACE” parameter:
    
    
    CREATE OR REPLACE VIEW staff_view AS
    SELECT staff_id, staff_name, staff_designation
    FROM staff_info
    WHERE staff_id <= 8;

The output shows that this time the CREATE VIEW command executed successfully. To verify the view’s creation/modification, you must execute the following command:
    
    
    SELECT * FROM staff_view;

The “staff_view” with desired records has been created successfully.

 **Example 3: Creating/Defining a View From Multiple Postgres Tables**

We have two sample tables named “employee_info” and “department_info”. The below snippet demonstrates the content of the “emp_info” table:

The content of the “department_info” table is shown in the following snippet:

Let’s learn how to create a view from multiple tables:
    
    
    CREATE VIEW emp_data_view AS
    SELECT employee_info.e_id, e_name, dpt_name
    FROM employee_info
    INNER JOIN department_info
    ON employee_info.e_id = department_info.e_id;

In the above snippet, the INNER JOIN is used with the CREATE VIEW statement to create a view from multiple tables:

The “ **CREATE VIEW** ” message in the output window signifies that the desired view has been created from multiple tables. Let’s execute the “SELECT *” command to fetch the data from the “emp_view_data”:

This way, you can use the CREATE VIEW statement to create/define a view from one or more tables.

 **Conclusion**

In PostgreSQL, the **CREATE VIEW** statement defines a new view based on the selected table(s). To create a view from several tables, use the CREATE VIEW statement with INNER JOIN. Creating an existing view will throw a “relation already exists” error. To avoid such an error, use the “OR REPLACE” parameter with the CREATE VIEW statement. This Postgre blog presented different examples to illustrate how to create a view in Postgres.

---
[View this page online](https://www.commandprompt.com/education/how-to-create-a-view-in-postgresql/)