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.